Listbox Requery

[AccessFreak via AccessMonster.com]

Ok... I'm going to try this again using the appropriate language...

Table = "resp" (Columns id_resp, make, model)
Form = "test" (queries "resp" based on List3.Column(0) - Therefore no records
are returned on load)
Listbox = List3 (queries "resp")
List3.Column(0) = id_type
List3.Column(1) = make
List3.Column(2) = model
id_rep (textbox populated by form query)
make (textbox populated by form query)
model (textbox populated by form query)
Label8 (Label with OnClick command)

OnClick command:

Private Sub Label8_Click()
Me.id_resp.SetFocus
DoCmd.Save acForm, "Test"
Dim i As Integer
If List3.ListCount = 0 Then Exit Sub
For i = 0 To List3.ListCount - 1
List3.Selected(i) = False
Me.List3.Requery
Next i
End Sub

Ok... here's the situation:

1. Open Form - no records (desired result)
2. Click Record in List3, Form requeries, textboxes populated - desired
results
3. Alter info "make" or "model", click Label8.

Results:
1. Table is updated properly and successfully
2. List3 does not properly requery to display correct information.
3. I have noticed that as soon as the DoCmd.Requery or Me.List3.Requery is
used, List3 "re-highlights" the record and I cannot properly requery to
update List3 as long as that particular record is highlighted.
4. If I select another record and click "Label8", then List3 properly
requeries and displays the right information.

Let me know if it's clear as mud and you need more information. This is a
test I have thrown together and am disregarding proper naming conventions,
etc for the sake of getting the process correct first. Thank you.

 

[Guest]

Requery will refresh the listbox contents from the query so there is no need
to do it in your loop wihich resets the .selected attribute. There is also no
need to do it unless the underlying query will return new data.
Are you just trying to turn the selected flags off?
Is this a multi-select listbox?

-Dorian

 

[AccessFreak via AccessMonster.com]

Single-select list box. If Label8 has been clicked, then there is new data
that needs to be reflected in List3. A requery will not do that if that
record is highlighed in List3. I have to select another record and click a
coded requery button to get it to work. I want the save to take place and
List3 to requery and display the updated information all in one click.

Requery will refresh the listbox contents from the query so there is no need
to do it in your loop wihich resets the .selected attribute. There is also no
need to do it unless the underlying query will return new data.
Are you just trying to turn the selected flags off?
Is this a multi-select listbox?

-Dorian

Ok... I'm going to try this again using the appropriate language...

[quoted text clipped - 42 lines]

test I have thrown together and am disregarding proper naming conventions,
etc for the sake of getting the process correct first. Thank you.

 

[Guest]

You don't need the requery where it is, it should go after the loop.
The listbox should also refresh whether there are selected items or not.
Are you sure the changes have already been applied to the table when you
click the button? Try doing a docmd.save before you do the requery.

-Dorian

Single-select list box. If Label8 has been clicked, then there is new data
that needs to be reflected in List3. A requery will not do that if that
record is highlighed in List3. I have to select another record and click a
coded requery button to get it to work. I want the save to take place and
List3 to requery and display the updated information all in one click.

Requery will refresh the listbox contents from the query so there is no need
to do it in your loop wihich resets the .selected attribute. There is also no
need to do it unless the underlying query will return new data.
Are you just trying to turn the selected flags off?
Is this a multi-select listbox?

-Dorian

Ok... I'm going to try this again using the appropriate language...

[quoted text clipped - 42 lines]

test I have thrown together and am disregarding proper naming conventions,
etc for the sake of getting the process correct first. Thank you.

 

[AccessFreak via AccessMonster.com]

The DoCmd.Save is performed prior to the loop.

I've verified the data is posted to the table immediately after the DoCmd.
Save. Also, when I have the requery anywhere in the sub, List3 will not
"deselect" anything. When I take the requery out, everything works except
for the requery of List3.

ID: Make: Model:
8209 3M 1161
8210 3M 1163
8211 GM 1802

If I select row 8209 and change the model 1160 and perform a DoCmd.Save, the
information successfully posts to the table. I have to now select row 8210
or 8211 and perform the requery to get the Model to change display from 1161
to 1160.

I agree the requery should have no issues, but it will update the displayed
data unless another record in the list is selected and the requery performed.

You don't need the requery where it is, it should go after the loop.
The listbox should also refresh whether there are selected items or not.
Are you sure the changes have already been applied to the table when you
click the button? Try doing a docmd.save before you do the requery.

-Dorian

Single-select list box. If Label8 has been clicked, then there is new data
that needs to be reflected in List3. A requery will not do that if that

[quoted text clipped - 15 lines]

 

[Dirk Goldgar]

Ok... I'm going to try this again using the appropriate language...

Table = "resp" (Columns id_resp, make, model)
Form = "test" (queries "resp" based on List3.Column(0) - Therefore no
records are returned on load)
Listbox = List3 (queries "resp")
List3.Column(0) = id_type
List3.Column(1) = make
List3.Column(2) = model
id_rep (textbox populated by form query)
make (textbox populated by form query)
model (textbox populated by form query)
Label8 (Label with OnClick command)

OnClick command:

Private Sub Label8_Click()
Me.id_resp.SetFocus
DoCmd.Save acForm, "Test"
Dim i As Integer
If List3.ListCount = 0 Then Exit Sub
For i = 0 To List3.ListCount - 1
List3.Selected(i) = False
Me.List3.Requery
Next i
End Sub

Ok... here's the situation:

1. Open Form - no records (desired result)
2. Click Record in List3, Form requeries, textboxes populated -
desired results
3. Alter info "make" or "model", click Label8.

Results:
1. Table is updated properly and successfully
2. List3 does not properly requery to display correct information.
3. I have noticed that as soon as the DoCmd.Requery or
Me.List3.Requery is used, List3 "re-highlights" the record and I
cannot properly requery to update List3 as long as that particular
record is highlighted.
4. If I select another record and click "Label8", then List3 properly
requeries and displays the right information.

Let me know if it's clear as mud and you need more information. This
is a test I have thrown together and am disregarding proper naming
conventions, etc for the sake of getting the process correct first.
Thank you.

I don't see any "DoCmd.Requery" statement, though you refer to it.
However, this line may at the root of your problem:

DoCmd.Save acForm, "Test"

That does *not* save the form's current record. It saves any design
changes you may have made to the form. To save the current record, you
can use

RunCommand acCmdSaveRecord

or

Me.Dirty = False

or a couple of other techniques. I prefer to set the Dirty property to
false, and usually make it conditional, like this:

If Me.Dirty Then Me.Dirty = False

if there's a reasonable chance the form might not be dirty at the time.
But it does no harm executed unconditionally, even if the form isn't
dirty.

 

[AccessFreak via AccessMonster.com]

That did the trick. Thank you to both Dirk and Dorian for your assistance.
I'm still learning more and more everyday. Now I have dug into learning the
various features of "dirty". Everything works like a champ and I ended up
with the following code:

Me.Dirty=False
Me.List3.Requery

Thanks again everybody!! This problem is now solved.

Ok... I'm going to try this again using the appropriate language...

[quoted text clipped - 44 lines]

conventions, etc for the sake of getting the process correct first.
Thank you.

I don't see any "DoCmd.Requery" statement, though you refer to it.
However, this line may at the root of your problem:

DoCmd.Save acForm, "Test"

That does *not* save the form's current record. It saves any design
changes you may have made to the form. To save the current record, you
can use

RunCommand acCmdSaveRecord

or

Me.Dirty = False

or a couple of other techniques. I prefer to set the Dirty property to
false, and usually make it conditional, like this:

If Me.Dirty Then Me.Dirty = False

if there's a reasonable chance the form might not be dirty at the time.
But it does no harm executed unconditionally, even if the form isn't
dirty.