Saving unbound textbox data

[joecamel9166]

I have created an unbound textbox that uses the following code to create a new ID# for each record.

Private Sub Form_Current()


If Me.NewRecord Then




Me.txtUnitID = 1 + DMax("Unit", "tblUnits")
End If
End Sub

The problem I am having with this is that the number that this generates doesn't save into the record.
So I can fill this form out and have 20 new entries, but the "Unit" field is left blank (unless I enter it manually into the table).

[rpeare]

Unbound fields have to be specifically saved or you can fake it.

I'm assuming you're doing this because you want sequential numbers on your table tblUnits with no breaks (i.e. if you use autonumber and cancel a record you'd have gaps in your numbering). If this is your goal you just need to change your txtunitid field to a bound field on your table. Just be aware you may get some errors if you don't correctly handle null values, for instance if the only required field on your table is the UNIT field leaving your form on an otherwise blank record would cause a record to be created in your tblUnits table with a UNIT field and everything else blank.

[joecamel9166]

Thanks. If I delete a record, I want it to still show, but as deleted. Do you have any suggestions on how to specifically save the unbound field?

[ssanfu]

rpeare suggested

...<snip> you just need to change your txtunitid field to a bound field on your table. </snip>.....

Open your form, set the control source for the control "txtunitid" to the field that holds the ID# for each record. (this makes the control "Bound")

But I would use the "Form_BeforeUpdate" event, instead of the "Form_Current" event.

Private Form_BeforeUpdate(Cancel As Integer)
  If Me.NewRecord Then
    Me.txtUnitID = 1 + DMax("Unit", "tblUnits")
  End If
End Sub