Update a control before it displays?

[servoss]

You guys are so good that I'm starting to rely too much on you! Forgive me for that.

Also forgive me for not searching for the answer to this question in previous postings - I'm in a bit of a rush, so I apologize if this is easily answered or has been answered before.

Ok, I have a form with a bunch of controls. On eo fhte controls - a textbox - will contain a tracking label for the record displayed in the form. What I'd like to do is to automatically generate a value for this textbox when a new record is generated, but before the record is displayed in the form. The generated value will be based upon the date that the record is created and an autonumber field.

I'm having a little trouble figuring out which event to tie the field value creation to, so I'm looking for advice. For example, I tried putting the VBA code in the OnCurrent event, but this does not seem to work. It seems as though the OnCurrent event is happening before the creation of the new record and the autonumber field is not yet populated for the new record.

Do you have any advice on how to best approach this?

Thank you so much,
Tom

 

[Fizzio]

By code you could try

On Current()
if me.newrecord then
me.textbox = default value
end if

But the autonumber value is not generated until you start adding a new record so the value in the textbox will not 'calculate' until then - unless you are using an autonumber from a previous record.

 

[suepowell]

could you just use the default value on the property sheet for the field, something like = date() & [autofield]

havn't tried this but I think it might work, it would certainly be there when you went to the new record.

Sue

 

[servoss]

Good try, but...

I tried the OnCurrent suggestion by Fizzio and I think it would work except the autonumber field is not generated until one of the other table fields is modified, as you said.

Here is the assignment that I am using:

Me.PNum = Format$(Now(), "yyyy") & "-" & Format$([RecNum], "0000")

Were RecNum is the autonumber field and PNum is the textbox (I want this value to be recorded in the table, as well, so the textbox is actually bound to a field, too).

Is there another event that I can tie this to that would work better? For example, is there an event that would run just after an autonumber value is generated for a new record? Is this once a new record is modified for the first time?

Also, I was unable to get Sue's approach to work, but maybe II'm misinterpreting what she means.

I also tried to set the default value for the textbox as the assignment above, but received an error message that indicated that the it was unable to locate the fields, even though they are right there!

Anyway, as always, your help is appreciated.
Tom

 

[servoss]

Nevermind

I figured out a way to do it.

For the benefit of those who want the answer, in a nutshell, I did the following:

1) created two new fields in the table: one for the year and one for a number.

2) in the OnCurrent event I selected only those rows that have the current year

3) I used dmax() to determine the largest number for the selected rows

4) for the new record I set the year equal to the current year and the number equal to the largest one plus 1

5) I added some checking logic to properly handle the case in which there are no rows for the current year

6) for the textbox, I simply set this value by concatenating the two fields together.

Works beautifully.

Thanks for your help.