Unanswered: Auto Increment Index Number when Opening Form
First the problem, then all the specifics.
I have a form that will either open an existing record or a new record. If it's a new record, I want to increment the index number. If the record is closed without inputting an data, I want it to discard the new number. If it is changed, it should save it.
I have two macros. One opens the form by passing it an existing ID number and opening the form in edit mode. The other opens the form in add mode without passing any numbers.
I have the Dmax function in the before update event of the form. This is working. However, the number doesn't show up in the field on the form. If I type in text and save the form, it saves the correct index to the query, however it never displays the actual number. Refresh and requery don't seem to work. Here's the code. Command7 is a discard & close button.
Option Compare Database
Private Sub Command7_Click()
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim lngNextID As Long
lngNextID = DMax("ID", "qryRecords") + 1
ID = lngNextID
Now for the specifics.
I have a table named tblRecords with three fields, one primary key that is a number, not autonumber.
I have a select query named qryRecords that just selects the fields from the table.
I have two forms, a form named frmDetails that has a record source of qryRecords, and frmList that has a record source of qryRecords.
I have two macros, named mcrNew and mcrOpen. New runs openform in add mode with no where clause, open runs openform in edit mode with a where clause that passes the selected index in frmList.
I'll happily elaborate any other details necessary.
Thanks for the reply. I'll have to wait until Monday to try it out, I don't have Access at home. I do have OpenOffice which has Base but I have a feeling I should just stick to one interface for the time being so I don't introduce more problems. I'll post back once I get to work.
Ok, so it sort of works. I created a button, ran a macro which ran the refresh command. If I open a new form, click refresh, it still doesn't work. If I type anything into a field, then click refresh, it will show the ID in the text box. Now, I've tried putting the refresh command several places but I can't get it to refresh automatically, or before anything is typed.