Dynamic SQL QUERY

[enfinity]

Hi *,

I have a hard time to generate a dynamic query. I want to have 2 combo boxes.

(1) I choose a customer in combo box 1 (aka cboCurrentCustomer).
(2) I want to show only projects which are related to the customer shown in combo box 1

(SEE SCREENSHOT)

Now, I entered the following SQL code as data source for combo box 2. The critical part is the WHERE clause.

SELECT project_extended.PROJECT_ID, project_extended.NAME, project_extended.CUSTOMER_ID
FROM project_extended
WHERE (((project_extended.CUSTOMER_ID)=[cboCurrentCustomer].[Value]) AND ((project_extended.STATUS_ID)=2))
ORDER BY project_extended.NAME;

Here I want to join the value of combo box 1 with the customer_id in the table. Now, everztime when I choose a customer from combo box 1, I have to press F5 in order to show the correct projects in combo box 2. Has someone an idea of how I can solve this problem without pressing F5?

Thanks!
Steve

 

[DCrake]

Simple Software Solutions

SELECT project_extended.PROJECT_ID, project_extended.NAME, project_extended.CUSTOMER_ID
FROM project_extended
WHERE (((project_extended.CUSTOMER_ID)=[cboCurrentCustomer].[Value]) AND ((project_extended.STATUS_ID)=2))
ORDER BY project_extended.NAME;

Should Read

"SELECT PROJECT_ID, NAME, CUSTOMER_ID
FROM project_extended
WHERE CUSTOMER_ID= '" & Me.cboCurrentCustomer & "' AND STATUS_ID=2
ORDER BY NAME;"

If Customer Code is numeric then remove the single quotes from around the Me.cboCurrentCustomer.


However you need to change the field name called NAME to another word. See Access Reserved words, characters and Naming Conventions.

CodeMaster:

 

[enfinity]

Yeah, I tried this before:

SELECT project_extended.PROJECT_ID, project_extended.NAME, project_extended.CUSTOMER_ID
FROM project_extended
WHERE project_extended.CUSTOMER_ID = '" & Me.cboCurrentCustomer & "' AND project_extended.STATUS_ID=2
ORDER BY project_extended.NAME;


Unfortunately, this gets me a Data Types Not Compatible Error, which I do not understand... the part project_extended.CUSTOMER_ID = '" & Me.cboCurrentCustomer & " has integer numbers on both sides of the equation.

Also, why do I have to enclose the Me.cboCurrentCustomer part in quotation marks? I thought this is for string values only?

Thanks!

 

[neileg]

The Me shorthand won't work. Specify the form collection and form name.

Forms!MyFormName!cboCurrentCustomer

 

[enfinity]

Thanks neileg! I think Access find the appropriate values but has a problem with the data types (???). I get an error message saying that the data types are not compatible.

 

[enfinity]

BTW: the query works when I type:

WHERE project_extended.CUSTOMER_ID = Forms!MyFormName!cboCurrentCustomer

Notice that I did not write quotation marks or anything like that (is it really necessary to do so or not). My only problem now is that I have to press F5 after I select a value in combo box 1. Otherwise combo box 2 (which runs the query we're discussing) won't show anything...

 

[enfinity]

Ok, I found a workaround to avoid pressing F5. I simply used a macro in the first combo box that updates the second combo box. So it seems to work this way. However, I was wondering what operands are appropriate for use in a MS ACCESS SQL QUERY:

I know that (1) literals, (2) identifiers such as a field name of the underlying table, and (3) functions can be used in criteria expressions. Is it good practice to reference a control element (e.g. combo box) directly in a SQL query?

Thanks!
Steve

 

[neileg]

Yes, why not?