Dlookup - What am I missing?! (Probably really easy)

[headtrip]

Hey all,

Sorry for what is probably an easy question, but I've looked over as many Dlookup example and threads in this forum as any sane person could and still can't figure out my issue.

Breakdown:
Table: tblISRregion
Fields: Region, Manager

Form: frmISRregion (drop-down that pulls Region values from tblISRregion)
Form: frmManager
(Both inside SampleDraftReq form)

All I would like the frmManager form to do is to pull the corresponding Manager value from tblISRregion table in regards to what Region choice is chosen in frmISRregion.

For example, if Bob is the Manager of the Central Region, whenever someone selects the Central Region in frmISRregion, Bob's name will automatically fill in frmManager form. Here is my Dlookup code below used in my Control Source, as it just gives me a looping #Error. Any help would be greatly appreciated!

=DLookUp("[Manager]","tblISRregion","[Region] =" & [Forms]![SampleDraftReq]![frmISRregion])

 

[vbaInet]

Hey all,

Sorry for what is probably an easy question, but I've looked over as many Dlookup example and threads in this forum as any sane person could and still can't figure out my issue.

Breakdown:
Table: tblISRregion
Fields: Region, Manager

Form: frmISRregion (drop-down that pulls Region values from tblISRregion)
Form: frmManager
(Both inside SampleDraftReq form)

All I would like the frmManager form to do is to pull the corresponding Manager value from tblISRregion table in regards to what Region choice is chosen in frmISRregion.

For example, if Bob is the Manager of the Central Region, whenever someone selects the Central Region in frmISRregion, Bob's name will automatically fill in frmManager form. Here is my Dlookup code below used in my Control Source, as it just gives me a looping #Error. Any help would be greatly appreciated!

=DLookUp("[Manager]","tblISRregion","[Region] =" & [Forms]![SampleDraftReq]![frmISRregion])

Are you referencing a form or a subform? Just to clarify. And is frmIsRegion a form or a field and if it is a field is it a string or integer data type?

 

[headtrip]

I am referencing the subform, frmISRregion. frmISRregion and frmManager are both subforms inside form SampleDraftReq.

frmISRregion is a subform. It pulls data from a field (Region) in a table (tblISRregion)


Let me know of any other questions you may have. Thanks for the quick reply!

 

[vbaInet]

I am referencing the subform, frmISRregion. frmISRregion and frmManager are both subforms inside form SampleDraftReq.

frmISRregion is a subform. It pulls data from a field (Region) in a table (tblISRregion)


Let me know of any other questions you may have. Thanks for the quick reply!

a DLookup has these parts (Expression, Domain, Criteria). Expression is the result or string you want to get, Domain could be a table name or query, and Criteria is like your WHERE clause in a sql string. So you don't reference the subform in the Criteria part, you reference the field that in the subform that contains the search criteria.

=DLookUp("[Manager]", "tblISRregion", "[Region] = " & [frmISRregion].Form![NameofControl])

Change the NameOfControl to that which you want to use as your search string

 

[boblarson]

And make sure you are referring to the subform CONTROL (the control on the main form which houses the subform) and NOT the subform name itself UNLESS the subform control and the subform both have the same exact name. If they don't, you have to use the subform control name.

And the .Form. part that vbaInet included stays just like it shows as it tells Access that you are wanting something on the subform and not the subform control.

For more about subforms, see this on my website.

 

[headtrip]

Okay, I'm sorry that I'm not getting it, but I'm making progress as now I just have the #Name error instead of the #Error. Maybe I'm confusing forms and controls and just not realizing it; I've attached some screenshots below, as well as the code I am now trying to use. (Also, sorry to confuse you, but I am using the table tblRegion now instead of tblISRregion).

=DLookUp("[Manager]","tblRregion","[Region] = " & [Forms]![SampleDraftReq]![frmISRregion].[Form]![ISRregion])

Once again, any help appreciated!

 

[vbaInet]

Okay, I'm sorry that I'm not getting it, but I'm making progress as now I just have the #Name error instead of the #Error. Maybe I'm confusing forms and controls and just not realizing it; I've attached some screenshots below, as well as the code I am now trying to use. (Also, sorry to confuse you, but I am using the table tblRegion now instead of tblISRregion).

=DLookUp("[Manager]","tblRregion","[Region] = " & [Forms]![SampleDraftReq]![frmISRregion].[Form]![ISRregion])

Once again, any help appreciated!

You've added some more stuff to what we gave you. You really don't need the Forms!SampleDraftReq bit here.

=DLookUp("[Manager]","tblRregion","[Region] = '" & [frmISRregion].[Form]![ISRregion] & "'")
The above should work

 

[headtrip]

I added it because I was reading on the link below and it told me I should add it.
http://support.microsoft.com/kb/113352

Hmmm, still getting the #Name error with your code (with or without extra stuff). I'm really scratching my brain on this one.

 

[vbaInet]

That is the full referencing. Access can work it out without the binding.

I'm a bit confused why you are using a Dlookup? Wouldn't it be easier to amend your query so it does it automatically by creating a join to that table? Is this dlookup filling in the ISR Manager text box?

 

[boblarson]

I added it because I was reading on the link below and it told me I should add it.
http://support.microsoft.com/kb/113352

Hmmm, still getting the #Name error with your code (with or without extra stuff). I'm really scratching my brain on this one.

So is frmISRregion really the name of the subform control? From the screenshots it doesn't even look like a subform. But if it is, then you need to refer to the control name and not the subform name:

 

[headtrip]

Yea, it is filling in the ISR Manager text box. After doing some more reading I guess it isn't a subform. You'll have to excuse me, as I come from a web design background, so anything with a user input is a form to me.

 

[vbaInet]

Yea, it is filling in the ISR Manager text box. After doing some more reading I guess it isn't a subform. You'll have to excuse me, as I come from a web design background, so anything with a user input is a form to me.

In that case create a query to include all those fields from the relevant tables and you won't need to use dlookup.
NB: And base your form on that query by setting its Record Source

 

[headtrip]

Thanks guys, yea I was definitely making it too hard because I still don't understand all the lingo. As you state vbaInet, I add the other fields and then used a

=[Forms]![SampleDraftReq]![frmISRregion].[Column](1)

To pull my manager field. Thanks your all your help guys!

 

[vbaInet]

Thanks guys, yea I was definitely making it too hard because I still don't understand all the lingo. As you state vbaInet, I add the other fields and then used a

=[Forms]![SampleDraftReq]![frmISRregion].[Column](1)

To pull my manager field. Thanks your all your help guys!

Glad we could help.