Insert record including value from user

[scottb]

Hi everyone,
I have a form frmOutOfStock with an unbound text box (name = quicksearch). The rowsource is qryOutOfStockItems - with fields ItemID, UPC, Item, Vendor, LastSale, and Cost fields. This text box displays orderable items. On frmOutOfStock there is also subfrmRestock. The data source is tblRestock containing fields: RestockID, ItemID, UPC, Item, Vendor, Cost, LastSale, Quantity, and AddedToListDate. This is the “shopping list” of items to reorder.

I am trying to create an event for Quicksearch which when an item is double clicked the following happens:
-Insert the clicked record values from Quicksearch into tblRestock (including fields: ItemID, UPC, Item, Vendor, Cost, LastSale
-Add the current date to tblRestock.AddedToListDate for the inserted record
-Prompt the user via msgbox or inputbox for a value to insert into tblRestock.Quantity field (text) for the inserted record.
-Refresh subfrmRestock.

I have the sql insert partially working but I don’t know how to prompt and add Quantity and current date to the inserted/current record.

Appreciate any help.
Thanks.
-Scott

[HansV]

A text box doesn't have a row source. Perhaps QuickSearch is a list box instead of a text box?

[scottb]

Sorry Hans. Yes it is an unbound list box. thanks.

[HansV]

I will assume that:
ItemID is a number field.
UPC, Item, Vendor and Quantity are text fields.
LastSale is a date/time field.
Cost is a number or currency field.

Code: Select all

Private Sub QuickSearch_DblClick(Cancel As Integer)
    Dim strQuantity As String
    Dim strSQL As String
    strQuantity = InputBox("Please enter the Quantity for the new record.")
    strSQL = "INSERT INTO tblRestock (ItemID, UPC, Item, Vendor, LastSale, " & _
        "Cost, Quantity, AddedToListDate) VALUES(" & Me.QuickSearch.Column(0) & _
        ", '" & Me.QuickSearch.Column(1) & "', '" & Me.QuickSearch.Column(2) & _
        "', '" & Me.QuickSearch.Column(3) & "', #" & Format(Me.QuickSearch.Column(4), _
        "mm/dd/yyyy") & "#, " & Me.QuickSearch.Column(5) & ", '" & strQuantity & _
        "', Date())"
    CurrentDb.Execute strSQL, dbFailOnError
    Me.subfrmRestock.Requery
End Sub

[scottb]

This is going to be a huge help. Working perfectly. Thank you Hans.

[scottb]

Hans,
I added a field “Category” (text) to the query and updated the column count to 7 and the sql to the following an am receiving a “Number of query values and destination fields are not the same” error.
The category field is the seventh/last field in the query and list box.

strQuantity = InputBox("Please enter the Quantity for the new record.")
strSQL = "INSERT INTO tblRestock (ItemID, UPC, Item, Vendor, LastSale, " & _
"Cost, Quantity, AddedToListDate) VALUES(" & Me.QuickSearch.Column(0) & _
", '" & Me.QuickSearch.Column(1) & "', '" & Me.QuickSearch.Column(2) & _
"', '" & Me.QuickSearch.Column(3) & "', #" & Format(Me.QuickSearch.Column(4), _
"mm/dd/yyyy") & "#, " & Me.QuickSearch.Column(5) & ", '" & Me.QuickSearch.Column(6) & _
"', '" & strQuantity & "', Date())"
CurrentDb.Execute strSQL, dbFailOnError

Thank you for your help.
-Scott

[HansV]

You should add the field name to the list after INSERT INTO tblRestock, in the correct place. For example, if Category is between Cost and Qantity:

Code: Select all

    strSQL = "INSERT INTO tblRestock (ItemID, UPC, Item, Vendor, LastSale, " & _
        "Cost, Category, Quantity, AddedToListDate) VALUES(...

[scottb]

I walked right over that. Thanks Hans.