DLookUp and Continuous forms

[mattdube]

Background: I have an .adp linked to SQL 2005. We have a continuous
form that is based off a select query that populates a field called
'material'. We need a continuous form here because 1) we need to have
combo boxes to select data for an insert (I have an ADO insert script
written - not the problem here) and 2) we need to see all the materials
(query rows) at once. In the On Current event, I have vba code that
parses the material field and does a DLookup and returns the material
description. The material field can contain two different formats of
materialID, thus the need to parse the ID and do a DLookup on the
appropriate table. If you would like to see my VBA code please ask but
I don't believe the code is the issue because it works.

Problem: The description field populated by the DLookup always shows
the value for the selected form. When I select a different recordset,
the DLookup on all the continuous form instances changes to show the
description of the material of the selected form. All the DLookups are
always the same for each instance of the continuous form, and only show
the selected records material description. Please help me figure out
how to have the returned DLookup value be unique for each record on the
form. Is there another event I should put the DLookup code in, or is
there a way to specify uniqueness that I don't know about?

Thanks!

Matt

 

[Ben]

Personally I would avoid using dlookup on a continuous form - find another
way - but I think I see what you're getting at.

The reason for your problem is that Access regards every instance of
controls on a continuous form as the the same control. The only thing that
differentiates them is the data they contain, through their data bindings
(controlsource property). If you change the value for one, or any property,
they all change.

The answer is not to update the field from code at all, but instead use the
dlookup expression (possibly wrapped in a iif) as the controlsource of the
description field. For example:

=iif(not isnull(material),dlookup("description","tblMaterial","material="""
& material & """"),"Please select a material")

If you need to pre-parse the "material" field then use a function like:

=iif(not isnull(material),dlookup("description","tblMaterial","material="""
& parseMaterialToMaterialCode(material) & """"),"Please select a material")

If you need a different dlookup entirely dependent on the value of material,
then wrap the whole thing in a function, binding the control source to:

=parseMaterialFieldAndDLookupDescription(nz([material],""))

Your function accepts the value of the material and returns whatever you
want in the Description field, using as much code as you like.

However, I stress that using Dlookups on continuous forms is generally
rather slow and inefficient. You might be better off using the function
idea, working with the recordsets as global variables which are opened with
the Form OnLoad event??

Let me know if this points in the right direction.

Ben.

 

[Jeff L]

The field you are populating is an unbound textbox and thus that is why
you are getting the same value for every row. I can think of a couple
of things you might try.
1. Put the DLookup in the Data Source of the text box...=YourDLookup
2. If there are other things that need to happen before the Dlookup
(you said something about parsing), create a function in a module that
you call and the returned value is put into the textbox.

Here's how to do the function if you don't know already.
Create a module and make a function, say GetDescription so now you
should have
Public Function GetDescription().
Change it a bit to Public Function GetDescription(ID as String) as
String.
Now put your code that you currently have here.
Your last statement should be GetDescription = YourDLookupStatement
Now in the textbox on your form, put =GetDescription([ID])

You will not need the On Current Event.

Hope that helps!

 

[mattdube]

Thank you both Jeff and Ben for your prompt and informative responses.
I was able to fix my problem with your guidance.

I created a public function which did the lookup for me and set the
control source of the txtbox to be that function. Brilliant!

-Matt