If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
setFocus & inner join query glitch...
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 |
#2
|
|||
|
|||
setFocus & inner join query glitch...
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" wrote: 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 . |
#3
|
|||
|
|||
setFocus & inner join query glitch...
On Jun 2, 3:13*pm, KARL DEWEY
wrote: 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" wrote: 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? Thanks, Stephen . |
Thread Tools | |
Display Modes | |
|
|