Adding 1 to field when form opened

[Caitlin11]

Hello - I have searched this website for an answer to my question and I can't find anything that will work the way I need it to work.

I have a main form with a button on it called "add record". When the user clicks the button another form is called where the user can add records. This form is based on a table that has a key field in it (level1_key) that is numeric. When the user clicks "add record" from the main form - when the next forms opens - I want the level1_key field to automatically populate with the next available number from the table.

I don't want to set the field as an auto number field because of some other linking issues I have with this and other tables. I want to accomplish this programatically. I've tried putting a SetValue macro action on the "add record" button. On the SetValue macro action I have the following code:

ITEM: [Forms]![frmLevel1]![fldLevel1_key]

EXPRESSION: DMax([tblEvidence_Location]![fldLevel1_key],[tblEvidence_Location])+1

This seems like it should work but I'm getting an error when this code is in. Can anyone tell me the easiest way to accomplish this?

thanks!
Caitlin

 

[Caitlin11]

Ok - I put the new syntax for DMax in my form and now I get a different error. I'm researching the error but not finding anything on it. Here's the error message:

"The object doesn't contain the Automation object 'tblEvidence_location.'

You tried to run a VB procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

Check the component's documentation for information on the properties and methods it makes available for Automation operations."

Does anyone have any idea where I should start to fix this problem?

tks
Caitlin

 

[smhill]

I have a similar problem

Pat,

I am trying to do something similar, and after perusing the forum, you seem to be the one to ask! I am setting up a test db to learn these things before putting them into action in the real thing--so my field/object names are pretty vague. Basically I want what everyone want: A field which automatically populates with a custom-formated ID number. The format I want to use for IDs is "yyyy-INQ-0000". I have learned from some of your previous postings how to insert the year and use a form to concatinate the year and serial number fields from the table--I'm just having trouble with populating the 4-digit serial number. I need for them to be sequential, beginning with 0001 for each new year. I do not want to use autonumbers due to the sequence problems with deleting records. My db is set up as so:

Table1
1. ID
2. Year =Format(Date(),"yyyy")

Form1
1. ID (invisible)
2. Year (invisible)
3. Unbound control =[txtYear] & "-INQ-" & [txtClearance ID]

This all works just fine as far as I can tell, except that I am using an autonumber for ID and I would like to change this to use a DMAX command. This is where I get lost. I've tried using the syntax you suggested in the above solution:

SetValue Macro
Item: [Forms]![frmForm1]![fldID]
Expression: DMax("fldID","tblTable1","[fldID] = " & [tblTable1]![fldID])+1

but an error tells me it can't find the name "Table1" and that I've specified a control that's not on the current object. Can you tell me what I am doing wrong?

Also, I would like for the serial number to restart at 1 for each new year. Ideally, when a user enters a new record in the form, I would like the ID to automatically populate like so:

2003-INQ-0001, 2003-INQ-0002, 2003-INQ-0003, 2004-INQ-0001

Is this possible? I think I am half way there. In a post from Feb of last year you helped someone to do this only using autonumbers because sequence was not important to her. It IS important to me--so is there another option? Thanks!

Stephanie