1

I'm currently making a mockup Access Database with a customer POS built into it via forms. What I am trying to do is pull 3 pieces of info from a form: the size of the coffee, the cream amount, and the sugar amount, and put them in the tempOrder table for later use. Problem being I grab the ID for the coffee from another table. If this isn't possible, I guess I'll have to hardcode the values into the form somehow instead of referencing another table. Here is what I have so far to get the coffee ID and put it into the table:

INSERT INTO tempOrder ( ProductID )
SELECT ProductID 
FROM Product
WHERE ProductName =  FORMS!HotCoffee!hotCoffeeChoice'

-edit-

In essence, what I'm trying to do is pull the size of the coffee the user wants, the amount of cream, and the amount of sugar all from one form once a button is clicked, and put these into a table that holds all items that are in the current order. The cream and sugar are in text boxes, while the coffee sizes are in a list box, the contents of which are from a query finding all items in a seperate Product table that have the description 'coffee' (Small, medium, large). When I pull this info from the form, it is simply the size in plaintext. To pull and convert this back to the ProductID, I call the above code. The problem is that I would like the cream and sugar to be placed in the same row as the ProductID, and do not know how to go about this.

FORM has productName, cream, and sugar readily available

orderTable requires productID, cream, and sugar

I convert productName to productID and insert it into orderTable with the above code, making it currently impossible to also grab cream and sugar and put them in the same record.

Improve this question
5
  • So what's the problem? Commented Apr 23, 2018 at 0:58
  • I am unable to pull the productID from a query as well as the cream and sugar amount from the form. Currently, that is the code that I have to get the ProductID from another table, but i also need to get cream and sugar in the same query if possible. Commented Apr 23, 2018 at 1:09
  • You don't want to hard code anything, ever... especially for a homework question. It's the equivalent of giving up. ...however it's hard to give you any advice without knowing anything about the data, and forms, etc Commented Apr 23, 2018 at 4:39
  • 1
    Look up the proper syntax for an INSERT stmt. You're missing the key word VALUES. Try INSERT INTO tempOrder (ProductID) VALUES DLookup(etc). Look up how to use DLookup. Commented Apr 23, 2018 at 4:49
  • The INSERT INTO statement doesn't require VALUES. The above code works fine on its own. My problem isn't getting the values from the form, it's getting them at the same time. Commented Apr 23, 2018 at 5:00

1 Answer 1

Reset to default
1

You can insert multiple values using an INSERT statement by specifying the destination columns in the INSERT INTO clause of the query, and ensuring that each destination column has a corresponding column in the data returned by the SELECT statement:

INSERT INTO tempOrder ( ProductID, Cream, Sugar )
SELECT ProductID, Forms!HotCoffee!Cream, Forms!HotCoffee!Sugar
FROM Product
WHERE ProductName = Forms!HotCoffee!hotCoffeeChoice

However, using the product name as criteria in order to choose the record to insert from the Products table seems wrong to me. You don't need any sort of mapping from the Products table; you just want to insert a record into the tempOrder table.

Think of it this way: what happens if you have two products with the same name? Your query will return two records which match the name, and insert two corresponding records into tempOrder; i'm assuming this is not what you want.

Instead, I would suggest focusing on the information from the form, and inserting that directly into the tempOrder table.

You should be able to bind the value of the listbox to the ProductID column, while displaying the ProductName column:

  1. Set the listbox's RecordSource to return both the ProductID column and the ProductName column
  2. Set the listbox's ColumnCount to 2
  3. Set the listbox's ColumnWidths property to 0;. This will hide the first column and display only the second column in the listbox. However, data from the first column will still be available via the listbox, and can be returned for the currently selected listbox item via the Value property.

Then, you could write your query as follows:

INSERT INTO tempOrder (productID, cream, sugar)
VALUES (
    Forms!HotCoffee!hotCoffeeChoice.Value,
    Forms!HotCoffee!Cream,
    Forms!HotCoffee!Sugar
)

Reference:

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is exactly what I was looking for, thank you very much. I wasn't aware that I could just call the cream and sugar in the SELECT statement to grab the values.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.