Form Selection - Refresh Listbox 1

[Allilue]

Hello!

I have a form where there are 2 selections to bring up records in a subform. The 1st selection is Month (combo box) and the 2nd selection is Name (List Box).

When I select the Month, this automatically brings up all records associated with the selection (ok). Then I select Name, and this filters the records to show the correct dataset (ok).

The issue I have is after I've made my first selection, I go back to change the Month selection, and the records will not show the Name selection that the Listbox is already on. It instead forgets the Listbox selection so you have to re-select the Name.

Is there a way to allow the Listbox to remember it's previous selection?

Thanks,
Allison

 

[Darrylles]

Hi,

You have to set the queries used by either list box to be dependent on the value displayed in the other list box.

1) The Name list box should have a value for ALL.
When the user selects a name, or a new month then you would refresh the form with a query such as:

IF cmbName <> "ALL" THEN
  SQLStr = "SELECT * FROM table WHERE [month] = " & cmbMonth &  AND [name] =" & cmbname & ";"
ELSE
  SQLStr = "SELECT * FROM table WHERE [month] = " & cmbMonth & ";"
ENDIF

me!recordsource = SQLStr
me.requery

This may not be syntactically correct, but you get the idea?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Allilue]

Hiya, thanks!

I've gotten this to work and there seems to be just one thing left before I'm done here! )

All of my queries/forms are working fine. Now it's just the report that is not picking up the selected Date from the Combobox on the form. I'm getting 3X of each record because the query it's working from has Jan-07/Feb-07/Mar-07 so it seems like it's not linking the date. So in the query I've entered in the date field [forms]![frmSalesForecast]![SelectDate]. When I test the query, it works fine and only outputs the records with the entered date.

But when this is working, I try to open the Report and I get an error message saying YOU CAN'T GO TO THE SPECIFIED RECORD. YOU MAY BE AT THE END OF THE RECORDSET.

I'm 'hoping this is an easy fix?

Help!
Thanks!

 

[Allilue]

just wanted to add...

When the form is already open, then i enter [forms]![salesforecast]![selectdate] in the query, the report comes out fine! so look like i just need to get the form open....

 

[Darrylles]

Hi Al,

Place a text box on your form called txtSelectDate (you can hide it later).

Whichever way you do it, make sure that just before your report is run, this text box (txtSelectDate) contains the correct date e.g. Feb-07 or Mar-08 etc.

In your query enter: [forms]![frmSalesForecast]![txtSelectDate].

Now, when your report runs - it will use the date contained in the text box.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Allilue]

Great, thanks! Works fine now!.. )