Syntax error when running RunSQL - Access 2010 (1 Viewer)

Schwaby01

New member
Local time
Yesterday, 21:19
Joined
Oct 10, 2018
Messages
2
I have a form that presents data about company printers in service and the current and new IP address to be assigned. This is all in Access 2010

The form is based off table called PRINTER_INFO
In the form the field that holds the the new IP Address is called [NEWIPADR]
[NEWIPADR] - Short Text (16)

A COMBO bOX IS USED TO REF. THE [REF_IPADR_TABLE]

The To be assigned IP address are stored in a table named [REF_IPADR Table]
Table has 3 fields
Key
NEWIPADR - Short Text (16)
IP ADR USED - Short Text (1)

When I open the form I have Event Form_Load that declares a string value to be used to store the CURRENT value of the [NEWIPADR] the statement is

Dim TMPNIPADR As String
TMPNIPADR = [NEWIPADR]

I have an event ON Change that I want to run an update to update the table [REF_IPADR Table]. [IP ADR Used] to 'N'

This is were I can not get this to work here is the code:

DoCmd.RunSQL "UPDATE [REF_IPADR TABLE] SET [REF_IPADR TABLE].USED = 'N' WHERE [REF_IPADR TABLE].[IPADR] ='"&TMPNIPADR&"'";

Thanks in advance for any help on this. I know it is simple but just cant figure it out. been out of access development for about 8 years so still rusty:banghead:
 

June7

AWF VIP
Local time
Yesterday, 17:19
Joined
Mar 9, 2014
Messages
5,423
If you set value of variable in one procedure and want to reference that variable with that value in another procedure, the variable must be declared in module header.

And if you want to reference variable in different modules, variable must be declared in header of a general module.

I have never used Change event, I use AfterUpdate. If you use Change and type an input into combobox, expect the code to execute after each keystroke.

I also prefer CurrentDb.Execute to DoCmd.RunSQL. RunSQL will trigger warning messages so you might want to use DoCmd.SetWarnings False and DoCmd.SetWarnings True to prevent.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Yesterday, 21:19
Joined
Jun 21, 2011
Messages
5,901
Try...

Code:
DoCmd.RunSQL "UPDATE [REF_IPADR TABLE] SET [REF_IPADR TABLE].USED = 'N' WHERE [REF_IPADR TABLE].[IPADR] ='"& TMPNIPADR &"';

Though if it were me I'd use...

Code:
      Dim strSQL As String

      strSQL = "UPDATE [REF_IPADR TABLE] " & _
                 "SET USED = 'N' " & _
                    "WHERE [IPADR] ='" & TMPNIPADR & "'"
                CurrentDb.Execute strSQL, dbFailOnError
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Jan 20, 2009
Messages
12,849
This is were I can not get this to work here is the code:

DoCmd.RunSQL "UPDATE [REF_IPADR TABLE] SET [REF_IPADR TABLE].USED = 'N' WHERE [REF_IPADR TABLE].[IPADR] ='"&TMPNIPADR&"'";

"cannot get to work" is not a useful description.

What actually happens?

Do you get an error?
 

JHB

Have been here a while
Local time
Today, 02:19
Joined
Jun 17, 2012
Messages
7,732
..
I have an event ON Change that I want to run an update to update the table [REF_IPADR Table]. [IP ADR Used] to 'N'

This is were I can not get this to work here is the code:

DoCmd.RunSQL "UPDATE [REF_IPADR TABLE] SET [REF_IPADR TABLE].USED = 'N' WHERE [REF_IPADR TABLE].[IPADR] ='"&TMPNIPADR&"'";
..
In addition to what the others have already mentioned, there is a difference in the field name you say it's called, (IP ADR Used), and the name you are currently using, (USED).
 

Cronk

Registered User.
Local time
Today, 12:19
Joined
Jul 4, 2013
Messages
2,770
Are you sure the event is triggered and runs? Add a stop in the code to check.


Where is the value TMPNIPADR coming from? What is its value when the code runs. Is it the bound field and not the name of the control? Is it coming from the unbound column of a combo?
 

Users who are viewing this thread

Top Bottom