Generating a new Quantity using 'table.field - variable'

  • Thread starter Thread starter Pringle9984
  • Start date Start date
P

Pringle9984

I'm currently working on the stock control side of a database, at th
moment I'm writing the code for transfer stock from one location t
another, it basically works something like this

DIM blah blah blah..

LocationFrom = Me.frombo
LocationTo = Me.tobo
Material = Me.material_i
qty = Me.stock_qt

numRowsFrom = DCount("*"
"[sc_object]", "[location_id] =
& LocationFrom & " AND [material_id] =
& Material

If numRowsFrom = 0 The
reply = MsgBox("The from location has none of tha
material in stock.", vbOKOnly, "Error!"
En
End I

newQty1 = sc_object.object_qty - qt

'Build SQL statement to update existing record (with QTY
CurrentQuantity - NewQuantity

numRowsTo = DCount("*", "[sc_object]"
"[location_id] = " & LocationTo & " AN
[material_id] = " & Material

If numRowsTo = 0 The

'Build SQL statement to insert record (with QTY = 0
End I

'Build SQL statement to update existing record (with QTY
CurrentQuantity + NewQuantity

End Su

So... effectively the way this would work is

Does Stock Exist at 'From' location
No - Error, En
Yes - Update Stock at 'To' location (decrease Qty by X), Continu
Does Stock Exist at 'To' location
No - Insert Stock into 'To' location, Continu
Yes - Continu
Update Stock at 'To' location (increase Qty by X

The problem comes when I build the first SQL statement

[code:1:91a6f8cdb5]strSQL1 = "UPDATE [sc_object] SE
[object_qty] = " & sc_object.object_qty - qty
" WHERE ([location_id]=" & LocationFro
& ") AND ([material_id]="
Material & ");"[/code:1:91a6f8cdb5

More specifically the sc_object.object_qty - qty part which shoul
determine the new quantity

I get a 434 'Object Required' error which I assume is because i
doesn't know which record in 'sc_object' I'm refering to but can'
seem to resolve the problem

Any help would be hugely appreciated

Thanks in advance
 
I don't see where you are defining sc_object. Without additional
information, I do not think I can tell you where the problem lies, although
from the error message, I do not think it has to do with locating the record
in sc_object. I would focus on how you define each object in the procedure
you are using.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm currently working on the stock control side of a database, at the
moment I'm writing the code for transfer stock from one location to
another, it basically works something like this.


DIM blah blah blah...

LocationFrom = Me.frombox
LocationTo = Me.tobox
Material = Me.material_id
qty = Me.stock_qty

numRowsFrom = DCount("*",
"[sc_object]", "[location_id] = "
& LocationFrom & " AND [material_id] = "
& Material)

If numRowsFrom = 0 Then
reply = MsgBox("The from location has none of that
material in stock.", vbOKOnly, "Error!")
End
End If

newQty1 = sc_object.object_qty - qty

'Build SQL statement to update existing record (with QTY =
CurrentQuantity - NewQuantity)

numRowsTo = DCount("*", "[sc_object]",
"[location_id] = " & LocationTo & " AND
[material_id] = " & Material)

If numRowsTo = 0 Then

'Build SQL statement to insert record (with QTY = 0)
End If

'Build SQL statement to update existing record (with QTY =
CurrentQuantity + NewQuantity)

End Sub


So... effectively the way this would work is:

Does Stock Exist at 'From' location?
No - Error, End
Yes - Update Stock at 'To' location (decrease Qty by X), Continue
Does Stock Exist at 'To' location?
No - Insert Stock into 'To' location, Continue
Yes - Continue
Update Stock at 'To' location (increase Qty by X)

The problem comes when I build the first SQL statement:

[code:1:91a6f8cdb5]strSQL1 = "UPDATE [sc_object] SET
[object_qty] = " & sc_object.object_qty - qty &
" WHERE ([location_id]=" & LocationFrom
& ") AND ([material_id]=" &
Material & ");"[/code:1:91a6f8cdb5]

More specifically the sc_object.object_qty - qty part which should
determine the new quantity.

I get a 434 'Object Required' error which I assume is because it
doesn't know which record in 'sc_object' I'm refering to but can't
seem to resolve the problem.

Any help would be hugely appreciated.

Thanks in advance.
 
Back
Top