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

HansUp · Accepted Answer · 2013-09-23 17:09:24Z

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

edited Sep 23, 2013 at 17:09

answered Sep 23, 2013 at 17:02

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Graham Warrender Over a year ago

The immediate window is showing false? This meaning that the query, returns no data?

majjam Over a year ago

I concur with Hans' advice, however in addition I think you've missed out whatever the compID column is, so instead of saying "Where ComputerID = " & compID you have just "Where = " & compID

HansUp Over a year ago

Of course! Sorry I didn't spot that sooner. Your code asks VBA whether "SELECT ..." is equal to CompID. But the string is not equal to that number, so the result of that expression is False which is then converted to a text value and stored in CompDetailSQL.

HansUp Over a year ago

bamie91 is right. Fix your code to construct the SQL statement you need. This is the first problem you must solve. Keep at it until Debug.Print shows you a SQL statement which works correctly when you test it as a new Access query.

Graham Warrender Over a year ago

I've ran the debugger as stated above and the debugger stated false. Played around with the code, and changed it to perform a query on only the single table and it completes without error. The error seems to be when performing a query on the two tables.

|

Graham Warrender · Accepted Answer · 2013-09-24 14:02:33Z

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

answered Sep 24, 2013 at 14:02

Graham Warrender

3651 gold badge8 silver badges21 bronze badges

1 Comment

majjam Over a year ago

You're very welcome :) and I think you can accept your own answers

Collectives™ on Stack Overflow

Access VBA Query

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related