Need Help with Lookup (or Alternative)

P

prettiwitty

I have a database (in Access 2007) in which we are recording names, dates,
and other info. I have only made the table and form for entry. No queries,
relationships, etc.

I need to make a lookup (or alternative) so that I can type in a name to
search for that name in the database. Then, once a name is found, to create
a report to print the found name(s) in the database.

Anyone help? The table name is "purchases info" and the form is called
"purchases info". I would think the lookup would be from the table? Can
this be done from a switchboard?

I'm a pretty novice user, so explanations in laymen's terms would be nice.
:)

Thanks.
 
T

Tom Wickerath

Hi Prettiwitty,

Yes, there is a way to create a form to assist with this need. This is
commonly referred to as QBF, which stands for Query By Form.

Here are some examples that you can download:

QBF Examples
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

http://www.accessmvp.com/TWickerath/downloads/elements.zip

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008


The first example, "customdialogbox", is the easiest example to implement.
It has minimal VBA code. For the present time, you might wish to just stick
with this method.

The second example, "elements", shows how one can use the .ItemsSelected
property of a list box with MultiSelect set to either simple or extended.
Note that this property does not exist for list boxes that are set for
MultiSelect = None. This is a bare-bones sample that is only intended to show
how to use the multiselect list box.

The third example, "Chap08QBF", takes things a step further. This is a
revised version of the QBF sample found in Chapter 8 of Access 2000 Power
Programming, written by F. Scott Barker. The revisions I added include the
ability to double-click a record in the subform to open just that one record
for editing. I also implemented Access MVP Allen Browne's calendar form.

The last example includes a Word document along with a sample database that
is a stripped down version of the venerable Northwind database (2003
version). The Word document makes an attempt to explain how this works. The
last three samples have in common an unbound QBF form (ie. a form with no
recordsource) which include a bound subform. The SELECT and ORDER BY portions
of a SQL (Structured Query Language) statement (ie. a query) are hard-coded
in the class module associated with the QBF form, although there's no reason
that the ORDER BY statement need be hard-coded. The WHERE clause is built
dynamically, as the user makes selections. These three clauses are then
joined together, and used to set the recordsource of the subform. That's how
it is suppost to work.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
P

prettiwitty

Thank you. I will give this a try to see if I can make it work.

prettiwitty
 
P

prettiwitty

Sooo, you're saying I need to make these tables, but instead of tblCustomer,
it would be tblLastName, and the fdlgAskForDatesAndCity would be
fdlgAskForDatesAndLastName?

Hmmmm....
 
C

Clif McIrvin

I'm not familiar with Tom's examples (I flagged his message for future
followup <grin> ) but I'm pretty certain that his intent is merely to
present an example.

You take the idea and the structure of his example, and use your own
tables and names.

Clear as mud?

Post back if you need more guidance.
 
T

Tom Wickerath

Hi Prettiwitty,
Sooo, you're saying I need to make these tables, but instead of tblCustomer,
it would be tblLastName, ...
Click to expand...

No. You don't need (or want) at table of just last names. Good database
design includes storing similar data in one place. So, for example, if you
had last names stored in a new tblLastName table, and also in your purchases
info table, then you would be duplicating data.

May I recommend that you help jumpstart your Access-related knowledge by
downloading a copy of a Word document that I have available in zipped form? I
call it "Access Links". The first four pages include important information
that anyone working with Access should be aware of. This includes reserved
words and special characters, naming conventions, database design, etc. My
advice is to avoid using any reserved words (Name, Date & Description are
three prime examples) or special characters (#, $, spaces, etc.) in anything
that you assign a name to within Access.
...and the fdlgAskForDatesAndCity would be fdlgAskForDatesAndLastName?
Click to expand...

You can give your QBF form a more generic name. How about something like
"frmQBFSearchForm" (without the quotes, of course)?

You'll want to settle on a database design before you spend too much time
creating any forms or reports, otherwise you might find yourself having to do
re-work. As for the names of people, my recommendation is to store the first
and last names in separate fields. That way, you don't have to worry so much
about searching a full name in a particular order, such as "Wickerath, Tom"
versus "Tom Wickerath" as an example. If the number of names in the table is
not too many, then consider having a combo box or list box, where one can
select the name they wish to search on, instead of having to spell it
correctly. You can even use techniques like filtering a combo or list box
after typing in the first two letters of the last name, so that the list
includes only last names that start with the letters you typed into a related
text box.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
P

prettiwitty

Thank you. I'm getting closer.

Tom Wickerath said:
Hi Prettiwitty,


No. You don't need (or want) at table of just last names. Good database
design includes storing similar data in one place. So, for example, if you
had last names stored in a new tblLastName table, and also in your purchases
info table, then you would be duplicating data.

May I recommend that you help jumpstart your Access-related knowledge by
downloading a copy of a Word document that I have available in zipped form? I
call it "Access Links". The first four pages include important information
that anyone working with Access should be aware of. This includes reserved
words and special characters, naming conventions, database design, etc. My
advice is to avoid using any reserved words (Name, Date & Description are
three prime examples) or special characters (#, $, spaces, etc.) in anything
that you assign a name to within Access.


You can give your QBF form a more generic name. How about something like
"frmQBFSearchForm" (without the quotes, of course)?

You'll want to settle on a database design before you spend too much time
creating any forms or reports, otherwise you might find yourself having to do
re-work. As for the names of people, my recommendation is to store the first
and last names in separate fields. That way, you don't have to worry so much
about searching a full name in a particular order, such as "Wickerath, Tom"
versus "Tom Wickerath" as an example. If the number of names in the table is
not too many, then consider having a combo box or list box, where one can
select the name they wish to search on, instead of having to spell it
correctly. You can even use techniques like filtering a combo or list box
after typing in the first two letters of the last name, so that the list
includes only last names that start with the letters you typed into a related
text box.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
Click to expand...
 
P

prettiwitty

I read your article and other zips, and quite frankly, I think I am brain
dead. I read it, but it's going right through me.

I have this database, and want it to do what you zipped (the Date and City
Query sample you sent). But I want to be able to look up by last name, then
generate a report in preview (like as shown in the sample) of all names that
match the name query. I like the choices of run a query, preview a report,
or Export to Excel. It would be nice to be able to lookup by address as
well. The dates aren't as important, as if I get a match on the name, I want
it to preview all records that match the name and/or address entered.

I appreciate your help and input, but I think I am over thinking it or
something, because I am just not grasping what I need to do. I work for a
state agency and am trying to make this database work for our purposes,
because we don't have the money to purchase a program that probably already
does this stuff. :)

Any way I could send what I have to see if you could fix it? Of course we
don't have any money to pay for services either. If you can help, my
e-mail is (e-mail address removed)(donotspam).

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Limiting data pulled from another table into a lookup column? 2
Lookup Box 8
Newbie: Lookup within form 7
Lookup of client names 6
Missing records when they're there 2
Lookup Table for Client Names 1
Relationship Help with Tables 1
Display Report Total in Form 1

Top