Real Real Problem

[zym1ne]

Going crazy here and desperate for some help if possible...

Have to say these forums are top rate and have really helped me get to grips with designing a DB from scratch...which is pretty good going as a total VB and Access newbie

Got most of my probs ironed out but still having a major headache trying to figure out how to create a form that allows me to search multi criteria

My DB consists of 5 tbls

1.Dealers
2.tblWorkRequests
3.tblAdminDetails
4.tblCompSpecs
5.tblSales

a common key for all is the DID# (unique PK for Dealers) for every dealer there can be many Work Requests or Admin Details.

Admin can consist of Install, Hardware, Software, Network and Admin status can be Open, Closed, Escalated finally each Admin detail will consist of the Date the Admin was carried out...each Admin Detail has unique Job# (autonumber-PK).

Work requests consists of SalesRep requesting, DID#, Dealer and Date of Request...each request has unique ID# (autonumber-PK).

Searched the forums and found some info but cant put into practice how I might create a form that would allow me to search for the above details?

i.e: A form that would allow me to search for all Admin carried out between dates A and B that has a staus of 'closed' or 'open' or Admin for a certain Dealer by DID# and so on...
Alternatively a search for all requests by a certain Sales Rep between dates or by Dealer ID (DID#)

And of course the real big question is how to combine the two...how to retrieve all Admin and Work Requests between dates for a Dealer by DID#

Cant find anything that exactly fits the bill any pointers to where I might look or any examples to follow would be a god send at this point in time, my coding aint exactly up to much at the moment but willing to give it my best

If the above was a little confusing have included a cut-down version of my DB to have a look at.

Any help really appreciated folks

zym

 

[WayneRyan]

zym,

You can search on this forum for "search forms".

What has worked for me in the past is the following:

Base your form on a query which includes all of the fields
in your table. The query has no criteria and will return
all records in your table.

Your form will use "bound" fields, that is they relate directly
to the fields in your table.

For this example let's add two new "unbound" fields to your
form. cboSalesRep is a combo with a rowsource to select all
Sales reps from your table, and txtDealer which is a textbox
where one can type in a fragment of a Dealer's name (just an
example).

Both of these two new controls have a default value of "*".

In each of these two new controls use the AfterUpdate event
to:

Me.NewControl = Nz(Me.NewControl, "*")
Me.Requery

Then change your query add criteria for the applicable
"bound" field:

Like "*" & Forms![YourForm]![NewControl] & "*"

This will support the fragment in the textbox or the entire
combo.

This method makes it easy to add search fields, should you
need to expand from 2 fields to 5 or so. It is worth a try.

Also, since you can specify Multi-criteria (with an "AND")
you need record navigation buttons to step through them.

Wayne

 

[zym1ne]

THx for the help...

Bright new day and hopefully can take in what u have suggested Wayne.

Will give it a go with your suggestions and let you know how it works out, thanks for the reply.

zym

 

[zym1ne]

Wayne your a pal

Wayne went with your suggestions and lo and behold I actually managed to get something right

Thanks for the tips and letting me pick your brains, if it was'nt for you guys and gals out there who have the time to help us newbies out don't, me for one would be up a river without a paddle...

thanks again

zym