Append to a table looping VBA code - need to add more data

[KashMarsh]

I added the 2 str's below. How do I add them into the SELECT INTO while still keeping the looping syntax? The field names in the table (tShipDetails) are Qty and StockID.
Any help is appreciated.
KM

Private Sub cmdShip_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim dteStartDate As Date
Dim intFreq As Integer
Dim strQty As String
Dim strStockID As String
Dim i As Integer
 
dteStartDate = Me.txtShipStart
intFreq = Me.txtShipDur
strTons = Me.txtQty
strItemID = Me.cboStockID
i = 0
 
Do Until i = intFreq
CurrentDb.Execute "INSERT INTO tShipDetails ( [StartDate]) SELECT #" & dteStartDate & "# AS FieldDate ;", dbFailOnError
dteStartDate = DateAdd("m", 1, dteStartDate)
i = i + 1
 
Loop
End Sub

[ADezii]

@KashMarsh

1. Why are you Declaring Quantity as a String?
2. Your Variable Declarations and Assignments seen to be out of sync.
3. Why is this Code being executed in the MouseUp() Event?
4. Stating the above, a General Template would be (Syntax not Verified):
   

   Dim dteStartDate As Date
   Dim intFreq As Integer
   Dim lngQty As Long
   Dim strItemID As String
   Dim i As Integer
    
   dteStartDate = Me.txtShipStart
    
   intFreq = Me.txtShipDur
   lngQty = Me.txtQty
   strItemID = Me.cboStockID
    
   i = 0
    
   Do Until i = intFreq
     CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
     dteStartDate & "#", lngQty & ", '" & strItemID & "')"
       dteStartDate = DateAdd("m", 1, dteStartDate)
   i = i + 1
   Loop

[KashMarsh]

ADezii thank you for your reply and expertise. I really appreciate you.

To answer your questions:
1. lng should be used instead of str, I just didn't know.
2. Yes, I noticed a typo on strTons - should be strQty, but really should be lngQty.
3. I prefer to use mouseUp instead of onClick, is there a better option?

Again, thanks you have helped me tremendously.
KM

[KashMarsh]

ADezii,
I'm getting a Run-Time error '3421'
Data Type conversion error.

It's highlighting this part:

CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
dteStartDate & "#", lngQty & ", '" & strItemID & "')"

StockID is a number data type in the table, not sure if that's the cause.

[ADezii]

@KashMarsh
Try the following changes:

Dim lngItemID As Long
 
CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
dteStartDate & "#", lngQty, lngItemID & ")"

[OldBirdman]

This is not directly addressing the issue but as a comment: OnMouseUp is not the same as OnClick.

I hate changing from mouse to keyboard to mouse to keyboard, and once on either, will stay as long as I can. I know the keyboard replacements for mouse-driven items. Using Tab, F4, and Arrow Keys I can dropdown and select from a combobox without triggering the MouseUp event. Therefore, this code would not work for me, and I would conclude the program had a bug.

MouseUp is valid at times, but I PERSONALLY don't feel that this is such a time.

[KashMarsh]

ADezii,
First of all, thank you for your help.

I have made the changes and still get the error on this part of the code:

CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  dteStartDate & "#", lngQty & ", '" & lngItemID & "')"

These two lines are highlighted in yellow but the arrow is pointing to the line starting with dteStartDate. Also, is there something I should check? Such as if I have something missing or wrong versions of any Jet Engines or anything of that sort?

Here is the entire code:

Private Sub cmdAddShipSched_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim dteStartDate As Date
Dim intFreq As Integer
Dim lngQty As Long
Dim lngItemID As Long
Dim i As Integer
 
dteStartDate = Me.txtShipStart
 
intFreq = Me.txtShipDur
lngQty = Me.txtQty
lngItemID = Me.cboStockID
 
i = 0
 
Do Until i = intFreq
  CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  dteStartDate & "#", lngQty & ", '" & lngItemID & "')"
    dteStartDate = DateAdd("m", 1, dteStartDate)
i = i + 1
Loop
 
End Sub

Also, OldBirdMan, thanks for your input. It's good to keep your comments in mind for users that are more keyboard oriented. The reason why I prefer to use onMouseUP is because most users I work with are heavy mouse users and prefer to click things. Since they are heavy mouse users, they know that they can cancel a click by moving off the button before completing the click. When I had code on the "on click" event, their way of canceling the click action didn't work. When I changed it to onMouseUp, cancelling a click worked as they expected. I have written confirmation prompts following onClick events but have stopped since using the onMouseUp and now only use it to confirm long or permanent actions.

[KashMarsh]

ADezii,
My apologies, I did not copy the code correctly on the 2nd fix. Here is the code snippet as modified:

CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  dteStartDate & "#", lngQty, lngItemID & ")"
    dteStartDate = DateAdd("m", 1, dteStartDate)

I now get the error:
"The expression On Mouse Up you entered as the event property setting produced the following error: Wrong number of arguments or invalid property assignment."

[ADezii]

CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
                   dteStartDate & "#, " & _
                   lngQty & ", " & lngItemID & ")"

[KashMarsh]

ADezii,
Much appreciated. Is there any reference I can consult that goes over this kind of syntax for Access VBA in detail? Particularly what order to place the variables and the characters used for each data type. I see that using a text data type is entered differently vs a number vs a date. This is similar to SQL but not exactly.
You have been most helpful, the changes worked beautifully!

[ADezii]

@KashMarsh
I'm glad everything worked out for you. As far as References go, they are too numerous to mention, but you may wish to consider an introductory Book on SQL (Beginner to Intermediate Level).

[NeoPa]

The original problem (a quite common one) was where the data was included in the string and where that ended.

I would also point out that printing a date exactly as it comes is not 100% reliable. For details see Literal DateTimes and Their Delimiters (#).

Welcome to Bytes!