Advanced Search / Query Method

[rabryant1]

Alright guys,

I've built a pretty simple Access database that my office uses to keep track of returned checks. My users are computer novices, so I try to follow KISS as much as possible when building the interface.

I have a form: frm_UpdateNFS that pulls in records via query: qsel_UpdateNSF. The query prompts users via pop ups from the criteria of Last Name, Check Number, and Barcode, each with its own parameter box, which causes some of my power users to misstep when trying to lookup the record when someone is on the phone.



What I am looking for is the ability to create something like an advanced query that pulls from a form or something????!?!?! I'm attaching a screenshot of an interface we uses for our student information system, that I would like to be able to base my advanced search on.


I have no idea how to go about doing this, as I am rather a access novice myself. So I may need some in depth steps on some of this.

[alansidman]

Does this help you get started?

http://www.datapigtechnologies.com/f...earchform.html

[rabryant1]

Does this help you get started?

http://www.datapigtechnologies.com/f...earchform.html

YES! Thank you! That got me the general idea working.


Next question, I have it now where the Frm_Search passes the information to the query, and the query displays its results, how can I get this passed on so its into my frm_UpdateNFS form? I used to be just able to select the data source as the query and it would pull the data in, now it just prompts for the input. Its about 30 fields, so having the data populated in form layout is very helpful for my users.

Thanks guys!

[alansidman]

Why not put the search criteria currently in the Frm_Search as a part of the frm_updateNFS form and have that form use the query as its record source. Without seeing your db, I cannot offer a better solution. If you want to upload your db for further analysis, then click on the advanced button and follow the wizard. Be sure to sanitize your db for confidential info.

[rabryant1]

Why not put the search criteria currently in the Frm_Search as a part of the frm_updateNFS form and have that form use the query as its record source. Without seeing your db, I cannot offer a better solution. If you want to upload your db for further analysis, then click on the advanced button and follow the wizard. Be sure to sanitize your db for confidential info.

alansidman,

Cant seem to get it to allow me to upload the database using attachments (I clicked go advanced), and I don't see anywhere else that would launch a wizard....

I've uploaded a copy of my db to dropbox, (backend and front) and am linking it here. I have Removed and changed alot of the tbl_nfsmaster contents, but everything should work fine. You wont be able to get the subforms to load, as they tie to our dbo_customers table in sql. I know the database is really really sloppy right now, but I'm trying to get core functionality, then I will go back and play house cleaning.....

What I am looking to do is be able to get do advanced searches on the database and have them populate to frm_updateNFS and Frm_ViewNFS to viewed or modified.


https://www.dropbox.com/sh/we5mrsmooquhzi1/ALIiyvOXNE


Update: Changed one of the files, I had messed up the search form and was unaware of it. File reuploaded.

[rabryant1]

Any help guys? Alansidman, I dont quite get what your saying to do in your previous post, so mabe you can help me out a little? Sorry, I'm just stuck on this and have no idea what to do.

[alansidman]

Opened the file and the record source for the form frm_UpdateNFS appears to be qsel_UpdateNSF. Is this the query that is to populate the form? Additionally, I downloaded the fe and be and cannot seem to link them together and cannot do a full analysis as it errors out. Suggest you put them together and try to load to this site. I really don't like downloading from unrestricted sites.

[rabryant1]

Opened the file and the record source for the form frm_UpdateNFS appears to be qsel_UpdateNSF. Is this the query that is to populate the form? Additionally, I downloaded the fe and be and cannot seem to link them together and cannot do a full analysis as it errors out. Suggest you put them together and try to load to this site. I really don't like downloading from unrestricted sites.

alansidman,

Thanks for taking the time to look over everything. When I tried to upload it here, I got the exceeds max file limit message. Thats why I hosted is on my personal Drop Box account. I've actually made some changes, and will have the uploaded file. But yes,

The way it works now is:

frm_UpdateNFS is sourced from qsel_UpdateNSF, and that currently populates via criteria pop up boxes.

[rabryant1]

Its still maxing out it says 500KB is the max and I'm at 1mb.

So the file is updated in the location where you downloaded it previously. I promise you its safe, its a private link, tied directly to my computer.


None of the subforms will work, because they're tied to the sql server at work. I have removed the dbo entries in tables.

frm_Search is the advanced find / search.
frm_UpdateNFS is what we use currently to look up records, the record source is based on user input from criteria prompts from qsel_UpdateNFS.

I would like to be able to pass the results from someone using frm_search (and qsel_Advanced_updateNFS), to a form like what happens with the frm_UpdateNFS.

[alansidman]

I added a form based upon your query to show the results. It opens when you run the query from the search form. A simple Docmd.Openform "YourFormName" inserted after the DoCmd.OpenQuery line.

I hope this is what you were looking for. If not, then I don't understand your issue.

[rabryant1]

Alansidman,

THANK YOU. Thank you a million times over - I would have never figured that out, I'm still learning some of the vba coding, so I would have never thought of that.

thanks again!