Listbox Rowsource - change before opening form

[downhilljon]

Hi there,

My apologies to those whoe have already read this post - I originally tacked it onto an existing thread, but just thought I would repost to try to get a solution.

My situation is that form A and form B can be used to open form C. However, depending on which form is used to reach form C, I need to change the RowSource of a ListBox on form C to one of two queries.

I am having all sorts of trouble. Once the 'open' command is fired from either form A or B, the listbox is immediately looking for parameters for the queries before any code can be used to set the rowsource in either the Open, Load or Activate events.

This is the code from forms A and B which open form C:

'Open form
DoCmd.OpenForm "frmOutputs_9_Track_Invoices", , , , , , 2

'Open form
DoCmd.OpenForm "frmOutputs_9_Track_Invoices", , , , , , 1

This is the code for when form C opens:

Private Sub Form_Open(Cancel As Integer)

Select Case Me.OpenArgs
    Case 1 'frmOutputs_2_Specific_Search_Results' was used
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices1"
    Case 2 'frmInputs_14_Track_Invoice' was used
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices2"
End Select

End Sub

I have even tried resetting the rowsource to an empty string on closing form C, but this won't save!!! The rowsource is still set to one of the queries when I open form C again.

Private Sub Form_Close()

Me.lstResults.RowSource = ""
DoCmd.Save acForm, Me.Name

End Sub

Any suggestions are very welcome!

 

[ptm0412]

I used to do like this:
Set the Property RowSource of that listbox (lstResults) = empty
Then I make a Public Var in Module:

Public WhoCall as Interger

When open C from A:

WhoCall = 1
Docmd. OpenForm "frmOutputs_9_Track_Invoices", acNormal

When open C from B:

WhoCall = 2
Docmd. OpenForm "frmOutputs_9_Track_Invoices", acNormal

And code of C:

Private Sub Form_Open(Cancel As Integer)
Select Case WhoCall
    Case 1 
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices1"
    Case 2 
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices2"
End Select
End Sub

It is no need to make Private Sub Form_Close().

 

[ecawilkinson]

When you say it does not work, what do you mean? do you get an error mesage or does it simply seem to ignore your settings, or does it put the query name in the listbox?

in your Form_Open procedure, you could add:

Me.lstresult.rowsourcetype = "Table/Query"

If you want people to help, you have to help them a bit by giving them as much information as possible. Just saying that it does not work, is not very informative. you've got to remember that we have not ever seen your database.

HTH,
Chris

 

[downhilljon]

ptm0412,

Thank you so much, you have helped me find where I had gone wrong!

The problem was that I had already set the Rowsource value of the listbox to one of the two queries, thinking that the code would simply change it when required. Clearing the rowsource property has solved a major, major headache.

Thanks again,
Jon

 

[shiznaw]

When you say it does not work, what do you mean? do you get an error mesage or does it simply seem to ignore your settings, or does it put the query name in the listbox?

in your Form_Open procedure, you could add:

Me.lstresult.rowsourcetype = "Table/Query"

If you want people to help, you have to help them a bit by giving them as much information as possible. Just saying that it does not work, is not very informative. you've got to remember that we have not ever seen your database.

HTH,
Chris

He was pretty clear on what was the problem.

 

[Uncle Gizmo]

Shiznaw, why are you responding to an eight year old thread in a cryptic manner?

 

[HorseNutz57]

I used to do like this:
Set the Property RowSource of that listbox (lstResults) = empty
Then I make a Public Var in Module:

Public WhoCall as Interger

When open C from A:

WhoCall = 1
Docmd. OpenForm "frmOutputs_9_Track_Invoices", acNormal

When open C from B:

WhoCall = 2
Docmd. OpenForm "frmOutputs_9_Track_Invoices", acNormal

And code of C:

Private Sub Form_Open(Cancel As Integer)
Select Case WhoCall
    Case 1
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices1"
    Case 2
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices2"
End Select
End Sub

It is no need to make Private Sub Form_Close().

This is great! May solve my problem but I have a question before I re-write all of my code.... What happens in this scenario?
User opens form C from A with listbox having one row source query 1 and leaves it open
Then the user goes to B with a button that opens C with listbox with the 2nd row source query?

 

[Minty]

@HorseNutz57 - You are replying to a 5+ year old thread that before that was 8 years old! I would
a) introduce yourself
b) start a new thread - you can reference this one if you like, and you will get a more focused response.

 

[MajP]

The solution as written is bad IMO. You are tightly binding the two forms.

The calling form can simply set the rowsource of called form's listbox immediately after opening it. No need for anything else.
In the above example they did not call the second form ACDIALOG. If there is a need to do that then pass in the query name in the open args and set it in the second forms on open event.