Solved Subform Record Selection (1 Viewer)

[stonegold87]

Hello,
I am using Access for sometime now i need help regarding subform selection. I have Customer Main Form with subform linked with customer ID. Subform includes 5 fields
Email ID
Customer ID
Email Type (Combo box with selections "Work", "Personal")
Email Address
Primary (Yes/No Field)
What I Need is if a customer have both Work Email Address and Personal Email address (Only one record can be selected as primary) if I change Primary From Personal to Work then Personal Address Field should be unchecked automatically
Using Access 2019.

 

[Isaac]

You can probably handle this in the form's BeforeUpdate event.
At such time when any record is saved this code will fire.

Compose an IF statement.

 

[theDBguy]

Hi. Welcome to AWF!

I have seen this done before, so I know it's possible. I just can't remember exactly how it was done at the moment. I am currently imagining if you use code to update the table, you might get a "write conflict" error.

If I find a sample db or code to do this, I will let you know.

 

[stonegold87]

Thx Isaac for your swift reply if you can help with the complete code
i know basic vba

 

[Isaac]

Can you upload a sample copy of db with just enough data to present the challenge?

 

[stonegold87]

Hi. Welcome to AWF!

I have seen this done before, so I know it's possible. I just can't remember exactly how it was done at the moment. I am currently imagining if you use code to update the table, you might get a "write conflict" error.

If I find a sample db or code to do this, I will let you know.

much appreciated

 

[stonegold87]

Can you upload a sample copy of db with just enough data to present the challenge?

 

[Isaac]

Ok thanks, I'll try to look at it a bit later today

 

[MajP]

I did not look but something like

Private Sub primary_AfterUpdate()
  Dim emailID As Long
  Dim strSql As String
  Dim rs As Recordset
  emailID = Nz(Me.emailID, 0)
  Set rs = Me.Recordset
  If Me.primary Then
   rs.MoveFirst
   Do While Not rs.EOF
     If emailID <> rs!emailID Then
       rs.Edit
        rs!primary = False
       rs.Update
     End If
     rs.MoveNext
   Loop
   Me.Requery
   End If
End Sub

 

[stonegold87]

I did not look but something like

Private Sub primary_AfterUpdate()
  Dim emailID As Long
  Dim strSql As String
  Dim rs As Recordset
  emailID = Nz(Me.emailID, 0)
  Set rs = Me.Recordset
  If Me.primary Then
   rs.MoveFirst
   Do While Not rs.EOF
     If emailID <> rs!emailID Then
       rs.Edit
        rs!primary = False
       rs.Update
     End If
     rs.MoveNext
   Loop
   Me.Requery
   End If
End Sub

Works Perfectly , Thx a alot