Link to home
Create AccountLog in
Avatar of SpaceCoastLife
SpaceCoastLife

asked on

Selecting a subform record in Access 2013

I have a command button on my main form that opens a second form using the On-Click event but I only want the form to open after the user selects a record. How do I do verify this in code?

Anyone?
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Well, generally, a record will automatically be selected, it is normally the first record in the form or subform.  However, depending on where the button is, and where the record is that you want to select (it would be useful to see a screen shot).  One method would be to use the Click event, something like:

If me.txt_ID & "" = "" Then
    msgbox "Please select a record"
Else
    docmd.openform "formname",,,"[ID] = " & me.txt_ID
EndIF
If the "second form" is an actual Subform, then you can set the SourceObject after the user selects the record:

<code here to allow the user to select the record>
Me.SubFormControl.SourceObject = "YourForm"
Avatar of SpaceCoastLife
SpaceCoastLife

ASKER

Sorry. I don't think I explained thoroughly enough what I'm trying to do. The subform record source includes an Id number field. What I need is when the user clicks on a record on the subform it captures that Id. The Record Selectors aren't shown in design view for either form so the On-Click event isn't available.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Something like this in your button OnClick:

Dim varID As Variant

If Me!subNameOfTheSubformControl.Form.RecordsetClone.RecordCount = 0 Then
     ' No records in subform.
Else
    varID = Me!subNameOfTheSubformControl.Form!ID.Value
    If IsNull(varID) Then
        ' Subform is on a new record
    Else
       ' varID holds the selected ID.
        ' Open your other form.
    End If
End If

/gustav
Perfect! One line of code and just what I was looking for.

Thanks for the help!