Help with form populating previous entry

[indyrobb]

Hi,

I've read through several pages of threads and didn't find anything that fit what I'm looking to do.

I have a very simple table with 5 Fields:
1. Provider Last Name
2. Provider First Name
3. Host Code
4. UPIN
5. NPI

What I'm wanting to do is create a form that would allow a person to enter the information so it'll save to the table, however, I need the host code to be prepopulated with the last entry +1. Unfortunately, I can not use the auto increment as we have some entries that are still blank in that field and I'll have to get those populated at some point in the future. If there isn't a way to have it populate the last entry +1, then would it be possible to leave that field blank and have Access display the host code that was just created after it's been saved?

I'm not an Access person, and I'm usually pretty good with just trying things till they work the way I want them to, however, this one is boggling my mind! Thanks!!

 

[dcobau]

use the Max function, eg.

Max([FieldName] + 1)

Dave

 

[indyrobb]

Thanks, but I'm not sure where to use it at. I've tried in a couple of different places under the properties of that entry box, but a no go. I apologize if this is such an easy question!

 

[ions]

1.

Use
DMax ("[Host Code]", "Table Name")

2. or what is usually done for your situation is a Systems Table is created that stores the last [Host Code] number. When you create a new record you look up that value from the Systems Table and then increment it by 1.

 

[dcobau]

Create a button on the form and attach code to it. Roughly:

DoCmd.GoToRecord , , A_NEWREC
Me!FieldName = DMax ("[FieldName]","TableName") + 1

You might have to set the focus on the field before giving it a new value, if that's the case:

Me!FieldName.SetFocus

Dave