Finding values in a Table with Public Function? 
Author Message
 Finding values in a Table with Public Function?

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



Sat, 26 Jan 2002 03:00:00 GMT  
 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



Sun, 27 Jan 2002 03:00:00 GMT  
 Finding values in a Table with Public Function?
The DLookup Function is exactly what I needed.

I know my structure in not the best right now (Qte 1 to Qte 7).
It's a temporary situation but with my function PrixQte, it's
going to be easy to change my table without modifying all of my forms.

Thanks

Quote:

> 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))


> > 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



Sun, 27 Jan 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Error finding Public Functions

2. Public function with public-object parameter

3. Passing Public user-defined types via Public Functions

4. Using UDT as parameter in public sub or public function

5. Help Please - .Find method will not return the first value in a table

6. function to find highest value of an array?

7. Calling Function in CBF from Toolbar via a Public Function

8. Finding the number value of a hex value

9. Value from a tbl to calc value in another table

10. ~Sum existing values with values from another table

11. ~Sum existing values with values from another table

12. Looking for a function that translates numeric values to string values

 

 
Powered by phpBB® Forum Software