Want all fields to show when Item selected

[LMB]

Hello Everyone,

I am just learning access but have no programming background. Using Access
2000. I have been adding components to an employee database for a long time
with the help of these groups. I have a form that I am making for data
entry and the main form has only employee name on it and is based on my
employee details table so the data entry person can pull the name up and
fill in the subform information for that person. The first subform has a
text box that is bound to EmpAudit_fkAuditID but I made it into a combo box
and instead of the ID number showing, I have the actual name of the ID in
there one name is "Blue Card". When I click in this box and select one of
the items in the list like "Blue Card", the subform below it pulls up my
Blue Card details and I can select one item at a time from the list to enter
data related to that item. Each AuditID has different items associated with
them. Right now, when I click on Blue Card, and then click on my combo box
on the subform, a list of items related to the Blue Card is displayed and I
have to select the first item and then enter the data associated with that,
then I need to click on the next combo box and select the second item
etc...until all the items have been selected. This works but I would like
to be able to have all of the items display so I don't make a mistake and
select an item twice or forget one. Would this be something I could do
using
a macro or changing the properties of the subform?

Thanks,
Linda

Here is what is in the properties of my subform.

Source Object sbfrmEmployeeAudits
Link Child Fields EmpAudit_fkEmpID
Link Master Fields strEmployeeID

When I click on the combo box which is bound to this field
EmpAudit_fkAuditID the code below runs so the proper list of items is
available on my fields on the bottom subform (at least that's what I think
it's doing). Obviously someone helped me with this and I have a teeny tiny
idea of whats going on but I wouldn't know the first thing about what to
type in there if this is what I need to do. <g>

Private Sub EmpAudit_fkAuditID_BeforeUpdate(Cancel As Integer)
Dim rstAuditDetails As Recordset

Set db = CurrentDb
Set rstAuditDetails = Me.sbfrmEmpAuditDetails.Form.Recordset
If Me.Form.NewRecord = False Then
If rstAuditDetails.RecordCount > 0 Then
MsgBox "Audit details exist - cannot change Audit type!",
vbOKOnly
Cancel = True
SendKeys "{Esc}"
Else
Me.sbfrmEmpAuditDetails.Form!EADetail_fkAItemID.Requery
End If
Else
Me.sbfrmEmpAuditDetails.Form!EADetail_fkAItemID.Requery

End If

 

[BruceM]

I'm not sure I quite follow all of this, but you may be able to get what you
want by using the form's property sheet to set the default view to Continuous
Form.
If you need more than this then please post the basic structure of your
database. For instance, the relationship between your main form and subforms
is not clear. You say that a combo box on the subform is bound to a field,
but how does that field fit in with the rest of the database? It is the
primary key field for the subform's record source table? When describing a
databases's structure you need only identify a general description of each
table, its key fields, and maybe a few words to describe other fields. An
Employee table might be something like:

tblEmployee
EmployeeID (primary key)
FirstName
LastName
etc.

A related table may be a benefits table:

tblBenefits
BenefitID (primary key)
EmployeeID (foreign key)
BenefitDescription

That's all that is needed to describe the structure.

 

[LMB]

Thanks Bruce. I'm not quite sure how to follow it either<g>. Thanks for
the guidelines on how to enter my question...I'll give it a try because I
have the forms property sheet set to continuous already. Here goes....and
let me know if I need to use another term or description for things.

tblEmployee (descriptions about employees)
EmpID (pk)
EmpLName
EmpFName
EmpOtherFields

tblAuditItems (items on documents being audited)
AItemID (pk)
AItem_fkAuditID (fk)
AItemName
AItemSortOrder

tblAudits (documents being audited)
AuditID (pk)
AuditName
AuditSortOrder

tblEmpAuditDetails
EADetailID (pk)
EADetail_fkEmpAuditID (pk)
EADetial_fkAItemID (pk)
EADetailFilledOut (Y, N, N/A)
EADetailsComments

tblEmployeeAudits
EmpAuditID (pk)
EmpAudit_fkEmpID (fk)
EmpAudit_fkAuditID (fk)
EmpAuditDate
EmpAuditAuditor (fk)linked with EmpID

We are auditing all items on several patient documents for accuracy and
completeness. If the person we are auditing documents the item correctly,
it is given a Y, if they don't it's a N. There are a few items on a few
documents that are not applicable to all patients so this would be an N/A.
Not filling these out is not counted against the person who filled out the
document.

On my form, when I click on the AuditName field and select the Audit Name I
am working on, the fields from the AuditItems table populate the combo box
on my subform. After I click on the first audit item and select y,n or n/a,
the next combo box field is visible, then I click there and select the
second item until and continue this process until I have selected them all.

This is what I would like to see if possible.

If there are five items in the AuditItems table for an Audit, I would like
the subform to show the five field names all at once that are ready to enter
the Y/N and N/A.

 

[BruceM]

One of your tables has three primary keys listed. I will assume that the
second two are intended to be foreign keys. I usually use the same field
name for a fk as I used for the pk. You can identify it as whatever you
like, but in my response I will assume the same field name for both.
As I understand it, employees fill out records, then those records are
audited to be sure the employee has filled them out correctly; the employees
table provides names that will appear as either auditor or auditee. Can an
employee be an auditor in some cases and an auditee in others?
On what table is your main form based? Is it on tblAudits? If so, its pk
(AuditID) would be a fk in Audit details. They would be related one-to-many
since each audit can have many details. Is AuditName in tblAudits the name
of the person being audited, or is it the auditor, or is it the title of the
audit? What is the point of the SortOrder fields? What is the difference
between tblAudits and tblEmployeeAudits?

You wrote "on my form when I click on the AuditName field and select the
Audit Name I am working on, the fields from the AuditItems table populate the
combo box on my subform". When you say you that on your form you "click on
the AuditName field and select the Audit Name I am working on", what form is
that? Do you select an employee name from a combo box? If so, is the combo
box bound to a field, or are you using it to select a record? It is not
clear what you mean when you say that selecting an AuditName on the main form
(which I assume is based on tblAudits), or when you say that making the
selection populates a combo box on the subform. I would expect you make a
selection from a combo box on the main form, which causes a record to appear,
and that related records appear on the subform (which is based on
tblAuditDetails). It sounds as if the subform is single form rather than
continuous. In a continuous form you can see all of the records at once (or
be able to scroll through them if they do not all fit on the screen). If it
is continuous and you see only one record, make the subform control larger so
that more records will fit.
If a single employee conducts each audit, EmployeeID should be a fk in
tblAudits, and AuditID should be a fk in tblAuditDetails. I would think you
would base your main form on tblAudits, with combo boxes to select auditor
and auditee from tblEmployees. A subform would be based on tblAuditDetails.
Define the pk fields in table design view, and use the Relationships window
to establish the foreign keys. Remember that foreign key fields are
established by their relationships to other fields. They are not defined in
table design view.
If you need to sort your data you can make queries based on tables, then
base your forms on those queries. A form also has an Order By property, but
to be honest I am not very familiar with that, so cannot advise as to whether
it should be used.

 

[LMB]

Hi Bruce,

Thanks for the reply. I am working on composing the answers but it's going
to take a little while as I am trying to figure it out myself and don't know
what everything is officially called. Hopefully I will have it all done by
mid afternoon tomorrow.

Linda