Access 2002 help please !

[Fraz]

Ok so I volunteered (silly me) to create the customer database thinking it
would be easy!

I have used a basic MS template and manipulated it to suit my needs. I now
need to put in a search button for companies, people and post codes . I also
need to create reports such as call visits, contact listings and company
listings with addresses etc. I am totally stumped and am goin round in
circless.
Help please?????

 

[Klatuu]

As Bonnie said, you do need to concentrate on one item at a time. If you
start with one thing, you can build on your experience from that. Since you
mentioned a search button, let's start with a way to search for a company.
But, we will not use a button, we will use a Combo Box. This is a very
common way to do a search on an Access form.
Now, in your Company table, There should be a field that is the primary key
field of the table. I don't know your data, but I would guess there may be
an Auto Nuumber field named something like CompanyID and a Text Field named
something like CompanyName. You will need those two fields to use as the
combo's row source. the CompanyID will be used to do the actual search. the
CompanyName will be used to display the companies to the user. Humans
understand the name, but the computer would be happier with the number.

So, put a combo box on your form. I will call it cboCompany. Since it will
be used for searching and not for storing or retrieving data in a table, it
will be an Unbound control. That is, we will not put a field name from the
form's record source in the Control Source property of the combo.
Here are some properties you need to set for your combo:
Row Source Type - Table/Query
Row Source - Select the two fields from the company table with the
Autonumber field first and the name field second.
Bound Column - 1 (Don't confuse this with Bound Control, it is different)
Column Count - 2
Column Widths - 0";3" (The 0" will hide the numeric column. The 3" can be
whatever size you need to display the company name)
Auto Expand - Yes
Limit To List - Yes

Now, to actually do the search, we need a bit of code. It will go in the
After Update event.
Choose the Events tab of the property dialog for the combo.
Click on the small button with the 3 dots just to the right of the text box
labeled After Update.
Select Code Builder
Paste following code into the VBA Editor when it opens:

With Me.RecordsetClone
.FindFirst "[CompanyID] = " & Me.cboCompany
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Now, if the field name in the table is not CompanyID, you will need to
change that to the actual name of the field. Also, the code is written
expecting CompanyID to be a numeric field. If it is a text field, you would
change the second line to this:

.FindFirst "[CompanyID] = """ & Me.cboCompany & """"

Don't expect it to work right the first time <g> We can expect errors until
you get the right names and an understanding of how it works.

Please post back when you have questions on this and we will get it working.
Once you are able to search on company, usind a different field to search on
will come prettty easy because you can copy the technique you have learned in
this exercise.

 

[Ken Sheridan]

Fraz:

If you want a flexible search form which allows you to optionally select
multiple criteria, i.e. you might select a company, a contact, a postcode or
any of these in combination (or even none of them to return all records),
then the easiest way is to create query which references the controls on the
search form as parameters.

Create an unbound search form, frmSearch say, and add unbound combo boxes
for selecting a company, contact or postcode. Dave Hargis has shown you how
to do this for companies. Contacts would be similar, e.g.

RowSource: SELECT ContactID, FirstName & " " & LastName FROM Contacts
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For postcodes it would be slightly different as I don't imagine you have a
separate postcodes table, but just a column in the Companies table, so it
would be set up like this:

RowSource: SELECT DISTINCT PostCode FROM Companies ORDER BY PostCode;

BoundColum: 1
ColumnCount: 1

Now create the query, e.g.

SELECT Company, AddressLine1, AddressLine2, City, PostCode,
FirstName, LastName
FROM Companies INNER JOIN Contacts
ON Contacts.CompanyID = Companies,CompanyID
WHERE (Companies.CompanyID = Forms!frmSearch!cboCompany
OR Forms!frmSearch!cboCompany IS NULL)
AND (ContactID = Forms!frmSearch!cboContact
OR Forms!frmSearch!cboContact IS NULL)
AND (PostCode = Forms!frmSearch!cboPostCode
OR Forms!frmSearch!cboPostCode IS NULL);

Note how each OR operation is parenthesised in the above. That's important
as it forces each to evaluate independently of the AND operations. This is
what makes the combo boxes on the search form optional. You can set the
query up in design view if you wish and then switch to SQL view and add the
WHERE clause.

Base a form and/or report on this query. Add a button, or buttons, to the
search form to open the form and/or report based on the query. You can now
select from any combination of the combo boxes optionally and click the
button to open the form or report.

For your other reports concentrate on creating queries to return the correct
data from the relevant tables. Once you have the queries creating the
reports is simple; in fact the report wizard can do all the work for you.

Ken Sheridan
Stafford, England