Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    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()
    Me.Undo
    DoCmd.Close
    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.NewRecord Then
    Dim lngNextID As Long
    lngNextID = DMax("ID", "qryRecords") + 1
    ID = lngNextID
    End If
    End Sub

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If ID is a TextBox on the form, you should try:
    Code:
    Me.ID.Value = lngNextID
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    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.

  4. #4
    Join Date
    Aug 2011
    Posts
    4
    Still not working. The index number saves to the table properly if I close the form, but never displays in the text box.

  5. #5
    Join Date
    Aug 2011
    Posts
    4
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •