VBA To Set Value Of TextBox That Has Control Source

[jo15765]

I am trying to use this VBA to add the next system generated userID from table tblUsers to my form. tbox1 has a control source set, but I want to populate it with the below syntax, altho I get an error. What is causing this error? This is not an autonumber field, or a PK field. And if I display the DMAX() value in a MsgBox it shows exactly as it should.



And I am calling this from the vBA syntax ONLoad() for the form.

Me!tbox1.Value = DMax("userID", "tblUsers") + 1

You can't assign a value to this object

[aytee111]

It is bound to the table so the value must come from the table. Are you trying to add a new record?

[aytee111]

Hmm, I just tried it and it works, so not sure what the problem is.

[jo15765]

It is bound to the table so the value must come from the table. Are you trying to add a new record?

Yes-wanting to add a new record. A button is pressed from my main form, and the main form is closed and this second form is opened, and I use this in the OnLoad() event.

[aytee111]

What is the control source? If you remove this line of code does the rest of the form work as expected?

[jo15765]

The controlsouruce is tblusers.userID - so that the table is updated with the id once the save button has been hit. If I comment out this line of code, yes the form works as expected EXCEPT the userID textbox being populated.

[aytee111]

Is tblUser the record source for your form?

[jo15765]

Yes, it is.

[aytee111]

I am out of ideas! Could you post your db, or just this part?

[Micron]

It seems to me that you are trying to use a function that returns the maximum value in a domain and add 1 to it, all the while binding the control to that domain. When you think about it that way, I don't believe it's possible to have both. The solution may be that the control can't be bound to the field while at the same time, be calculated.