syntax problems

[jlabre01]

Hi,
I've created a form that uses a drop down menu for the user to select the company they're looking for and a secondary drop down menu that shows the invoice reference number for the company's invoices. The problem is with the secondary drop down menu, the statement I used to define what I wanted it to show is as follows:
SELECT Projects.InvoiceNumber FROM Projects WHERE Projects.CompanyID = Forms!Invoice!CompanyID;

For some reason it displays nothing, when I remove the where statement it shows all invoice numbers, please help.

jlabre01

 

[boblarson]

If this is in the rowsource property then refer to it like:

SELECT Projects.InvoiceNumber FROM Projects WHERE Projects.CompanyID = [Forms]![Invoice]![CompanyID];

 

[jlabre01]

Hi,
I tried to add the square brackets around the names at the end as suggested but when i save it the square brackets are gone and it doesn't work, what should I do?

jlabre01

 

[boblarson]

Are you sure your form name is Invoice and your CompanyID is a number and not text?

 

[pbaldy]

You would have to requery the second combo after making a selection in the first. Have you done that?

 

[jlabre01]

Hi,
ok I think I figured it out, in the projects table companyID is names, but in the companies table it's numbers. So how would i use the where statement to do the same thing as before?if i have to get the invoice number to output from the projects table and the company id from the companies table?

jlabre01

 

[boblarson]

You should redesign the table that is storing it as a foreign key to be a Number (Long Integer). Don't try to Band-aid fix it. Fix the actual design flaw.

 

[jlabre01]

Hi,
I just looked in the design view at the projects table, companyID is defined as a number (long integer) but somehow in the table there are just names. not sure how to fix that.

 

[pbaldy]

Sounds like

http://www.mvps.org/access/lookupfields.htm

 

[boblarson]

Ah, you would appear to be suffering from the ill-effects of LOOKUP FIELDS AT TABLE LEVEL. You want to remove them.


To remove the lookup field:

 

[jlabre01]

thanx that helped