DLookup in Subform returns Error.....

[jdbogie]

I have a table that maintains a list of components for items to be assembled.
[ComponentCode][ComponentDescrip][ComponentSupplier]...

Another table maintains a list of all items being assembled and the components needed in that item.
[ItemCode][ComponentCode][other misc fields]

Each ItemCode can have multiple records, 1 for each component needed in the item.

I created a form (Continous), that reports for a selected Item, only the components related to that item, using DLookUp, to pull the ComponentDescrip from the component table. It works great as a stand alone form.

I then put that form in another form.. (this 'parent' form, in addition to other things, provides the selection of the 'item' we are finding components for)

when i run the combined form.. all my DLookUps are filled in with '#Error'

And I thought this was simple....
Anyone have any ideas??

 

[Isadore]

Cd just be syntax...I'm a technical user myself but here's what I'd try:

Straight up in the textbox on the subform, i.e., in its "Control Source":

=DLookUp("[Item","tblA","[ID]=Forms![MyMainForm].Form![MySubForm]&quot

Alternative, in the "On Current" event of the subform:

Dim X As Variant 'in case you get a null value
X = DLookUp("[Item","tblA","[ID]=Forms![MyMainForm].Form![MySubForm]&quot
If IsNull (X) Then
Exit Sub
Else
[MytextboxinSubForm] = X
Exit Sub
End If

 

[Isadore]

Don't forget in the first line of code to add your text box name, q.v.,
".....Form.[MySubForm]![Mytextbox]&quot

sorry...

 

[Isadore]

Having a bad code day...sorry...I'll make sure in the future to get it right...let me start this one over...couple of syntax errors...so, here's the scoop one more time:

Straight up in the textbox on the subform, i.e., in its "Control Source":

=DLookUp("[Item","tblA","[ID]=Forms![MyMainForm].Form![MySubForm]![Mytextbox]&quot

Alternatively, in the "On Current" event of the subform:

Dim X As Variant 'in case you get a null value
X = DLookUp("[Item]","tblA","[ID]=Forms![MyMainForm].Form![MySubForm]![Mytextbox]&quot
If IsNull (X) Then
Exit Sub
Else
[MytextboxinSubForm] = X 'try full line above if needed...
Exit Sub
End If

 

[jdbogie]

Ok Isadore

I'll take a look at that. I just assumed the code was correct because it worked as a stand-alone form..

Thanks for an Idea... i was out of them

 

[Oppenhiemer]

Well jdbogie -

The behaviour you describe sounds like a limitation in Access, I know a similar thing happens sometimes with reports and sub-reports.

PLace the "Information" field either in an appropriate area on the main form - or on the "Footer" section of the embedded form. It may be possible to have a field on the continuos form displaying the results - but this would look odd. It would display the same data in all rows at once - changing as you selected a different record (sorry but the phonomenon in question is hard to descibe.)

This is an information field, so think of it like a "Totals" field - as it summerises information. And as such - place it on the footer of the subform or on the main form.

I would also suggest using a user-defined function to get any values. So on the main form, the control source for the dummy field would be =GetData()
where "Getdata" is a function returning (or setting) the required values.

Anyway, just a thought - hope it helps.

Cheers..