Forms and Queries and Count fields

[Guest]

I have been asked to do a small database, where there is a "location" table
(this is all static information, and contains details of specific locations).
My company does work at these locations (3000) of them, and wants to have a
record of proposed jobs on some of these locations, as well as a history of
previous jobs at these locations.
I have created a structure where simplistically there is a location table,
as well as a job descriptor table. the job descriptor has the location as a
FK. (there are lots of other tables, but they are (easy). The location table
has an autonumber PK, because many of the locations are very similar, eg High
St
Now what I am trying desperately to do is get a search function to work,
which looks for information in the location table, and then places it into
the Job descriptor table. The query I base my form on, only seems to want to
look at current jobs and their locations. Because there are so many records,
the simple combo box I am using for contractors and my other smaller forms is
unwieldly, so I need to use a filter, to source out the correct location to
enter.
The second issue is I need to log jobs at each location. I am trying to
figure out how to do a "count field", so that I can distinguish in a
numerical order the jobs performed at each location - eg (1998) (1) (High
St) (replace traffic lights); (1998) (2) ( High St) (dig up road); etc, in
such a way that they can then select the job and look at it in more detail,
eg who worked on it, how much it cost etc.....

 

[Guest]

Firstly the foreign key in the job descriptor table should be a long integer
number LocationID or similar field referencing the primary key of the
Locations table. Even with the large number of locations a simple combo box
on the form based on the Job Descriptor table should work satisfactorily if
you set its AutExpand property to True (Yes in the properties sheet). As a
user enters each character of the location name the combo box will go to the
first match. The RowSource of the combo box would be along the lines of:

SELECT LocationID, Location FROM Locations ORDER BY Location;

Its other properties would be as follows:

ControlSource LocationID
AutoExpand Yes
BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, LocationID, column, so only the
location name shows.

As regards your second question I'd suggest using a subform embedded in an
unbound form from which you can select the location from a combo box so that
the jobs for that location are then listed in the subform. You can then
select the relevant row in the subform to open a form to view more detailed
information about that job.

First create a form for use as the subform. Make this continuous form view
and use a query based on the Job Descriptor table which orders the jobs by
date (either ascending or descending as you wish). Add a command button to
the form, which can be in the detail section or in the header or footer as
you prefer. This button will be to open the more detailed jobs form for the
selected row using code along these lines in its Click event procedure:

Dim strCriteria As String

strCriteria = "JobID = " & Me.JobID

DoCmd.OpenForm "frmJobs", _
WhereCondition:= strCriteria, _
WindowMode:=acDialog

where frmJobs is the name of the more detailed jobs form to be opened and
JobID is the numeric primary key of the Job Descriptor table, e.g. an
autonumber.

Embed this form as a subform in an unbound form and above the subform add a
combo box exactly the same as the one described above (in fact you can simply
copy and paste it from the first form). In the case of the combo box in the
subform delete the entry for its ControlSource property however as you want
this combo box to be unbound.

Set the LinkMasterFields property of the subform control (i.e. the control
housing the subform) to the name of the unbound combo box, e.g. cboLocations,
and set its LinkChildFields property to LocationID, i.e. the name of the
foreign key field in the subform's underlying table.

When a location is selected in the combo box on the main form the subform
should show only those jobs for that location sorted by date in ascending or
descending order as determined by the underlying query. When you click on
the button on the subform the more detailed frmJobs form will open at the
selected job record. Because the form is opened in dialogue mode using the
acDialog setting for the WindowMode argument of the OpenForm method you will
be forced to close this detailed form before returning to the original form.

Ken Sheridan
Stafford, England