query problems 1

[mustangcoupe]

I am running a query from a command button on my form it works on AC2000 but not on 2003.... [forms]![job_form]![jobID] in the criteria in field JobID and [forms]![job_form]![atr_subform]![ATRID] in the criteria in field ATRID

If I run it without anything in the criteria line it runs fine.... But when I run it with the above criteria it displays nothing. If I run it with the form closed and I am prompted I enter good data in the two prompts I get good data back! any suggestions? full query below

SELECT tbl_ATR.ATRID, tbl_job.JobID, tbl_job.Jobnumber, tbl_customer.CustomerName, tbl_eut.Sample, tbl_eut.ModelNumber, tbl_ATR.ATRDATE, tbl_ATR.ATRDescription, tbl_ATR.ATRAmount, lu_location.Location, tbl_job.PONumber
FROM ((lu_ATR_Desctiption RIGHT JOIN (((tbl_contact RIGHT JOIN (tbl_customer RIGHT JOIN tbl_job ON tbl_customer.CustomerID = tbl_job.CustomerID) ON tbl_contact.ContactID = tbl_job.ContactID) LEFT JOIN tbl_ATR ON tbl_job.JobID = tbl_ATR.JOBID) LEFT JOIN tbl_eut ON tbl_job.JobID = tbl_eut.JobID) ON lu_ATR_Desctiption.ATRDESCRIPTIONID = tbl_ATR.ATRReason) LEFT JOIN lu_location ON tbl_job.Location = lu_location.LocationID) LEFT JOIN lu_ContactPosition ON tbl_contact.ContactPosition = lu_ContactPosition.ContactPositionID
WHERE (((tbl_ATR.ATRID)=[forms]![job_form]![atr_subform]![ATRID]) AND ((tbl_job.JobID)=[forms]![job_form]![jobID]));

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[PHV]

Replace this:
[forms]![job_form]![atr_subform]![ATRID]
By this:
[Forms]![job_form]![atr_subform].Form![ATRID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mustangcoupe]

ok, that seems to work. in 2003 will it still work in 2000?

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[PHV]

Yes, as it was already the correct syntax but not well enforced by ac2k.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mustangcoupe]

PH, maybe you can help me on this one... I changes the code in the access 2003 and brought it home opened it on my laptop. access 2000..... now I get a compile error, can't find project or library. it highlights Right$ in the line GetCurrentDB = Right$(Dir(CurrentDb.Name), 4)

it also errors on left$, Chr$.... arrgh

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[PHV]

While in VBE, menu Tools -> References ...
Check for any MISSING reference

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mustangcoupe]

I have a missing referance for outlook 11

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[mustangcoupe]

ok, I removed the referance to outlook 11 and checked outlook 9 and it seems to work! why would outlook effect this?

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)