Passing Variables

[Guest]

Good morning
I would like to see how to make the VALUES in an SQL INSERT INTO statement accept variables. It's rather messy to keep doing this

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES (Forms!Formname.username, Forms!Formname.userdiv, Forms!Formname.requestTS)" '<--- wrapped, not a line brea

What I'd like to be able to do (and I know the syntax is wrong) is this

dim user as strin
dim div as strin
dim TS as dat

user = Forms!Formname.usernam
div = Forms!Formname.userdi
TS = Forms!Formname.RequestT

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES (user, div, TS)" '"X

While it is a bit longer, it's significantly neater code in organized blocks. Can someone please provide me with the correct syntax for the code with the "X" next to it

Thank you very much in advance
Derek

 

[Newbie]

Good morning,
I would like to see how to make the VALUES in an SQL INSERT INTO statement

accept variables. It's rather messy to keep doing this:

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES

(Forms!Formname.username, Forms!Formname.userdiv, Forms!Formname.requestTS)"
'<--- wrapped, not a line break

What I'd like to be able to do (and I know the syntax is wrong) is this:

dim user as string
dim div as string
dim TS as date

user = Forms!Formname.username
div = Forms!Formname.userdiv
TS = Forms!Formname.RequestTS

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES (user, div, TS)" '"X"

While it is a bit longer, it's significantly neater code in organized

blocks. Can someone please provide me with the correct syntax for the code
with the "X" next to it?

 

[solex]

(1) Create a stored parameterized query, as an example:

PARAMETERS [@PartnerID] Long, [@DistributionScenarioName] Text ( 50 ),
[@Percent] IEEEDouble;
INSERT INTO Temp_PDS ( PartnerID, DistributionScenarioName, [Percent] )
SELECT [@PartnerID], [@DistributionScenarioName], [@Percent];

(2) in your code use dao to call the stored parameterized query, as an
example:

Dim cmd As DAO.QueryDef

Set cmd = CurrentDb.QueryDefs("sp_Temp_PDS_Add")
With cmd
.Parameters("@DistributionScenarioName").Value = rng.Cells(2,
column)
.Parameters("@PartnerID").Value = rng.Cells(row, 1)
.Parameters("@Percent") = rng.Cells(row, column)
.Execute
End With

In my opinion this is much cleaner and faster especially if you will be
calling this stored query many times.

Dan

Good morning,
I would like to see how to make the VALUES in an SQL INSERT INTO statement

accept variables. It's rather messy to keep doing this:

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES

(Forms!Formname.username, Forms!Formname.userdiv, Forms!Formname.requestTS)"
'<--- wrapped, not a line break

What I'd like to be able to do (and I know the syntax is wrong) is this:

dim user as string
dim div as string
dim TS as date

user = Forms!Formname.username
div = Forms!Formname.userdiv
TS = Forms!Formname.RequestTS

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES (user, div, TS)" '"X"

While it is a bit longer, it's significantly neater code in organized

blocks. Can someone please provide me with the correct syntax for the code
with the "X" next to it?

 

[Newbie]

As I am not sure of your datatypes and table structure . . . .

eg This worked for me

Sub insertrec()

expire = "#12/10/04#"
Customer = "Z"
strSQL = "Insert Into tblWarranties (WarrantyExpire, Cust) VALUES ("
strSQL = strSQL & expire & ", '" & Customer & "')"

DoCmd.RunSQL strSQL
End Sub


HTH

Good morning,
I would like to see how to make the VALUES in an SQL INSERT INTO statement

accept variables. It's rather messy to keep doing this:

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES

(Forms!Formname.username, Forms!Formname.userdiv, Forms!Formname.requestTS)"
'<--- wrapped, not a line break

What I'd like to be able to do (and I know the syntax is wrong) is this:

dim user as string
dim div as string
dim TS as date

user = Forms!Formname.username
div = Forms!Formname.userdiv
TS = Forms!Formname.RequestTS

docmd.runsql "Insert Into tblTABLE (username, userdiv, requestTS) VALUES (user, div, TS)" '"X"

While it is a bit longer, it's significantly neater code in organized

blocks. Can someone please provide me with the correct syntax for the code
with the "X" next to it?

 

[Guest]

Thank you, all. It seems the trouble I was having was with the proper use of the single and double quotation marks. I really appreciate all the insight. Now, I'm on the DAPs... I'm scared. :

----- TF wrote: ----

If you are getting those variables from a form or an inputbox, here's a method that I use. It just surrounds the variables with quotes and formatting (like #'s and datevlaue) to get it to be part of the SQL statement

Dim TheExpDate as Dat
TheExpDate = "5/1/03" ' or read this in from a for

DoCmd.RunSQL ("Insert Into tblWarranties (WarrantyExpire, Cust) VALUES (#" & Format(Datevalue(TheExpDate),"MM/DD/YYYY") & "#, '" & Customer & "')

Ti


----- Newbie wrote: ----

As I am not sure of your datatypes and table structure . . .

eg This worked for m

Sub insertrec(

expire = "#12/10/04#
Customer = "Z
strSQL = "Insert Into tblWarranties (WarrantyExpire, Cust) VALUES (
strSQL = strSQL & expire & ", '" & Customer & "')

DoCmd.RunSQL strSQ
End Su


HT

Good morning
I would like to see how to make the VALUES in an SQL INSERT INTO statemen

accept variables. It's rather messy to keep doing this(Forms!Formname.username, Forms!Formname.userdiv, Forms!Formname.requestTS)
'<--- wrapped, not a line brea

dim div as strin
dim TS as dat
div = Forms!Formname.userdi
TS = Forms!Formname.RequestT

blocks. Can someone please provide me with the correct syntax for the cod
with the "X" next to it