VBA variable to population Table Question

[Zachary]

I am using the following code to fill a line in one of my tables based
off what someone enters into a text box. The problem is whenever the
code runs a popup dialog box comes up asking the user to enter the
variable values over again. How can I set it up so the values are just
taken from the text box instead of being asked for in the dialog
window?

DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO Keywords (ReferenceID, Keyword) VALUES
(RefID, KeyPart)")
DoCmd.SetWarnings True

 

[Stuart McCall]

I am using the following code to fill a line in one of my tables based
off what someone enters into a text box. The problem is whenever the
code runs a popup dialog box comes up asking the user to enter the
variable values over again. How can I set it up so the values are just
taken from the text box instead of being asked for in the dialog
window?

DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO Keywords (ReferenceID, Keyword) VALUES
(RefID, KeyPart)")
DoCmd.SetWarnings True

Dim Vals As String, SQL As String

Vals = RefID & "," & KeyPart
SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"
SQL = SQL & " VALUES(" & Vals & ")"
DBEngine(0)(0).Execute SQL, dbFailOnError

Notice the use of DBEngine(0)(0).Execute instead of DoCmd.RunSQL. This is
both more efficient and avoids the need to bypass warning messages.

 

[Zachary]

Dim Vals As String, SQL As String

Vals = RefID & "," & KeyPart
SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"
SQL = SQL & " VALUES(" & Vals & ")"
DBEngine(0)(0).Execute SQL, dbFailOnError

Notice the use of DBEngine(0)(0).Execute instead of DoCmd.RunSQL. This is
both more efficient and avoids the need to bypass warning messages.

I keep getting syntax errors in my INSERT line with this code.

 

[Stuart McCall]

Dim Vals As String, SQL As String

Vals = RefID & "," & KeyPart
SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"
SQL = SQL & " VALUES(" & Vals & ")"
DBEngine(0)(0).Execute SQL, dbFailOnError

Notice the use of DBEngine(0)(0).Execute instead of DoCmd.RunSQL. This is
both more efficient and avoids the need to bypass warning messages.

I keep getting syntax errors in my INSERT line with this code.

So you get a syntax error on this line? :

SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"

I don't see why that is, at all. It's just a simple string assignment. If
you have changed what I posted in any way, perhaps you'd better show us what
you've ended up with.

 

[Steve Sanford]

Zachary,

In you code, you have the variables for "RefID" and "KeyPart" inside the
quotes, so the values for the variables aren't evaluated.

Stuart posted the revised code. It will work if both variables are numbers.
If "KeyPart" is text, it must be properly delimited.

Modified for a text variable:
'----snip--------
Dim SQL As String

Vals = RefID & "," & KeyPart
SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"
SQL = SQL & " VALUES(" & RefID & ",'" & KeyPart & "')"
Currentdb.Execute SQL, dbFailOnError
'-----snip---------

Expanded, the second SQL line is:

RefID & ", ' " & KeyPart & " ' ) "


The single quotes delimit the text string. If one of the variables were a
date type, it would be delimited using the "#" signs.


HTH

 

[Zachary]

Zachary,

In you code, you have the variables for "RefID" and "KeyPart" inside the
quotes, so the values for the variables aren't evaluated.

Stuart posted the revised code. It will work if both variables are numbers..
If "KeyPart" is text, it must be properly delimited.

Modified for a text variable:
'----snip--------
Dim SQL As String

Vals = RefID & "," & KeyPart
SQL = "INSERT INTO Keywords (ReferenceID, Keyword)"
SQL = SQL & " VALUES(" & RefID & ",'" & KeyPart & "')"
Currentdb.Execute SQL, dbFailOnError
'-----snip---------

Expanded, the second SQL line is:

   RefID & ", ' " & KeyPart & " ' ) "

The single quotes delimit the text string. If one of the variables were a
date type, it would be delimited using the "#" signs.  

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






- Show quoted text -

Thank You! I apparently needed to add some char[34] type thing too
because of my older version of access. Thanks for the help though,
everything else worked beautifully.