Incrimenting Numbers

[STaL1N]

pseudocode for the code i want like is as follows, i am just having problems applying the correct syntax.

 IF NewlyaddedCompanyName = unique then
          InvoiceNumber = 1
       End If
       IF NewlyAddedCompanyName = has appeared x times then
          InvoiceNumber = x

this code is an attempt to create an incrimenting invoice number for each company ie when a company appears multiple times in a job query it is assigned the appropriate invoice number

 

[mndrlion]

I think that you really want to increment the last invoice number that was used for a company.

dim varLastInvoiceNum as variant

varLastInvoiceNumber = DMAX("CompanyNameField", "TableName", "CompanyNameField = '" & NewlyAddedCompanyName & "'")

if isnull(varLastInvoiceNum) then
    InvoiceNumber = 1
else
    InvoiceNumber = varLastInvoiceNum + 1
endif

 

[STaL1N]

and would i put this code in a module or..?

 

[mndrlion]

Place this code in a module. Note correction in DMAX parameters.

Function NextInvoiceNumber(NewlyAddedCompanyName as string) as integer

    dim varLastInvoiceNum as variant
    dim InvoiceNumber as integer

    varLastInvoiceNumber = DMAX("InvoiceNumberField", "TableName", "CompanyNameField = '" & NewlyAddedCompanyName & "'")

    if isnull(varLastInvoiceNum) then
        InvoiceNumber = 1
    else
        InvoiceNumber = varLastInvoiceNum + 1
    endif

    NextInvoiceNumber = InvoiceNumber
end function

 

[STaL1N]

= '" & NewlyAddedCompanyName & "'")

was wondering do i need to amending this to my database?
am thinking i dont need to touch this part of the code.

 

[STaL1N]

also would i have to call this function and if so where would i do it?

 

[mndrlion]

You can call the function from a query, from the control source of a form, from another module, etc.

From a query

SELECT CompanyName, NextInvoiceNumber(CompanyName) as NewInvoiceNum FROM myTable

 

[STaL1N]

i created a form with a new text box and built an expression but the value returned was "#Name?"

also i did not know how to incorporate it into an existing query.

SELECT [tblJobs].[JobID], [tblCompany].[CompanyName], [tblCars].[Registration], [tblTyres].[Tyre Type], [tblTyres].[ManafacturersCode], [tblJobs].[NoOfTyres], [tblTyres].[Price], [tblJobs].[Comment], ([NoOfTyres]*[Price])*1.175*[DiscountValue]+50 AS Total, [tblCars].[CompanyID], [tblJobs].[CarID], [tblJobs].[TyreID], [tblJobs].[Discount], [tblDiscounts].[DiscountName], [tblDiscounts].[DiscountValue], [tblJobs].[Job Date], [tblJobs].[Mileage], [tblTyres].[Amount In Stock], [tblCompany].[InvoiceNumber]
FROM tblTyres INNER JOIN (tblDiscounts INNER JOIN (tblCompany INNER JOIN (tblCars INNER JOIN tblJobs ON [tblCars].[CarID]=[tblJobs].[CarID]) ON [tblCompany].[CompanyID]=[tblCars].[CompanyID]) ON [tblDiscounts].[DiscountID]=[tblJobs].[Discount]) ON [tblTyres].[TyreID]=[tblJobs].[TyreID];

where my relationships are setup was follows

http://www.v-loaded.com/stal1n/relate.gif

 

[STaL1N]

also in the original code

IF NewlyaddedCompanyName = unique then
          InvoiceNumber = 1
       End If
       IF NewlyAddedCompanyName = has appeared x times then
          InvoiceNumber = x

NewlyAddedComanyName is pseudocode i should have rephrased to

IfTheCompanyIJustAddedToMyQuery = NotAlreadyThere then
InvoiceNumber = 1
       End If
       IF NewlyAddedCompanyName = has appeared x times then
          InvoiceNumber = x