Code developed in 2002 not working in 2000

[Guest]

Good day

I'm wondering if you can identify a function or command in the following code that doesn't execute properly if an .mdb is developed in Access 2002, but then run from within Access 2000? Can you offer a solution

The subsequent code is executed as an Afterupdate event for a combo box when a value (product) is chosen from the list. The code executes properly in Access 2002 but causes the following error dialog in Access 2000

run-time error -2147352567 (80020009) The value entered isn't valid for this field

Private Sub ProductID_AfterUpdate(
Dim strWhere As Strin
If Not (IsNull(Me.ProductID) Or IsNull(Me.Parent.CustomerID)) The
strWhere = "(ProductID = " & Me.ProductID &
") AND (CustomerID = " & Me.Parent.CustomerID & ")

Me.UnitPrice = DLookup("UnitPrice", "tblCustPrice", strWhere
Me.UnitP = DLookup("Unit", "tblCustPrice", strWhere
Me.USP = DLookup("US", "tblCustPrice", strWhere
End I

If Not (IsNull(Me.ProductID) Or IsNull(Me.Parent.SupplierID)) The
strWhere = "(ProductID = " & Me.ProductID &
") AND (SupplierID = " & Me.Parent.SupplierID & ")

Me.UnitCost = DLookup("UnitCost", "tblSuppCost", strWhere
Me.UnitC = DLookup("Unit", "tblSuppCost", strWhere
Me.USC = DLookup("US", "tblSuppCost", strWhere

End I

End Su

Cheers
Jod

 

[Dan Artuso]

Hi,
The code seems pretty standard.
What you want to do is identify the line that's causing the error
by stepping through the code (it will almost certainly be on one of the
assignment statements).

Make sure all the values are what you think they should be and go from there.

--
HTH
Dan Artuso, Access MVP

Good day,

I'm wondering if you can identify a function or command in the following code that doesn't execute properly if an .mdb is

developed in Access 2002, but then run from within Access 2000? Can you offer a solution?

The subsequent code is executed as an Afterupdate event for a combo box when a value (product) is chosen from the list. The code

executes properly in Access 2002 but causes the following error dialog in Access 2000:

 

[Guest]

Hi Dan

Thanks for your input. I did a little more testing and the line of code that is highlighted on debug is:

Me.USP = DLookup("US", "tblCustPrice", strWhere

When the US field (yes/no) does not have a value in tblCustPrice, an error occurs. Interestingly this doesn't happen if the UnitPrice (currency) or Unit (text) fields are null. There must be some feature in Access 2002 that traps that error for a yes/no field. Access 2000 does not

My problem is that I am not really familiar with VBA coding. Can you suggest the code line(s) that would trap that error and also suggest where I should place it with my existing code? It would be very much appreciated

Jod
----- Dan Artuso wrote: ----

Hi
The code seems pretty standard
What you want to do is identify the line that's causing the erro
by stepping through the code (it will almost certainly be on one of th
assignment statements)

Make sure all the values are what you think they should be and go from there

-
HT
Dan Artuso, Access MV

 

[Treebeard]

You could try:

If Not IsNull( DLookup("US", "tblCustPrice", strWhere)) then
Me.USP = DLookup("US", "tblCustPrice", strWhere)
Else
Me.USP = False
end If

Hi Dan,

Thanks for your input. I did a little more testing and the line of code

that is highlighted on debug is:

Me.USP = DLookup("US", "tblCustPrice", strWhere)

When the US field (yes/no) does not have a value in tblCustPrice, an error

occurs. Interestingly this doesn't happen if the UnitPrice (currency) or
Unit (text) fields are null. There must be some feature in Access 2002 that
traps that error for a yes/no field. Access 2000 does not.

My problem is that I am not really familiar with VBA coding. Can you

suggest the code line(s) that would trap that error and also suggest where I
should place it with my existing code? It would be very much appreciated.

Jody
----- Dan Artuso wrote: -----

Hi,
The code seems pretty standard.
What you want to do is identify the line that's causing the error
by stepping through the code (it will almost certainly be on one of the
assignment statements).

Make sure all the values are what you think they should be and go from there.

following code that doesn't execute properly if an .mdb is

developed in Access 2002, but then run from within Access 2000? Can you offer a solution?

combo box when a value (product) is chosen from the list. The code

 

[Guest]

Worked like a charm! Thanks Treebeard

Jod

----- Treebeard wrote: ----

You could try

If Not IsNull( DLookup("US", "tblCustPrice", strWhere)) the
Me.USP = DLookup("US", "tblCustPrice", strWhere
Els
Me.USP = Fals
end I

Hi Dan

occurs. Interestingly this doesn't happen if the UnitPrice (currency) o
Unit (text) fields are null. There must be some feature in Access 2002 tha
traps that error for a yes/no field. Access 2000 does notsuggest the code line(s) that would trap that error and also suggest where
should place it with my existing code? It would be very much appreciated

The code seems pretty standard
What you want to do is identify the line that's causing the erro
by stepping through the code (it will almost certainly be on one o th
assignment statements)
HT
Dan Artuso, Access MV

following code that doesn't execute properly if an .mdb i

developed in Access 2002, but then run from within Access 2000? Ca you offer a solution

combo box when a value (product) is chosen from the list. The cod