Should be a simple fix... My Code Problem (Beginner)

[MamadouNdaw]

I'm a beginner so go easy on me.

Ok, what I would like to do is simple. I have a combo box in a subform with a number of product related fields. I would like the form to automaticall display the unit price upon selecting the product from the combo box. So, what am I overlooking here....

The error I'm getting is

ERROR 2766 "The object doesn't cotain the automation object 'XXXX'

Where XXXX is the Product ID selected by the combo box. The product ID's follow the format P100, P101, P102, etc... Maybe I should also include my sql statemet for the combo box.

SELECT qryInventaire.[ProduitID], qryInventaire.[CatégorieID], qryInventaire.[Produit], qryInventaire.[QtyDisponible] FROM qryInventaire WHERE qryInventaire.[CatégorieID]="Marchandise" ORDER BY qryInventaire.[Produit];

So, I assume the problem is coming from my tblListeProduit table, but it does contain the ProduitID as a Primary Key.

My Code
------------------------------------------

Module Code:
---------------
___________________________________________________________________________


Public Enum StatutVenteEnum
Nouveau_StatutVente = 0
Facturé_StatutVente = 1
Fermé_StatutVente = 2
End Enum

______________________________________________________________________________

Public Function DLookupNumber(Expr As String, Domain As String, Optional Criteria As String, Optional ValueIfNull = 0) As Variant
DLookupNumber = Nz(DLookup(Expr, Domain, Criteria), ValueIfNull)
End Function

______________________________________________________________________________

Function GetPrixUnitaireProduit(lProduitID) As Currency
GetPrixUnitaire = DLookupNumber("[PrixUnitaire]", "tblListeProduit", "[ProduitID] = " & lProduitID)
End Function

_______________________________________________________________________________

Form Code:
-----------

_______________________________________________________________________________

Private Sub ProduitIDBox_AfterUpdate()
If Not IsNull(Me![ProduitID]) Then
Me![Quantité] = 1
Me![PrixUnitaire] = GetPrixUnitaireProduit(Me![ProduitID])
Me![Remise] = 0
Me![StatutID] = Nouveau_StatutVente
End If
End Sub
_____________________________________________________________________________

 

[pbaldy]

You're doing it the hard way. Add the price field to the combo, and then simply:

Me.[PrixUnitaire] = Me.ProduitIDBox.Column(x)

where x is the column number, starting at 0.

 

[MamadouNdaw]

Yeah, that was definately a much easier way to get it done... It worked like a charm, although I had to add the PrixUnitaire column to my Inventory query. Is there a way to avoid that in my SQL statement?

I tried just referring to a external table, tblListeProduit... but it returned mixed up values for the unit price.

I'm also still a bit curious as to why my primary effort didn't work. If anyone has the time to explain, I'd love to hear it.

Thanks for the help

 

[pbaldy]

Probably because your DLookup value is text rather than numeric, which would need this:

GetPrixUnitaire = DLookupNumber("[PrixUnitaire]", "tblListeProduit", "[ProduitID] = '" & lProduitID & "'")