Problem on requery a form

[piet]

I have created a continues form, based on a query. In the header, there are
some comboboxes that gives the user the possibility to make certain
selections. Everytime a selection is made on a combobox, there is a VBA
action: ME.REQUERY. That works very fine: the user sees always the result of
his selection(s). There is also a textbox in the header that shows the
number of records in the form (Therefore I use DCOUNT).
Now I've got two problems, when the query returns no records (because the
user choose a selection of items that aren't in the database):

1. The textbox is not updated (should show 0 records).

2. The combobox where the last selection has been made, becomes empty????

I thought I could interfere with some code after the requery, but that don't
works. Is there any idea how to check if the underlying query returns no
records and do some action before updating the form? I can by example show a
message to the user: "there are no records that match your criteria".

Txs
Piet

[Br]

piet <pi******@pando ra.be> wrote:

I have created a continues form, based on a query. In the header,
there are some comboboxes that gives the user the possibility to make
certain selections. Everytime a selection is made on a combobox,
there is a VBA action: ME.REQUERY.
There's no reason to requery... just change the forms .recordsource.
That works very fine: the user
sees always the result of his selection(s). There is also a textbox
in the header that shows the number of records in the form (Therefore
I use DCOUNT). Now I've got two problems, when the query returns no
records (because
the user choose a selection of items that aren't in the database):

1. The textbox is not updated (should show 0 records).

2. The combobox where the last selection has been made, becomes
empty????
Is the combobox bound?
I thought I could interfere with some code after the requery, but
that don't works. Is there any idea how to check if the underlying
query returns no records and do some action before updating the form?
I can by example show a message to the user: "there are no records
that match your criteria".
Txs
Piet

I use something like this which is called from each control's
AfterUpdate event.......

Private Sub ApplyFilter()
On Error GoTo ApplyFilter_err
Dim SQLWhere As String, SqlOrderBy As String, MySQL As String
Select Case Me![FilterList]
Case 2:
SQLWhere = " WHERE [EndDate] > #" & Format(DateAdd( "d", -1, Date),
"mm/d/yyyy") & "#"
Case 1:
SQLWhere = " WHERE [EndDate] <= #" & Format(DateAdd( "d", -1,
Date), "mm/d/yyyy") & "# AND [WrittenToHistor y]"
Case 3:
SQLWhere = ""
Case 4:
SQLWhere = " WHERE [EndDate] >= #" & Format(DateAdd( "d", -1,
Date), "mm/d/yyyy") & "# OR NOT [WrittenToHistor y]"
End Select
Select Case Me![SortOrder]
Case "Date": SqlOrderBy = " ORDER BY [StartDate], [Module
Code], [Class]"
Case "Module Code": SqlOrderBy = " ORDER BY [Module Code],
[StartDate], [Class]"
Case "Module Name": SqlOrderBy = " ORDER BY [Module Name],
[StartDate], [Class]"
End Select
MySQL = "SELECT * FROM qryScheduleScan " & SQLWhere & SqlOrderBy & ";"
Me.RecordSource = MySQL
ApplyFilter_exi t:
Exit Sub
ApplyFilter_err :
MsgBox Err.Description , 48, "Error in ApplyFilter()"
Resume ApplyFilter_exi t
End Sub
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response