DLookUp Help

[lauriecking]

I am working on a subform for an orders database. The subform is based
on a query called qryOrdersExtended, the fields are as such:

OrderID tblOrderDetails
ServicesID tblOrderDetails
ServicesName tblServices
UnitPrice tblOrderDetails
Quantity tblOrderQuantity
ExtendedPrice: CCur([tblOrdersDetail].UnitPrice*[Quantity])

On the AfterUpdate on the combobox with the ServicesID and
ServicesName (SELECT DISTINCT tblServices.ServicesID,
tblServices.ServicesName FROM tblServices ORDER BY
tblServices.ServicesName

The code used as follows:
Private Sub ServicesID_AfterUpdate()
On Error GoTo Err_ServicesID_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "ServicesID = " & Me!ServicesID

'Look up service's unit price and assign it to UnitPrice control
Me!unitprice = DLookup("UnitPrice", "tblServices", strFilter)

Exit_ServicesID_AfterUpdate:
Exit Sub

Err_ServicesID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ServicesID_AfterUpdate

End Sub

Everytime I would run my form, I would select a Service, I will get
"Data Type Mismatch in Criteria Expression" I cannot figure out what
is wrong. There are no data type mismatch at all. Could someone help
me.? I have have been dealing with this one issue for over a week.
Thanks.

 

[Pete D.]

Hi,
In the query select sql view, copy and post with table documentation for
fields.
Pete D.

 

[Wolfgang Kais]

Hello.

I am working on a subform for an orders database. The subform is
based on a query called qryOrdersExtended, the fields are as such:

OrderID tblOrderDetails
ServicesID tblOrderDetails
ServicesName tblServices
UnitPrice tblOrderDetails
Quantity tblOrderQuantity
ExtendedPrice: CCur([tblOrdersDetail].UnitPrice*[Quantity])

On the AfterUpdate on the combobox with the ServicesID and
ServicesName (SELECT DISTINCT tblServices.ServicesID,
tblServices.ServicesName FROM tblServices ORDER BY
tblServices.ServicesName

The code used as follows:
Private Sub ServicesID_AfterUpdate()
On Error GoTo Err_ServicesID_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "ServicesID = " & Me!ServicesID

'Look up service's unit price and assign it to UnitPrice control
Me!unitprice = DLookup("UnitPrice", "tblServices", strFilter)

Exit_ServicesID_AfterUpdate:
Exit Sub

Err_ServicesID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ServicesID_AfterUpdate

End Sub

Everytime I would run my form, I would select a Service, I will get
"Data Type Mismatch in Criteria Expression" I cannot figure out what
is wrong. There are no data type mismatch at all. Could someone help
me.? I have have been dealing with this one issue for over a week.
Thanks.

Either the bound column of the combo box isn't 1 or the ServicesID
has datatype text. If the datatype is text, try this:
strFilter = "ServicesID = '" & Me!ServicesID & "'"

 

[UpRider]

If your combo box is on the main form, Code on the main form module
referring to Me! does not refer directly to controls on the subform. Do it
like this:

Me!subfrmControlName.Form!unitprice = DLookup.....

subfrmControlName is the name of the control holding the subform. It may or
may not be the same name as the subform; you can determine that by looking
at the property sheet. A week on this is a lesson well learned that you
won't have to learn again.

UpRider

 

[lauriecking]

If your combo box is on the main form, Code on the main form module
referring to Me! does not refer directly to controls on the subform. Do it
like this:

Me!subfrmControlName.Form!unitprice =DLookup.....

subfrmControlName is the name of the control holding the subform. It may or
may not be the same name as the subform; you can determine that by looking
at the property sheet. A week on this is a lesson well learned that you
won't have to learn again.

UpRider

I am working on a subform for an orders database. The subform is based
on a query called qryOrdersExtended, the fields are as such:

OrderID tblOrderDetails
ServicesID tblOrderDetails
ServicesName tblServices
UnitPrice tblOrderDetails
Quantity tblOrderQuantity
ExtendedPrice: CCur([tblOrdersDetail].UnitPrice*[Quantity])

On the AfterUpdate on the combobox with the ServicesID and
ServicesName (SELECT DISTINCT tblServices.ServicesID,
tblServices.ServicesName FROM tblServices ORDER BY
tblServices.ServicesName

The code used as follows:
Private Sub ServicesID_AfterUpdate()
On Error GoTo Err_ServicesID_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed toDLookupfunction.
strFilter = "ServicesID = " & Me!ServicesID

'Look up service's unit price and assign it to UnitPrice control
Me!unitprice =DLookup("UnitPrice", "tblServices", strFilter)

Exit_ServicesID_AfterUpdate:
Exit Sub

Err_ServicesID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ServicesID_AfterUpdate

End Sub

Everytime I would run my form, I would select a Service, I will get
"Data Type Mismatch in Criteria Expression" I cannot figure out what
is wrong. There are no data type mismatch at all. Could someone help
me.? I have have been dealing with this one issue for over a week.
Thanks.- Hide quoted text -

- Show quoted text -

The Northwind example which I used for this form had this based on the
subform only. My code is on the subform level as well. I am going to
try some of the suggestions out but I am not sure if it will work.

 

[lauriecking]

I am working on a subform for an orders database. The subform is based
on a query called qryOrdersExtended, the fields are as such:

OrderID tblOrderDetails
ServicesID tblOrderDetails
ServicesName tblServices
UnitPrice tblOrderDetails
Quantity tblOrderQuantity
ExtendedPrice: CCur([tblOrdersDetail].UnitPrice*[Quantity])

On the AfterUpdate on the combobox with the ServicesID and
ServicesName (SELECT DISTINCT tblServices.ServicesID,
tblServices.ServicesName FROM tblServices ORDER BY
tblServices.ServicesName

The code used as follows:
Private Sub ServicesID_AfterUpdate()
On Error GoTo Err_ServicesID_AfterUpdate

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "ServicesID = " & Me!ServicesID

'Look up service's unit price and assign it to UnitPrice control
Me!unitprice = DLookup("UnitPrice", "tblServices", strFilter)

Exit_ServicesID_AfterUpdate:
Exit Sub

Err_ServicesID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ServicesID_AfterUpdate

End Sub

Everytime I would run my form, I would select a Service, I will get
"Data Type Mismatch in Criteria Expression" I cannot figure out what
is wrong. There are no data type mismatch at all. Could someone help
me.? I have have been dealing with this one issue for over a week.
Thanks.

Thank you everyone for your help. I was able to get it work now. I am
very amazed.