Code not Working

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Code not Working

Post by burrina »

I get no error but it does not work, I did have it working but did something, don't know what!

Code: Select all

DoCmd.RunSQL "UPDATE tblReportList SET tblReportList.rptAccess = 0 " & _
         "WHERE (((tblReportList.userID)='" & Me.byUserID.Value & Me!lstemployees.ItemsSelected & "','""')"

'lstemployees is a listbox that contains a list of all reports.
'byUserID  is the matching user name in tblReportList

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code not Working

Post by HansV »

What exactly do you want to specify in the WHERE-condition?
Is lstemployees a multi-select list box?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

Yes, it is set to Simple. I am selecting a Report from that listbox. Updated code, no errors but does not work.

Code: Select all

 Dim db   As DAO.Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Dim strSQL As String
    
    
        'Update Users Report Access Status. User can NOT Access Report(s).
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblReportList SET tblReportList.rptAccess = 0 " & _
          "WHERE (((tblReportList.userID)='" & Me.lstemployees.Value & "'))"
       Call MsgBox("This User does NOT have Access to the Selected Report(s).", vbExclamation, "No Report Access")
    DoCmd.SetWarnings True

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code not Working

Post by HansV »

You have to handle a multi-select list box differently. I'll get back to you.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code not Working

Post by HansV »

Don't you have to specify report names or report IDs somewhere in there?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

I thought referring to the .Value would do that, guess not!

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

Code: Select all

DoCmd.RunSQL "UPDATE tblReportList SET tblReportList.rptAccess= 0 & '" & " Me.rptlists.Column(0) " & _
          "WHERE (((tblReportList.userID)='" & Me.lstemployees.Column(0) & "'))"
But no luck?

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

Code: Select all

 DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblReportList SET tblReportList.rptAccess = 0 " & _
          "WHERE (((tblReportList.userID)='" & Me.byUserID.Value & "'))"
This works if I dont use lstemployees but instead use an unbound combobox which is NOT what I want.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code not Working

Post by HansV »

I'm still confused. Is lstemployees really a list box with report names?
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

Sorry, my pc was getting an update, everything got real slow.
Old version working Hans, just new version is not. Sorry, I was working on may versions at the same time.
Apologies.
This OLD code works.

Code: Select all

 DoCmd.RunSQL "UPDATE tblUserSecurity_Sec SET tblUserSecurity_Sec.rptac = 1 " & _
            "WHERE (((tblUserSecurity_Sec.userID)='" & Me.byUserID.Value & "'))"

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code not Working

Post by HansV »

Does this work?

Code: Select all

    Dim strIn As String
    Dim strSQL As String
    Dim v As Variant
    
    'Update Users Report Access Status. User can NOT Access Report(s).
    If Me.lstemployees.ItemsSelected.Count = 0 Then
        MsgBox "No reports selected!"
        Exit Sub
    End If
    For Each v In Me.lstemployees.ItemsSelected
        strIn = strIn & ",'" & Me.lstemployees.ItemData(v) & "'"
    Next v
    strIn = "(" & Mid(strIn, 2) & ")"
    strSQL = "UPDATE tblReportList SET rptAccess = 0 WHERE UserID In " & strIn
    CurrentDb.Execute strSQL, dbFailOnError
Best wishes,
Hans

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Code not Working

Post by burrina »

I will check it out Hans. My brain is fried at the moment.
Many Thanks,