Auto-populate new records in a Form

[dhicks]

First-time Poster here! I have an Access form that contains a subform. I want to create a Command button that will open a combo box list where the user can select the item they want (from Table A). When the item is selected, I want a new record to open in the subform (Table B) and auto-populate the first 3 fields with corresponding information from Table A. I'm stuck as to what commands to use in order to achieve this. Can anyone help? Many thanks.

[Homegrownandy]

Table B should have a relationship to table A. Not duplicate data. This is the whole point of a relational database.

Could you explain what you are trying to achieve with your project. (without saying what you want access to do)

Often especially when you are new.. the path you want to follow is not the correct one.

[dhicks]

Tables A & B do have a relationship (one-to-many). Table A is a list of inventory items that can be checked out or in by a user. This table contains fields such as the item name, model, serial number, etc. Table B is a history of what is checked-out/in, along with user contact information, etc. When an item is checked out, I want to select the specific item from a drop-down box (from Table A), which will then populate a new record in the subform with the item name, model and serial number (appending to Table B). The rest of the fields for that record are filled out manually. I hope I am explaining this correctly. Thanks for responding!

[aytee111]

You don't need a "command button that will open a combobox". Have the combobox on a form, the user selects an entry from the list.

In the AfterUpdate event of the combobox:
- run a query to add a record to tableB (either an existing query with DoCmd.OpenQuery, or you can use DoCmd.RunSQL "...")
- tableB is the record source of the subform
- link the subform to the main form using the Link properties
- requery the subform (Me!subformname.Requery)

[dhicks]

Hi aytee111, Yes I had gone ahead and made it a combo box after my first post as I realized the command button wasn't going to work. I created an Append query to insert the new record into the subform. However, when I select an item from the combo box on the form, I end up getting ALL the records from Table A, and it never updates Table B. It just returns the results in a temporary table. I've attached the SQL code. I hope you can show me where I'm missing the mark.

INSERT INTO tblChkInOutHist ( Item, Service_Tag_SN, Make_Model )
SELECT DISTINCTROW tblInventory.Item, tblInventory.Service_Tag_SN, tblInventory.Make_Model
FROM tblInventory
WHERE (((tblInventory.Item)="Laptop 1" Or (tblInventory.Item)="Laptop 2" Or (tblInventory.Item) Like "Laptop2*" Or (tblInventory.Item)="Laptop 4" Or (tblInventory.Item)="Projector 1" Or (tblInventory.Item)="Projector 3" Or (tblInventory.Item)="Screen"))
ORDER BY tblInventory.Item;

[aytee111]

Rethink!

Create a form with tblChkInOutHist as the record source.
Have an unbound combobox with a list of inventory items - have those three fields as DISPLAY ONLY once the item has been selected.
In the AfterUpdate event of the combobox,
DoCmd.GoToRecord,,,acNewRec
Me!InventoryID=Me!combobox

The only field that is common between the two tables is InventoryID - or whatever the PK is named on tblInventory.

The subform is optional and can display the checkout history for that inventory item if desired. It will be read-only and linked with InventoryID.

[dhicks]

Forgive my ignorance.....how do I set the fields as DISPLAY ONLY once the item has been selected?

[aytee111]

What I am saying is that these fields must not be stored on the checkout history table. Do you need to show the data to the user after they select or is inventory item enough for identification?