J G
asked on
Access Split Form Filter
I have a split form bound to a table with a control button that will filter the table.
below is the macro builder code attached to the click event of the button.
If DCount("PLU","[tbl_Item]", [PLU] Like "*" & [Forms]![Item_Master]![Sea rchField] & "*")=0
Message: Item does not exist
Else
Apply Filter
Where [PLU] Like "*" & [Forms]![Item_Master]![Sea rchField] & "*"
The issue is:
the form defaults to the first record of the table and then the filter will just looks at the first record and not the entire table. If the "*new record" row on the bottom of the split form is selected every time the filter is run (manual mouse click) then the filter will work.
I tried to add the code: GoToRecord new at the top of the macro. After I did this, the filter works if I enter a value in the search box that exists in the table. However, if a unmatched value is entered in the search box the message box function does not work: the filter will select the "*new record" bottom row, and display 1 of 1 records filtered.
below is the macro builder code attached to the click event of the button.
If DCount("PLU","[tbl_Item]",
Message: Item does not exist
Else
Apply Filter
Where [PLU] Like "*" & [Forms]![Item_Master]![Sea
The issue is:
the form defaults to the first record of the table and then the filter will just looks at the first record and not the entire table. If the "*new record" row on the bottom of the split form is selected every time the filter is run (manual mouse click) then the filter will work.
I tried to add the code: GoToRecord new at the top of the macro. After I did this, the filter works if I enter a value in the search box that exists in the table. However, if a unmatched value is entered in the search box the message box function does not work: the filter will select the "*new record" bottom row, and display 1 of 1 records filtered.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My filter works fine by itself, when i add the code for the message box it breaks(as stated in my original question)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the marcro condition works.
However, I couldn't get the VBA to work (preferred) when I enter in values into the PLU field, the all come up as "Item does not exist"
However, I couldn't get the VBA to work (preferred) when I enter in values into the PLU field, the all come up as "Item does not exist"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
before I post...
is it possible to create a message box using the macro builder that will have a yes/no option?
is it possible to create a message box using the macro builder that will have a yes/no option?
ASKER
can you send me an email? I don't want my database to be viewable
ASKER
attached
test.accdb
test.accdb
ASKER
When you type in 3013 or Link it shows up as 'item does not exist' when It clearly does in the table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the filter doesn't work accurately with the code provided. In the sample dbase, search for 30. then clear the filter and search for another value, the results will be incorrect.
The filter works the first time you run it, but all attempts after have inaccurate results.
The filter works the first time you run it, but all attempts after have inaccurate results.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER