Full Version: Easy Question on Forms and Filters, but Driving me Nuts
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
subsound90
Hi,
bet this is the easy question in the world but it's driving me nuts. I'm very new to Access, and every time I look up the answer it's like "Oh, just do this", when I have no idea what they are refrencing!
What I am trying to do is build a form that comes up blank, and you can enter data from it into 1 table. Then you can search off any of the fields, with no errors coming from blank fields, and it comes up with all the records matching that filter/search without entering the data into the table you are conducting the search on. You can append then those records that come up, or hit new record and a blank sheet comes up.
If some one would upload, or point me to one, a quick database so I can see how this is done I would be so happy! This is driving me bananas and I bet it is so easy if I just saw it then I would smack myself in the head and feel dumb.
Aquadevel
Sub,
would suggest that you take a look at the sample database that comes with Access, NorthWind.
You can load it, look at 'how' the forms are <attached> to the tables and querys.
take it apart & even break it, sad.gif
good luck, sad.gif
Aqua
subsound90
I really appreciate the help, but it doesn't really do what I need to do.
It provides a good example of displaying forms, but you can't filter or search by what is put into the fields (save the drop down box), the forms all come up fully bound which means any data put into the fields is automatically entered into the table. I think the biggest thing I need is the filter by the fields in the form, then displays them into the fields on the form. I can do searches but it only displays the table form for the search criteria and then only from 1 field and not dynamically from any field.
granny
Just a thought, but can you just use an append query to attach the records you find in the search?
ranny
Aquadevel
SubSound,
After re-reading your original post. I think you want to check out 'ListBoxes', although it sounds like you
want to be able to search from ~various~ controls on the form and not just 1 or 2. :(
If you can post your database with no sensitive data in it, it might help.
good luck, sad.gif
Aqua
subsound90
Here is a copy of the databases without anything in it. I tried posting last week, but for some reason I was getting an error. I have a form in it with a search function (Though the Cancel doesn't work, I need to fix that as well). Any sort of filter to allow for an append would be wonderful, or a filter to see if there is some one fitting that name.
Thank you so much again!
jmcwk
No Attachment!
subsound90
Dang the thing won't upload! However, I found a good way to filter by messing around with some stuff online. It doesn't filter via the form, but from a prompt to the user. They like it so I might run with it (have to keep them happy).
However I have some code I found online that I need to fix up to run in something other then the before update event (plus the Cancel button didn't function so I took it out). It runs on the beforeupdate event, so it runs before the filter and saves the filter criteria in to the feilds or closes the form. It's just so I can prompt the user to save what they have or discard it, or cancel when the form closes. I tried putting it in the Dirty and unload and that hasn't worked, so....I'm stumped.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strResponse As String

If Me.Dirty Then
Select Case MsgBox( _
Prompt:="This record has been changed" & _
vbCrLf & "Do you want to save?", _
Buttons:=vbExclamation Or vbYesNo)
Case vbYes
'Save changes to current record before advancing
'to next record.
DoCmd.RunCommand acCmdSave
Case vbNo
'Discard changes to current record before advancing
'to next record.
DoCmd.RunCommand acCmdUndo
End Select
End If
End Sub
jmcwk
Try changing this line: DoCmd.RunCommand acCmdSave
To This:DoCmd.Save
subsound90
Okay, that allows the filter to function! Perfect.
How the cancel button, I can make the button but it still just discards the data. I need to it go back to the form unsaved but the data the user entered still there. I've tried looking it up, and so far nothing has really done the trick.
jmcwk
Subsound90,
read your original post are you just needing a search form?
subsound90
Well, both the search form and the Cancel function seem to be the issue. I have a filter in place, but it only does one filter at a time. This is it.
Private Sub ApplyFilter2_Click()
On Error GoTo Err_ApplyFilter2_Click
DoCmd.ApplyFilter , "Filter = LastName"
Exit_ApplyFilter2_Click:
Exit Sub
Err_ApplyFilter2_Click:
'MsgBox Err.Description
Resume Exit_ApplyFilter2_Click

End Sub
I would like it to prompt the user for multiple fields instead of 1 field, and not puke if the user doesn't enter anything, but it was working well enough for this database at the moment. If anyone knows how to make it do that without puking I would love the help, as I bet this database will get more fields when more people are added to the project.
The second thing was just to clean up some code where I was prompting the user to save, discard or cancel when the form was closed. The save and discard work perfectly, and even better with your help. I had something in there I was playing around with but it would give me a run time error when I hit the cancel button, so I took it out.
jmcwk
Well I can help you wit the search form as far as the code for the cancel etc. I am not sure at all. Anyway see if the attached helps in regard to the search needed.
subsound90
Cool, this will be a great help! I posted another topic on the code for the new questions so they will be at the top.
Thank you so much for your help, I really appriceate it!
jmcwk
Your Welcome, Good Luck With Your Project!
subsound90
Hummm, I've tried pulling out the query for a search from your search documents and I'm not quite getting the same thing as your database. I just have first name and last name, and I have a form that has these 2 fields and just runs these 2 at the hit of a button that runs the query.
Like "*" & [Forms]![SearchMult]![txtFirstName] & "*"
Like "*" & [Forms]![SearchMult]![txtLastName] & "*"
And the fields are right, but everytime I run it I get it just searched for the first name and only if I put in the last name. If I don't put in the last name, both, or just the last name it pulls everything in the query. The fields corrispond, first name is on the criteria and the last name is on the or line. I'm don't know why I'm not getting it......
jmcwk
Can you attach a stripped Version of your Db no larger than 500000kb and no sensitive data? Am guessing that you need a Primary Key reference to the First and Last name that you are selecting. Your SQL for your listbox in the search form should look something like this.
CODE
SELECT qrySearchExample. [color="red"] pkSearchExampleID[/color] , qrySearchExample.LastName, qrySearchExample.FirstName
FROM qrySearchExample
WHERE (((qrySearchExample.LastName) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*")) OR (((qrySearchExample.FirstName) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*"))
Ordertach a stripped Version of your Db no larger than 500000kb and no sensitive data? Am guessing that you need a Primary Key reference to the First and Last name that you are selecting. Your SQL for your listbox in the search form should look something like this.
CODE
SELECT qrySearchExample. [color="red"] pkSearchExampleID[/color] , qrySearchExample.LastName, qrySearchExample.FirstName
FROM qrySearchExample
WHERE (((qrySearchExample.LastName) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*")) OR (((qrySearchExample.FirstName) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*"))
ORDER BY qrySearchExample.LastName, qrySearchExample.FirstName;

The Red Colored text is the Primary Key Identifier for the first and last name selected
subsound90
Okay, I have a sample DB I have been working with to improve the concepts before going into a live DB.
didn't have a primary key, which could definatly be the problem, but I'm not sure where that code would go to do the searches. I put a one in real quick.
Unfortunetally I don't seem to be allowed to upload it, so I can't really show what's going on. I'll try from a different computer later today (the system is telling me I can only upload .jpg, .gif, and .png type files).
jmcwk
If it is a work system it more than likely will not make any difference. Can you zip it and e-Mail to my E-Mail Address? It is in my Profile Page. Just go to Whos On line and click jmcwk and you will find my E-mail address.
subsound90
Done and done.
Thank you so much!
jmcwk
Just letting you know I have not received it yet and am on my way to lunch. I will let you know one way or the other when I get back if I received it.
jmcwk
Michael,
See attached and open the frmCustomerCards and click the Search Button. was not to sure what you were doing in regards to your Filters form. If you can elaborate OR if the frmCustomerCards does not work to your liking let me know.
subsound90
I think I was just having it do a search and bringing up a table view because I thought it would be easier.
think this is perfect for what I need, but I'm going to be sitting here playing with it so I can understand it better. I've done allot of data mining, but being a finance guy I have not done very much form building and VB scripting.
Thank you so much for your help, I know it's frustrating with some one like me with such little experience.
jmcwk
Michael,

sad.gif sad.gif

Your Welcome! Not frustrating to me at all I have been there done that as we all have glad I could help out. Need more help just post up and I will do what I can.

Good Luck With Your Project sad.gif

BTW: Delete the qrySearchExample that was in the Demo NOT USED OR NEEDED
Edited by: jmcwk on Wed Aug 2 11:13:55 EDT 2006.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.