Using Dlookup in form - New to Access

[Denise Doak]

I am trying to use dlookup in a form where I would like an unbound combo box
to fill-in with all the grants of an organization where the organization name
is entered in the form. The grant and organization fields reside in
tblGrantInformation and the form is bound to tblGrantPayment. When attempting
to follow the suggested Dlookup format (walking through it via the dlookup
function) I am not able to get any results - the unbound combo box is empty.
Any help is much appreciated!

 

[Mr B]

Denise,

First, dlookup is used to return a specific value not a list of values.

From your post I am assuming that you are having the user to type in the
organization name. I would suggest that the first thing that you might do is
to create a combo box that would provide the list of organizations, thus
making it much easier for users to specify the organization. This will also
prevent users from making simple typeo errors which will prevent the rest of
your process from returning the correct values.

Next, you might consider using a listbox rather than the combo box to
display the Grants for the selected organization. This listbox would simply
need a query as its record source. This query would use the value from the
combo box with the selected organization as the criteria for returning the
grant values.

Then in the AfterUpdate event of the combo box for the selection of the
organization, you could have a line of code that would requery the listbox
that would display the list of grants for the selected organization. This
way the list of grants will change each time a selection is made from the
combo box of organizations.

 

[Denise Doak]

Mr. B,

Gosh, I didn't think of that - dlookup, not unlike Excel's lookups, returns
a specific value and not a list. Thank you!

I understood everything you mentioned including the part about writing a
"line of code" for requery -- I've got a combo box set up for picking for the
organization, I understand listboxes, queries and events . However, I have
never written code in Access (or anywhere else for that matter). Am I in over
my head?

Again, many thanks!
Denise

 

[Mr B]

Denise,

I don't think you are in over you head. I think that with help from folk
around here, you can get it done.

Use the AfterUpdate event of you combo box for selecting the organization
and place the following untested code:

me.NameOfYourComboBox.requery

Just change "NameOfYourComboBox" to the actual name of your combo box and
try selecting an organization. If the query that is supplying the data for
the listbox is written correctly and using the value from the combo box as
the criteria for the recordset the you should see the list of grants for that
organization.