I have a query that pulls up from a

[zahara666]

I have a query that pulls up from a table (lets say for simplicity sake), a client code, file number, last name, first name and invoice number...

CC, FN, LN, FN, IN

1 1 Smith, John, E123
1 2 Doe, Jane, E123
2 5 Renolds, Ron, E124
2 6 Black, Joe, E124
3 10 Tang, Jack, E125

As you can see the invoice number changes only when the client code changes. this process needs to be automated and the first nummber (E123) can be inputed by the individual through a promptbox or something. But the rest MUST be automated.

If someone could please help out, that would be great!

I posted this message on the general access section earlier but did not receive an accurate response - and I am hoping someone with a litle more VBA knowledge (since my VBA knowledge is limited) can assist me?

Thanks,
Julie

 

[tomschafer]

This might not require any VBA. What do you mean when you say "this process needs to be automated and the first nummber (E123) can be inputed by the individual through a promptbox or something. But the rest MUST be automated."
Do you mean that you want the user to be prompted for which records the query should return or do you want the invoice number to be automatically generated?

 

[zahara666]

I want the invoice numbers to be generated automatically.. So the user will input E123 (for example) and then for all the same client codes it will have E123 as its invoice number. And with the next client code (in my example is 2) the invoice number should automatically increment by 1.

The numbers I am using for the client codes and invoice numbers are for illustrative purposes. The point is that the user is prompted for the first invoice number and the query lists all the relevant records sorted by client code. So each time the client code changes, the invoice number must automatically increment by 1.

Hope this makes sense - thanks for you help.

Julie

 

[tomschafer]

The invoice number is stored in a table though right? Then when data is entered into the table is when you need the invoice number to be generated then, not when you run the query. Or is that what you mean- when you say "So the user will input E123 (for example) and then for all the same client codes it will have E123 as its invoice number."- is that when records are entered the user will be prompted for the invoice number for that client.

 

[zahara666]

The invoice number is EVENTUALLY stored into the table. But at this point, there are no invoice numbers stored. When the user is prompted to enter the first invoice number (say E123), it will use that number to make up subsequent invoice numbers AND THEN store it into the table. Which is why I need the database to go INTO the query and plop these invoice numbers into it. But it needs to be smart enought to know the increment only when the client code changes...

Hope this makes more sense?

Thanks for your help..

Julie