Update Subform using apend query

[burrina]

I hope I can explain this well enough.Some of you have my db already without of course the latest revisions. I need for the append query I run to update the subform which is located on my main form. Main form is named TimeCards and subform is named TimeCardCatAndProdSub. I am using a Listbox to tag my product selection then append to the Products table. I also need a way to make it add those selected products to my subform named TimeCardCatAndProdSub. Listbox form is named FProductMultiItemSelect.
I run 2 append queries at a time using the Append command button on the form FProductMultiItemSelect, one named QAppendToProducts and the other named QTimeCardCatAndProdSub.

Warning, "Please don't try this on an empty stomach or after drinking!!!" It will make you lose your religion, I know, I have already lost mine.

Thanks in Advance,

Dave

[June7]

Open TimeCards and immediately get error message: The form name 'TimeCardPaymentSub' is misspelled or refers to a form that doesn't exist. I deleted the SourceObject property of the subform control so not annoyed by that message. I also reset form ShortcutMenu property to Yes because I like the shortcut menu when I debug. I turn off shortcut menu in project Options and override with shift key when I open project.

Do the append queries successfully add records?

Is TimeCards already open when the append queries are executed? Then just need to requery the subform? Try:
Forms!TimeCards!TimeCardCatAndProdSub.Requery

[burrina]

Yes, the append queries work correctly. Forgot to remove frustrating stuff form scaled down db, sorry!

Yes, the TimeCards form will be open. NO, don't think that will work since I tried it. It adds a blank record though.

[burrina]

One more thing, for some reason the Qty does NOT get updated when adding items to the tblOtherTable, it does however update the OtherValue correctly.
Maybe force it somehow to use the stored value of OtherValue, ? Ran into a problem with error message ; Enter Parameter Value for Forms!TimeCards!TimeIDA
Had to make the control visible for it to go away.All my hidden controls are in RED BTW ! TimeIDA is in the bottom left corner of TimeCards form. Sorry for the confusion.

[June7]

So user has to click AddItems to create and populate temporary table tblOtherTable (where is code to create this table?) then they have to click Append to run the query. Personally, instead of creating temp table and adding records to that table in a loop, I would just append directly to the Products table in that loop. I would make only one button to accomplish this.

For Each varItem In ctl.ItemSelected
CurrentDb.Execute "INSERT INTO Products([Product Name], [Unit Price], [TaxStatus], [Qty] " & _
"VALUES('" & ctl.Column(1, varItem) & "', " & _
ctl.Column(2, varItem) & ", '" & _
ctl.Column(3, varItem) & "', " & _
InputBox("Enter a Numeric Quantity for product " & ctl.Column(1, varItem))
Next

Why is the Qty field in the listbox? Isn't this is the value requested by the InputBox?

[burrina]

So, to be clear, it will look like this?

If Me.ListOne.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 Item"
Exit Sub
End If

Set ctl = Me.ListOne
For Each varItem In ctl.ItemsSelected
rs.AddNew
CurrentDb.Execute "INSERT INTO Products([Product Name], [Unit Price], [TaxStatus], [Qty] " & _
"VALUES('" & ctl.Column(1, varItem) & "', " & _
ctl.Column(2, varItem) & ", '" & _
ctl.Column(3, varItem) & "', " & _
InputBox("Enter a Numeric Quantity for product " & ctl.Column(1, varItem))
rs.Update
Next

[June7]

Or could be:

If Me.ListOne.ItemsSelected.Count = 0 Then
    MsgBox "You must select at least 1 Item"
Else
    Set ctl = Me.ListOne
    For Each varItem In ctl.ItemsSelected
        rs.AddNew
        CurrentDb.Execute "INSERT INTO Products([Product Name], [Unit Price], [TaxStatus], [Qty] " & _
        "VALUES('" & ctl.Column(1, varItem) & "', " & _
        ctl.Column(2, varItem) & ", '" & _
        ctl.Column(3, varItem) & "', " & _
        InputBox("Enter a Numeric Quantity for product " & ctl.Column(1, varItem))
        rs.Update
    Next
End If

[burrina]

I get a syntax error in INSERT INTO statement.

(also, just thinking out loud, what if the user has MultiItems form open and either they or another user adds items to the table? If you navigate to another record, what then? Will it update the record you were on, same TimeID or what? Finally, will this add the products to the subform TimeCardCatAndProdSub and if not then, no new calculations will be made for that order.) ? ( Maybe you cant get there from here)

Private Sub cmdAddRecords_Click()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rs = db.OpenRecordset("Products", dbOpenDynaset, dbAppendOnly) ' Changed from tblOtherTable


'New Code
If Me.ListOne.ItemsSelected.Count = 0 Then ' Make sure a seleciton has been made
MsgBox "You must select at least 1 Item"
Else
Set ctl = Me.ListOne
For Each varItem In ctl.ItemsSelected
rs.AddNew
CurrentDb.Execute "INSERT INTO Products([Product Name], [Unit Price], [TaxStatus], [Qty] " & _
"VALUES('" & ctl.Column(1, varItem) & "', " & _
ctl.Column(2, varItem) & ", '" & _
ctl.Column(3, varItem) & "', " & _
InputBox("Enter a Numeric Quantity for product " & ctl.Column(1, varItem))
rs.Update
Next
End If


ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select

End Sub

[June7]

Sorry, I missed paren:

...[TaxStatus], [Qty])

Can remove all the recordset related code.

[burrina]

I still get the same error message.
CurrentDb.Execute "INSERT INTO Products([Product Name], [Unit Price], [TaxStatus], [Qty]) " & _

[June7]

If you hadn't guessed, I was writing the code directly into post, not testing. So I finally put it in the db and tested and debugged - parens will kill me, another I missed.

InputBox("Enter a Numeric Quantity for product " & ctl.Column(1, varItem)) & ")"

Why don't you experiment with debugging the code before doing this edit. To debug sql statement, useful to use a variable for the sql string. Set breakpoint in code and after the variable is populated print it to the immediate window. Link at bottom of my post has guidelines for debugging techniques.

[burrina]

I am trying to clear the list of selected items form my listbox named ListOne, here is my code that does NOT work.

For i As Integer = (ListOne.SelectedItems.Count - 1) To 0 Step -1 ' This line of code is the problem. It highlites As and points to it as the problem.
ListOne.Items.Remove (ListOne.SelectedItems(i))
Next
__________________________________________________ _____________________________________
Tried this code as well.;
' Determine if the currently selected item in the ListBox ' is the item displayed at the top in the ListBox. If listOne.TopIndex <> listBox1.SelectedIndex Then
' Make the currently selected item the top item in the ListBox.
ListOne.TopIndex = ListOne.SelectedIndex
End If
' Remove all items before the top item in the ListBox. Dim x As Integer
For X = ListOne.SelectedIndex - 1 To 0 Step -1
ListOne.Items.RemoveAt (X)
Next X

' Clear all selections in the ListBox.
ListOne.ClearSelected() ' This is where it errors out, it says expected ='s

[June7]

Review http://allenbrowne.com/func-12.html

[burrina]

Thanks, with an adaption, I was able to make that work.