one form two sources help needed

[techexpressinc]

i have a Form with a data source for most the fields on the form. But I need to bring in values from another table and I am having trouble getting the right syntax.
This is what I have now:
Employer Name «Expr» [QryCAPSLlink]![Employer Name]

The query is just pulling one record.
<<Code>>
SELECT Tbl403bCAPSLlink.[Remitter/ Plan Number], Tbl403bCAPSLlink.[Employer Name]
FROM Tbl403bCAPSLlink
WHERE (((Tbl403bCAPSLlink.[Remitter/ Plan Number])="1291"));
<<Code>>

Attached are screen prints. Any help will be greatly appreciated.

Thank you

Russ

 

[pbaldy]

You can't do that. You can use DLookup() to grab the value directly:

=DLookup("[Employer Name]", "Tbl403bCAPSLlink", "[Remitter/ Plan Number])='1291'")

 

[techexpressinc]

The field displays
#error
now.

I think I put the code you suggested in the right place.
See the attached.

Thx
Russ

 

[pbaldy]

Sorry, "bonus parentheses" copy/paste typo on my part. Try this:

=DLookup("[Employer Name]", "Tbl403bCAPSLlink", "[Remitter/ Plan Number]='1291'")

 

[techexpressinc]

Re: one form two sources help needed - Solved!

Paul - Thx for your good help, repetitively.

Russ

- I will probably have a follow-up on this one. That is how to use the field-Plan-id from above in the form, putting in the query as a parameter to load data into this field.

- I figured one miracle at a time. First get the data from the other source, by the way is in another Access-DB. Next, get the right data.

 

[pbaldy]

Fairly simple (and I figured you'd be heading there). See the syntax here for referring to a form control:

http://www.mvps.org/access/general/gen0018.htm

 

[techexpressinc]

Re: one form two sources help needed -working so far

This is a real tricky and a pain to put in for every field. I have i going so far.


=DLookUp("[Employer Name]","Tbl403bCAPSLlink","[Remitter/ Plan Number]='" & [PLAN_NO] & "'")


Russ - now at RMneuman @ lfg.com

 

[techexpressinc]

An Access-Developer from another dept. stopped by this morning and said the DLookup would work but recommended a sub-form within the tab for getting the fields from the other DB.

Two advantages:
1) less coding per field within the field properties
2) smooth process, efficiency wise, the sub-form would use one query to get the data, each DLookup is like a mini query running

 

[pbaldy]

Maybe you should describe what you're trying to achieve overall. For one value a DLookup is fine. If you're trying to get several, as noted a subform or something else might be better.