lookup for 2 values code

  • Thread starter Thread starter ghost
  • Start date Start date
G

ghost

Greeting,

I have the following code to lookup for two values in table and put them in
two controls in subform as follows:


Private Sub RevenueID_AfterUpdate()
Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")
Me.Price = DLookup("[Price]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")

The problem is , once I select the Revenue number from the RevenueID Combo
box the following msg appears:

Run-time erro’3464’:
Data type mismatech in critieria expression.


Any help please
 
If you open the Revenue table in design view, what data type is the ID
field?

If Number (including AutoNumber), not Text, lose the extra quotes:

Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]=" &
Nz([Forms]!Sales!Sales_subform.Form![RevenueID],0))

For an explanation of DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For an explanation of the .Form bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

The Nz() is to prevent the error if the RevenueID in the subform is null.
 
It works, many thanks!!

Allen Browne said:
If you open the Revenue table in design view, what data type is the ID
field?

If Number (including AutoNumber), not Text, lose the extra quotes:

Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]=" &
Nz([Forms]!Sales!Sales_subform.Form![RevenueID],0))

For an explanation of DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For an explanation of the .Form bit, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

The Nz() is to prevent the error if the RevenueID in the subform is null.

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

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

ghost said:
Greeting,

I have the following code to lookup for two values in table and put them
in
two controls in subform as follows:


Private Sub RevenueID_AfterUpdate()
Me.Revenue = DLookup("[Revenue]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")
Me.Price = DLookup("[Price]", "[Revenue]", "[ID]='" &
[Forms]!Sales!Sales_subform![RevenueID] & "'")

The problem is , once I select the Revenue number from the RevenueID Combo
box the following msg appears:

Run-time erro’3464’:
Data type mismatech in critieria expression.


Any help please
Click to expand...
Click to expand...
 
Back
Top