A default Value problem

[advancesystems]

I have a field that needs to be autoentered(defaultvalue) when a new record is added. This default value is quiote awkward and I simply can't get it to work. The field is "Ref_No" in a table called "Registrations". To simpliy this example I will use one other field only.
"Department"
In the registrations table a refno is assigned to each record, however this reference number is not unique but the same reference no cannot exist in 2 registrations where the department is the same. Therefore I cant use an autonumber field.
I was trying to make the default value lookup the highest used ref no for all registrations where the department number is the same as the one just entered for this record.

Any help would be warmly welcomed.

 

[FoFa]

Use dmax to return the max value of a ref-no for a dept. and inc. that. Only problem is if it is a shared app. it is possible for 2 users to get the same ref. if they request it for the same dept. at the same time. Not likely, but I had some thing like that happen to me once.

 

[neileg]

Some thoughts:

1) Why can't you make the reference unique spanning all departments?

or alternatively

2) Create a mini table for each department that has an autonumber field. Pop the relevant mini table up when you create a new record in the Registrations and grab the autonumber in this table to populate your reference. Create a composite key with this autonumber and a department ref in your Registrations (or concatenate the autonumber with a dept prefix).

or alternatively

3) Hold a variable for each of your departments and increment this by one each time you create a new record for this department

 

[Pat Hartman]

There are only a limited number of functions that can be used to specify default values in tables. Those functions would be specifically SQL functions. Remember that Jet is the real database manager NOT Access. Access does NOT need to be installed on a computer for an Access db to be used as a data store (ie just the tables). For example, a VB application that uses Access tables to store its data does NOT need Access to be installed. It only needs Jet. Therefore, VBA or user defined functions could not be allowed since there is no way for Jet to interpret them without Access.

Either switch to an autonumber (preferred) or assign your sequence number in code behind the form used for adding data to the table.

 

[advancesystems]

Thankyou all for the help, the dmax function as a default value expression seems to do the trick perfectly. a dlookup function to validate also works a treat.

Thanx again