setFocus & inner join query glitch...

  • Thread starter Thread starter Stephen Flan
  • Start date Start date
S

Stephen Flan

I have the following query which works excellently to populate my
form, using the criteria [txtFieldID] from my form.


SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM
AS Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
food_master.commodity
FROM food_master INNER JOIN tblFieldIDsPRNUMsNumeric ON
food_master.prnum = tblFieldIDsPRNUMsNumeric.prnum_num
WHERE (((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]!
[txtFieldID]));

Then I added a new table and related field [tblStudyDirecto.sd] and
now I get an error when I load the form and the onLoad command tries
to txtFieldID.setFocus. The error says "runtime error 2501, you can't
go to specified record. If I substitute a value for the txtFieldID the
query works fine, there just a glitch in getting it to take the value
from the form with the query below.

SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM
AS Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
food_master.commodity, tblStudyDirector.sd
FROM tblStudyDirector INNER JOIN (food_master INNER JOIN
tblFieldIDsPRNUMsNumeric ON food_master.prnum =
tblFieldIDsPRNUMsNumeric.prnum_num) ON tblStudyDirector.project =
tblFieldIDsPRNUMsNumeric.PRNUM
WHERE (((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]!
[txtFieldID]));

Any ideas out there?

Thanks,

Stephen
 
Don't know but try this --
SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM AS
Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
food_master.commodity, tblStudyDirector.sd
FROM tblStudyDirector LEFT JOIN (food_master LEFT JOIN
tblFieldIDsPRNUMsNumeric ON food_master.prnum =
tblFieldIDsPRNUMsNumeric.prnum_num) ON tblStudyDirector.project =
tblFieldIDsPRNUMsNumeric.PRNUM
WHERE
(((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]![txtFieldID]));
 
Don't know but try this --
SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM AS
Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
food_master.commodity, tblStudyDirector.sd
FROM tblStudyDirector LEFT JOIN (food_master LEFT JOIN
tblFieldIDsPRNUMsNumeric ON food_master.prnum =
tblFieldIDsPRNUMsNumeric.prnum_num) ON tblStudyDirector.project =
tblFieldIDsPRNUMsNumeric.PRNUM
WHERE
(((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]![txtFieldID]));

--
Build a little, test a little.

Stephen Flan said:
I have the following query which works excellently to populate my
form, using the criteria [txtFieldID] from my form.
Click to expand...
SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM
AS Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
food_master.commodity
FROM food_master INNER JOIN tblFieldIDsPRNUMsNumeric ON
food_master.prnum = tblFieldIDsPRNUMsNumeric.prnum_num
WHERE (((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]!
[txtFieldID]));
Click to expand...
Then I added a new table and related field [tblStudyDirecto.sd] and
now I get an error when I load the form and the onLoad command tries
to txtFieldID.setFocus. The error says "runtime error 2501, you can't
go to specified record. If I substitute a value for the txtFieldID the
query works fine, there just a glitch in getting it to take the value
from the form with the query below.
Click to expand...
SELECT tblFieldIDsPRNUMsNumeric.FLD_ID, tblFieldIDsPRNUMsNumeric.PRNUM
AS Project, tblFieldIDsPRNUMsNumeric.prnum_num, food_master.pesticide,
Click to expand...
Click to expand...
Errors out w/ "join not supported"


food_master.commodity, tblStudyDirector.sd
FROM tblStudyDirector INNER JOIN (food_master INNER JOIN
tblFieldIDsPRNUMsNumeric ON food_master.prnum =
tblFieldIDsPRNUMsNumeric.prnum_num) ON tblStudyDirector.project =
tblFieldIDsPRNUMsNumeric.PRNUM
WHERE (((tblFieldIDsPRNUMsNumeric.FLD_ID)=[forms]![frmFieldIDinput]!
[txtFieldID]));
Click to expand...
Any ideas out there?

Stephen
.
Click to expand...
Click to expand...
 
Back
Top