Creating a new reference

[motscotland]

Hi.
I am looking for a way to look up the latest reference code of a record, increment it by 1 and then store this new value on the main data entry form - this ref and other newly added data (entered on entry form) then to be added as a new record to the main data table.

Main table is cvlist
Reference Field is u_id-1 (format is Mnnnn )
The form where entry is to be done is cvlistfrmNEW

I have a query qrycvlistMaxRef that returns the max ref as I am looking for, but need to know how to add 1 to this value and put it where I describe above.

The reason for this is I am building an Access version of an older FoxPro db which has records dating back 14 or 15 years which are all referenced by the u_id-1 number.

I am a newbie to Access programming so apologies for any gross stupidity!!

Any help would be appreciated.

Cheers,
Myles

 

[neileg]

You could:
-Try using DMax instead of the query, or
-Create a text box on the form to hold the max value from the query and refer to the text box in your formula. You can make the text box invisible if you don't want the users to see it.

 

[motscotland]

-Create a text box on the form to hold the max value from the query and refer to the text box in your formula. You can make the text box invisible if you don't want the users to see it.

Thanks for that Neil! However, apologies for my ignorance but HOW do I refer to the value in a formula? The code builder dialog is new to me and my knowledge of VB code is very limited!

I can set the value to appear in a list box which can then be 'copied' to the ref field by selecting it BUT this is still the max ref and not incremented by 1 so it creates a 'duplicate' record. I would rather the user did not have to do anything and the 'Ref' number was automatically incremented. As it has an M prefix I assume I am unable to use the autonumber facility here?

Thanks in advance for your help.

Cheers,
Myles

 

[Mile-O]

HOW do I refer to the value in a formula?

In a textbox's ControlSource, the simple forumula: =[MyText] would display the value of another textbox.

So, rather than say the explicit value, you just put the control instead.

other examples:

=[Text1] + [Text2]

=IIf([MyText] = 0, "Hello", "Goodbye")

 

[motscotland]

Thx! This forum always has the answer!

Merry seasonals to all.

Cheers,
Myles