REQ Assistance - Problem with new dbase

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Good evening.
I am trying to correct some code that errors out while trying to update an
inventory. This code has been copied from a similar dbase. I understand
most of it except where it errors out (UPPERCASE). What is that line
asking me? If think if I understood it, I could probably figure it out.

Thanks for any and all assistance.



Private Sub cmdSignSuppliesIn_Click()

'On Error Resume Next
Dim intQuantity As Integer

'Find amount currently in inventory
intQuantity = DLookup("[Quantity]", "tblInventory", "[SUPPLYTYPEID]=" &
Me.CBOSUPPLYTYPE)

'Increase quanity by amount signed in
intQuantity = intQuantity + CInt(Me.txtQuantityIn)

'Update tblInventory to correct quantity
DoCmd.RunSQL "UPDATE tblInventory SET tblInventory.Quantity =" &
intQuantity & " WHERE tblInventory.InventoryID=" & Me.cboInventoryID


DoCmd.Close acForm, Me.Name

End Sub
 
intQuantity = DLookup("[Quantity]", "tblInventory", "[SUPPLYTYPEID]=" &
Me.CBOSUPPLYTYPE)
Click to expand...

This should be all one line, it is probably wrapped due to the newsgroup,
but I just want to make sure.

To get the quantity (defined by the variable intQuantity) a DLookup call is
used. The DLookup call will let you retrieve a single record from a single
record source (table or query). You tell DLookup which field to look in,
which table/query to find this field in, and what the parameter is to limit
the search. In this case, the field to look in is [Quantity], the table or
query is tblInventory and the parameter is the rest.

The parameter is limiting the search to where the field [SUPPLYTYPEID] is
equal to the value in the form's control CBOSUPPLYTYPE. The value from the
form's control is being concatenated into the parameter, which is the reason
for the &. The way it is typed, the value should be a number. If the value
is a text value, you will also need to concatenate in some quote marks.

Example:
intQuantity = DLookup("[Quantity]", "tblInventory", "[SUPPLYTYPEID]='" _
& Me.CBOSUPPLYTYPE & "'")

This will add single quotes around the value from Me.CBOSUPPLYTYPE. If the
value is text AND there are NO apostrophes in the possible values, this will
work. Since this example will probably be wrapped by the newsgroup reader, I
shortened it by using the continuation character "_" to place it on two
lines, hopefully it won't wrap now.

--
Wayne Morgan
MS Access MVP


Dan said:
Good evening.
I am trying to correct some code that errors out while trying to update an
inventory. This code has been copied from a similar dbase. I understand
most of it except where it errors out (UPPERCASE). What is that line
asking me? If think if I understood it, I could probably figure it out.

Thanks for any and all assistance.



Private Sub cmdSignSuppliesIn_Click()

'On Error Resume Next
Dim intQuantity As Integer

'Find amount currently in inventory
intQuantity = DLookup("[Quantity]", "tblInventory", "[SUPPLYTYPEID]=" &
Me.CBOSUPPLYTYPE)

'Increase quanity by amount signed in
intQuantity = intQuantity + CInt(Me.txtQuantityIn)

'Update tblInventory to correct quantity
DoCmd.RunSQL "UPDATE tblInventory SET tblInventory.Quantity =" &
intQuantity & " WHERE tblInventory.InventoryID=" & Me.cboInventoryID


DoCmd.Close acForm, Me.Name

End Sub
Click to expand...
 
Thanks for the great information. You were correct, it was wrapped
because of the newsgroup. I appreciate your prompt reply and detailed
response.

Dan


intQuantity = DLookup("[Quantity]", "tblInventory",
"[SUPPLYTYPEID]=" &
Me.CBOSUPPLYTYPE)
Click to expand...

This should be all one line, it is probably wrapped due to the
newsgroup, but I just want to make sure.

To get the quantity (defined by the variable intQuantity) a DLookup
call is used. The DLookup call will let you retrieve a single record
from a single record source (table or query). You tell DLookup which
field to look in, which table/query to find this field in, and what
the parameter is to limit the search. In this case, the field to look
in is [Quantity], the table or query is tblInventory and the parameter
is the rest.

The parameter is limiting the search to where the field [SUPPLYTYPEID]
is equal to the value in the form's control CBOSUPPLYTYPE. The value
from the form's control is being concatenated into the parameter,
which is the reason for the &. The way it is typed, the value should
be a number. If the value is a text value, you will also need to
concatenate in some quote marks.

Example:
intQuantity = DLookup("[Quantity]", "tblInventory", "[SUPPLYTYPEID]='"
_
& Me.CBOSUPPLYTYPE & "'")

This will add single quotes around the value from Me.CBOSUPPLYTYPE. If
the value is text AND there are NO apostrophes in the possible values,
this will work. Since this example will probably be wrapped by the
newsgroup reader, I shortened it by using the continuation character
"_" to place it on two lines, hopefully it won't wrap now.
Click to expand...
 
Back
Top