etech0
asked on
access 2003 filter no results - "Enter parameter value"
Hi!
I have some VBA code that filters a form dynamically, based on the values of combo boxes.
When the user chooses a value in a combo box that has no results on the form, instead of just getting no records, access throws an "Enter Parameter Value" error, asking for ID, which is the first field on the form.
I tried setting up an OnError, but it seems that OnError does not pick up "Enter Parameter Value".
Is there a way to set this up so that if there are no results, either no results are displayed, or else the user gets a message box and then sees the results from the previous filter?
Thanks!
I have some VBA code that filters a form dynamically, based on the values of combo boxes.
When the user chooses a value in a combo box that has no results on the form, instead of just getting no records, access throws an "Enter Parameter Value" error, asking for ID, which is the first field on the form.
I tried setting up an OnError, but it seems that OnError does not pick up "Enter Parameter Value".
Is there a way to set this up so that if there are no results, either no results are displayed, or else the user gets a message box and then sees the results from the previous filter?
Thanks!
Have you tried setting a default value of " " for the properties of the field?
can you post the code you are using to define your filter? Without that we are just guessing.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is that a double space? Where should I make it a default value? The combobox?
Here is the code. It's two subroutines that are called when either of the comboboxes is changed. There will eventually be a third one, when I get the first two straightened out.
Here is the code. It's two subroutines that are called when either of the comboboxes is changed. There will eventually be a third one, when I get the first two straightened out.
Private Sub BuildSQLstatement()
Dim MySQL As String
'set base sql
MySQL = "SELECT [CatWeb Work].ID, [CatWeb Work].ACVENDT, [CatWeb Work].DescripChanges, [CatWeb Work].Location FROM [CatWeb Work] "
Select Case FilterCombo 'edits the sql accd to filter chosen
Case 1:
MySQL = MySQL & "WHERE status = 'In Production' AND location = " & GetUser()
Case 2:
MySQL = MySQL & "WHERE status = 'In Production'"
Case 3:
MySQL = MySQL & "WHERE status = 'Under Consideration'"
Case 4:
MySQL = MySQL & "WHERE status = 'Complete' AND location = " & GetUser()
Case 5:
MySQL = MySQL & "WHERE status = 'Complete'"
End Select
Me.CatWebWork2SummaryF.Form.RecordSource = MySQL
UpdateFilters
End Sub
Private Sub UpdateFilters()
Me.CatWebWork2SummaryF.Form.FilterOn = False
If Not IsNull(VendorFilterCombo) And VendorFilterCombo <> "" And VendorFilterCombo <> 99999 Then Me.CatWebWork2SummaryF.Form.Filter = (("Lookup_ACVENDT.VenName='" & VendorFilterCombo.Column(1) & "'"))
On Error GoTo error
Me.CatWebWork2SummaryF.Form.FilterOn = True
Exit Sub
error:
MsgBox ("No results.")
VendorFilterCombo = Null
Me.CatWebWork2SummaryF.Form.FilterOn = False
End Sub
ASKER
When it does not return results, it does so appropriately. I can check in the table, and there are no results.
I'm just trying to get rid of the Enter Parameter Value error, and set up something a little more user friendly.
I'm just trying to get rid of the Enter Parameter Value error, and set up something a little more user friendly.
which line is raising the error ?
comment this line
'On Error GoTo error
then run your code, when you get the error clikc debug
comment this line
'On Error GoTo error
then run your code, when you get the error clikc debug
ASKER
No line is raising the error. I put that in, hoping it would pick up the "Enter Parameter Value", but it didn't. Maybe because sometimes people put it in on purpose.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For example, instead of letting users enter any date, give them a combo box filled with actual date values from a table or query.
ASKER
I would do that, but sometimes they need to select a vendor (in the combo box) who has no records in this table, in order to add one. Ideally, they would be able to select the vendor, see no records, and add one.
<Sometimes they need to select a vendor (in the combo box) who has no records in this table,>
Then perhaps this would all be clearer if you first took a step back and explained what this "System" is ultimately doing
(Select a vendor to enter Orders...?)
In a true database system you would first add a Vendor (to the vendors table)
Then you would use a main/subform to select them, and display their "Orders" (even if they had none...)
Sample attached
Database80.mdb
Then perhaps this would all be clearer if you first took a step back and explained what this "System" is ultimately doing
(Select a vendor to enter Orders...?)
In a true database system you would first add a Vendor (to the vendors table)
Then you would use a main/subform to select them, and display their "Orders" (even if they had none...)
Sample attached
Database80.mdb
ASKER
What this system is doing is as follows:
I have a subform that shows all open catalog work tasks.
I have a combobox that changes the sql dynamically to show all open tasks, all my open tasks, all completed tasks, all my completed tasks, or all tasks. (With 'my' based on the task owner, compared to the username of the computer in use.)
I have another combobox with a list of all our vendors. This combobox edits the 'filter' property of the table, to show all tasks from that vendor that fit the criteria in the first combobox.
Sometimes, there are no tasks that fit both criteria. If that is the case, the user should see no results, and have the option to create a task if they want.
How can I accomplish this?
Thanks!
I have a subform that shows all open catalog work tasks.
I have a combobox that changes the sql dynamically to show all open tasks, all my open tasks, all completed tasks, all my completed tasks, or all tasks. (With 'my' based on the task owner, compared to the username of the computer in use.)
I have another combobox with a list of all our vendors. This combobox edits the 'filter' property of the table, to show all tasks from that vendor that fit the criteria in the first combobox.
Sometimes, there are no tasks that fit both criteria. If that is the case, the user should see no results, and have the option to create a task if they want.
How can I accomplish this?
Thanks!
<I have a subform that shows all open catalog work tasks.>
And what is the main form? (Purpose?, Recordsource?)
<I have a combobox that changes the sql>
On the main form or the subform?
<I have another combobox with a list of all our vendors.>
On the main form or the subform?
Why are there two filtering systems...?
One to change the SQL and one to change the filter.
Why not make this simple and have one "Filtering" system?
This being said, the main issue seems to be that if there are no records after you filter, you cannot add records.
This is the issue with trying to create a system that filters *and* adds records...
You can do it, ...but then a *Lot* of other issue have to be considered
...You have to put the form into "Data Entry mode. This *IS NOT* filtered*, but it is the only way to create new record in a filtered form.
...what if they enter a different Vendor or "designation" that is not what you filtered for?
...you then have to consider if you will reapply the filter of leave it as is...
...etc
(These are questions (and all the other considerations) that are better dealt with in a separate thread...)
Again, these are the issues associated with trying to create a system that filters *and* adds records...
(This is not commonly done,...)
Here is a sample,
...Cust 53, for Designation 4 has no records.
You will have to study it and adapt it to work in your database...
Jeffcoachman
Database83.mdb
And what is the main form? (Purpose?, Recordsource?)
<I have a combobox that changes the sql>
On the main form or the subform?
<I have another combobox with a list of all our vendors.>
On the main form or the subform?
Why are there two filtering systems...?
One to change the SQL and one to change the filter.
Why not make this simple and have one "Filtering" system?
This being said, the main issue seems to be that if there are no records after you filter, you cannot add records.
This is the issue with trying to create a system that filters *and* adds records...
You can do it, ...but then a *Lot* of other issue have to be considered
...You have to put the form into "Data Entry mode. This *IS NOT* filtered*, but it is the only way to create new record in a filtered form.
...what if they enter a different Vendor or "designation" that is not what you filtered for?
...you then have to consider if you will reapply the filter of leave it as is...
...etc
(These are questions (and all the other considerations) that are better dealt with in a separate thread...)
Again, these are the issues associated with trying to create a system that filters *and* adds records...
(This is not commonly done,...)
Here is a sample,
...Cust 53, for Designation 4 has no records.
You will have to study it and adapt it to work in your database...
Jeffcoachman
Database83.mdb
ASKER
1) Main form is a shell, there is a subform to select a task, and then 4 other subforms with data (three of which are in a tab control).
2) On the main form.
3) Main.
4) Thought it would be simpler to have the second level of filtering in a different property. Would this whole thing work better if I had all filters edit the sql directly? If I do that, what would happen if there were no results? Or am I better off having it all done with filtering?
2) On the main form.
3) Main.
4) Thought it would be simpler to have the second level of filtering in a different property. Would this whole thing work better if I had all filters edit the sql directly? If I do that, what would happen if there were no results? Or am I better off having it all done with filtering?
I don't see a need to swap the recordsource SQL if all you are really doing is "Filtering"
Six of one, half dozen of another...
Let's just stick with the issue at hand...
Does my sample do what you originally requested?
Six of one, half dozen of another...
Let's just stick with the issue at hand...
Does my sample do what you originally requested?
ASKER
With adaptions it might. What does RecordsetClone mean?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all of you for your input!
I think I'm going to edit the rowsource of the combo box to match the record source of the form. It will be complicated, but I think I can do it.
If I have any more questions, you'll be hearing from me on EE!
I think I'm going to edit the rowsource of the combo box to match the record source of the form. It will be complicated, but I think I can do it.
If I have any more questions, you'll be hearing from me on EE!