If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Based on a lookup of Primary Key display data in the first table
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. |
#2
|
|||
|
|||
Based on a lookup of Primary Key display data in the first table
hi,
On 10.03.2010 22:04, Dwade wrote: 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 he 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 -- |
Thread Tools | |
Display Modes | |
|
|