Access VBA Query

Question

I've been searching the net on how to run a query in Access using VBA & SQL and come up with this. The general idea is that the value selected from the combo box should run a query based on the ID Selected for instance. The user selects a company which has an ID of 5 in the companies table. This then populates the text boxes below with the relevant data. I also have another table with employee data in which has a relationship with the companies tables using the CompanyID as the primary key, if the employees company id = 5 they work for asda in this case. For some reason when running this query i'm receiving an error stating that there may have been an errro evaluating the function

Public Function DataLookup()
Dim CompDetailSQL As String
Dim rst As DAO.Recordset
Dim CompID As String
CompID = Me.lstBoxCompanyName.Value
CompDetailSQL = "SELECT Companies.CompanyID, Companies.CompanyName,       Companies.AddressNo, Companies.AddressLine1, Companies.AddressLine2, Companies.AddressLine3, Companies.AddressPostcode, Companies.AddressCounty, Link_Table.FirstName, Link_Table.LastName FROM Companies INNER JOIN Link_Table ON Companies.CompanyID = Link_Table.CompanyID WHERE " = CompID
Set rst = CurrentDb.OpenRecordset(CompDetailSQL, dbOpenSnapshot)
Me.lblAddressLine1.Value = rst!Companies.AddressLine1
Me.lblAddressLine2.Value = rst!Companies.AddressLine2
Me.lblAddressLine3.Value = rst!Companies.AddressLine3
Me.lblAddressPostcode.Value = rst!Companies.AddressPostcode
Me.lblAddressCounty.Value = rst!Companies.AddressCounty
rst.Close
Set rst = Nothing
End Function

Answer 1

Cheers guys problem solved. The SQL query itself wouldn't run under access query design.

CompDetailSQL = "SELECT Companies.CompanyID, 
Companies.CompanyName, 
Companies.AddressNo, 
Companies.AddressLine1, 
Companies.AddressLine2, 
Companies.AddressLine3, 
Companies.AddressPostcode, 
Companies.AddressCounty,
Link_Table.FirstName, 
Link_Table.LastName
FROM Companies 
INNER JOIN 
Link_Table ON Companies.CompanyID = Link_Table.CompanyID 
WHERE Companies.CompanyID = " & Me.lstBoxCompanyName.Value

However the query above did work, it seemed i was missing Companies.CompanyID Problem solved which now creates a new problem. But a completely different question. Thank you for your help @hansUp @bamie9l

Answer 2

You likely have a problem here ...

CompDetailSQL = "SELECT ... WHERE " = CompID

Whether or not it throws an error, I suspect that WHERE clause will not do what you need.

Give yourself an opportunity to examine the completed SQL statement your code creates. Include this line after CompDetailSQL = ...

Debug.Print CompDetailSQL 

Afterwards, run the code and go to the Immediate window (Ctrl+g) to view the output. You can copy the statement text and then paste it into SQL View of a new Access query for testing.

When you have the query you need, then reference the recordset fields by field name ... not qualified with the name of the source table.

'rst!Companies.AddressLine1
rst!AddressLine1

You said populate text boxes. If lblAddressLine1 is really a text box, this should work ...

Me.lblAddressLine1.Value = rst!AddressLine1

However, if lblAddressLine1 is actually a label control, set its .Caption instead of .Value property.

Me.lblAddressLine1.Caption = rst!AddressLine1