Autofill record information in datasheet subform (1 Viewer)

[RogueVector]

[Access 2007]

Alrighty, gents, thanks in advance for helping me with this problem as I do believe its a little tricky:

I have a table "Items" with a text field (DeliveryCode) that is linked in a many-to-one relationship with a table of DeliveryCodes (the only column, as the primary key).

The Items field contains information on each item we had/have in inventory. (Item ID, width, height, thick etc.).

I have a form (frmDeliveryList) that navigates by the Delivery Code table with Record Source "SELECT Delivery.DeliveryCodes FROM Delivery;" that works just fine in finding all items that have the selected DeliveryCode and displaying them in the subform inside of frmDeliveryList (Master and Child fields are linked).

The subform itself has a combobox that lists all unsent items (SELECT Items.ItemID, Items.Sent FROM Items WHERE ((Items.Sent) = "No"); as well as text boxes for displaying the rest of the relevant information. That all works perfectly fine in helping autocomplete the item ID when typing in a new one.

There is a query (qryDelivery) to find all records in the Items table that has a delivery code matching that of the one selected by the frmDeliveryList.

qryDelivery has an INNER JOIN (FROM Deliveries to Items) for Deliveries.DeliveryCode = Items.DeliveryCode.

What I want the subform to do is have it so that when I enter a new ItemID in the subform, it will automatically pull the rest of the information on the item from its table rather than think its a new item.

 

[Pat Hartman]

To pull in "lookup" fields for display, use a query as the RecordSource for the form. The query joins the main table to the lookup table. When you do this, it is best to set the locked property for all the lookup fields to yes so the user doesn't accidentally update them.

 

[RogueVector]

Pat, that's been done and is working fine. When I navigate to a specific JobCode with the parent form, it displays all items that are tagged with that particular JobCode in the Item record's JobCode field/column.

However what I want to do is add existing Items from the Items table by typing their ItemNo to the datasheet view subform. In effect, adding the selected JobCode to that Item's JobCode field.

 

[Pat Hartman]

The concept is the same. Use a combo to select JobCode. If you have data you want to display from the job code table, use a query as the RecordSource for the subform that joins the main table to the job code table.

 

[RogueVector]

Pat, again I say it: I have the JobCode links to Items table working fine. The Jobs table (which I use to store JobCodes) only have one column with JobCodes in them. The Items table has a field that contains JobCodes, and they are linked with a one to many relationship.

Displaying Items with a selected JobCode works just fine.

 

[Pat Hartman]

I ment to say ItemNo rather than JobCode. Does that help you? In the subform, you select an ItemNo from a combo and the query will populate all the lookup fields. If you are asking to COPY data from the lookup table into columns of the other table, that is a different thing and probably unnecessary. Post a picture of the form with a description of what it is doing and what you want it to do. Also post the query used as the RecordSource for the subform.