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
11 3205 @KashMarsh - Why are you Declaring Quantity as a String?
- Your Variable Declarations and Assignments seen to be out of sync.
- Why is this Code being executed in the MouseUp() Event?
- 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
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
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.
@KashMarsh
Try the following changes: - Dim lngItemID As Long
-
-
CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
-
dteStartDate & "#", lngQty, lngItemID & ")"
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.
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.
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."
- CurrentDb.Execute "INSERT INTO tShipDetails ([StartDate], [Qty], [StockID]) VALUES (#" & _
-
dteStartDate & "#, " & _
-
lngQty & ", " & lngItemID & ")"
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!
@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 32,554
Expert Mod 16PB
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JMCN |
last post by:
hi
i need some advice on whether if it would be better to use an append
query or an update query. here is the situation, i have linked another
database table to my current database. then i...
|
by: Danny |
last post by:
I want to extract a subset of fields from one table into another
the master table has many fields
the subset has about half, but still many.
Is there a way I can just append the master into the...
|
by: David Barger |
last post by:
Greetings,
It appears that an Append Query I run in Access XP is randomly failing
to append a field.
I have payroll data being entered into a payroll database. This data
is exported daily to...
|
by: Wayne Wengert |
last post by:
I am using VB ASP.NET. In my page I convert an uploaded XML file to a
dataset as follows:
Dim ds1 As DataSet = New DataSet
ds1.ReadXml(strPathName, XmlReadMode.Auto)
Now I want to append...
|
by: Reggie |
last post by:
Hi and TIA. I have an OleDB connection to an Access db which I connect to
and place data from a table into a recordset. What I want to do is copy
this recordset to an SQL table. I have no...
|
by: robboll |
last post by:
When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.
If I have an Oracle ODBC connection to...
|
by: wvmbark |
last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.
Every day we...
|
by: ray well |
last post by:
i need to copy in code a table from a legacy dbase III file into an access
mdb file.
both source and destination tables have the same table names, field names,
field types, & field sizes.
the...
|
by: dougmeece |
last post by:
Morning Everyone...
I have a table that needs to be append to and also updated. All the fields in the table are populated with data from the text boxes and combo boxes on a form. The Date...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
| |