CODEWITHSUNDEEP
sqlms-accessvb6vba
Zorpho
Zorpho

Reputation: 182

VB6 Access update query not updating table

I am trying to add a number sequentially to a field for a job number in a do loop. My code is not breaking but it is not adding the records to the table. The code is written in a function that is being called when the access form button is pressed to update the records. Below is my code:

Dim NumofBatches As Integer
    NumofBatches = [Batches]
    Dim startnum As Integer
    startnum = 1
    Dim jobnum As String
    jobnum = [JobNumber]

    Do While startnum <= NumofBatches
        Dim mynumString As String
        mynumString = startnum
        DoCmd.RunSQL "INSERT INTO Production (CardCode,JobItemNo,JobIndex,DrawingRef,DRDescription,[CreationDate],Quantity,FinishDate,LastLocation,DateLastMoved) VALUES ('" & jobnum & mynumString & "', ItemNumber, JobNumber, DrawingRef, DRDescription, [CreationDate], Quantity, FinishDate, LastLocation, DateLastMoved)"
        startnum = startnum + 1
    Loop

I realize I will have duplicated rows of fields except for the cardcode field which is what I am trying to achieve. The cardcode fields should be sequential in each row for the jobnumber. An example, 100011, 100012, where 10001 is the job number and it is adding 1, 2, etc. sequentially.

Upvotes: 0

Views: 512

Answers (1)

John Mo
John Mo

Reputation: 1326

You have a couple of problems. The first is in the field list in the INSERT part of your SQL statement. Unless you have a column in your Production table named "CardCode + mynumString" (and if you did it would have to have square brackets around it because it has a space and a symbol in it) it's not going to work. Inside the parentheses after INSERT INTO <TableName> you have to list column names from the table you are inserting into. Fix your target column names first.

Next you have a SELECT statement as the source of values you're inserting. Unless the values you need are found in a table, you should probably use a VALUES list:

INSERT INTO <TableName> (Col1, Col2, ...) VALUES (Val1, Val2, ...)

The final issue is that you have to concatenate your variables into the SQL string. I'm going to assume that [Text90] is a control with your CardCode value in it and you're wanting to concatenate it with mynumString. That would look something like this:

DoCmd.RunSQL "INSERT INTO Production (Col1, Col2, ...) VALUES ('" & [Text90] & mynumString & "', Val2, ...)"

Note that you have to resolve your value outside of the quoted SQL string and concatenate that value into the string and set all of that off with single-tick quotes inside the SQL string.

I find it handy to build dynamic SQL strings like this using a variable and then execute it as:

DoCmd.RunSQL mySQLVariable

Having the SQL string in a variable makes it a little easier to spot errors in the concatenation if you inspect the variable during debugging or if you just dump the value to the immediate window with Debug.Print.

Upvotes: 2

Related Questions