Subform datasheet: select one record to update form?

[AccessIdiot]

Hello all,

I have a form with a subform in datasheet view. The subform shows name and phone number and a checkbox that says "use this contact". When selected, the name and number populate text fields on the main form.

The problem is that if the subform contains multiple records, the checkbox for each can be selected. The form holds the values of the last selected, but I'd like for there to be only one check - that is, if a user selects one, and then another, the first should automatically uncheck.

Code on subform:

Private Sub chkUseThisContact_AfterUpdate()
  If Me!chkUseThisContact Then
    Forms!frm_LandownerContact.LandownerContact = Me.FullName
    Forms!frm_LandownerContact.LandownerPhone = Me.Phone
  Else
    Forms!frm_LandownerContact.LandownerContact = Null
    Forms!frm_LandownerContact.LandownerPhone = Null
  End If
  Me.Requery
End Sub

** Edit **

[Rabbit]

Run an update query to uncheck every record in the subset of records.
Then run an update query to recheck the chosen contact.
Finally, requery the subform.

[AccessIdiot]

Thanks for the quick response. Can you please ignore the fact that I have 491 posts on this site and pretend that I am the newbiest of all newbs?

I'm not even sure where to begin creating an update query.

[NeoPa]

Hey M. Good to hear from you again :-)

UPDATE queries can be created in the query design view if you change the type of the query using either the Query Type button in the toolbar or select Update Query from the Query menu. These can also be created in SQL directly in your code (Put the SQL code into a string) and executed (Call CurrentDb.Execute(strSQL)) from there too.

That said, there is an alternative to using an action query on your data, which is to manipulate the data from either the Recordset or RecordsetClone properties of the form. The benefit of this latter approach would be that it avoids having to requery the data and thereby lose (or have to implement extra code to avoid losing) the settings of where you are on the form within the dataset.

If you'd like, and can send me a copy of your database (You can attach here if you prefer or have lost my email address - See Attach Database (or other work)), I'll look at it for you and see if I can post a suggestion in the specific terms of the database.

[AccessIdiot]

Thanks A, I'll have a look at the properties, then contact you. :-)

[NeoPa]

8 - )