how do i set an Access table field equal to a VB variable?

[Guest]

i'm working on teaching my .mdb to adjust an inventory value based on a
quantity that is shipped to a customer. i've set up a tiny practice DB to
work out how to do this before i try to implement it in the real version.
i've checked the value of my variables and i can calculate the value that the
inventory needs to be set to after the ship quantity is subtracted, but i
cannot for the life of me figure out how to insert that new value
(intNewOnhand) back into my table.

here's the code so far:

Public Sub ADJUST_BUTTON_Click()

Dim intOnhand As Integer
Dim intShipQuantity As Integer
Dim intNewOnhand As Integer

intShipQuantity = Forms![form 2]![SHIP QUANTITY]
intOnhand = DLookup("QUANTITY", "qryUpdateField", "")
intNewOnhand = intOnhand - intShipQuantity

' now we just need to get intNewOnhand back into the table

' DoCmd.RunSQL "UPDATE [TABLE 1] SET QUANTITY=intNewOnhand where [TABLE
1].ID=FORMS![FORM 2]![LOOK ID]"
' that didnt work b/c it couldn't recognize the variable intNewOnhand

' DoCmd.RunMacro "Macro2", 1
' that still didn't work b/c the update query in the macro still
' could not recognize the variable either


please for the love of god someone help me out here.

 

[Jeff Boyce]

Why are you trying to set a "calculated" value in a table? Have you
considered using a query to derive that value?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

basically, the table keeps a running tab of our inventory by part number
(primary key). all i need is to look up the current value in the inventory
field by part number and then subtract a certain ship quantity we are
sending. if this is possible with a query than i will gladly use one (i'm
not struggling through VB code becuase i enjoy it, thats for sure [ok maybe a
little]).

thanks

Why are you trying to set a "calculated" value in a table? Have you
considered using a query to derive that value?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

i'm working on teaching my .mdb to adjust an inventory value based on a
quantity that is shipped to a customer. i've set up a tiny practice DB to
work out how to do this before i try to implement it in the real version.
i've checked the value of my variables and i can calculate the value that the
inventory needs to be set to after the ship quantity is subtracted, but i
cannot for the life of me figure out how to insert that new value
(intNewOnhand) back into my table.

here's the code so far:

Public Sub ADJUST_BUTTON_Click()

Dim intOnhand As Integer
Dim intShipQuantity As Integer
Dim intNewOnhand As Integer

intShipQuantity = Forms![form 2]![SHIP QUANTITY]
intOnhand = DLookup("QUANTITY", "qryUpdateField", "")
intNewOnhand = intOnhand - intShipQuantity

' now we just need to get intNewOnhand back into the table

' DoCmd.RunSQL "UPDATE [TABLE 1] SET QUANTITY=intNewOnhand where [TABLE
1].ID=FORMS![FORM 2]![LOOK ID]"
' that didnt work b/c it couldn't recognize the variable intNewOnhand

' DoCmd.RunMacro "Macro2", 1
' that still didn't work b/c the update query in the macro still
' could not recognize the variable either


please for the love of god someone help me out here.

 

[james_keegan]

here's the code so far:

Public Sub ADJUST_BUTTON_Click()

Dim intOnhand As Integer
Dim intShipQuantity As Integer
Dim intNewOnhand As Integer

intShipQuantity = Forms![form 2]![SHIP QUANTITY]
intOnhand = DLookup("QUANTITY", "qryUpdateField", "")
intNewOnhand = intOnhand - intShipQuantity

' now we just need to get intNewOnhand back into the table

' DoCmd.RunSQL "UPDATE [TABLE 1] SET QUANTITY=intNewOnhand where [TABLE
1].ID=FORMS![FORM 2]![LOOK ID]"
' that didnt work b/c it couldn't recognize the variable intNewOnhand

So what you really want to do is not pass the variable to the sql engine, but
the current VALUE of that variable.

So what you need is:

Dim Selstring
Selstring="update [table 1] set quantity = "& intNewOnHand & " where {yada
yada yada}"
DoCmd.RunSQL selstring

That way, the SQL command that the engine is running is actually "...set
quantity = 3..." (assuming the value of 'intNewOnHand' is '3')

I use this all the time, not only for SQL strings, but for dlookup strings
and all sorts of stuff.

JK

 

[james_keegan]

here's the code so far:

[quoted text clipped - 13 lines]

1].ID=FORMS![FORM 2]![LOOK ID]"
' that didnt work b/c it couldn't recognize the variable intNewOnhand

So what you really want to do is not pass the variable to the sql engine, but
the current VALUE of that variable.

So what you need is:

Dim Selstring
Selstring="update [table 1] set quantity = "& intNewOnHand & " where {yada
yada yada}"
DoCmd.RunSQL selstring

That way, the SQL command that the engine is running is actually "...set
quantity = 3..." (assuming the value of 'intNewOnHand' is '3')

I use this all the time, not only for SQL strings, but for dlookup strings
and all sorts of stuff.

JK

P.s.

Be careful with spaces and delimiters. If your value is a date/time, you
need to surround date-literals with "#" so that the SQL engine will read it
correctly!

 

[Guest]

You da man! I can manage pretty well using SQL but using it from within VB
is a whole new ballgame. I don't suppose you know any good reference
material off the top of your head? I already have 'Access Inside Out' by
John Viescas which is a great reference but i still stumble over the VB
syntax alot when i need to know how to do specific maneuvers.

Anyway, thanks a million!

here's the code so far:

[quoted text clipped - 13 lines]

1].ID=FORMS![FORM 2]![LOOK ID]"
' that didnt work b/c it couldn't recognize the variable intNewOnhand

So what you really want to do is not pass the variable to the sql engine, but
the current VALUE of that variable.

So what you need is:

Dim Selstring
Selstring="update [table 1] set quantity = "& intNewOnHand & " where {yada
yada yada}"
DoCmd.RunSQL selstring

That way, the SQL command that the engine is running is actually "...set
quantity = 3..." (assuming the value of 'intNewOnHand' is '3')

I use this all the time, not only for SQL strings, but for dlookup strings
and all sorts of stuff.

JK

P.s.

Be careful with spaces and delimiters. If your value is a date/time, you
need to surround date-literals with "#" so that the SQL engine will read it
correctly!