Combo box wizard not working as expected

[LadyIlsebet]

I'm creating a database that needs to track many many items related to
one employee. To do this, I have had to split up the data into
multiple tables, each with an ID field, with each ID field having a
one-to-one relationship with the ID field in the "personal" table.

On each form (which deals with one table only), I want to have a combo
box assist in changing records quickly, so when you're done with one
person, save the info, and then you can use the combo box to quickly
go to the next record you need to deal with. I therefore need the
combo box to change records based on info in the "personal" table.

The problem is, the combo box wizard is not giving me the "Find a
Record on My Form Based on the Value I selected in My Combo Box"
option. At first, I did get the option, but could only choose fields
from the form I was working with. So I tried adding fields from the
"personal" table (intending to hide them if they had to stay on the
form), but now I don't get the wizard option I need.

Any ideas? I'm hoping to make navigation to different records in the
form simpler than using the bar at the bottom that access provides.
I'm not "stuck" on using a combo box, but it was the best option I
could find in my Access book.

Many Thanks,
Amy

 

[Beetle]

First, one-to-one relationships are not common and are sometimes used
incorrectly. If you want to post an example of your
tables/fields/relationships
someone may be able to offer advice as to your structure.

As far as your combo box, it should be unbound (nothing in the control
source). The row source should be a query that selects, for example, the
EmployeeID an a concantenated First and Last name form the personnel
table i.e.;

Select tblPersonnel.EmployeeID, [FirstName] & " " & [LastName] As
[Employee Name] From tblPersonnel Order By tblPersonnel.LastName;

The combo box should also have the following properties;

Bound Column: 1
Column Count: 2
Column Widths: 0",1" (or whatever works best for you)

Then, in the After Update event of the combo box you would put code like
the following (this is air code, so it may need some tweaking)

With Me.RecordsetClone
.FindFirst "[EmployeeID]=" & Me.YourComboBox
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
Msgbox "No records found for this employee"
End If
End With

 

[Tom Wickerath]

To add some to Sean's answer, the reason you are not seeing the third option
in the wizard is that your form's recordset is a SQL statement. It needs to
be either a table, or a saved query, in order for the combo box wizard to
present you with the "Find a Record on My Form Based on the Value I selected
in My Combo Box" option.

Combo Box to Find a Record
http://www.access.qbuilt.com/html/find_a_record.html


I also question your design with many 1:1 relationships. You are working
much harder than you should be.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

To add some to Sean's answer, the reason you are not seeing the third option
in the wizard is that your form's recordset is a SQL statement. It needs to
be either a table, or a saved query, in order for the combo box wizard to
present you with the "Find a Record on My Form Based on the Value I selected
in My Combo Box" option.

ahhhh....

Thanks, Tom. I'd seen the wizard Apparate and Disapparate but didn't realize
that was the reason.

If I quit learning from this newsgroup I'll quit posting... but I really don't
expect that to happen for years!

 

[Tom Wickerath]

Hi John,

I feel the same as you...I'm constantly learning new stuff by reading
threads in the various Access related newsgroups!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[LadyIlsebet]

I also question your design with many 1:1 relationships. You are working
much harder than you should be.

</snip>

I tried putting most of everything into one table already, and ran
into the number of fields limit. Using the database documenter gives
me about 12 pages, which I'll email someone, but I don't think anyone
wants to see here. I unfortunately am tasked on other things today
keeping me from having the time to edit the 12 pages down to something
usable for Usenet. For many things in this database, I have to track
the date the requirement was passed, as well as what type of
documentation was provided. This is for a local paramedic service, so
there are a TON of requirements to track. I also have separate tables
to track hire dates and position types (we have to keep a history of
every position held by every employee), and one of their ID card
numbers (provincially granted, and replaced with a different ID card
and number if lost, for which, of course, we need a complete history
for every employee).

Since I'm using the 1:1 relationships to get around the 255 field
limit in a table, is there a better way around that?

I will try coding the combo box per Sean Bailey's suggestion, although
it will have to wait until later in the week - thank you very much for
that!

Amy

 

[John W. Vinson]

Since I'm using the 1:1 relationships to get around the 255 field
limit in a table, is there a better way around that?

YES.

"Fields are expensive. Records are cheap".

If each record has many dates (of events of some kind??) you need two tables
in a one to many relationship, with each event date *in a record by itself*.
If one person has 54 reporting dates, you don't need 54 fields - you need a
different table with 54 *RECORDS*, one for each event.

My guess is that you will have a typical many to many relationship. You don't
say what the "requirements" apply to, so I'll speculate that it's a position;
if so you would need three tables:

Positions
PositionID
<information about the position as an entity>

Requirements
RequirementID
<description of the requirement>

MetRequirements
PositionID <link to Positions>
RequirementID <link to Requirements>
DateAttained
<other info about THIS position and THIS requirement>

 

[LadyIlsebet]

Ok, I *think* I get this. Can you tell it's been a decade since my
rather crappy single database course in University? Here's what I'm
thinking I'd do - the training requirements apply to each person.

Personal
ID
Name, Sex, addresses, phone numbers, etc.
Training
TrainingID
Training Description

CopyOnFileTypes
CopyType
CopyDescription

CompletedTraining
ID <linked to Personal>
TrainingID <linked to Training>
Date <could be attained, or date this will expire - i know date is a
reserved word in access>
CopyOnFile <linked to CopyOnFileTypes table>

Scans
ID<linked to personal>
<individual fields for all the scanned files, using hyperlink type>

OASISHistory
ID <linked to personal>
OASISNumber
OASISDate <date that particular OASIS number issued>

EmpHistory
ID <linked to personal>
PositionNumber <linked to Positions>
DateHired

FluShot
ID <linked to personal>
FluType
FluDate

Positions
PositionNumber
PositionDescription


Is this more along what you were describing? I'm also worried that
this will make coding my forms more difficult, as well as make ad-hoc
reports trickier for my end user, but I suppose I should just get
started on this version of the design

Thanks for the help!

Amy

On May 12, 12:52 pm, John W. Vinson

"Fields are expensive. Records are cheap".

If each record has many dates (of events of some kind??) you need two tables
in a one to many relationship, with each event date *in a record by itself*.
If one person has 54 reporting dates, you don't need 54 fields - you need a
different table with 54 *RECORDS*, one for each event.

</snip>

 

[John W. Vinson]

Ok, I *think* I get this. Can you tell it's been a decade since my
rather crappy single database course in University? Here's what I'm
thinking I'd do - the training requirements apply to each person.

Personal
ID
Name, Sex, addresses, phone numbers, etc.
Training
TrainingID
Training Description

CopyOnFileTypes
CopyType
CopyDescription

I'm not sure what a "Copy" or "CopyOnFile" might be - a document? What's the
primary key of this table?

CompletedTraining
ID <linked to Personal>
TrainingID <linked to Training>
Date <could be attained, or date this will expire - i know date is a
reserved word in access>
CopyOnFile <linked to CopyOnFileTypes table>

Linked to what field?

Scans
ID<linked to personal>
<individual fields for all the scanned files, using hyperlink type>

STOP. You're doing it again! Each scanned file should be *an individual
record* in a table; the Scans table should have only one file field.

If the same file might pertain to multiple students, you need a many to many
relationship, modeled by a table of Files (or Scans, or whatever you're
calling them), and a table of StudentFiles, linked many to one to both
Personal and to Scans.

OASISHistory
ID <linked to personal>
OASISNumber
OASISDate <date that particular OASIS number issued>

EmpHistory
ID <linked to personal>
PositionNumber <linked to Positions>
DateHired

Maybe DateTerminated, blank if they're still in the position, and some other
fields about the person's employment (reason terminated, employer comments,
etc.)

FluShot
ID <linked to personal>
FluType
FluDate

If there's a need for other medical information, generalize this to
MedicalTreatments with flu shots as just one type.

Positions
PositionNumber
PositionDescription


Is this more along what you were describing? I'm also worried that
this will make coding my forms more difficult, as well as make ad-hoc
reports trickier for my end user, but I suppose I should just get
started on this version of the design

It'll make things EASIER not harder, eventually. Your forms should have Forms
for the "one" side, with multiple Subforms for the "many" side tables; and
yes, your ad-hoc reports will require getting up close and cosy with the Query
grid, and probably even the SQL window (the query grid is just a tool to build
SQL strings).

Thanks for the help!

Glad to be of assistance.

 

[Tom Wickerath]

Hi Amy,

Since I'm using the 1:1 relationships to get around the 255 field
limit in a table, is there a better way around that?

I see that John Vinson has been helping you some today, so I'll just add a
few comments here. I suggest reading the first two database design documents,
written by Michael Hernandez, available here:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

One of them is a very easy read; it is only four pages in length.

Personal table
Name

Name is a reserved word. You should avoid using any reserved words in Access
for things that you assign a name to, including fields, tables, queries,
forms, reports, macros, modules, procedures (ie. subroutines and functions),
constants and variables within procedures, and controls on forms and reports.
Access MVP Allen Browne has a nice collection of reserved words here:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

Here, you should see a link for downloading his "Database Issue Checker
Utility", which can quickly and easily scan your field and table names for
the use of reserved words. This utility does not currently include scanning
the names of controls on forms and reports for reserved words.

CompletedTraining
Date <could be attained, or date this will expire - i know date is a

reserved word in access>

Okay, so you're not actually going to use the field name "Date", right?

For the various ID fields, I suggest a more descriptive name such as
PersonelID.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________