OpenForm Criteria vs.Open Args? 4

Vittles · Jun 27, 2005

I have a pop up search form that I use to find National level Contact names (located in a Main Form) that are connected to Local level Contact names (located in that Main Form's Subform). [This form pulls one or more National Names in connection with a Local name, as each local name could be entered under several national level records.]

[The regular system find mechanism doesn't work to search 'all' records for both main & subforms - so I created multiple mini-search forms for various needs.]

When the National level Contact search results are populated into the search form, I want users to be able to double click on the National level Contact name and return to the main form with that record in focus. Then they can go to the subform record if they want (I couldn't figure out how to take them to that exact subform record). While they use the pop up search form, the main form remains open with the pop up being a modal form on top.

I am using the OpenForm command on doubleclick event of the pop up form's search results field to do the following vba code:

Private Sub CTU_PI_Name_DblClick(Cancel As Integer)
On Error GoTo Err_CTU_PI_Name_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Main Admin Book Form" 'Main data entry form name
stLinkCriteria = "[CTU ID]=" & Me![CTU ID] 'go to the record where the primary key fields match
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CTU_PI_Name_Click:
Exit Sub

Err_CTU_PI_Name_Click:
MsgBox Err.Description
Resume Exit_CTU_PI_Name_Click

DoCmd.Close "CRS SL Search" 'close Pop up main form
End Sub

This code works well for filtering the form to just that record, but I don't want it 'filtered' as much as just having the selected record in focus.

I have read some on the openargs portion of the openform command, but I am not sure how to apply it to this situation as both forms are actually open.... I have also tried to play with DLookup. Worst case scenerio is to have a 'filter off' button on the main form that removes the search filter when staff are done with that specific record.

Any other ideas would be great!

Specific database/form info:
Main data entry form: Main Admin Book Form
Related subform: CRS Info subform
Main table: CTU Info (one side of rel. join)
Subform table: CRS Info (many side of rel. join)
Popup Form: CRS SL Search
Popup Form subform: CRS SL Search Results
Popup subform Field linked to event: CTU PI Name
Linked primary key fields at national level: CTU ID
Linked primary key fields at local level: CRS ID

PHV · Jun 27, 2005

Have you tried to play with the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the Form/DAO.Recordset objects ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

TheAceMan1 · Jun 27, 2005

How are ya Vittles . . . . .

Your really looking for two ID's, [blue]CTU ID[/blue] to [blue]synchronize[/blue] the [blue]mainform[/blue] and [purple]CRS ID[/purple] to [purple]synchronize[/purple] the [purple]subform[/purple].

If [purple]CRS ID[/purple] exists in the search results of your pop-up, try this in your [blue]DoubleClick[/blue] event (double check all names in [purple]purple[/purple]):

Code:

[blue]   Dim rst As DAO.Recordset, frm As Form, sfrm As Form
   
   Set frm = Forms![[purple][b]Main Admin Book Form[/b][/purple]]
   Set sfrm = frm![[purple][b]CRS Info subform[/b][/purple]].Form
   
   Set rst = frm.RecordsetClone
   rst.FindFirst "[[purple][b]CTU ID[/b][/purple]]=" & Me![[purple][b]CTU ID[/b][/purple]]
   
   If rst.NoMatch Then
      MsgBox "MainForm 'CTU ID' not found!"
   Else
      frm.Bookmark = rst.Bookmark
      
      Set rst = sfrm.RecordsetClone
      rst.FindFirst "[[purple][b]CRS ID[/b][/purple]]=" & Me![[purple][b]CRS ID[/b][/purple]]
             
      If rst.NoMatch Then
         MsgBox "subForm 'CRS ID' not found!"
      Else
         sfrm.Bookmark = rst.Bookmark
      End If

   End If

   Set rst=nothing
   Set sfrm=nothing
   set frm=nothing[/blue]

If the degugger stops on [blue]DAO.Recordset[/blue]:
The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[purple]Give ita whirl & let me know . . .[/purple]

See Ya! . . . . . .

Vittles · Jun 28, 2005

You are awesome - that is perfect - no changes were needed!

I was hoping that there was a way just to have one code in the double click event versus one there & then one in the main form on current or such. I just bought a new Access/VBA coding book - so hopefully I can learn what your code does & can write my own soon.

Thank you again!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

OpenForm Criteria vs.Open Args? 4

Vittles

Programmer

PHV

MIS

TheAceMan1

Programmer

Vittles

Programmer

Similar threads

Part and Inventory Search

Sponsor