By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

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

P: 7
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShip_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. Dim dteStartDate As Date
  3. Dim intFreq As Integer
  4. Dim strQty As String
  5. Dim strStockID As String
  6. Dim i As Integer
  7.  
  8. dteStartDate = Me.txtShipStart
  9. intFreq = Me.txtShipDur
  10. strTons = Me.txtQty
  11. strItemID = Me.cboStockID
  12. i = 0
  13.  
  14. Do Until i = intFreq
  15. CurrentDb.Execute "INSERT INTO tShipDetails ( [StartDate]) SELECT #" & dteStartDate & "# AS FieldDate ;", dbFailOnError
  16. dteStartDate = DateAdd("m", 1, dteStartDate)
  17. i = i + 1
  18.  
  19. Loop
  20. End Sub
Jun 10 '09 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,680
@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):
    Expand|Select|Wrap|Line Numbers
    1. Dim dteStartDate As Date
    2. Dim intFreq As Integer
    3. Dim lngQty As Long
    4. Dim strItemID As String
    5. Dim i As Integer
    6.  
    7. dteStartDate = Me.txtShipStart
    8.  
    9. intFreq = Me.txtShipDur
    10. lngQty = Me.txtQty
    11. strItemID = Me.cboStockID
    12.  
    13. i = 0
    14.  
    15. Do Until i = intFreq
    16.   CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
    17.   dteStartDate & "#", lngQty & ", '" & strItemID & "')"
    18.     dteStartDate = DateAdd("m", 1, dteStartDate)
    19. i = i + 1
    20. Loop
Jun 10 '09 #2

P: 7
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
Jun 10 '09 #3

P: 7
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.
Jun 10 '09 #4

ADezii
Expert 5K+
P: 8,680
@KashMarsh
Try the following changes:
Expand|Select|Wrap|Line Numbers
  1. Dim lngItemID As Long
  2.  
  3. CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  4. dteStartDate & "#", lngQty, lngItemID & ")"
Jun 11 '09 #5

100+
P: 675
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.
Jun 11 '09 #6

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  2.   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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddShipSched_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2. Dim dteStartDate As Date
  3. Dim intFreq As Integer
  4. Dim lngQty As Long
  5. Dim lngItemID As Long
  6. Dim i As Integer
  7.  
  8. dteStartDate = Me.txtShipStart
  9.  
  10. intFreq = Me.txtShipDur
  11. lngQty = Me.txtQty
  12. lngItemID = Me.cboStockID
  13.  
  14. i = 0
  15.  
  16. Do Until i = intFreq
  17.   CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  18.   dteStartDate & "#", lngQty & ", '" & lngItemID & "')"
  19.     dteStartDate = DateAdd("m", 1, dteStartDate)
  20. i = i + 1
  21. Loop
  22.  
  23. 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.
Jun 11 '09 #7

P: 7
ADezii,
My apologies, I did not copy the code correctly on the 2nd fix. Here is the code snippet as modified:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  2.   dteStartDate & "#", lngQty, lngItemID & ")"
  3.     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."
Jun 11 '09 #8

ADezii
Expert 5K+
P: 8,680
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
  2.                    dteStartDate & "#, " & _
  3.                    lngQty & ", " & lngItemID & ")"
Jun 12 '09 #9

P: 7
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!
Jun 12 '09 #10

ADezii
Expert 5K+
P: 8,680
@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).
Jun 12 '09 #11

NeoPa
Expert Mod 15k+
P: 31,712
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!
Jun 17 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.