VBA variable to population Table Question

  • Thread starter Thread starter Zachary
  • Start date Start date
Z

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
 
Zachary said:
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
Click to expand...

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.
 
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.
Click to expand...

I keep getting syntax errors in my INSERT line with this code.
 
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.
Click to expand...

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.
 
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,

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 -
Click to expand...

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.
 
Back
Top