Using List/Combo Boxes in a datasheet subform for data entry

[Tait]

I am trying to set up a form in a database for data entry.
The data is being used to create receipts for customers and tracking overall sales.
The Tables and Relations are as follows:

Receipts:

• Receipt ID (Primary)
• Transaction Date
• Name

Sales:

• Sale ID (Primary)

• Receipt ID (Foreign)

• Product ID (Foreign)

• Unit Price

• Quantity

Products:

• Product ID (Primary)
• Department
• Product Name
• Product Description

I would like to create a form with the Receipt fields above a datasheet subform containing the following fields:

Data Entry Form:

• Product Name
• Product Description
• Unit Price
• Quantity

Where I am getting hung up is using a list box or combo box within the datasheet with a dropdown to choose the product name.
After choosing the product name, I would like it to update the Product ID on the Sales table as well as showing the proper Product Description for the Product chosen.
I have managed to add a list box or combo box in the table, and had it show the right list, but it will not update the Product Name or Product Description.
Is what I am trying to do possible? If so, how would I go about doing it? If not, what other methods could I follow for a similar result.
My ultimate goal is to be able to enter all the linked information from one form. Then create a button that saves the record and brings up a corresponding Report with all the information in place.

I have a sample database, but it won’t let me attach it because I have no points, and this is my first time on one of these forums.

[Ratslinger]

Your description of what you want on the form is a bit confusing. It appears you actually have two tables to enter here.

The first is the Receipts. Simple enough - ID (auto), Date and Name.

Next you have another table record to complete - Sales (guessing items from receipt). This is just a table grid containing the mentioned fields.

Now you also have a table containing the different available items which you handle the entry through a different form.

The tables are linked as Receipt.ID->Sales.ReceiptID and Products.ID->Sales.ProductID with both as a one-to-many relation.

SOOOOO… This form you want. Receipts is the main form (individual fields) and Sales is the subform (grid control). In the grid control you want to use a list box for the Product ID field but show the Product Description. Easy enough. The ProductID field in the grid is to be a list box (as you already seem to suggest you have). On the Data tab of the list box properties, the Data field is set to the ProductID, Input required = Yes, Type of list contents = Sql, List content is SELECT statement to select Description & then ID from Products Table, and finally the Bound field = 1.

So the select statement displays the first item in the select statement. The Bound field states which field from the Select statement will be stored within the field for the record (fields numbers are relative to 0 so 1 = second field). Now the description is displayed and the ID is stored.

Sample attached for further clarification if needed: ReceiptsSales.odb

As you are new here, if you have any questions or comments, please do NOT use an Answer to do so. Rather respond with a add a comment found below the answer you are responding to (may be more than one answer).

If this answers your question please click on the (upper left area of answer).

[Tait]

Thank You.
I believe I had misunderstood how the bound field operates, and was trying to store the description instead of the ID. I will try adjusting things when I get home tonight and then let you know if I have any further questions.

[Tait]

In all the tutorials I’ve watched, the bound field had not yet been clearly defined. Thank you for your time and experience. You have been a tremendous help.

[irsmalik]

Hello
I have the same problem… Main Form… Subform… Combo box selection… I want to see the attached file.
I have download ReceiptSales.odb… how to run it… it is no mdb
kindly guide me how to open this attached file.

[Ratslinger]

@irsmalik The sample is a LibreOffice Base file. If LO Base is is installed and operative on your system, the sample runs like any other Base file.