form requery, first record problem

[stefano.troiani]

Hi,

I have a form based on a table that fetches all the records and shows
the first one when the form opens.
Inside this form I can filter the records based on a listbox choice
that simply adds a "...where..." condition to the previous "select
* ..." and Requery the form.
The filter is working well, I can see the total of records changing
according to the choice and I can browse the results, but....starting
from the second record.
Yes, the problem is that the first record never changes, no matter the
filter I choose, the first record, the one that appears on open, with
all its fields, is always there and the total of the filtered records
is always the sum of the records I filtered PLUS the first record of
the form.
I don't know how to get rid of that record when performing a filter,
any solution?

Thanks a lot,

stefano

 

[Allen Browne]

This is not normal behavior, i.e. if the record does not match the criteria,
it should not be shown. There must be some other factor at work here.

Just in case it's a bad index, try a compact/repair.

Assuming that doesn't solve it, look to see if you have code in any of the
form's events that is dirtying the record (i.e. assigning a value to a bound
contol. Look in the form's Open, Load, Current, Activate events etc, and the
events of the first control to get focus. There is never a valid reason for
dirtying a record just because you visited it (other than logging), since
that would imply that the data would be invalid if you did not visit it.

If you still cannot trace it, provide a simple example of the filter string
you are creating that gives the problem. Indicate the data type of the field
you are filtering on, and the RowSource of the list box.

 

[stefano.troiani]

Thanks for your answer.

I tried to repair the table with no luck.
The form doesn't have any code, the code is in the 3 listboxes
(before_update) for filtering and in a search box with a button,
perhaps, if I use the search textbox instead of the listboxs the
problem doesn't appear...
The code inside the listboxes is only: str = "select * from <table>
where <field> = textbox.value" ; Me.recordsource = str; Me.requery
The data type is always a text type,( I saw that the table doesn't
have a primary key, it was already done when i got it, and i guess
that i can not add it now)

The code for the searchbox is: Me.filter = "select....." ;
Me.filterOn and it works flawlessly

Thanks a lot,

stefano

 

[Allen Browne]

Try concatenating the value from the listbox into the string, e.g.:

Dim strSql As String
If Me.Dirty Then Me.Dirty = False
strSql = "SELECT * FROM MyTable WHERE MyField = """ & Me.MyListBox &
""";"
Me.RecordSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your answer.

I tried to repair the table with no luck.
The form doesn't have any code, the code is in the 3 listboxes
(before_update) for filtering and in a search box with a button,
perhaps, if I use the search textbox instead of the listboxs the
problem doesn't appear...
The code inside the listboxes is only: str = "select * from <table>
where <field> = textbox.value" ; Me.recordsource = str; Me.requery
The data type is always a text type,( I saw that the table doesn't
have a primary key, it was already done when i got it, and i guess
that i can not add it now)

The code for the searchbox is: Me.filter = "select....." ;
Me.filterOn and it works flawlessly

 

[stefano.troiani]

Thank you Allen,

since the last tip didn't work I tried to do this:

- deleted all the code inside the form, the only code left if this one
related to a listbox as you suggested.

Private Sub cuadro_comb_tiposocio_AfterUpdate()
Dim strSQL As String
Dim tipo As String
If Me.Dirty Then Me.Dirty = False
strSQL = "SELECT * FROM TAB_ASOC WHERE TIPOSOCIO = """ &
Me.cuadro_comb_tiposocio & """;"
Me.RecordSource = strSQL
End Sub

Not only the problem remains, but I found that, changing the value on
the listbox (the filter is still well working) it UPDATES the related
field in the table,
how is it possible? No code inside the form to write on the table, i
think that a simple select can't do that.

Thank you very much,

stefano

 

[Allen Browne]

The line:
If Me.Dirty Then Me.Dirty = False
causes the current edits to be saved. Since you say that this updates your
record, you have proved the record is already dirty before you set the
RecordSource.

That's exactly what I told you was going on in my first reply. You now have
to find what you have done that is dirtying the record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen,

since the last tip didn't work I tried to do this:

- deleted all the code inside the form, the only code left if this one
related to a listbox as you suggested.

Private Sub cuadro_comb_tiposocio_AfterUpdate()
Dim strSQL As String
Dim tipo As String
If Me.Dirty Then Me.Dirty = False
strSQL = "SELECT * FROM TAB_ASOC WHERE TIPOSOCIO = """ &
Me.cuadro_comb_tiposocio & """;"
Me.RecordSource = strSQL
End Sub

Not only the problem remains, but I found that, changing the value on
the listbox (the filter is still well working) it UPDATES the related
field in the table,
how is it possible? No code inside the form to write on the table, i
think that a simple select can't do that.

Thank you very much,

stefano

 

[stefano.troiani]

Thank you Allen,

since it seemed that i couldn't track the problem I decided to create
a new form from table using the wizard, so, no old code to dirty the
form, just a click on the wizard and that's it.
Then I applied this code to a listbox:

Private Sub TIPOSOCIO_AfterUpdate()
Dim stef As String
stef = "SELECT * FROM TAB_ASOC WHERE TIPOSOCIO = """ & Me.TIPOSOCIO &
""";"
Me.RecordSource = stef
End Sub

It is happening again, every time I select a new item on the listbox,
the table is automagically updated....
I am really lost....

thank you,

stefano

 

[Allen Browne]

Think about what's going on here:

a) If the list box is bound to a field, then changing the value dirties the
record.

b) If you use the AfterUpdate event of the list box to change the form's
RecordSource, Access must deal with any current edits before it can load the
records matching the new source. Therefore is saves the current edits.

If you don't intend to change the current record when you click on the list
box, then the list box must be unbound (i.e. nothing in its Control Source
property.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen,

since it seemed that i couldn't track the problem I decided to create
a new form from table using the wizard, so, no old code to dirty the
form, just a click on the wizard and that's it.
Then I applied this code to a listbox:

Private Sub TIPOSOCIO_AfterUpdate()
Dim stef As String
stef = "SELECT * FROM TAB_ASOC WHERE TIPOSOCIO = """ & Me.TIPOSOCIO &
""";"
Me.RecordSource = stef
End Sub

It is happening again, every time I select a new item on the listbox,
the table is automagically updated....
I am really lost....

 

[stefano.troiani]

Thank you very much Allen,

I finally got it working, even if, based on what you told me before,
I should have realized that the problem was the option a) (the listbox
was bound to a field) I was always looking in other
directions....shame on me...

thanks a lot again.

stefano

 

[stefano.troiani]

I forgot to ask if there is a way to NOT to update the table when
bounded fields change, but only when I click the "save" button.
I want to show the records, let the users move from one record to
another and still have the freedom not to save accidental changes when
moving between them.

thank you again.

stefano

 

[Allen Browne]

Cancel Form_BeforeUpdate, and Undo the form, e.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ... Then
Cancel = True
Me.undo
MsgBox "Changes not saved"
End If
End Sub

There are so many ways that the record can be saved, that this is the *only*
way to catch them all.

You might consider locking the controls if accidental changes are likely to
be a problem:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I forgot to ask if there is a way to NOT to update the table when
bounded fields change, but only when I click the "save" button.
I want to show the records, let the users move from one record to
another and still have the freedom not to save accidental changes when
moving between them.

thank you again.

stefano

 

[stefano.troiani]

Cancel Form_BeforeUpdate, and Undo the form, e.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If ... Then
        Cancel = True
        Me.undo
        MsgBox "Changes not saved"
    End If
End Sub

There are so many ways that the record can be saved, that this is the *only*
way to catch them all.

You might consider locking the controls if accidental changes are likely to
be a problem:
    Locking bound controls on a form and subforms
at:
   http://allenbrowne.com/ser-56.html

Thank you so much Allen.

stefano