Looking up a value in a table based on two field values? How?

[Guest]

Hi,

I am creating an ordering system and am working on the delivery note section.
I have a table called Orders_Details with the fields:
Order ID
Order Items ID
Item Number
Item Type
Quantity
Price

In my delivery notes section I want the user to be able to enter the Order
ID followed by the Item Number and then the form will lookup the quantity for
that order?

So how do I do this?
Thanks in advance.

 

[Niklas Östergren]

Hi!

Well you can do this in several ways, like everything else in Access. But
one of the easyer way is to use function <DLookup> and sett the controls (I
asume you want to display the result in a fom!?) to the value you locked up.

Try this:

Use this code in the LostFocus_Event of control <Order Items ID>

If <Order Items ID> is of datatype integer or long use this:
Me.Quantity = DLookup("Quantity", "Order_Details", "Order Items ID = " &
Me.Order Items ID)

If <Order Items ID> is of datatype string use this:
Me.Quantity = DLookup("Quantity", "Order_Details", "Order Items ID = " &
"'" & Me.Order Items ID & "'")


What might get you into trouble here is that you use fieldnames and
controlnames (I asume) with spaces. Try to change dem to something like
this:

Order Items ID = OrderItemsID (field name)
Order Items ID = txtOrderItemsID (control name)

If you use this type of naming convention it´s much easyer to read the code
and to know WHAT you are refering to.

I hope this help you out. You might have to make some validation of control
Me.Order Items ID first. It depends on how your application is done.

// Niklas

 

[Allen Browne]

Something like this:
DLookup("Quantity", "Orders_Details",
"([Order ID] = " & [txtOrderID] & ") AND ([txtItemNumber] = " & [Item
Number] & ")")

That assumes you have a form with text boxes named "txtOrderID" and
"txtItemNumber" where the user enters the 2 values, and that both fields are
of type Number (not Text).

 

[Guest]

Allen where exactly do I apply this code???

Something like this:
DLookup("Quantity", "Orders_Details",
"([Order ID] = " & [txtOrderID] & ") AND ([txtItemNumber] = " & [Item
Number] & ")")

That assumes you have a form with text boxes named "txtOrderID" and
"txtItemNumber" where the user enters the 2 values, and that both fields are
of type Number (not Text).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am creating an ordering system and am working on the delivery note
section.
I have a table called Orders_Details with the fields:
Order ID
Order Items ID
Item Number
Item Type
Quantity
Price

In my delivery notes section I want the user to be able to enter the Order
ID followed by the Item Number and then the form will lookup the quantity
for
that order?

So how do I do this?
Thanks in advance.

 

[Niklas Östergren]

Hmm, sorry!

I was a little bit to fast. Her´s a betther way:

Use this code instead in the same event

' Code starts here
'************************************************
Dim db As DAO.Database
Dim rec As DAO.Recordset ' Holding recordset
Dim strSQL As String 'Holding SQL-string

Set db = CurrentDb()

strSQL = "SELECT Order_Details.* FROM Order_Details WHERE
Order_Details.[Order Items ID] = " & Me.Order Items ID & " AND
Order_Details.Order ID = " & Me.Order ID"

Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)

' Check if there is any records at all
' If not exit sub else get value from recordset
If rec.RecordCount = 0 Then

Exit Sub

Else

Me.Quantity = rec!Quantity

End If

' End of code
'****************************************************

Sorry that I was a little bit to fast!

I hope this will help you out!?

// Niklas

 

[Allen Browne]

You could add an equal sign to the start, and put it into the Control Source
of another text box on your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen where exactly do I apply this code???

Something like this:
DLookup("Quantity", "Orders_Details",
"([Order ID] = " & [txtOrderID] & ") AND ([txtItemNumber] = " & [Item
Number] & ")")

That assumes you have a form with text boxes named "txtOrderID" and
"txtItemNumber" where the user enters the 2 values, and that both fields
are
of type Number (not Text).

I am creating an ordering system and am working on the delivery note
section.
I have a table called Orders_Details with the fields:
Order ID
Order Items ID
Item Number
Item Type
Quantity
Price

In my delivery notes section I want the user to be able to enter the
Order
ID followed by the Item Number and then the form will lookup the
quantity
for
that order?

So how do I do this?
Thanks in advance.