Query SQL change from Listbox AfterUpdate error (A2K)

Topic

New Reply

Ok, this was working fine up until about an hour ago, now I’m receiving an error (see screenshot)…any troubleshooting tips? This form works fine in a previous version of the database…

Reply | Quote

Replies

Check your references. This is usually indicative of a “Missing Reference”.

Reply | Quote

Mark,
Thanks a lot. I went into the references, and it showed Microsoft Office 9.0 library, (MISSING Microsoft Office 10.0 Library)….so I unchecked it, closed out the dialog box, opened it, and when I scrolled down it showed Office 10.0 library again.

New problem though – now I receive an error Run-time error ‘3296’:

Join expression not supported.

—I highlighted the line that is highlighted when I click debug…

Here is my code:
——————————————————————-
Private Sub lstNOKNames_AfterUpdate()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = “SELECT tblPersonnel.RANK, tblPersonnel.LNAME, tblPersonnel.FNAME, tblPersonnel.MI, tblPersonnel.SSN, tblPersonnel.MOS, tblPersonnel.COMPANY, tblAddresses.NOK_NAME, tblAddresses.NOK_RELATION, tblAddresses.NOK_ADDRESS, tblAddresses.NOK_CITY_STATE_ZIP, tblAddresses.NOK_PHONE FROM tblPersonnel INNER JOIN tblAddresses ON tblPersonnel.SSN = tblAddresses.SSN”

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & “((tblPersonnel.SSN) = ‘” & Me.ActiveControl.ItemData(varItem) & “‘) Or ”
Next

strSQL = strSQL & “HAVING (” & Left(strTemp, Len(strTemp) – 4) & “)” & “;”

CurrentDb.QueryDefs(“qryNOKRoster”).SQL = strSQL

End Sub

Reply | Quote

Next time you get to that place, go to immediate window and type ?strSQL so you can see what the value of strSQL is. You might even want to create a new query and paste-in the value, then try to run it or go to design mode. Access will probably give you a better explanation of your problem then.

Reply | Quote

Mark, I found my error, it was that I was using “HAVING”, instead of “WHERE”. I guess HAVING is used only when grouping records…

Thanks again for your help

Reply | Quote