Based on a lookup of Primary Key display data in the first table

[Dwade]

I have 4 tables (amongst about 20) that are related in a DB for surplus
material

1st Table is SurplusReport_TBL primary key is SurplusNbr. The table holds
the information on items surplus to needs. To donated, salvaged, sold, or
dumped etc. One surplus report per shipment.

2nd Table is SurplusListing_TBL and the primary key is SurlusNBR. This
tables hold individual items, many for each individual surplus. contains
ItemNBR this is a counter of all items being surplused, autonumber.

in Surplus Listing is SurplusItemCount and also SurplusDescription,
AssetNBR, and ConditionCode, (and a few others)

Table 3 is Donation_TBL and the primarykey is DonationID. This table holds
information an organization that material may be donated to.

Table 4 is DonationListing primarykey is DonationId. Lookup for
SurplusItemCount from SurplusListing_TBL and on selection adds the data to
the table.

Now all this works on the form and sub form.

I want the subfrom to also show the SurplusDescription, AssetNBR, and
ConditionCode from the table but not store it.

I can't get the information to just display for some reason I have to store
it and I don't want to store information twice.

 

[Stefan Hoffmann]

hi,

I can't get the information to just display for some reason I have to store
it and I don't want to store information twice.

As far as I understand yourproblem, you have a couple of possible
solutions here:

1) Using DLookup() in the TextBoxes Control Source, e.g.
=DLookup("value", "yourTable", "id = " & [lookupId])

2) Using a LEFT JOIN in the subforms Record Source.

3) Using another subform only displaying the lookup values.

4) Populate the TextBoxes in the subforms On Current event, e.g.

Private Sub Form_Current()

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT * FROM yourTable WHERE id = " & Me![lookupId] & ";"
Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapShot)

txtCtl1.Value = rs![field] '...

rs.Close
Set rs = Nothing

End Sub


mfG
--> stefan <--