OpenForm on Subform on Tab Control

[wiklendt]

I have tried to use

                .FindFirst "CallID = " & Me![lstsearch]

but it tells me that this method cannot be used.

mafhobb

it would seem that your lstSearch bound column is ContactID.

if you want to also use the CallID column from the same list box, you need to tell access to use a different column to the bound one.

remember access begins counting with 0. so column 3 in a listbox would actually be .Column(2) for access.

so try

"CallID = " & Me![lstSearch].Column(X)

where X is the column number -1 that you have your CallID at in the listbox.

but sorry, i don't know if you can use .FindFirst in this methodology.

 

[wiklendt]

The code in the double click event (thanks for other people's help) in the listbox is the following:

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Contacts"
    stLinkCriteria = "[ContactID]=" & Me![lstsearch]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

      With Forms(stDocName)![Call Listing Subform]
              .SetFocus
              .Form!CallID.SetFocus
       End With

This opens the form and subform, but the focus goes to the 1st CallID item in the list, not the one I need.

Ok, so i have a similar code and problem. i am double-clicking a listbox that i am using as a search tool. once double-clicked, the code ought to take the user to the patient form, select the correct patient (not by filtering the form record source, because i still want the form to be used to view other patients and other isolates), then also select the correct isolate for that patient in a subform.

i.e.

Parent form: frmPatients
Listbox on parent form: lstPatients
Sub form: fsubIsolates (Master/Child key: PatientID)
Listbox on sub form: lstIsolates

i have a listbox on frmPatients (lstPatients) which, when clicked, finds the correct record in frmPatients. The same for the isolates subform (a listbox of existing isolates, which goes to the correct record there)

now, when i double-click on the search form, the frmPatients opens, and the correct record is selected in lstPatients, but the form does not go to that record (i.e., the AfterUpdate/Click even it not fired).

how can i use the code below to include firing the event of the listbox to not only select the correct record in the form, but to go to it without limiting the form to just that one record?

i've tried ![lstPatients].Afterupdate = "[Event Procedure]", but i read in the help file that is not for firing code, but on the event the code is fired... which in my case it doesn't seem to...

Code: lstSearch_DblClick from a separate form

    Dim strDocName As String
    
    strForm = "frmPatients"
    DoCmd.OpenForm strForm
    
    With Forms(strForm)
         ![lstPatients].Value = Me.lstIsolateSearch
         ![fsubIsolates].SetFocus
         ![fsubIsolates].Form!lstIsolates.Value = Me.lstIsolateSearch.Column(1)
    End With

here's a screen of the form/subform for those more visually-atuned...

thanks.

 

[wiklendt]

found a solution for both me and mafhobb! i ended up adding a .FindFirst to my code, but you have to prefix it with a .Recordset, mafhobb:

    With Forms(strForm)
         ![lstPatients].Value = Me.lstIsolateSearch [COLOR="SeaGreen"]'highlight correct item in listbox[/COLOR]
         [COLOR="Red"].Recordset.FindFirst "PatientID = " & Me.lstIsolateSearch[/COLOR] [COLOR="SeaGreen"]'goto the correct record on the form[/COLOR]
         ![fsubIsolates].SetFocus [COLOR="SeaGreen"]'now go to the subform...[/COLOR]
         ![fsubIsolates].Form!lstIsolates.Value = Me.lstIsolateSearch.Column(1) [COLOR="SeaGreen"]'highlight the correct item in the listbox[/COLOR]
         [COLOR="red"]![fsubIsolates].Form.Recordset.FindFirst "IsolateID = " & Me.lstIsolateSearch.Column(1)[/COLOR] [COLOR="SeaGreen"]'and go to the record on the subform[/COLOR]
    End With

hope this helps someone else