Error 3085 Undefined function 'lstPrintCheckedStaff.Column'' in expression

[William Hamilton]

Hi all,

Hoping someone might be able to point me in the right direction with getting the code below to work properly.
Effectively, the code:

• Requerys the listbox in lstPrintCheckedStaff
• Selects all the items rows in lstPrintCheckedStaff

Then for each row in the list box:

• Calls a msgbox to confirm if an e-mail should be sent to that person. (Yes / No)
• If yes, sends an e-mail and records that in the table AuditLog

The problem I have is with the very last bit - recording to the Audit Log and specifically the Name (obtained from lstPrintCheckedStaff.Column(1) and lstPrintCheckedStaff.Column(3). When running the code it works fine (and sends the e-mail), but presents me with error 3085 "Undefined function 'lstPrintCheckedStaff.Column' in expression" every time. I've tried loads of different ways I can think of (including "Dim"ing it first, but it jsut requests the parameter value then...

Any suggestions/assistance greatly apprecicated!
Cheers,
Will

Private Sub cmdIssueEmailNotifications_Click()
On Error GoTo ErrorHandler
 
    Forms!frmEditPrePayment!lstPrintCheckedStaff.Requery
    Call ListBoxSelectAll(Forms!frmEditPrePayment!lstPrintCheckedStaff)
 
    Dim varItemCheckedStaff As Variant
    Dim varItemCheckedStaffName As Variant
    Dim varItemCheckedStaffUsername As Variant
 
    For Each varItemCheckedStaff In Me.lstPrintCheckedStaff.ItemsSelected
        varItemCheckedStaffName = Me.lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & " " & Me.lstPrintCheckedStaff.Column(2, varItemCheckedStaff)
 
        Dim mbxResponse As VbMsgBoxResult
        mbxResponse = MsgBox( _
        "Do you want to send an e-mail to " & varItemCheckedStaffName & "?", _
        vbQuestion + vbYesNo, TSM_APPLICATION_STANDARDCAPTION & "Send an e-mail...")
 
        If mbxResponse = vbYes Then
 
            Dim oApp As Object, oAcct As Object
            Dim oMessages As Object, oMessage As Object
            Dim ToYou As String, TheSubject As String
 
            varItemCheckedStaffUsername = Me.lstPrintCheckedStaff.Column(4, varItemCheckedStaff)
 
            ToYou = varItemCheckedStaffUsername    'Issue mailbox
            TheSubject = "A QMS Feedback Sheet has been sent to your DIP tray"
 
            Set oApp = CreateObject("NovellGroupWareSession")
            Set oAcct = oApp.login("", "")
            Set oMessages = oAcct.mailbox.messages
            Set oMessage = oMessages.Add()
            oMessage.Priority = 2
            oMessage.Recipients.Add ToYou
            oMessage.Subject = TheSubject
            oMessage.Send
 
            Dim strAuditLogUser As String
                strAuditLogUser = txtUserID.Value
            Dim strAuditLogComputer As String
                strAuditLogComputer = txtComputerID.Value
            Dim strEntryID As String
                strEntryID = txtEntryID.Value
            Dim strAuditLogForm As String
                strAuditLogForm = Form.Name
            Dim strAuditLogVBA As String
                strAuditLogVBA = "cmdIssueEmailNotifications_Click"
            Dim strAuditLogAction As String
                strAuditLogAction = "Issued e-mail notification to " & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & " " & lstPrintCheckedStaff.Column(2, varItemCheckedStaff)
 
            DoCmd.SetWarnings (False)
            Rem CurrentDb.Execute ("INSERT INTO AuditLog ([EntryID], [AuditLogUser], [AuditLogComputer], [AuditLogForm], [AuditLogVBA], [AuditLogAction])VALUES (strEntryID, strAuditLogUser, strAuditLogComputer, strAuditLogForm, strAuditLogVBA, strAuditLogAction)")
            DoCmd.RunSQL ("INSERT INTO AuditLog ([EntryID], [AuditLogUser], [AuditLogComputer], [AuditLogForm], [AuditLogVBA], [AuditLogAction]) VALUES (txtEntryID.Value, txtUserID.Value, txtComputerID.Value, Form.Name, 'cmdClose_Click', 'Issued e-mail notification to ' & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & ' ' & lstPrintCheckedStaff.Column(2, varItemCheckedStaff))")
            DoCmd.SetWarnings (True)
 
        End If
 
    Next
 
Exit_cmdIssueEmailNotifications_Click:
    DoCmd.SetWarnings (True)
    Exit Sub
 
ErrorHandler:
    Beep
    Call MsgBox("An error was encountered.  Please try again." & vbCrLf & _
    vbCrLf & _
    "If the problem persists, please contact " & TSM_APPLICATION_SYSTEMADMIN & " quoting:" & vbCrLf & _
    "       Error code:     " & Err.Number & vbCrLf & _
    "       Description:    " & Err.Description, vbCritical, TSM_APPLICATION_ERRORCAPTION)
    Resume Exit_cmdIssueEmailNotifications_Click
End Sub

[MikeTheBike]

Hi

I'm just gesing here but I think perhaps you should go back to you first(?) thoughts but change this

strAuditLogAction = "Issued e-mail notification to " & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & " " & lstPrintCheckedStaff.Column(2, varItemCheckedStaff) 
 

to this

strAuditLogAction = "'Issued e-mail notification to " & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & " " & lstPrintCheckedStaff.Column(2, varItemCheckedStaff) & "'"

ie. put apostrophes in the string ??

In you current code you are confusing the us of these ' with these " and hence confusing the compiler!


MTB

[William Hamilton]

Hi Mike,

Thanks for replying... really appreciate it.
Unfortunately, that doesn't resolve it. I've run through the code using Debug > Step Into and the error occours on this line of code:

DoCmd.RunSQL ("INSERT INTO AuditLog ([EntryID], [AuditLogUser], [AuditLogComputer], [AuditLogForm], [AuditLogVBA], [AuditLogAction]) VALUES (txtEntryID.Value, txtUserID.Value, txtComputerID.Value, Form.Name, 'cmdClose_Click', 'Issued e-mail notification to ' & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & ' ' & lstPrintCheckedStaff.Column(2, varItemCheckedStaff))")

If I take out:

& lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & ' ' & lstPrintCheckedStaff.Column(2, varItemCheckedStaff)

it runs fine. I can't see any " or ' errors and it still doesn't work if I take out all of the text from the last parameter and just leave

lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & lstPrintCheckedStaff.Column(2, varItemCheckedStaff)

Really stumped! Any further thoughts are greatly appreciated!
Thanks again for taking to time to post.
Will

[MikeTheBike]

Hi

Yes I know the problem (I think!), and I thought I had suggested an answer, but if you insist on using your current code, then try repacing this

DoCmd.RunSQL ("INSERT INTO AuditLog ([EntryID], [AuditLogUser], [AuditLogComputer], [AuditLogForm], [AuditLogVBA], [AuditLogAction]) VALUES (txtEntryID.Value, txtUserID.Value, txtComputerID.Value, Form.Name, 'cmdClose_Click', 'Issued e-mail notification to ' & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & ' ' & lstPrintCheckedStaff.Column(2, varItemCheckedStaff))")

with this

DoCmd.RunSQL "INSERT INTO AuditLog ([EntryID], [AuditLogUser], [AuditLogComputer], [AuditLogForm], [AuditLogVBA], [AuditLogAction]) VALUES (txtEntryID.Value, txtUserID.Value, txtComputerID.Value, Form.Name, 'cmdClose_Click', 'Issued e-mail notification to " & lstPrintCheckedStaff.Column(1, varItemCheckedStaff) & " " & lstPrintCheckedStaff.Column(2, varItemCheckedStaff) & "')"

I is a bit difficult to 100% sure the syntax is correct without having the code in the correct environment to compile, but I think it is OK!?

MTB

[William Hamilton]

Yeah!!!
It worked!

Thank you ever so much... really appreciate it!
Cheers,
Will