DLookup in a SubForm

[Guest]

I have a form "UserSoftware" which has two fields: An SoftwareID Field and a
SoftwareName field. The ControlSource for the SotfwareName field is a
DLookup to find the value in another table based on the SoftwareID value.
This is the syntax for the DLookup, and it works great in this form!

=DLookUp("[SoftwareName]","Software","[SoftwareID] = " &
Forms!UserSoftware!SoftwareID & "")

The SubForm Data results look like this:

ID Name
1 First Name
2 Second Name
3 Third Name

I then insert this form into another form as a Sub-Form and the main form is
called Users. The User record in the User form can have many instances of
the SoftwareIDs and SoftwareNames from the subform.

The problem I have is the DLookup from the inserted SubForm breaks and gives
the error: #Name? when displayed from the main form Users.

This is how the UserSoftware subform displays the results in the User Form
where for example the User = User1

ID Name
1 #Name?
3 #Name?

Does anyone know why I am getting the #Name? error? Is there anyone who can
help me with this?

Thanks,
Leslie

 

[Guest]

Gunny,

You are my hero! Two resolutions in two days! THANK YOU, THANK YOU, THANK
YOU!

Can you explain to me why shortening the RH side of the = sign made this
work? Also, I am curious about your sage comments of setting up a query to
perform this same function... I think I have an idea why, but I'd like to
hear your point of view.

Thank you again!
Leslie

Hi, Leslie.

The problem I have is the DLookup from the inserted SubForm breaks and gives
the error: #Name? when displayed from the main form Users.

In the subform's text box's Control Source Property, try:

=DLookUp("[SoftwareName]","Software","[SoftwareID] = " & [SoftwareID])

While this should work, I would recommend redesigning your subform and the
subform's record source so that it takes advantage of a precompiled query
instead of Domain functions to automatically fill in the text boxes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a form "UserSoftware" which has two fields: An SoftwareID Field and a
SoftwareName field. The ControlSource for the SotfwareName field is a
DLookup to find the value in another table based on the SoftwareID value.
This is the syntax for the DLookup, and it works great in this form!

=DLookUp("[SoftwareName]","Software","[SoftwareID] = " &
Forms!UserSoftware!SoftwareID & "")

The SubForm Data results look like this:

ID Name
1 First Name
2 Second Name
3 Third Name

I then insert this form into another form as a Sub-Form and the main form is
called Users. The User record in the User form can have many instances of
the SoftwareIDs and SoftwareNames from the subform.

The problem I have is the DLookup from the inserted SubForm breaks and gives
the error: #Name? when displayed from the main form Users.

This is how the UserSoftware subform displays the results in the User Form
where for example the User = User1

ID Name
1 #Name?
3 #Name?

Does anyone know why I am getting the #Name? error? Is there anyone who can
help me with this?

Thanks,
Leslie