User Defined Report

[Ozmosis]

Ok, I'd like to start this thread with a plea for sanity. Have a little patience and humanity with me.

Here's the situation: I recently began a project to build a database for my company. In this database is about 10000 entries, each entry having 5 columns.

My question: Is there a way (or a module) to have the user enter data, and have the module do a comparison to the apropriate field, and present a list of possible matches to the users?

I'll be checking on this post, so please, -help me-!

 

[MakeItSo]

Hi Ozmosis!
What exactly do you intend?
a) Perform a search, i.e. have the user enter some search criteria and then present a results form with all entries containing the criteria? or
b) Let the user not type in manually each entry but present him with a list of entries for that field (e.g. a combobox for "Status", allowing to choose from "open, resolved, closed&quot?

To a): See thread702-700957 . that might help you
To b): Use query builder in the row source of the combo box to select the entries from the desired field, WHERE one field matches your criterion. Put a your combobox.Requery in the Sub AfterUpdate() of your criteria field on the form.

Does that help?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Ozmosis]

Hello MakeItSo,

The first item you mentioned is the primary goal. I have built the 2 forms, and query you mentioned, but when running the search from the from, and hitting the 'Go' button, I get this message:

Microsoft Access can't find the macro 'DoCmd'.

I'm new to all this, but I catch on quick, so please advise.

 

[Ozmosis]

Ok, bypassed that macro error (programming error on my behalf, of course) and now have the blank form as the other fellow did. I'm going to follow the course of actions he took, so I'll be reading that thread.

If I really get stuck, I'll be right back here again.

 

[Ozmosis]

Would you do me a favor and step by step me through the portion about 'setting all standard controls to "*"'.

That's one portion that has me slightly confused.

Thanks !

 

[Ozmosis]

Ok, more changes.

It presents a list now, which is awesome. Now I need to do clean up. How do I get the input to recognize a wildcard character, for instance:
user inputs '00-004' as the entry, and I want it to pull up all containing that.. like '00-004b' and such. Is there a way I can get the program to look for the user input data, without it having to match perfectly?

I want the list to contain all entries with that particular data ANYWHERE in it.

Thanks in advance,

Ozmosis

 

[MakeItSo]

Hi Ozmosis!

You have got far on your won, that's good!

1) The properties of a text/combo box always has a "default value" setting. Just type "*" there.

2) Click on "Code" for the event AfterUpdate for each text/combo box you want to surround with wildcards, insert this:

Me![Your control name]="*" & Me![Your control name] & "*"

Let me know how you succeed.
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Ozmosis]

Alright,

The changes I made have added an asterik to the beginning and end of the user input. Only problem is, the program isn't treatin' it like a wildcard. Now it's actually looking for the asterik as well. Nothing wants to come up in the form now, because it's actually looking for, in example, '*00-004*', versus '00-004'.


We're getting closer, I can feel it.

 

[MakeItSo]

Hmm. Check your references.
You might have ADO reference instead of DAO.
This means, that not the asterisk is the SQL wildcard character, but the percent sign.
Will %00-004% give you the correct results?

 

[Ozmosis]

Tried the '%' sign just to be safe, but no beans. I'm pretty sure the asterik is the wildcard. For some reason, between the entry and the results, the wild card character isn't being used. Could it be something with the query?

%00-004% brought a blank results page, as did *00-004*.

I'll be fiddling with it..

 

[Ozmosis]

I figured it our! Woohoo!

Solution: In the Query, I had to add a 'Like' just before the [Forms] and all that. It's a forced wildcard, but it works for me.

I'm sure I'll have more questions later, so keep an eye out for.

PS: Thanks so much for your help, MakeItSo. =)

 

[Ozmosis]

Wow.
Did you see the typos up there? ^^^^

Almost hurts to read it. I'm going back to bed.