setFocus & inner join query glitch...

[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

 

[KARL DEWEY]

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]));

 

[Stephen Flan]

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.

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,

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]));

Any ideas out there?

Stephen
.