list box doesn't store the selected value until closing the form

I created a bound list box. But it doesn't store the selected value until I
close that form.

A form has a list box which let user select which year, then go to next form
to select which report. The report will give that year's report.

This is how I created the list box:
1. in form window, click "new" button
2. select "design view" and also chose the table where the object's data
come from (In fact, I am very confusing here. I think this table here I chose
is the field list for this form and will store what user selected. But it
reads like it is the row source data for this form. Or, I am wrong? I should
select the row source table here? But if I chose row source table, later in
control wizard, I have to store the selected value in this table. this is not
right. I need another table to store what user select)
3. then in form design view (control wizard is selected); then I click list
box and drop it in detail area.
4. then follow the wizard. chose its row source table, which field to store
data...
5. finish creating list box. it only have one column.
6. then I added a "open form" command button. this button will bring user to
next form to select which report user want to see. The list box should stored
the value selected and the table which stored this value is connected to many
queries. Then the report will correspondingly show that year's report.

When I select 2003, report will not show 2003's. I have to close the form
which contains this list box. reopen it, select whatever year, report will
show 2003's result.

Do I need to write some VBA code?

Thank you so much for your great help!
Lin

"Lin" wrote in message

I created a bound list box. But it doesn't store the selected value
until I close that form.

A form has a list box which let user select which year, then go to
next form to select which report. The report will give that year's
report.

This is how I created the list box:
1. in form window, click "new" button
2. select "design view" and also chose the table where the object's
data come from (In fact, I am very confusing here. I think this table
here I chose is the field list for this form and will store what user
selected.

Right.

But it reads like it is the row source data for this form.

I'm not sure what you mean, but form's don't have "row sources", they
have "record sources". Combo and list boxes have row sources. The
table you choose at this point should be the table where the list box's
value should be stored, not the one from which the items in the list are
drawn.

Or, I am wrong? I should select the row source table here? But if I
chose row source table, later in control wizard, I have to store the
selected value in this table. this is not right. I need another table
to store what user select)
3. then in form design view (control wizard is selected); then I
click list box and drop it in detail area.
4. then follow the wizard. chose its row source table, which field to
store data...
5. finish creating list box. it only have one column.
6. then I added a "open form" command button. this button will bring
user to next form to select which report user want to see. The list
box should stored the value selected and the table which stored this
value is connected to many queries. Then the report will
correspondingly show that year's report.

When I select 2003, report will not show 2003's. I have to close the
form which contains this list box. reopen it, select whatever year,
report will show 2003's result.

Do I need to write some VBA code?

Yes, but only one line. The problem is that, when you choose the year
in the list box, you have modified the form's current record, but you
haven't yet saved that record. What you need to do is add a line to the
Click event procedure for the "open form" command button, to save the
current record before opening the form. So if it currently looks like
this:

'---- start of example "original" code -----
Private Sub cmdNextForm_Click()
On Error GoTo Err_cmdNextForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NextForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNextForm_Click:
Exit Sub

Err_cmdNextForm_Click:
MsgBox Err.Description
Resume Exit_cmdNextForm_Click

End Sub

'---- end of example "original" code -----

.... you would modify it to look like this:

'---- start of example "corrected" code -----
Private Sub cmdNextForm_Click()
On Error GoTo Err_cmdNextForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NextForm"

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNextForm_Click:
Exit Sub

Err_cmdNextForm_Click:
MsgBox Err.Description
Resume Exit_cmdNextForm_Click

End Sub

'---- end of example "corrected" code -----

That ought to take care of the problem.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,

Thank you so much for your help! Yesterday, I added another command button
on that form to "save record". Then I copy that button's code into the "open
next form" command button. Although it works, I know that is not "normal" way
to do it.

Now I am using your code to do it again. And you clarified my
misunderstanding about row sources and record sources.

Lin

"Dirk Goldgar" wrote:

"Lin" wrote in message

I created a bound list box. But it doesn't store the selected value
until I close that form.

A form has a list box which let user select which year, then go to
next form to select which report. The report will give that year's
report.

This is how I created the list box:
1. in form window, click "new" button
2. select "design view" and also chose the table where the object's
data come from (In fact, I am very confusing here. I think this table
here I chose is the field list for this form and will store what user
selected.

Right.

But it reads like it is the row source data for this form.

I'm not sure what you mean, but form's don't have "row sources", they
have "record sources". Combo and list boxes have row sources. The
table you choose at this point should be the table where the list box's
value should be stored, not the one from which the items in the list are
drawn.

Or, I am wrong? I should select the row source table here? But if I
chose row source table, later in control wizard, I have to store the
selected value in this table. this is not right. I need another table
to store what user select)
3. then in form design view (control wizard is selected); then I
click list box and drop it in detail area.
4. then follow the wizard. chose its row source table, which field to
store data...
5. finish creating list box. it only have one column.
6. then I added a "open form" command button. this button will bring
user to next form to select which report user want to see. The list
box should stored the value selected and the table which stored this
value is connected to many queries. Then the report will
correspondingly show that year's report.

When I select 2003, report will not show 2003's. I have to close the
form which contains this list box. reopen it, select whatever year,
report will show 2003's result.

Do I need to write some VBA code?

Yes, but only one line. The problem is that, when you choose the year
in the list box, you have modified the form's current record, but you
haven't yet saved that record. What you need to do is add a line to the
Click event procedure for the "open form" command button, to save the
current record before opening the form. So if it currently looks like
this:

'---- start of example "original" code -----
Private Sub cmdNextForm_Click()
On Error GoTo Err_cmdNextForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NextForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNextForm_Click:
Exit Sub

Err_cmdNextForm_Click:
MsgBox Err.Description
Resume Exit_cmdNextForm_Click

End Sub

'---- end of example "original" code -----

.... you would modify it to look like this:

'---- start of example "corrected" code -----
Private Sub cmdNextForm_Click()
On Error GoTo Err_cmdNextForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NextForm"

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNextForm_Click:
Exit Sub

Err_cmdNextForm_Click:
MsgBox Err.Description
Resume Exit_cmdNextForm_Click

End Sub

'---- end of example "corrected" code -----

That ought to take care of the problem.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)