default value is an expression

[etcrier]

I have a field in a table that is a ticket #
it is an incremented number
constant+ record ID (unique KEY)

for example 4000+ID
on a form i cannot get this to work

i have
default =4000+[ID] in data tab of properties
but only 0 is ever in field


thanks

 

[Remou]

If ID is an autonumber, this will not work because it is null at the time the default value is set. You will need to add code to an event that occurs after the autonumber is created. It is a bit difficult for me to say which event would suit without knowing how this form works. Why not just use the ID for the Ticket Number?

 

[etcrier]

Thanks
I'll work on it now that i know the default issue

they need to change the base value from time to time and its too much trouble teaching them how to set the unique keey to a know value. i wish there was a button for it in the toolbar


thanks

 

[etcrier]

i am stuck,
here is what i have
a work order form needs a incrementing order #
its a field in the work order table called order-general number. I am trying to come up with a way to increment the number so there could never be a duplicate. thats why i chose to use the unique key made by access + a base starting #. This stuff is so easy in "C" but its all a big pain in access VB i guess.

how would you suggest i do this. because an order needs a torder # thats always unique. also the customer needs to adust the base value once in while, Not sure why but they do.
the method of setting the autonumber key to a know value is too complex for this customer.

thanks

 

[Remou]

What to do now depends on how many users you have or expect to have. I do not think you should tell the customer about the autonumber, keep it to yourself as a unique key and let them mess with some incrementing number. If this is a small database, that is, not a lot of people updating at the same time, it may be possible to get a number from a table. Date/Time (down to second) is often good for ticket numbers.

You may wish to get rid of the autonumber, in which case this FAQ may be of interest.
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184

 

[etcrier]

This by luck is not the situation in this application.
its multi user BUT
only 1 person ever adds orders and others just change field values of an existing record.
BTY i looked at the FAQ700-184 and there is a lot of code.
do you put all that including Dims etc in to an event procedure on a field after Private Sub and before End Sub

I am never sure where and how to add coding statements like the one in FAQ700-184

any other ideas drop me a note

thanks for all your time.

it blows me away such a simple and common process for business is so difficult in Access- OH well
lets got back to Cobol and punched cards

thanks

 

[Remou]

The FAQ is a function that would go in a module. It requires the set up of a few tables. I thought it might show you another approach.

Here is a simple number, got from a table, which holds numbers:

  Set rsInfo = CurrentDb.OpenRecordset("tblInfo")
        
  rsInfo.Edit
  rsInfo!CertNumber = rsInfo!CertNumber + 1
  rsInfo.Update
                
  Me.CertNo = rsInfo!CertNumber
  rsInfo.Close

Here is a 'number', based on the table it is in:

strStudentNo = "S" & Format( _
            DMax("Mid(StudentNo,2,4)", "tblStudents", "Right(StudentNo,4)=" _
            & Year(Date)) + 1, "0000") & Year(Date)

Neither of the above examples is used as a primary key, so the user can mess about to a certain extent, without getting into trouble.