How to update details on MS ACCESS as a member?

Question

I have created a registration form which enables me to become a member once registered and my data gets stored in the tblMember table.

I have created a login system for members where they can update their personal details once logged in. I connected the update membership form to the homepage.

When creating the update form, I added existing fields from the membership table which means the details are already saved on the fields when a member clicks on the membership details page.

However the issue is, it stores the information of the latest member on the update form. For example, I registered a member with a name of John, and his details will also be updated on update form because of the fields being added directly from the tblMember table. However if I log in as Peter (account I created previously), it still shows the details of John on the update form. The updated form will only show the details on the latest member regardless of who I login as.

I am assuming I will need to do some sort of query to make sure whoever logs in, can update their own personal details instead of the form showing details of the latest member. Does anyone know how I can make this work? I have been trying to figure this out for quite some time and had no luck. Many thanks

Here is my coding:

Register member:

Private Sub btnSubmit_Click()

On Error GoTo ErrHandler:
If IsNull(MemberID) Then
   MsgBox "Please enter your personal details", , "Data Required"
ElseIf IsNull(Title) Then
    MsgBox "Title is required", , "Required Field"
ElseIf IsNull(FirstName) Then
    MsgBox "First Name is required", , "Required Field"
ElseIf IsNull(LastName) Then
    MsgBox "Last Name is required", , "Required Field"
ElseIf IsNull(MobileNo) Then
    MsgBox "Mobile Number is required", , "Required Field"
ElseIf IsNull(MembershipType) Then
    MsgBox "Membership Type is required", , "Required Field"
ElseIf IsNull(Username) Then
    MsgBox "Username is required", , "Required Field"
ElseIf IsNull(Password) Then
    MsgBox "Password is required", , "Required Field"
ElseIf IsNull(txtCardName) Then
    MsgBox "Card Name is required", , "Required Field"
ElseIf IsNull(txtCardType) Then
    MsgBox "Card Type is required", , "Required Field"
ElseIf IsNull(txtCardNumber) Then
    MsgBox "Card Number is required", , "Required Field"
ElseIf IsNull(txtCVV) Then
    MsgBox "CVV is required", , "Required Field"
ElseIf IsNull(txtExpiryDate) Then
    MsgBox "Expiry Date is required", , "Required Field"
Else
   Msg = "Please select 'yes' to confirm membership"
   Style = vbYesNo + vbCritical
   Response = MsgBox(Msg, Style)
   If Response = vbYes Then
     DoCmd.Close
    Exit Sub
   End If
   If Response = vbNo Then
      Me.Undo
      DoCmd.Close
      Exit Sub
    End If
End If
Exit_ErrHandler:
Exit Sub
ErrHandler:
    MsgBox Err.Description, vbCritical
    Err.Clear
End Sub



Private Sub Username_AfterUpdate()

Dim NewMember As String
Dim stLinkCriteria As String
NewMember = Me.Username.Value
stLinkCriteria = "[Username] = " & "'" & NewMember & "'"
If Me.Username = DLookup("[Username]", "tblMember", stLinkCriteria) Then
    MsgBox "This Username, " & NewMember & ", already exists, please choose another username." _
    & vbCr & vbCr & "Please check name again or contact the Administrator", vbInformation, "Duplicate Name"
    Me.Undo
End If
End Sub

Login:

Private Sub btnLogin_Click()

If IsNull(Me.txtUsername) Then
    MsgBox "Please enter Username", vbInformation, "Username is required"
    Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
    If (IsNull(DLookup("[Username]", "tblMember", "[Username] ='" & Me.txtUsername.Value & "' And password ='" & Me.txtPassword.Value & "'"))) Then
    MsgBox "Incorrect Username or Password"
    Else
    MsgBox "You have successfully logged in"
    DoCmd.OpenForm "frmHomepage"
    End If
End If
End Sub

Update form

Private Sub btnUpdate_Click()
On Error GoTo ErrHandler:
If IsNull(MemberID) Then
   MsgBox "Please enter your personal details", , "Data Required"
ElseIf IsNull(Title) Then
    MsgBox "Title is required", , "Required Field"
ElseIf IsNull(FirstName) Then
    MsgBox "First Name is required", , "Required Field"
ElseIf IsNull(LastName) Then
    MsgBox "Last Name is required", , "Required Field"
ElseIf IsNull(MobileNo) Then
    MsgBox "Mobile Number is required", , "Required Field"
ElseIf IsNull(MembershipType) Then
    MsgBox "Membership Type is required", , "Required Field"
ElseIf IsNull(Username) Then
    MsgBox "Username is required", , "Required Field"
ElseIf IsNull(Password) Then
    MsgBox "Password is required", , "Required Field"
ElseIf IsNull(CardholderName) Then
    MsgBox "Card Name is required", , "Required Field"
ElseIf IsNull(CardType) Then
    MsgBox "Card Type is required", , "Required Field"
ElseIf IsNull(CardNumber) Then
    MsgBox "Card Number is required", , "Required Field"
ElseIf IsNull(SecurityCode) Then
    MsgBox "CVV is required", , "Required Field"
ElseIf IsNull(ExpiryDate) Then
    MsgBox "Expiry Date is required", , "Required Field"
Else
   Msg = "Are you sure you want to update your membership?"
   Style = vbYesNo + vbCritical
   Response = MsgBox(Msg, Style)
   If Response = vbYes Then
     DoCmd.Close
    Exit Sub
   End If
   If Response = vbNo Then
      Me.Undo
      DoCmd.Close
      Exit Sub
    End If
End If
Exit_ErrHandler:
Exit Sub
ErrHandler:
    MsgBox Err.Description, vbCritical
    Err.Clear
End Sub

My update form is the same as my register form I need to know what the correct code is for the update form

Answer 1

I have these functions to get the current windows user name (place this in a module):

Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function LoginName() As Variant
    Dim ret As Long
    Dim s As String * 255
    Dim pos As Long

    ret = GetUserName(s, 255)
    pos = InStr(s, vbNullChar)
    If pos <= 1 Then
        LoginName = Null
    Else
        LoginName = Left$(s, pos - 1)
    End If
End Function

Use it to open the form with the right user. You can use the WhereCondition when opening the form to filer the records. Something like this:

DoCmd.OpenForm "frmMember", _
    WhereCondition:="UserName='" & Replace(LoginName(), "'", "''") & "'"

Assuming that you have a column UserName in the table.

---

I also have a handy function that formats strings as SQL strings and makes sure single quotes are replaced by double quotes.

Public Function SqlStr(ByVal s As String) As String
'Input: s=""      Returns: NULL
'Input: s="abc"   Returns: 'abc'
'Input: s="x'y"   Returns: 'x''y'

    If s = "" Then
        SqlStr = "NULL"
    Else
        SqlStr = "'" & Replace(s, "'", "''") & "'"
    End If
End Function

Using this function you would open the form like this:

DoCmd.OpenForm "frmMember", WhereCondition:="UserName=" & SqlStr(LoginName())

---

Another option is to base the form on a query and to use the LoginName() function in the query itself.

SELECT * FROM tblMember WHERE UserName = LoginName()

---

If the form is already open, you can filter the record source with (e.g. in Form Load or open events):

Me.Filter = "UserName = " & SqlStr(LoginName())

Answer 2

You could use the environment variable to return the current user.

Dim strUserName as String
strUserName = Environ("USERNAME")