VBA variable to population Table Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zachary - 31 Dec 2007 15:15 GMT

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

Reply to this Message

Stuart McCall - 31 Dec 2007 15:38 GMT

>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
[quoted text clipped - 7 lines]
> (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.

Reply to this Message

Zachary - 02 Jan 2008 14:41 GMT

> >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
[quoted text clipped - 17 lines]
> 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.

Reply to this Message

Stuart McCall - 02 Jan 2008 18:31 GMT

On Dec 31 2007, 10:38 am, "Stuart McCall" <smcc...@myunrealbox.com>
wrote:

> "Zachary" <zlehm...@gmail.com> wrote in message
>
[quoted text clipped - 21 lines]
> 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.

Reply to this Message

Steve Sanford - 03 Jan 2008 03:54 GMT

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

Signature

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

> 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
[quoted text clipped - 7 lines]
> (RefID, KeyPart)")
> DoCmd.SetWarnings True

Reply to this Message

Zachary - 03 Jan 2008 14:45 GMT

> Zachary,
>
[quoted text clipped - 41 lines]
>
> - 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.

Reply to this Message