Syncronizing Combo Boxes

T

Tim

I have a continuous form that has 2 linked combo boxes and I trying to
synchronizing them so that when you scroll through the records the 2 fields
in focus are exact.

Here is my table structure:

• MASTER
X (auto ID)
ss#
Surname
First

• Medical
Y (auto ID)
PERSON (X, surname)
Date

• Physical
Z (auto ID)
Person (X, surname)
Medical (Y, DATE)

To put into words what the structure is:
The Master table is the primary.
Medical & Physical references the Auto ID from the Master.
And I am referencing Date from the Medical table in the Physical table.

Now my form (frmPhysical) is based on the Physical Table.
I have successfully cascaded the 2 fields, Person & Medical to when you
select Person, it shows the dates they have had Medicals.

My next task is when you scroll through the records, you will see the
correct date for each person.
 
S

Sprinks

Tim,

I'm afraid I can't help without knowing more about the purpose of your
tables and their relationships.

However, I can say immediately that your tables are not normalized. The
only field you need in the Medical and Physical tables to identify a person
in the Master table is a numerical foreign key corresponding to the X primary
key. Similarly, the only field required in the Physical table to identify
the correct Medical record is a numerical foreign key corresponding to Y.

Also, do not name a field Date, since it is a reserved word. The use of
reserved words as fieldnames can produce unpredictable behavior.

IF there is a one-to-many relationship between Master and Medical, and a
one-to-many relationship between Medical and Physical, this relationship is
the same as Customers, Orders, and Order Details. You may benefit from
studying the Customer Orders form in the Northwind sample database that ships
with Access. (Help, Sample Databases, Northwind).

Sprinks
 
T

Tim

It is one-to-many relationships between master-medical & master-physical.
The use of the [X,Surname] & [Y,surname] was my way of trying to convey that
I am using the numerical ID from the master table to reference the person and
that SURNAME is there for astetic reason when it comes to the output on forms
& reports. A multi-column combo box.

You say my tables are not normalized, I do not know what that means.

As for the purpose, each person will have multiple phyiscals and multiple
medical (at least 1 a year)
__________
 
S

Sprinks

Hi, Tim.

Database normalization is a set of design rules for relational databases.
Employing them minimizes storage requirements and greatly improves data
integrity. An introduction to the topic is available at:

http://databases.about.com/od/specificproducts/a/normalization.htm

Google "database normalization" for further information.

The reason I was saying that your tables were not normalized was that I
thought you were including a surname field in tables other than the master
table. But it sounds as if you are only displaying the surname on a form, so
perhaps your tables are normalized already.

Regarding your question, I can't say without knowing more about your combo
boxes and form:

- in what sense are they "linked"?
- What are their RowSource and ControlSource properties?
- Do you have any code attached to them? For example, are you trying to
set the RowSource of one based on a selection from another?
- Do you have a single continuous form, or a main form and a subform? If
Master-Medical and Master-Physical are one-to-many relationships, then the
normal strategy is to have a main form based on the one side, and a
continuous subform on the many side. The subform's LinkMasterFields and
LinkChildFields properties should be set to the name of the common field in
the one and the many sides, respectively. This will cause the child records
to display with the master record.

Sprinks


Tim said:
It is one-to-many relationships between master-medical & master-physical.
The use of the [X,Surname] & [Y,surname] was my way of trying to convey that
I am using the numerical ID from the master table to reference the person and
that SURNAME is there for astetic reason when it comes to the output on forms
& reports. A multi-column combo box.

You say my tables are not normalized, I do not know what that means.

As for the purpose, each person will have multiple phyiscals and multiple
medical (at least 1 a year)
__________

Sprinks said:
Tim,

I'm afraid I can't help without knowing more about the purpose of your
tables and their relationships.

However, I can say immediately that your tables are not normalized. The
only field you need in the Medical and Physical tables to identify a person
in the Master table is a numerical foreign key corresponding to the X primary
key. Similarly, the only field required in the Physical table to identify
the correct Medical record is a numerical foreign key corresponding to Y.

Also, do not name a field Date, since it is a reserved word. The use of
reserved words as fieldnames can produce unpredictable behavior.

IF there is a one-to-many relationship between Master and Medical, and a
one-to-many relationship between Medical and Physical, this relationship is
the same as Customers, Orders, and Order Details. You may benefit from
studying the Customer Orders form in the Northwind sample database that ships
with Access. (Help, Sample Databases, Northwind).

Sprinks
Click to expand...
Click to expand...
 
T

Tim

Would it help if I e-mailed you a copy of my database so you could see what I
have coded?
 
S

Sprinks

Hi, Tim.

I've looked at your form now; it took me a bit to figure out I was missing a
reference.

You have stumbled on the notorious "disappearing data syndrome"!

On a continuous form, the RowSource for a combo box has only one value at
any given time, it cannot have a separate value for each record. So, if you
enter a new record, your AfterUpdate event procedure changes the RowSource to
display the Medical records for this person and TestDate. Since the values
entered in other records aren't in the new RowSource, Access cannot display
them, so they seem to disappear. The values are, however, still intact in
the underlying table.

The only workaround to this problem that I'm aware of, other than using a
single record form is to use a combo box on a popup dialog form to select the
value, and then write the selection to a bound textbox on the main form in
the combo box' AfterUpdate event. Since the RowSource of your current
Medical combo box uses the full reference to the officer textbox in your
WHERE clause (rather than using the Me! shortcut), you can use the same
RowSource for the combo box on the Dialog form.

‘ Textbox on main form’s On Enter event
DoCmd.OpenForm _
FormName:="YourDialogForm", _
View:=acNormal, _
WindowMode:=acDialog
' Code resumes here after the dialog form closes. Set the focus to the
next field.
Me!YourNextField.SetFocus

' Dialog form AfterUpdate event
Forms!YourMainForm!YourTextBox = Me!YourComboBox
' Close the dialog form
DoCmd.Close

As to the dates not changing as you scrolled through the records, you were
not requerying the combo box in the form's OnCurrent event.

Other Comments:
----------------------
- Avoid naming fields or controls "Date" or "Year", which are reserved
Access words. Use "ExamDate" or "ExamYear" instead. Using reserved words
can cause unpredictable behavior. Google "Access Reserved Words" for a
complete listing.

- GROUP BY is a clause used for doing summary totals (Sum, Count, Avg,
etc.). Although it doesn't cause an error, it is not relevant in your combo
box query.

- You are using a lot of Lookup fields. Search this forum for a thorough
discussion of this topic. This feature is problematic, and avoided by most
developers.

- Since tables are analogous to "things", and fields analogous to
"attributes", I prefer to name tables by nouns that define the thing, mainly
for the benefit of other users or inheritors of my applications. This may be
a personal preference, but Officers, AgilityTests, and MedicalExams seem more
straightforward names that describe the records.

- As a convenience issue, if you use no blanks in your field names, you
don't have to delimit them in their reference.

Forms!MyForm!LastName instead of:
Forms!MyForm![Last Name]

Hope that helps.
Sprinks
 
Top