Error 2107 when setting the forms recordsource ?

[Rob Hofkens]

Hello everyone !

I have this weird problem with an Adress Form I cannot figure out.
The function of the form is to display a list of adress names in a listbox
(left part of the form) which will synchronise the field textboxes of the
selected record (right part of the form).
So when you go through the listbox on the left you see the fields change on
the right.

When the form opens:

1) The listbox gets filled by an SQL that filters all adresses by "A".
The listbox_AfterUpdate sub creates an SQL for the me.recordsource.

2) The recordsource provides the values for record textboxes on the form
which are bound.

3) A search textbox is empty.

After I opened the form I see a list of named starting with the "A" and the
first entry in the list is shown in the record textboxes.
When I click through the list with the mouse I notice that the record
textboxes are synchronised.
No problem so far!

Now I have this search textbox that I can use to enter a name and the
listbox is updated after each character I type.
The bad thing is that this doesn't trigger the listbox_AfterUpdate sub so I
need to synchronise the recordsource after each charecter too.
I do this with the same code in as in the listbox_AfterUpdate sub.

And now I get this weird error:

A window apears with Microsoft Visual Basic caption.
(next is translated from Dutch so I might use the wrong words here but I
hope you get the point
Error 2107 during execution:
The value you used is in conflict with the validation rule that is set for
the field or control.

When I hit the error tracing button on that window it takes me to the line
where I set the me.recordsource with another SQL.
I checked and double checked to see if there are mistakes in the new SQL but
since I use the same code as in the listbox_AfterUpdate sub (wich works) it
is ok.

I googled this problem but couldn't find anything so I hope someone knows on
this newsgroup !

Thanks in advance for your advice.

Rob.

 

[Guest]

can you post the code for the after_update event you are trying to re-use in
the on_change event.

btw are you using the me.textbox value in the on_change event or the
me.textbox.text value?

TonyT

 

[Rob Hofkens]

can you post the code for the after_update event you are trying to re-use

in
the on_change event.

Here is the listbox After_Update sub:

Private Sub lstName_AfterUpdate()
ZoekAdresID (Me![lstName].Column(0))
End Sub

This is the ZoekAdresID sub :

Public Sub ZoekAdresID(strAdresID As String)
Dim strSQL As String
strSQL = "SELECT * FROM qryAdressenVolledig WHERE AdresID=" & strAdresID &
";"
Me.RecordSource = strSQL
End Sub

btw are you using the me.textbox value in the on_change event or the
me.textbox.text value?

I use the textbox.text value to create a filter :
strFilter = "Like (" & Chr(34) & Me![SearchText].Text & "%"")"

Then I create a SQL with this Filter and use it for the
Me![lstName].RowSource

After I set the new RowSource for the listbox I call the ZoekAdresID sub.
Then the error shows up.

Thanks Tony for looking into this
Hope you find out what goes wrong here ;P

Rob.

 

[Rob Hofkens]

I got tiered of this weird error and besides the problem there was something
else I noticed that didn't work very well with setting the rowsource.
So I changed the form to an unbound form and that solved all my problems.

Thanx for the help.

Rob.

can you post the code for the after_update event you are trying to re-use
in
the on_change event.

Here is the listbox After_Update sub:

Private Sub lstName_AfterUpdate()
ZoekAdresID (Me![lstName].Column(0))
End Sub

This is the ZoekAdresID sub :

Public Sub ZoekAdresID(strAdresID As String)
Dim strSQL As String
strSQL = "SELECT * FROM qryAdressenVolledig WHERE AdresID=" & strAdresID
& ";"
Me.RecordSource = strSQL
End Sub

btw are you using the me.textbox value in the on_change event or the
me.textbox.text value?

I use the textbox.text value to create a filter :
strFilter = "Like (" & Chr(34) & Me![SearchText].Text & "%"")"

Then I create a SQL with this Filter and use it for the
Me![lstName].RowSource

After I set the new RowSource for the listbox I call the ZoekAdresID sub.
Then the error shows up.

Thanks Tony for looking into this
Hope you find out what goes wrong here ;P

Rob.

can you post the code for the after_update event you are trying to re-use
in
the on_change event.

btw are you using the me.textbox value in the on_change event or the
me.textbox.text value?

TonyT