Fill data entry textbox with variable

[broecher]

Hi,



I have read a lot of threads about this but I can't seem to find one exactly like what I am trying.

I have a form that is used for adding a single record only. The properties for Allow Edits and Deletions is No, Allow Additions is Yes. It works fine right now but I would like make the field "AnimalID" automatically be filled with the next available AnimalID upon opening so that the users don't have to figure out what the next available AnimalID is.

I made a button that will show the next available AnimalID in a msgBox through a query, but I don't know where to put this code so that it will fill the text box on my form as soon as I open the form. On load, Before Update?? do i need some sort of public variable for this???

Code for button/msgBox:

Private Sub Command0_Click()
Dim rst As New ADODB.Recordset
Dim str As Integer
Dim NextID As String
      
rst.Open "SELECT Max(Val(Right([AnimalID],3))) AS MaxAnimalID FROM Tbl_AnimalList;", CurrentProject.Connection
 
    str = rst.GetString
    NextID = "BH" & Format((str + 1), "000")
   
    rst.Close
 
    Set rst = Nothing
MsgBox NextID
End Sub

Also I am struggling with the line that sticks the value into the box.
The name and control source of the box is: Tbl_AnimalList_AnimalID
The name of the form is: Form_AddLamb

Form!Tbl_AnimalList_AnimalID.AnimalID = NextID

[boblarson]

I would be using the DMax function instead of a recordset but you can populate the form from the variable NextID by using

Me!AnimalID = NextID

after it is set if this button is on the same form as you are adding the record to.

[broecher]

OK, I'm trying dmax now. My ID string looks like 'BH123'. This sort of works in a query:

="BH" & Format(DMax(Val(Right([AnimalID],3)),"Tbl_AnimalList")+1,"000")

It adds 1 to the current record, not the max of the whole table.

When I put it in the text box control source it just says #Name?

[boblarson]

I think the SIMPLEST way to go is to just build a query (and save it as qryMaxAnimalID) to pull the highest number and then use it.

So, in the saved query:

SELECT Max(Right([AnimalID],3)) As MyID FROM Tbl_AnimalList

And then just call it using

="BH" & Format(DMax("MyID","qryMaxAnimalID"), "000")

[broecher]

Thanks so much for your help. I'm sure this should be really simple, but I keep running into problems. It finally is showing my next available ID the way I want on the form. I used a query and then an expression like what you recommended. Now when I fill the remaining textboxes on the form out and try to save it, I get an error as if I left the textbox for this AnimalID blank.

"The field 'Tbl_AnimalList.AnimalID' cannot contain a Null value because the Required property for this field is set to True. enter a value in this field."

[boblarson]

You aren't actually filling the text box by using a formula. You would need to use code to set the field to that ID. So, put another text box (hidden) on the form and bind it to the AnimalID field. Then in the form's BEFORE UPDATE event you would set it to the value held in the text box you had your formula in.

Me.MyHiddenBoxNameHere = Me.MyTextBoxWithDMaxInItHere

[broecher]

Its working!

I'm so happy.

I left the box that was supposed to be hidden visible at first so I could see if it was working. The original box had the ID like it was supposed to but the 'hidden' box did't so I kept trying to change things to get it to work... Then I FINALLY realized that it doesn't show the value until I actually click in one of the next data entry boxes and start typing...

What a relief!!! Now that I know how to do this I can make a bunch of improvements on my forms.

Thanks so much for your help!!!