Access add Record issue with autonumber

Deke 0
Viewed 68

I'm having an issue with my code generating an autonumber for my UserID field. I am trying to generate my UserID through VBA since Access's autonumber doesn't actually go sequentially.

For some reason my code will not fill in the Autonumber but instead UserID always ends up as 0 which is the default value. Not sure what I'm missing.

Any help would be greatly appreciated!!

Private Sub RegistrationBtn_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim strMember As String

strMember = "" & Me!CallSign
Autonumber = Nz(DMax("[UserID]", "[Memberstbl]"), 0) + 1

If Len(strMember) = 0 Then Exit Sub
Set db = CurrentDb
Set rst = db.OpenRecordset("Memberstbl", dbOpenDynaset)

With rst
    .FindFirst "CallSign = '" & strMember & "'"
    If .NoMatch Then
        .AddNew
        !UserID = Autonumber
        !CallSign = strMember
        MsgBox "Welcome to the UNCC"
        DoCmd.OpenForm "Logonfrm"
        DoCmd.Close acForm, "Registration"
    Else
        MsgBox "That Call Sign is already in use!"
    End If
.Close
End With

Set rst = Nothing
Set db = Nothing

End Sub
ms-access-2016 ms-access vba
3 Answers

I think that the problem that you are having is that you don't finish the adding of the record using .Update. Try:

    If .NoMatch Then
        .AddNew
        !UserID = Autonumber
        !CallSign = strMember
        .Update    'Needed to "commit" the adding of the new record
Applecore0

Ok, lets back the truck up here a bit.

User enters a call sign. Check if call sign exists - if yes, then don't proceed.

If call sign does not exist, generate new number AND ALSO NEW record.

Then launch form to the NEW record for additonal information to fill out.

In other words, we can't, and don't launch the 2nd form in "add mode", else you get two records (that explains your other issue - two records). So we can't and don't want to launch the 2nd form to a new record, since we ARE going to assume we created that record already, right??

so, first bit of code - check for existing call sign - lets do that 100% separate and not attempt to combine that operation if we have a green light to add the call sign record (along with a new generated number).

So, first bit of code should look like this:

   Dim strTable     As String
   Dim strMember    As String
  
   strTable = "Memberstbl"
  
   If Nz(Me.CallSign, "") = "" Then
       MsgBox "Please enter a call sign", vbCritical, "Enter Call sign"
       Exit Sub
  End If
  
  ' get here, user entered a call sign.
  ' check if exists.
  
  Dim strWhere      As String
  strWhere = "CallSign = '" & Me.CallSign & "'"
  
  If Nz(DLookup("CallSign", strTable, strWhere), "") <> "" Then
       
       MsgBox "That call sign is already in use, try another", vbInformation, "In use"
       Exit Sub
       
  End If
  
  ' if we reach here, then user entered a call sign, and it not in use.
  ' create new reocrd, new UserID, and then launch form to this record.
  
  Dim NewUserID        As Long
  NewUserID = Nz(DMax("UserID", strTable), 0) + 1
  
  Dim rstMember        As DAO.Recordset
  Set rstMember = CurrentDb.OpenRecordset(strTable)
  With rstMember
     .AddNew
     !UserId = NewUserID
     !CalSign = Me.CallSign
     .Update
     .Close
  End With
  
  ' now launch form to this new record
  DoCmd.OpenForm "Logonfrm", , , "UserID = " & NewUserID
  DoCmd.Close acForm, Me.Name

Do make sure that Logonfrm is set with data entry = "no". (if you set yes, then the form can ONLY add records - but we already did that in code).

Albert D. Kallal0

see June 7th's comments but if you want to continue try these things. Make sure UserID is not still an autonumber and make sure that UserID has the same datatype as the foreign key. I've seen both those cases. Then make sure UserID is still the primary key for the UsersTable. At that point you can set up relationships but there are issues with cascading updates. My copy of Access 2016 refused to cascade updates but would allow cascading deletes. I can't see any problems with this except for the error message as access already makes changing primary keys difficult. see here: https://support.microsoft.com/en-us/office/guide-to-table-relationships-30446197-4fbe-457b-b992-2f6fb812b58f#:~:text=When%20you%20enforce%20referential%20integrity,that%20reference%20the%20primary%20key.

mazoula0
Related