Hi,

I 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.

Sub,

I 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, (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

good luck, (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Aqua

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.

Just a thought, but can you just use an append query to attach the records you find in the search?

Granny

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, (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Aqua

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!

No Attachment!

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

Try changing this line: DoCmd.RunCommand acCmdSave


To This:DoCmd.Save

Okay, that allows the filter to function! Perfect.

Now 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.

Subsound90,

I read your original post are you just needing a search form?

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.

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.

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!

Your Welcome, Good Luck With Your Project!

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......

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.



The Red Colored text is the Primary Key Identifier for the first and last name selected

Okay, I have a sample DB I have been working with to improve the concepts before going into a live DB.

I 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).

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.

Done and done.

Thank you so much!