Using SQL in VB coding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a button in a form that when clicked will select 3
field values from a specific record in a table and insert them into another
table. Here is what I have already coded:

Private Sub cmdUpdate_Click()
Dim HCR, HID, S, Se, Dw, Dd, As String

HCR = [Holding Company Rates]
HID = [HoldingCoID]
S = Me.[HoldingCoID]
Se = "SELECT HCR.[SEP] FROM HCR WHERE HCR.HID = S"
Dw = "SELECT HCR.[Dewitt] FROM HCR WHERE HCR.HID = S"
Dd = "SELECT HCR.[Deduct] FROM HCR WHERE HCR.HID = S"

INSERT INTO tblJob[(SEP[, Dewitt[, Deduct]])]
VALUES (Se[, Dw[, Dd])




End Sub

I am getting a compile error: Expected end of statment fom the line that
begins with INSERT INTO and another error from the line that starts with
VALUES. I cut and pasted the lines from the help in Access thought I was
doing the right thing, what is missing or what have I done wrong?
 
hi Frank,
I am trying to create a button in a form that when clicked will select 3
field values from a specific record in a table and insert them into another
table.
Click to expand...
Private Sub cmdUpdate_Click()
Click to expand...
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub
Click to expand...



mfG
--> stefan <--
 
Stefan

I tried this coding, but no values are being place in the Table tblJob.

Frank

Stefan Hoffmann said:
hi Frank,
I am trying to create a button in a form that when clicked will select 3
field values from a specific record in a table and insert them into another
table.
Click to expand...
Private Sub cmdUpdate_Click()
Click to expand...
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub
Click to expand...



mfG
--> stefan <--
Click to expand...
 
Frank:

Add the following to your statement:

Dim strSQL as String 'Declare string variable

strSQL = "INSERT INTO [yourTable] (yourFields) VALUES (yourValues)"

CurrentProject.Connection.Execute strSQL


As for passing your variables into the SQL statement, you need to separate
the variables by quotes. I am unclear on your variables (i.e. SEP does not
appear to be declared) so I will give you an example from something I used:


Dim strAgreeNo As String
Dim strSQL As String
Dim numExMaMil As Double


strAgreeNo = Me.LOANERAGREEMENTNUMBER
numExMaMil = (Me.NEXTMAINTMILES.Value - Me.ReturnMileage.Value)

strSQL = "INSERT INTO [BypassMaintRequired] (LOANERAGREEMENTNUMBER,
EXCESSMAINTMILES) " & "values (" & strAgreeNo & ", " & numExMaMil & ");"

CurrentProject.Connection.Execute strSQL

Note that all the variables are separated by ampersand (&). The double
quotes add a comma and a space. Finally note the semicolon at the end.

Let me know if this helps.


Thanks,

Randy


Frank said:
Stefan

I tried this coding, but no values are being place in the Table tblJob.

Frank

Stefan Hoffmann said:
hi Frank,
I am trying to create a button in a form that when clicked will select 3
field values from a specific record in a table and insert them into another
table.
Click to expand...
Private Sub cmdUpdate_Click()
Click to expand...
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub
Click to expand...



mfG
--> stefan <--
Click to expand...
Click to expand...
 
Randy

SEP, Dewitt and Deduct are field values and not variables. The SQL statement
that Stefan created seems to be what I want to do which is: Insert into
table [tblJob] SEP, Dewitt, and Deduct from the table [Holding Co Rates]
which has several entries, so I want to select the 3 field values from the
record that is equal to the [HoldingCoID] that is in the current form.

It seems that the coding that Stefan created is what I want to do, so why
doesn't it insert the value into table tblJob when I click the button in run
mode?

Randy Wayne said:
Frank:

Add the following to your statement:

Dim strSQL as String 'Declare string variable

strSQL = "INSERT INTO [yourTable] (yourFields) VALUES (yourValues)"

CurrentProject.Connection.Execute strSQL


As for passing your variables into the SQL statement, you need to separate
the variables by quotes. I am unclear on your variables (i.e. SEP does not
appear to be declared) so I will give you an example from something I used:


Dim strAgreeNo As String
Dim strSQL As String
Dim numExMaMil As Double


strAgreeNo = Me.LOANERAGREEMENTNUMBER
numExMaMil = (Me.NEXTMAINTMILES.Value - Me.ReturnMileage.Value)

strSQL = "INSERT INTO [BypassMaintRequired] (LOANERAGREEMENTNUMBER,
EXCESSMAINTMILES) " & "values (" & strAgreeNo & ", " & numExMaMil & ");"

CurrentProject.Connection.Execute strSQL

Note that all the variables are separated by ampersand (&). The double
quotes add a comma and a space. Finally note the semicolon at the end.

Let me know if this helps.


Thanks,

Randy


Frank said:
Stefan

I tried this coding, but no values are being place in the Table tblJob.

Frank

Stefan Hoffmann said:
hi Frank,

Frank wrote:
I am trying to create a button in a form that when clicked will select 3
field values from a specific record in a table and insert them into another
table.

Private Sub cmdUpdate_Click()
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub



mfG
--> stefan <--
Click to expand...
Click to expand...
Click to expand...
 
hi Frank,
It seems that the coding that Stefan created is what I want to do, so why
doesn't it insert the value into table tblJob when I click the button in run
mode?
Private Sub cmdUpdate_Click()
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub
Click to expand...
Click to expand...
Click to expand...
Append a dbFailOnError - CurrentDb.Execute "..", dbFailOnError - to
catch errors.
Use Form.Requery or Control.Requery to update the displayed data, if you
are inserting into a bound table.


mfG
--> stefan <--
 
Stefan

I appended the code with the dbFailOnError as you suggested and I am getting
a "Run-time error"3421" Data type conversion error. I am not sure what the
error means, I have checked the data types of all 3 fields in both tables and
they are the same because I copied and pasted them.

Stefan Hoffmann said:
hi Frank,
It seems that the coding that Stefan created is what I want to do, so why
doesn't it insert the value into table tblJob when I click the button in run
mode?
Private Sub cmdUpdate_Click()
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
End Sub
Click to expand...
Click to expand...
Append a dbFailOnError - CurrentDb.Execute "..", dbFailOnError - to
catch errors.
Use Form.Requery or Control.Requery to update the displayed data, if you
are inserting into a bound table.


mfG
--> stefan <--
Click to expand...
 
hi Frank,

Frank wrote
I appended the code with the dbFailOnError as you suggested and I am getting
a "Run-time error"3421" Data type conversion error. I am not sure what the
error means, I have checked the data types of all 3 fields in both tables and
they are the same because I copied and pasted them.
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
Click to expand...
Click to expand...
Click to expand...
Is your HoldingCoID alphanumeric? Then you have to change the SQL
statement to "... WHERE HoldingCoID = '" & Me.[HoldingCoID] & "'"


mfG
--> stefan <--
 
Stefan

I think that I was mistaken when said that it was the line that began with
WHERE. The whole statement was highlited in yellow. My fields SEP, Dewitt and
Deduct are data type Double, is there something that I need to define in
regards to that.

Stefan Hoffmann said:
hi Frank,

Frank wrote
I appended the code with the dbFailOnError as you suggested and I am getting
a "Run-time error"3421" Data type conversion error. I am not sure what the
error means, I have checked the data types of all 3 fields in both tables and
they are the same because I copied and pasted them.
CurrentDb.Execute "INSERT INTO tblJob(SEP, Dewitt, Deduct) " & _
"SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates]" & _
"WHERE HoldingCoID= " & Me.[HoldingCoID]
Click to expand...
Click to expand...
Is your HoldingCoID alphanumeric? Then you have to change the SQL
statement to "... WHERE HoldingCoID = '" & Me.[HoldingCoID] & "'"


mfG
--> stefan <--
Click to expand...
 
hi Frank,
I think that I was mistaken when said that it was the line that began with
WHERE. No.

The whole statement was highlited in yellow. My fields SEP, Dewitt and
Deduct are data type Double, is there something that I need to define in
regards to that.
Click to expand...
Your fields SEP, Dewitt and Deduct have the same data type (double) in
both tables, therefore the error must emerge from somewhere else.
Note:
Create a query with the following SQL statement to verify your source
data: SELECT SEP, Dewitt, Deduct FROM [Holding Company Rates].
Is your HoldingCoID alphanumeric? Then you have to change the SQL
statement to "... WHERE HoldingCoID = '" & Me.[HoldingCoID] & "'"
Click to expand...
Click to expand...
If your HoldingCoID is alphanumeric, e.g. A123, then the WHERE
conditions translates to "... WHERE HoldingCoID = A123". This can cause
the conversion error.
Use a MsgBox Me.[HoldingCoID] to display the value for your WHERE
condition. Try to filter your test query with this value.


mfG
--> stefan <--
 
Back
Top