Finding values in a Table with Public Function?
May I suggest that there is a problem with your data structure.
Whenever you see fields repeating (Prix1 to Prix8, Qte1 to Qte7,
etc.) it is an indiation that these fields should be taken into
a separate table, related many-to-one to the first table.
The new table would contain fields such like this:
ProductID Number (related to Product.ProductID)
QteID Number (how do you identify these?)
Prix Currency
The Primary key might be a combination of ProductID and QteID.
You can then get a price from this table with simply:
DLookup("Prix", "NewTable", "ProductID = " & Nz([ProductID],0) &
" AND QteID = " & Nz([QteID],0))
Quote:
> I'm a beginner but would need your help.
> I'm using Access 97
> I want to create a public function to return the prise of the produit
> depending on the quantity.
> I created this Function in the Module. It work ok.
> But I can get to work on a Public Function in the Global Declarations.
> I have table call "Products" with all the products (NumProduct) and
> multible prise (Prix1 to Prix8)
> and Quantity (Qte1 to Qte7).
> My Function is : PriceQte(ProductID, Qte) and return the price.
> How can I get the value of the ProductID in my "Products" table.
> What do I use : RecordSet, RecordSetClone, TableDef...
> I have look in book and Help from Access 97 but nothing works.
> I HAVE THIS (PLEASE BE GENTEL I'M VERY NEW)
> Public Function PrixParQte (pNumProduct As String, Qte As Single) As
> Single
> Dim Criteria As String
> Dim MyRS As Recordset
> Set MyRS = Forms![Products].RecordsetClone
> Criteria = "[NumProduit] = " & pNumProduit
> MyRS.FindFirst Criteria
> If Not MyRS.NoMatch Then
> Select Case Qte
> Case Is >= Me!Qte3
> PrixParQte = Me!Prix4
> Case Is >= Me.Qte2
> PrixParQte = Me!Prix3
> Case Is >= Me!Qte1
> PrixParQte = Me!Prix2
> Case Else
> PrixParQte = 10
> End Select
> Else
> PrixParQte = 0
> End If
> MyRS.Close
> End Function