Copy Parts of one Table to Another on Form Load

[ajochung]

Access 2013

Hey,

I have a table that I would like to contain columns from other tables when the form containing the table is loaded.

Example:

I open a form to input information into the table 'Equipment Input', but I want Equipment Input to be partially loaded with columns from another table 'Equipment and Rates.'

Thank you for your help.

 

[David R]

Google 'normalization'. You don't need to copy that information from record to record, you store it in the appropriate table and then join it together with QUERIES to get the extra data you need.

 

[ajochung]

Hey,

I've tried to manipulate the tables using a query but I don't know how to make it work for my purposes. I also know there is a recordset solution for my purpose but am not familiar yet with that method.

The reason I'd like to copy in parts of a different tables column is so that I can enter in new information on the same table (different columns).

 

[David R]

Okay, post your table structures then, because I'm not convinced of the need.

You can format them like this, since you don't have enough posts to do screenshots:

tableEquipment
---------------
EquipmentID (PK)
EquipmentModel
EquipmentPurchaseDate
etc

 

[ajochung]

Okay, here is the gist of it:
tblEquipmentList
---------------
EquipmentID
EquipmentName
EquipmentRate

tblEquipmentInput
-----------------
Checkbox
EquipmentName
QuantityUsed
HoursUsed

tblEquipmentTransaction
-----------------------
EquipmentName
QuanityUsed
HoursUsed
EquipmentRate
TotalCost

What I would like to do is use tblEquipmentInput as a kind of UI where a user can see all of the equipment at one time (scrollable) and check off which equipment they've used along with the quantity and hours. Then they would press a button once finished and only the used equipment along with the user inputted information would be recorded for that day - tblEquipmentTransaction.

 

[Pat Hartman]

Your problem is that you are not using the correct column to make the relationships. EquipmentID is the PK of tblEquipmentList and so that is the column that should be used in tblEquipmentInput and tblEquipmentTransaction to implement the relationship. You may have chosen to define the foreign keys as lookups and that would certainly confuse the issue. If you have done that, get rid of the lookup and replace the Name fields with the ID field. In a proper relationship, you store the ID as the FK. To get the text value, you use a query that joins the tables or on a form, you can use a combo box.

 

[ajochung]

Hey Pat,
EquipmentID is already within the two tables (I shorthanded the table fields), but I still don't know see how to use tblEquipmentInput as a UI - especially using the checkbox field as a means to filter which records should be transferred to tblEquipmentTransaction

 

[jdraw]

You should review the purpose of Queries, and review Normalization and queries of two or more tables.

Can you write a 2 or 3 line definition for each of your tables? I think that will help you understand comments from Pat and David R.

Here is a tutorial that may help also.

Good luck