Auto Complete Fields

#1 (**permalink**) 28-Jul-2006, 08:27 AM

I am using three different forms for my database, one of which is for
"personal information". I want some of the fields on the Personal Info form
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12174-auto-complete-fields.html
to repeat at the top of the other two forms, but i need them to "auto
complete" as each of the other forms is designed to track multiple entries
for the selected person. I'm not sure how to accomplish this. Any help
would be greatly appreciated. Thanks.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12174

*

#2 (**permalink**) 28-Jul-2006, 08:27 AM

Derek,

It's hard to tell without information about the tables and/or queries your
forms are based on, but "AutoComplete" raises a red flag to me--it suggests
that you are attempting to *store* information from your PersonalInfo table
into the RecordSources on which the other two forms are based. This is
non-normalized design and bad practice. The only field that should be
replicated in the other tables is the primary key of your PersonalInfo table
(a foreign key).

If, on the other hand, you already have normalized tables, and merely wish
to *display* the fields from the PersonalInfo table, there are 2 ways to do
it:

1) Use a combo box Bound to the foreign key field. Set the RowSource to
select the primary key & any other fields from your PersonalInfo tables. Set
the BoundColumn to 1 to store the primary key in the foreign key field. Set
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12174
the first column width to 0" so that the 2nd field displays in the drop-down
list and after selection. If you enable the combo box wizard, it's very
straightforward. Accept the default Hide Key Field (Recommended) when
prompted.

Then use the Column property of the combo box to display the other fields in
textboxes. The index starts with zero, so to display the third column, set
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12174
the textbox' ControlSource to = YourComboBox.Column(2)

2) Base your form on a query that links the two tables by the
primary/foreign key fields, and include those fields you wish to display.
Don't include the primary key field from PersonalInfo or your query will be
non-updateable; include the foreign key instead. Use textboxes to display
the additional fields and a combo box for the foreign key.

Hope that helps.
Sprinks

"DerekCraine" wrote:

> I am using three different forms for my database, one of which is for
> "personal information". I want some of the fields on the Personal Info form
> to repeat at the top of the other two forms, but i need them to "auto
> complete" as each of the other forms is designed to track multiple entries
> for the selected person. I'm not sure how to accomplish this. Any help
> would be greatly appreciated. Thanks.

#3 (**permalink**) 28-Jul-2006, 08:27 AM

Thanks, Sprinks. I appreciate the help, and I'm running with it now.

"Sprinks" wrote:

> Derek,
>
> It's hard to tell without information about the tables and/or queries your
> forms are based on, but "AutoComplete" raises a red flag to me--it suggests
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12174
> that you are attempting to *store* information from your PersonalInfo table
> into the RecordSources on which the other two forms are based. This is
> non-normalized design and bad practice. The only field that should be
> replicated in the other tables is the primary key of your PersonalInfo table
> (a foreign key).
>
> If, on the other hand, you already have normalized tables, and merely wish
> to *display* the fields from the PersonalInfo table, there are 2 ways to do
> it:
>
> 1) Use a combo box Bound to the foreign key field. Set the RowSource to
> select the primary key & any other fields from your PersonalInfo tables. Set
> the BoundColumn to 1 to store the primary key in the foreign key field. Set
> the first column width to 0" so that the 2nd field displays in the drop-down
> list and after selection. If you enable the combo box wizard, it's very
> straightforward. Accept the default Hide Key Field (Recommended) when
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12174
> prompted.
>
> Then use the Column property of the combo box to display the other fields in
> textboxes. The index starts with zero, so to display the third column, set
> the textbox' ControlSource to = YourComboBox.Column(2)
>
> 2) Base your form on a query that links the two tables by the
> primary/foreign key fields, and include those fields you wish to display.
> Don't include the primary key field from PersonalInfo or your query will be
> non-updateable; include the foreign key instead. Use textboxes to display
> the additional fields and a combo box for the foreign key.
>
> Hope that helps.
> Sprinks
>
> "DerekCraine" wrote:
>
> > I am using three different forms for my database, one of which is for
> > "personal information". I want some of the fields on the Personal Info form
> > to repeat at the top of the other two forms, but i need them to "auto
> > complete" as each of the other forms is designed to track multiple entries
> > for the selected person. I'm not sure how to accomplish this. Any help
> > would be greatly appreciated. Thanks.