Code not Working

[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

[HansV]

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

[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

[HansV]

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

[HansV]

Don't you have to specify report names or report IDs somewhere in there?

[burrina]

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

[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?

[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.

[HansV]

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

[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 & "'))"

[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

[burrina]

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