Open Close Forms

[Trogdor]

Hi All,

I have created a simple search ability for my database, where I can put in my search parameters, and using a drop down box pick the field to be searching in. Then I open another form where I display in a listbox the query generated from the search parameters.

What I want to do now is to be able to load the main form with the appropriate record number selected in the listbox. Make sense?

I know which record I want to open, as I am finding that in my query but I am getting confused with the opening / closing of the forms and open the main form to the record I want.

here is what I have:

    DoCmd.OpenForm "frmMain"
    DoCmd.GoToRecord , , Searchtable.Value
    Form.SetFocus
    DoCmd.Close

Thanks in advance.

Trogdor

 

[John Big Booty]

Given that you are using a query to populate a list box on "frmMain" I wouldn't have thought the DoCmd.GoToRecord line to be necessary.

Now I'm guessing that you want DoCmd.Close to close the form that is opening "frmMain", in which case you will need to explicitly tell it which form to Close, so your command line will need to look something like;

DoCmd.Close acForm, "FRM_FrmNameYouWishToClose", acSaveYes

So you should be able to condense the code down to;

DoCmd.OpenForm "frmMain"
DoCmd.Close acForm, "FRM_FrmNameYouWishToClose", acSaveYes

 

[Trogdor]

Thanks for the reply John Big Booty,

So I am not sure I explained myself properly. I have two forms frmMain, and frmSearch, frmSearch being the form with the listbox showing the results of the query.

I want to select a result from the query and then goto that record on the frmMain.

At the moment, thanks to your docmd.close line I am only opening the frmMain and closing the frmSearch.

The frmSearch is just a pop-up form over the top of frmMain so I don't really need to open it, I just need it to goto the selected record. I tried this:

DoCmd.OpenForm "frmMain", , , lbSearch.Value

and I have tried:

DoCmd.GoToRecord "frmMain", , acGoTo = lbSearch.Value

Cheers.

 

[John Big Booty]

In that case you could do something like;

    Dim stDocName As String
    Dim stDocName1 As String

    Dim stLinkCriteria As String

    If IsNull(Me.ListBoxName) Then
        MsgBox "Please make a Selection in the ListBox"
        Me.ListBoxName.SetFocus
        Exit Sub
   End If

    stDocName = "frmMain"
    stDocName1 = "frmSearch"
    
    stLinkCriteria = "[ID]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, stDocName1, acSaveYes

This will open the form filtered to the record/s which match the ID of the current record in your List Box on frmSearch.

If however you don't want a filtered result but still want the full set of records available try DoCmd.FindRecord this will find a specific record.

 

[John Big Booty]

The GoToRecord Method can not search for a specific record it can only go to acFirst, acLast, acNext, acPrevious or acNew. The acGoto argument can only got to a specific number record ie. record 5, or 10 etc. it can't search as you are trying to do, so use the FindRecord Method.

 

[Trogdor]

cheers,

the DoCmd.OpenForm stDocName, , , stLinkCriteria worked a treat, thanks! Didn't realise I had to use [ID]= id_number.

Thanks again!

 

[SOS]

Do NOT use acSaveYes when closing a form. It will save filters and stuff that you probably do not want to save. acSaveYes or acSaveNo have nothing to do with RECORDS, it has to do with DESIGN CHANGES to the form or report you use this with. So you should use:

DoCmd.Close acForm, Me.Name, acSaveNo

in most cases.

 

[Trogdor]

SOS.. OK cool, thanks. I was wondering what that actually did.

I have another quick idea, but I haven't tried it yet. At the moment in my frmSearch I have the listbox with the query, and I select one and click a button to confirm. Can I also have it so that when I doubleclick the nominated result in the listbox that I can do the same thing as the btnOK?

 

[Trogdor]

Ignore my last tidbit. I was having issues with it yesterday, but I re-compacted the file and issues are gone.