DLookup in a SubForm

[Guest]

I have a Main Form with an imbedded SubForm. I am doing a DLookup in the
ControlSource using a TextBox in the SubForm to Display the SoftwareName.
This works great (thanks to Rick Brandt) when I open the SubForm alone.
However, when I view the main form, the DLookup doesn't display the data in
the imbedded subform.

Any ideas from the gurus?

Thanks,
Leslie

 

[Al Camp]

Leslie,
Posting your Dlookup would have been helpful...
I'm guessing that you may be referring to some value on the subform in
your DLookup argument.
Try using the Full address/name of that field in the Where of your
DLookup...
Forms!frmYourMainForm!frmYourSubform.Form![YourSubformField]
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al,

I tried your suggestion, but it's still not working. I'm pretty sure it's
my syntax. Here's my info:

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

The parent form is "User"
The sub form is "UserSoftware"
The table with the "SoftwareName" value is "Software"
The SoftwareID field is numeric.

Thanks!
Leslie

Leslie,
Posting your Dlookup would have been helpful...
I'm guessing that you may be referring to some value on the subform in
your DLookup argument.
Try using the Full address/name of that field in the Where of your
DLookup...
Forms!frmYourMainForm!frmYourSubform.Form![YourSubformField]
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a Main Form with an imbedded SubForm. I am doing a DLookup in the
ControlSource using a TextBox in the SubForm to Display the SoftwareName.
This works great (thanks to Rick Brandt) when I open the SubForm alone.
However, when I view the main form, the DLookup doesn't display the data
in
the imbedded subform.

Any ideas from the gurus?

Thanks,
Leslie

 

[Al Camp]

Try,
=DLookUp("[SoftwareName]","Software","[SoftwareID] =
Forms!User!UserSoftware.Form!SoftwareID")
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,

I tried your suggestion, but it's still not working. I'm pretty sure it's
my syntax. Here's my info:

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

The parent form is "User"
The sub form is "UserSoftware"
The table with the "SoftwareName" value is "Software"
The SoftwareID field is numeric.

Thanks!
Leslie

Leslie,
Posting your Dlookup would have been helpful...
I'm guessing that you may be referring to some value on the subform in
your DLookup argument.
Try using the Full address/name of that field in the Where of your
DLookup...
Forms!frmYourMainForm!frmYourSubform.Form![YourSubformField]
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a Main Form with an imbedded SubForm. I am doing a DLookup in
the
ControlSource using a TextBox in the SubForm to Display the
SoftwareName.
This works great (thanks to Rick Brandt) when I open the SubForm alone.
However, when I view the main form, the DLookup doesn't display the
data
in
the imbedded subform.

Any ideas from the gurus?

Thanks,
Leslie

 

[Guest]

Al,

Thank you. I think I understand. This is what I now have in the
SoftwareName ControlSource in the SubForm UserSoftware.

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

This works beautifully when I'm viewing the data in the subform
UserSoftware. My output looks like this:

SoftwareID SoftwareName
1 Software A
2 Software B
3 Software C

I then go to my main form "User", which has the SubForm UserSoftware
imbedded. When I pull up a user record in the User Form, the SubForm
Displays the following Data for my user record:

SoftwareID SoftwareName
1 #Name?
3 #Name?

This is actually the original problem I had from the beginning. When I
previously had the #Name? error in the subform (as I was developing it), this
was caused by a field-type mismatch and I was able to correct that. But that
doesn't seem to be the case here.

Were you able to get your sub-form data to display from the main form? I'm
wondering if it could possibly have to do with my table linking... It's like
trying to find a needle in the haystack.

Leslie

Leslie,
The Dlookup is on the Main form?? There's the problem... My code is
written to display the SoftwareName for each SoftwareID record (the Many)
entered in the subform. It assumes that it is the ControlSource for a
calculated text control in the subform Detail section.

If you have multiple SoftwareIDs entered against a User, how does the
Main form know which SoftWareID to DLookup?

If you list several SoftwareID's against each user, (you said One to
Many) then you should display the SoftWare name on each of those records in
the sub.
Your sub should look somewhat like this...
SoftwareID SoftwareName
19 MSWord
22 MSExcel
8 MsAccess
Create an unbound field in the detail section of your subform
(SoftwareName) with the Dlookup as the ControlSource. Whenever you select a
SoftWareID, the SoftwareName field should display the appropriate Name.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,

In answer to your questions:

What is the table you are using the Dlookup against (Software?) Software
What is the name of your Main form (UserSoftware?) User
What is the Name of your SubForm (SoftwareID? I doubt that...) UserSoftware
What field in the subform contains the value that you will use to
identify the Dlookup value (SoftwareID?)\

SoftwareID

SQL Syntax for the subform would be:
Select Software.SoftwareName from Software
Left Outer Join UserSoftware on Software.SoftwareID =
UserSoftware.SoftwareID

The User form is joined to the UserSoftware form based on UserID. The
relationship is 1:* (many UserSoftware records to one User Record).

Based on your input below, this is the "exact" syntax I have in the "User"
main Form right now:

=DLookUp("[SoftwareName]","Software","[SoftwareID] =
Forms![User]![UserSoftware].Form![SoftwareID]")

When I run the form, and put in a Software ID, I get a "#Error" in the
SoftwareName field.

Also, I do think I need the & around the second half of the "criteria"
statement (after the = sign), as that knows to look up the value in the
table, no?

I wish I could send attachments, as I think that would help. Thanks again
for your "perseverence".

Leslie

Leslie,
That's not the code I gave you to try.

And, my code used the names you used in your posts... so if they're
wrong, I can't tell that. Let's try again....
Let me write the code using example names, and you replace those
names
with your own...
(all code on one line..)


=DLookup("[FieldNameYourLookingUp]","TableYourUsing","[TheFieldInYourTable]
=
Forms![YourMainFormName]![YourSubFormName].Form![FieldInSubformThatHasTheValueYourLookingUp]")

Still fails? Then... post your code exactly... and...


Al,

Thank you for hanging in there with me. I have definitely not had luck
with
this. I'm obviously doing something wrong, and DLookup is not an easy
function to work with.

This is my current syntax which works in the SubForm -- by itself:

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

I do get a warning on my SoftwareName in the UserSoftware field which
has
the Lookup tied to it saying that there is a "circular reference" in
the
DLookup.

I don't understand the need for the & sign, but if I remove that & sign
and
the quote before the first & sign and the first of the two quotes after
the
second &, the query gives me an error. Possibly my fields are not
defined
exactly the same??? Software ID is an "Auto Numbered" field in the
Software
table and it is a "Numeric" field in my UserSoftware table.

If I modify the above in the User Form's imbedded SubForm and change
the
Syntax to:

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

I get the #Name? error.

These are the tables and fields of interest:

UserSoftware Table:
UserID (Numeric)
SoftwareID (Numeric and ComboBox)
SoftwareName (Text and a "Text Box" field)

Software Table:
SoftwareID (AutoNumbering)
SoftwareName (Text)

UserSoftware FORM:
SoftwareID (Does a Query on Software ID and SoftwareName)
SoftwareName ( Performs
=DLookUp("[SoftwareName]","Software","[SoftwareID]
=
" & Forms!UserSoftware!SoftwareID & "") )

User FORM:
Has UserSoftware FORM imbedded, tied by User.UserID and
UserSoftware.UserID
(both numeric - User.UserID is autonumbered and UserSoftware.Userid is
numeric)

Thank you for your help. If you want I can send you some screen shots
offline, if you think that would help. You can reach me at leslief at
oregonaero dot com.

Thanks!
Leslie

:

Sounds like a syntax error... I tested my code, and it works... I
just
subsituted my form/table names and controls, but left the syntax just
as
it
was.

**Cut and Paste what you have directly into your next email reponse.

**The code goes all on one line, and drop any >'s email might add.

=DLookUp("[SoftwareName]","Software","[SoftwareID] =
Forms!User!UserSoftware.Form!SoftwareID")

Also, make sure all your names/controls are correct.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al,

I tried this and got the following error:'

"The expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression
to
variables."

I had cut and pasted what you typed.

Any other ideas?
Leslie


:

Try,
=DLookUp("[SoftwareName]","Software","[SoftwareID] =
Forms!User!UserSoftware.Form!SoftwareID")
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions