A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

setFocus & inner join query glitch...



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 08:46 PM posted to microsoft.public.access
Stephen Flan
external usenet poster
 
Posts: 2
Default 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  
Old June 2nd, 2010, 11:13 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old June 3rd, 2010, 07:14 PM posted to microsoft.public.access
Stephen Flan
external usenet poster
 
Posts: 2
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.