DLookUp Frustration

[raymondp]

Hey,
I'm in the process of creating a form to fill in a database. I have a table
(siteid) with the following fields.

Order
SiteID (alphanumeric)
pmID (alphanumeric)
wmoID (numeric)
juliandeployed
Deployed
Today

The form (TDLForm) used to enter new data has the fields:

Date: =Now()
Time
SiteID
pmID
Adrift
Sensor
Activity
Description
Monitor

What I need is when I fill in the SiteID field, the pmID field fills in with
the correct value from the siteid table. Below is one of the many versions
of the expression I have tried in the control source field with no success.

=DLookUp("[pmID]","[TDLForm]","[SiteID] = Tables!siteid!SiteID")

Please let me know what I'm doing wrong.

Thanks,
-Ray

 

[John W. Vinson]

What I need is when I fill in the SiteID field, the pmID field fills in with
the correct value from the siteid table. Below is one of the many versions
of the expression I have tried in the control source field with no success.

Why?

Storing the pmID field redundantly in the second table sounds unnecessary. You
can always find it by joining on SiteID.

If pmID is always related to the siteID, then you should store it in the table
for which siteID is the Primary Key.

Or is it OK for one table to have one value of pmID, and the other table a
DIFFERENT value (edited by the user after entering it, say) for the same
SiteID?

John W. Vinson [MVP]

 

[UpRider]

Ray, it's one form and one table, right?
You don't need dlookup. You need a combobox control.

Just too keep everything independent of what you have already done:
Use the form wizard to create a new tabular form based on your siteid table.
Use the controls wizard to create a combo box on that form to lookup a
record to display on your form.
When it's done, if you type in a siteid in the combo box and hit enter, the
form will display the entire record.
You can do the whole thing in less than 2 minutes.

HTH, UpRider

 

[Pat Hartman \(MVP\)]

I think there is something missing from your description. But I'm guessing
that you need to include an additional table in your form's RecordSource
query. That will allow you to select the fields from the lookup table and
bind the fields to controls. No DLookup is required. This is autofill and
Access takes care of the whole thing for you.

Warning - when including lookup fields on your form, set their locked
property to yes to avoid accidental updates.

 

[raymondp]

Yeah, I guess that I wasn't too clear as to what I'm trying to do.

The siteid table is only a reference. Where the SiteID will not change,
however, the pmID will. In the database, it is important to have the
historical pmID with the siteID. The form is being used to enter data into
another table for storage. This second table is called TAODailyLog2. What I
have been able to do is create a lookup reference where the pmID field in the
form yields a list of pmIDs with the corresponding siteID. While this does
work, the procress is combersome and can result in selecting the incorrect
pmID when you're trying to put in a large number of entries in a sitting.
What I would like the form to do is when I enter the siteID, the pmID fills
in with the appropriate information without having me to select anything at
all. For example, in the Date field I have the expression =Date(). So, on
each new entry, today's date is filled in automatically. I can just hit enter
and move on to the next field.

I hope this clears up everything.
Thanks for your replies.
-Ray

Hey,
I'm in the process of creating a form to fill in a database. I have a
table
(siteid) with the following fields.

Order
SiteID (alphanumeric)
pmID (alphanumeric)
wmoID (numeric)
juliandeployed
Deployed
Today

The form (TDLForm) used to enter new data has the fields:

Date
Time
SiteID
pmID
Adrift
Sensor
Activity
Description
Monitor

What I need is when I fill in the SiteID field, the pmID field fills in
with
the correct value from the siteid table. Below is one of the many
versions
of the expression I have tried in the control source field with no
success.

=DLookUp("[pmID]","[TDLForm]","[SiteID] = Tables!siteid!SiteID")

Please let me know what I'm doing wrong.

Thanks,
-Ray

 

[John W. Vinson]

While this does
work, the procress is combersome and can result in selecting the incorrect
pmID when you're trying to put in a large number of entries in a sitting.
What I would like the form to do is when I enter the siteID, the pmID fills
in with the appropriate information without having me to select anything at
all.

Ok... that's different! Thanks for the explanation.

In that case you can "push" the pmID into its bound control in the AfterUpdate
event of the SiteID combo box:

Private Sub cboSiteID_AfterUpdate()
If Not IsNull(Me!cboSiteID) Then ' if the user selected a site...
Me!txtpmID = Me!cboSiteID.Column(2)
End If
End Sub

This will copy the third column (Column() is zero based) from the combo
cboSiteID's RowSource into the textbox named txtpmID (which would be bound to
the pmID field in your table).

John W. Vinson [MVP]