Double Click on SubForm and Open Up New Form Filtered on Record (1 Viewer)

Mari111 · Feb 9, 2018

I have a subform with a list of records and I want to set up an operation where a user can double click on one of the records in the subform and open up another form with the details for that specific record.

I have created a Double Click event on the subform which opens the second form, but I can’t make the second form filter and bring up the details for that specific record only. Instead, it brings up the oldest record with a continuous form set up (which I’ve tried to set to ‘single form’ and ‘current record’ but this doesn’t seem to have had much effect). Can you help? Here is my code and property settings:

On the subform I want to double click on I have:

Code:

[COLOR=#1f497d]Private Sub Form_DblClick(Cancel As Integer)[/COLOR]
[COLOR=#1f497d] [/COLOR]
[COLOR=#1f497d]    Dim RecordId As Integer[/COLOR]
[COLOR=#1f497d]   [/COLOR]
[COLOR=#1f497d]    DoCmd.OpenForm "FrmRecordDetails", acNormal, , , acFormEdit, acWindowNormal, Me.RecordName[/COLOR]
[COLOR=#1f497d]    [/COLOR]
[COLOR=#1f497d]    Forms("FrmRecordDetails").Requery[/COLOR]
[COLOR=#1f497d]End Sub[/COLOR]

For properties on the form I want opened on double click (i.e. FrmRecordDetails), I have put (on data tab in properties):

Record Source: qryRecordDetails
Recordset Type: Dynaset
Fetch Defaults: Yes
Filter: RecordName=
Filter On Load: Yes
Order By On Load:Yes
Wait for Post Processing: Yes
Data Entry: No
Allow Additions: Yes
Allow Deletions: No
Allow Edits: Yes
Allow Filters: Yes
Record Locks: No Locks

Therefore I want the database to filter on RecordName, which I have listed in the subform.

Can anyone tell me why this is not filtering on the record when the record details form is opened?

Mark_ · Feb 9, 2018

Should be

Code:

Private Sub Form_DblClick(Cancel As Integer)

    Dim RecordId As Integer
[COLOR="RoyalBlue"]    Dim asFilter as String
    asFilter = "What ever you need to identify the current record"
[/COLOR]
    DoCmd.OpenForm "FrmRecordDetails", acNormal, ,[COLOR="royalblue"]asFilter [/COLOR], acFormEdit, acWindowNormal, Me.RecordName

    Forms("FrmRecordDetails").Requery
End Sub

For help with the DoCmd.OpenForm, look HERE

Mari111 · Feb 13, 2018

Hi Mark,

Thanks for your response. I changed my code and it comes up with run-time error 13: type mismatch. My code is currently:

Code:

Private Sub Form_DblClick(Cancel As Integer)
    Dim RecordId As Integer
    Dim asFilter As String
    asFilter = "VolumeName"
   
    DoCmd.OpenForm "FrmUpdateDetails", acNormal, asFilter, acFormEdit, acWindowNormal, Me.VolumeName
    
    Forms("FrmUpdateDetails").Requery
End Sub

Do you know what is causing this? the VolumeName field is nvarchar data type and I haven't had any conflicts until now. All the VolumeNames in my database should match the nvarchar type.

Thanks
Mari

Minty · Feb 13, 2018

asFilter = "VolumeName" - This isn't telling access to do anything.

It should be something like

Code:

  asFilter = "[VolumeName] = 'AnyStringValue'"

So if Me.VolumeName is the field in your sub form it would be

Code:

 asFilter = "[VolumeName] = '" & Me.VolumeName & "'"

Mari111 · Feb 13, 2018

Thanks, the details form now comes up blank though when I double click the subform.

I've tested the details form by opening up by itself (when not embedded in another form) and it comes up with data in it as it is supposed to.

How do I change it so that it doesn't come up blank?

Thanks

Minty · Feb 13, 2018

Can you post up the full code as you have it now, include the names of the Form, Sub form and the form you are opening.

I notice you are passing the current value of Me.VolumeName as the OpenArgs property (See in red below), are you doing anything in the form based on this?

Code:

DoCmd.OpenForm "FrmUpdateDetails", acNormal, asFilter, acFormEdit, acWindowNormal,[COLOR="Red"] Me.VolumeName[/COLOR]

Mark_ · Feb 13, 2018

Mari,

Most often you would be passing the unique record identifier as your filter. Normally this would be something like

Code:

"[RecordDetails_ID] = " & Me.RecordDetails_ID

As you are using "VolumeName" (had been "RecordName" before?) can you verify that the value being passed is unique in your table? Or that it exists?

One of the reasons I fill a string variable is so that I can use MsgBox or Debug.Print to tell me exactly what it is going to do before it does it. It helps identify those annoying times when you THINK you are filling it with a value but you accidentally reference the wrong field/variable.

Mari111 · Feb 13, 2018

So my code is currently:

Code:

Private Sub Form_DblClick(Cancel As Integer)
    Dim RecordId As Integer
    Dim asFilter As String
    asFilter = "[VolumeName] = '" & Me.VolumeName & "'"
   
    DoCmd.OpenForm "FrmUpdateDetails", acNormal, asFilter, acFormEdit, acWindowNormal
    
End Sub

VolumeName (yes this was RecordName before for the forum) is unique in my table. It is not the primary key (which is "LoadId") but there should only be one of each VolumeName and it definitely exists because I'm double clicking on it in the subform!

The Me.VolumeName was an argument I tried before, so that on double click the form would filter on the VolumeName selected, but this hasn't worked.

Mark_ · Feb 13, 2018

Mari,

Between setting asFilter and opening the form, I would recommend adding

Code:

msgbox "My filter is " & asFilter

Note what VolumeName is prior to doubleclicking.
Note what you are setting your filter to.

This way we can help track where you are having the problem.

Mari111 · Feb 14, 2018

Hi,

Thanks again, so my code is now:

Code:

Private Sub Form_DblClick(Cancel As Integer)
    Dim RecordId As Integer
    Dim asFilter As String
    asFilter = "[VolumeName] = '" & Me.VolumeName & "'"
    MsgBox "My filter is " & asFilter
   
    DoCmd.OpenForm "FrmUpdateDetails", acNormal, asFilter, acFormEdit, acWindowNormal
    
End Sub

And so when I double click the list of volumes in the subform, the message box comes up and it names the filter correctly as the Volume Name that I've selected. So that seems fine. Yet the details form is still coming up blank and this is the problem. I've checked the settings of the details form (see 1st post) and data entry is set to 'no', so it's not this (as from experience it can be).

Any suggestions?

JHB · Feb 14, 2018

You're missing a ,

Code:

DoCmd.OpenForm "FrmUpdateDetails", acNormal, asFilter, [COLOR=Red][B],[/B][/COLOR] acFormEdit, acWindowNormal

Mari111 · Feb 14, 2018

Thanks, that extra comma means that the details form is now no longer blank, but it still not filter on the record I select despite the message box showing the correct VolumeName as the filter.

Any answers?

Minty · Feb 14, 2018

What code do you have in the OnLoad and Open event properties of the form FrmUpdateDetails ?

JHB · Feb 15, 2018

Sorry, you've to move the ,

Code:

DoCmd.OpenForm "FrmUpdateDetails", acNormal, [COLOR=Red][B],[/B][/COLOR] asFilter, acFormEdit, acWindowNormal

Here is some note from the Help file it explain what expected for the Filtername argument: FilterName Optional Variant A string expression that's the valid name of a query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) in the current database.

Mari111 · Feb 15, 2018

Thanks JHB, the moving of the comma solved the problem

Many thanks!

JHB · Feb 15, 2018

You're welcome, good luck.

Double Click on SubForm and Open Up New Form Filtered on Record (1 Viewer)

Mari111

Registered User.

Mark_

Longboard on the internet

Mari111

Registered User.

Minty

AWF VIP

Mari111

Registered User.

Minty

AWF VIP

Mark_

Longboard on the internet

Mari111

Registered User.

Mark_

Longboard on the internet

Mari111

Registered User.

JHB

Have been here a while

Mari111

Registered User.

Minty

AWF VIP

JHB

Have been here a while

Mari111

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread