Open a second form to that uses data queried and selected on the first form

[graccess]

I know this question might have been addressed earlier today on this forum but I might have a slight nuance to the problem. When clicking on one of the results, I wish to open a second form and view the information in this new format as well as print or email that form. The issue I have is how to populate the record source since my fieldname could be one of three possibilities, and I'm not sure what to choose as my control name.


To explain further, I have a complex query with multiple criteria and it feeds the first form with the help of three combo box filters. I currently use this to display at least several rows of data that had matched the filter, not all the filters are used at once, typically only one gets applied depending on the search. These results are based on several tables as well as a query on query, hence my complex query. If I wanted to use that result, would I use the query that provided the first form's data as my record source for the 2nd form or would I create a completely new query based on the results?

I read the baldyweb link (http://www.baldyweb.com/wherecondition.htm) but I'm not sure how to apply this correctly to my problem.

thx in advance.

[ItsMe]

It sounds like you want to create a search form. There are basically two approaches to this. You can use a dynamic parameterized query or you can use VBA to create a WHERE clause or filter criteria.

The easiest may be to use a parameterized query based on a main form and display the results in a subform. I prefer VBA.

[graccess]

Want to have a 2nd form which only displays data, maybe with one control. Not sure if that makes it a search form.

With vba, would I just use current record source and then open new form? Before I saw your post I was considering making my filtering query fire the main form into a create table query. This way I'd have a unique identifier key to load the form with the data I wanted. Not sure that would work either.

How would you write the code for the parameterized query? And I assume you'd place the code in a double click event on the field names so that the user could click the record and launch the form with that filtered selected data? How would you build the 2nd form, all unbounded fields or with a record source to the filtering query?

[ItsMe]

If the first form is bound AND ALL OF ITS RELAVANT CONTROLS are bound, you could use its filter property as a criteria for a new form/recordset.

something like

Dim strWhere as string
strWhere = ""
if me.filteron = true then
strWhere = Me.filter
end if

DoCmd.OpenForm "SecondFormName", , , strwhere

[graccess]

The first form has a main form with unbound controls. It has a subform which is bound to a query that uses the unbound controls to filter. Would that code still apply?

[ItsMe]

You would need to get the filter property of the subform container. So....
strWhere = Me.filter
will get filter property of the main form (if we a reworking in the main form's module)

Change it to something like
strWhere = Me.SubformContainerName.filter
Maybe

strWhere = Me.SubformContainerName.Form.filter

I can't remember which.


Now, having said that,.... How the heck are you managing to apply a filter to the subform????

[graccess]

The subform is bound to a query with criteria set by the main form controls. After I select the filter on the control, I have a command button to requery the subform.
Having said that, the subform then displays several records so I will try that code on an event to open a 2nd form and hopefully populate that form with the data that I clicked on. Make sense?

[ItsMe]

Hmmmm, not understanding it. But, that doesn’t mean you are not explaining it right. I don't normally use parameterized queries and I am unfamiliar with "select the filter on the control".

You might try something like
strWhere = Me.SubformContainerName.Form.filter
msgbox strwhere

or
strWhere = Me.filter
msgbox strwhere

to make sure the filter property is returning something.

[graccess]

Hmmmm, not understanding it. But, that doesn’t mean you are not explaining it right. I don't normally use parameterized queries and I am unfamiliar with "select the filter on the control".

You might try something like
strWhere = Me.SubformContainerName.Form.filter
msgbox strwhere

or
strWhere = Me.filter
msgbox strwhere

to make sure the filter property is returning something.

I'm probably not explaining it correctly, lol.... I did try the code:
strWhere = Me.filter
msgbox strwhere

in the new form but i received an 'Invalid outside procedure' error when I ran my first form.

Not sure if it would help any but to give more detail how it's working thus far, my 1st form has a main and subform to it. the main has three unbound combo boxes for selecting to filter a selection of tables by company, or industry or by product category. the selection is made in one of those boxes then a command button will query the subform. the subform will return multiple entries fitting the criteria set by those unbound boxes in the mainform. from here, i get stuck. everything works fine until I try to click on one of the multiple entries or query records. I'd like to open up a new form that lays out all the details of that selection in the subform. i've been able to do this in another form but that was linked to one table and a form that was ordered by the key field, so no problems there. the tricky part here is I don't know what to use as the control name that contains the values to be shown on the second form because i have three controls and the one used could change each instance. besides, after the filter, I'm still selecting one of several values getting returned. did that explain it better or not so much? thanks again for trying to help me out here.

[ItsMe]

Neither your Main Form or Subform have filter properties. The main form dictates the recordsource for the subform via a parameterized query. There is not a filter property to retrieve. The relative data for the subform is contained within its Recordset. You can duplicate this via DAO and create a recordset clone or duplicate the query using the criteria in the Main Form.

What is it that is not in your subform that you want to show in another form? Why not create a second parameterized query based on your Main Form's unbound controls?

[graccess]

Because after I filter I want to select one of the returned records from the remaining list in the subform and display only that one.

The query and its criteria doesn't filter the list down to just one result. A selection must be made among the filtered results to view in greater detail. I'm also not displaying all the related info in the subform. I'm beginning to lean on using the same query to create a new table of the results so whichever selection is made i can at least have a key field ID to load the second form with. Does that sound like a good approach? Otherwise I don't know how to display my selection in greater detail on another form.

[ItsMe]

I have done something like that before. I would create code in the Form's VBA module, the form that is the subform. I would use the double click event of the control the user considers as the one that identifies the unique record (like the client name). Then I would use VBA to grab the ID from that current record, store it in a variable.

I would concatenate that variable with the relevant control values from the main form, using the same double click event use Docmd.Openform and include the WHERE criteria that is your concatenated string.

Something like this, assuming number values for your controls...

Dim lngClientID As Long
Dim strWhere As String
If Forms!NameOfMainForm.Forms.NameofControl1.Value = Null Or Forms!NameOfMainForm.Forms.NameofControl2.Value = Null Then
MsgBox "Please make a selection before opening form."
Exit Sub
End If
lngClientID = Me.ClientID.Value
strWhere = "[ClientID] = " & lngClientID & " AND [Field1] = " & Forms!NameOfMainForm.Forms.NameofControl1.Value & _
           " AND [Field2] = " & Forms!NameOfMainForm.Forms.NameofControl2.Value
DoCmd.OpenForm "frmFormName", acNormal, , strWhere

[graccess]

I have done something like that before. I would create code in the Form's VBA module, the form that is the subform. I would use the double click event of the control the user considers as the one that identifies the unique record (like the client name). Then I would use VBA to grab the ID from that current record, store it in a variable.

I would concatenate that variable with the relevant control values from the main form, using the same double click event use Docmd.Openform and include the WHERE criteria that is your concatenated string.

Something like this, assuming number values for your controls...

Dim lngClientID As Long
Dim strWhere As String
If Forms!NameOfMainForm.Forms.NameofControl1.Value = Null Or Forms!NameOfMainForm.Forms.NameofControl2.Value = Null Then
MsgBox "Please make a selection before opening form."
Exit Sub
End If
lngClientID = Me.ClientID.Value
strWhere = "[ClientID] = " & lngClientID & " AND [Field1] = " & Forms!NameOfMainForm.Forms.NameofControl1.Value & _
           " AND [Field2] = " & Forms!NameOfMainForm.Forms.NameofControl2.Value
DoCmd.OpenForm "frmFormName", acNormal, , strWhere

Thanks a lot, this code makes sense, I think this might work for me. Going to insert this but just to be sure I'm following you correctly... Do I include in the code portion for strWhere each field name that I want in the new form, or every field that is used in the data that I'm double clicking, or just the field names from the main controls that I used to filter the subform? Looks like just the control names but wanted to be certain.

[ItsMe]

You mentioned that you have unbound controls on your main form. There is a query that looks at these controls to determine the recordsource for the subform. You want to open another form based on (basically) the subform’s recordset. So grab the values from the controls in the main form, just like the query did and add the record ID for the record the user double clicks.

The act of the user double clicking any control on a given record within the subform will "select" said record. The user can double click Field277 or Field1 or FieldPK, regardless of which control is clicked, only one record/row will be selected/returned. Which control name you choose to place your code for the Double Click Event is determined by which control the user will be clicking. You could place the code behind each control’s double click event, if you so desired.

The following will initialize your variable with the PK value of the record selected.
lngClientID = Me.ClientID.Value
Or
lngClientID = Me![ClientID]!Value
Adjust the text in red to the name of the field for the PK.

This is adding the same PK value to your string variable that is your Where criteria.
"[ClientID] = " & lngClientID
Adjust the text in red to the name of the field for the PK. Notice the name of the variable does not matter as long as you spell it the same after you declare it.

This is how you reference a control on a form that is not the form you are working behind. When writing VBA in a form's module you can use the Me shortcut. If you are referencing a form outside of the Class Module you are working in, you need to use the fully qualified name of the Object. In this case you are going after a control that resides on a form.
Forms!NameOfMainForm.Forms.NameofControl1.Value
Adjust the names of the objects


I made a couple of guesses as to how your forms/DB are set up. The code may need a couple of adjustments. Also, it is air code that I just typed out so... give it a try and let us know how it goes.

[graccess]

Didn't get too far before getting a run time error 2465, ' Application-defined or object-defined' error. I used the code you had in post#13 replacing my names naturally but I think I'm getting stuck on the syntax for the fieldname control? Does anything jump out at you that looks incorrectly defined?

Private Sub Ticker_DblClick(Cancel As Integer)
Dim lngCompanyID As Long
Dim strWhere As String


If Forms!frmuserresearch.Forms.[cboRTicker].Value = Null Or Forms!frmuserresearch.Forms.[cboRIndustry].Value = Null Or Forms!frmuserresearch.Forms.[cboRSubsector].Value = Null Then
MsgBox "Please make a selection before opening form."
Exit Sub
End If
lngCompanyID = Me.CompanyID.Value


strWhere = "[CompanyID] = " & lngCompanyID & " AND [cboRTicker] = " & Forms!frmuserresearch.Forms.cboRTicker.Value & _
" AND [cboRIndustry] = " & Forms!frmuserresearch.Forms.cboRSubsector.Value & " AND [cboRSubsector] = " & Forms!frmuserresearch.Forms.cboRSubsector.Value

DoCmd.OpenForm "frmResearchDetail", acNormal, , strWhere


End Sub