Search Form: link subform records to expanded form views?

[hillary123]

Hi -

I'm using Access 2003.

I have a main form SEARCH_FORM with criteria that filters qrySEARCH and displays the records in subSEARCH.

I want the user to be able to click on one of the search result rows (which displays only a few of the record's attributes) and have the full record open on PROFILE_FORM.

Can anyone tell me how to do this??

Thanks.

 

[dhookom]

I would use the command button wizard to write the code that opens a form based on a value from the current form. Then create a double-click event in the module window. Cut the code from the command button and paste it into the double-click sub.

If you had provided field and form names as well as data types, someone might be able to provide more exact code. Try something like:

Private Sub txtComplaintID_DblClick()
On Error GoTo Err_cmdEditComplaint_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmVendorComplaint"
    If IsNull(Me.lboComplaint) Then
        MsgBox "You must select a complaint record to edit", vbOKOnly + vbCritical, "Select Complaint"
     Else
        stLinkCriteria = "[cmpCmpID]=" & Me![txtComplaintID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog
        Me.lboComplaint.Requery
    End If

Exit_cmdEditComplaint_Click:
    Exit Sub

Err_cmdEditComplaint_Click:
    MsgBox Err.Description
    Resume Exit_cmdEditComplaint_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

Hi Duane -

Thanks for your response.

I can provide field names in addition to the form names I mentioned:
-the main form here is SEARCH_FORM and it includes different criteria.
-the subform is subSEARCH (bound to qrySEARCH) and it includes the text fields (in order): LNAME, FNAME, POSITION, BRANCH, PHONE, UPDATED.
- these fields are arranged side-by-side across the subform to look like one row/record of results.

I looked through the command wizard but I'm not sure which would feed the current record into a form, maybe Open Form or Apply Form Filter?

Based on what you provided me I tried a module:

Option Compare Database

Private Sub LNAME_DblClick()
On Error GoTo Err_cmdOpenProfile_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "PROFILE"
    If IsNull(Me.[b]lboComplaint[/b]) Then
        MsgBox "No profile selected. Double-click on the LAST NAME of the profile you would like to open.", vbOKOnly + vbCritical, "Expand Profile"
     Else
        stLinkCriteria = [b]"[cmpCmpID]="[/b] & Me![LNAME]
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog
        Me.[b]lboComplaint[/b].Requery
    End If

Exit_cmdOpenProfile_Click:
    Exit Sub

Err_cmdOpenProfile_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_cmdOpenProfile_Click
    
End Sub

But I'm not sure what type lboComplaint and cmpCmpID are...?

 

[dhookom]

But I'm not sure what type lboComplaint and cmpCmpID are...?"
These are the primary key fields/controls. You should have a primary key in your search results that you can use in the WHERE CONDITION of the DoCmd.OpenForm method.



Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

Ok, so is lboComplaint my subform and cmdCmpID my primary key? (Im my case, subSEARCH and STAFF_IF)...is the where condition this then:
Else
stLinkCriteria = "[STAFF_ID]=" & Me![LNAME]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog
Me.subSEARCH.Requery
End If

I'm still working on the code for the search form filter to get it working, but I don't have a text bos for STAFF_IF displayed in the search results - I was hoping the user would be able to click anywhere in that row or on the record selector and have it open the form. Is this not possible..?

 

[dhookom]

You must have some value/field(s) in the search results that uniquely identifies the record to display in the form. If you don't, how would you know which record to display?

Is STAFF_ID actually the same as a LNAME? I typically think of STAFF_ID as being a unique number or code that won't change when a staff person changes their last name.

Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

The results are based on a query that contains but doesn't show STAFF_ID, so I thought maybe the record could still link to STAFF_ID without actually displaying it and the user could double click the record selector when it's highlighting that record. Maybe this is just not how it can work.

So, I can add STAFF_ID to the search results. It's not displayed on the profile form that will open, either - does it need to be there too?

I'm confused what the difference between lboComplaint, cmpCmpID and txtComplaintID are.
Is txtComplaintID the text box name in the search result subform and cmcCmpID the corresponding text box name in the form that will open on dbl click?

 

[dhookom]

You don't have to display the primary key field but it needs to be in the record source. If you want to be able to open the form based on the Staff_ID of the current record by double-clicking the record selector, your code might be:

Private Sub Form_DblClick(Cancel As Integer)
    Dim strWhere As String
    Dim stDocName As String
    stDocName = "PROFILE"
    'assumes Staff_ID is numeric
    strWhere = "Staff_ID=" & Me.Staff_ID
    DoCmd.OpenForm stDocName, , , strWhere, acFormEdit, acDialog
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

This works perfectly - thanks so much for your help!

 

[hillary123]

Oh one question - that code seems to open the form as a pop-up, is there any way to open is as it normally would be?

 

[dhookom]

The acDialog (and last comma) can be removed from the DoCmd.OpenForm line of code.

Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

Thanks, that worked! I have the form PROFILE set to open to a new record when someone wants to add to it...that seemed to be overriding acFormEdit so it opens to a specific record with WHERE but then clears. Do you know if there's a way to stop this in DoCmd.OpenForm stDocName, , , strWhere, acFormEdit or if I need to remove newrecord from PROFILE's on load event?

 

[dhookom]

You can use the OpenArgs of the DoCmd.OpenForm to send a "message" to the newly opened form like "don't go to new record".

Duane
Hook'D on Access
MS Access MVP

 

[hillary123]

OK, will try that - thanks very much for your help.

 

[hillary123]

Hi again - Sorry to come back with another question about this, but now that I'm adding records to the database I see something wrong with my coding.

I'm using this:

Private Sub Form_DblClick(Cancel As Integer)
    Dim strWhere As String
    Dim stDocName As String
    stDocName = "VIEW_PROFILE"
    'assumes Staff_ID is numeric
    strWhere = "Staff_ID=" & Me.STAFF_ID
    DoCmd.OpenForm stDocName, , , strWhere, acReadOnly
End Sub

VIEW_PROFILE is a main form with two subforms that are linked through the primary key STAFF_ID. When this code opens VIEW_PROFILE, the main part of the form displays the data for the right STAFF_ID record, but the two subforms don't. They only show the first record from their tables on every form. Is there something I need to put into the on Dbl_Click event or VIEW_PROFILE on Load event to make STAFF_ID carry down to the two subforms?

 

[hillary123]

I think I have it figured out now, I redid some of the code and whatever wasn't working is working now. Thanks.