getting a form field to change based on criteria in another

[boyd]

I am working with an Access Form and I would like to have a field that chooses it's criteria based on an option that is chosen. I am not writing this in code so I will need to be guided on how to do this without (or how to do it with, if it is pretty simple).

Basically, I am trying to assign a particular accounting code to an order based on what department the order is entered for. I would like to use consecutive auto numbers to generate the accounting codes. I have three departments and their accounting codes are 7#####, 9##### and J#####. I was thinking that I could set up three tables to generate these codes separately and then use an option group in my form that would tell the form which table to get the code from when the Order is entered. I don't know if I need a separate table to tie my Order Number with the Accounting Code and how I get the form to choose which table to get the code from.

I may be trying to do the impossible on too little knowledge but I would really like to make this easy for my users. Can anybody offer me some help or advice on this?

Thanks,

boyd

[nico5038]

I am working with an Access Form and I would like to have a field that chooses it's criteria based on an option that is chosen. I am not writing this in code so I will need to be guided on how to do this without (or how to do it with, if it is pretty simple).

Basically, I am trying to assign a particular accounting code to an order based on what department the order is entered for. I would like to use consecutive auto numbers to generate the accounting codes. I have three departments and their accounting codes are 7#####, 9##### and J#####. I was thinking that I could set up three tables to generate these codes separately and then use an option group in my form that would tell the form which table to get the code from when the Order is entered. I don't know if I need a separate table to tie my Order Number with the Accounting Code and how I get the form to choose which table to get the code from.

I may be trying to do the impossible on too little knowledge but I would really like to make this easy for my users. Can anybody offer me some help or advice on this?

Thanks,

boyd

Hmm, you're trying to create tables, just for the sake of different departments. In general we just make sure that the departmentcode is a field in the ordertable.
To het the Accounting Code we have a different tblDepartment holding the Departmentcode and the Accounting Code. Thus we only have to change an Accounting Code once when it's changed for a department :-)

When the orders need to be numbered per department you can use a DCOUNT() function, but in general wh just use a "dummy" autonumber and use that for teh unique reference. When you have a limited number of orders, you could use the date/time as the order identifier, but when you want to number them per department you could use for new records a default fieldvalue like:

=NZ(DMax("OrderNumber","tblOrder")) + 1
 

Getting the idea ?

Nic;o)