Best practice...

[brian3d via AccessMonster.com]

Information: Access2007
Requirement: I have four text box's. The first one I would like to auto fill
based on a selection made in another form. Once the auto fill is done, I want
the three corresponding box's to auto fill based off of the first box's
information.

The first box's information is an object, while the three corresponding boxs
are the three characteristics of that object. So in one form, that object is
selected, then it is autofilled into a box on another form, were I then want
it's three characteristics to auto populate.

The question: I'm basically looking for a best practice solution to cascading
the characteristics into the three boxs, based off of what object is selected.


I hope I have explained this clearly enough for understanding. Any help would
be apprecated.

 

[Scott McDaniel]

Information: Access2007
Requirement: I have four text box's. The first one I would like to auto fill
based on a selection made in another form. Once the auto fill is done, I want
the three corresponding box's to auto fill based off of the first box's
information.

The first box's information is an object, while the three corresponding boxs
are the three characteristics of that object. So in one form, that object is
selected, then it is autofilled into a box on another form, were I then want
it's three characteristics to auto populate.

The question: I'm basically looking for a best practice solution to cascading
the characteristics into the three boxs, based off of what object is selected.

Assuming you're storing the default values of your "object" in a table, then just open a recordset after the main object
is filled, then fill your other textboxes:

Dim rst As ADODB.Recordset

Set rst = new ADODB.Recordset
rst.OPen "SELECT * FROM SomeTable WHERE SomeObjectID=" & TheSelectedObjectID, CurrentProject.Connection

If Not (rst.EOF and rst.BOF) Then
'/now fill your textboxes
With Forms("YourOtherForm")
.Textbox1 = rst("Field1")
.Textbox2= rst("Field2")
.Textbox3= rst("Field3")
End With
End IF

Set rst = Nothing

I hope I have explained this clearly enough for understanding. Any help would
be apprecated.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[John W. Vinson]

Information: Access2007
Requirement: I have four text box's. The first one I would like to auto fill
based on a selection made in another form. Once the auto fill is done, I want
the three corresponding box's to auto fill based off of the first box's
information.

The first box's information is an object, while the three corresponding boxs
are the three characteristics of that object. So in one form, that object is
selected, then it is autofilled into a box on another form, were I then want
it's three characteristics to auto populate.

The question: I'm basically looking for a best practice solution to cascading
the characteristics into the three boxs, based off of what object is selected.


I hope I have explained this clearly enough for understanding. Any help would
be apprecated.

Two points:

1. You're not storing data in a Form. The form is JUST A WINDOW; the data is
stored in your Table, not in the textboxes.

2. If you're extracting these three fields from one table and storing them
redundantly in another table, you are probably making a big mistake.
Relational databases use the "Grandmother's Pantry Principle": "a place - ONE
place! - for everything, everything in its place". You may just want to store
a foreign key, a link to the object table to *display* the characteristics.
It's convenient to use a Combo Box to select from the list of objects; you can
include the other three fields in the combo's Row Source query and display
them on the form using textboxes with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field you want displayed.

If you have a good reason to store the data redundantly please explain - it
can be done, it's just that "best practices" would usually prohibit doing so.

John W. Vinson [MVP]

 

[brian3d via AccessMonster.com]

Thank you for the info Scott, apprecate the advice.

Information: Access2007
Requirement: I have four text box's. The first one I would like to auto fill

[quoted text clipped - 9 lines]

The question: I'm basically looking for a best practice solution to cascading
the characteristics into the three boxs, based off of what object is selected.

Assuming you're storing the default values of your "object" in a table, then just open a recordset after the main object
is filled, then fill your other textboxes:

Dim rst As ADODB.Recordset

Set rst = new ADODB.Recordset
rst.OPen "SELECT * FROM SomeTable WHERE SomeObjectID=" & TheSelectedObjectID, CurrentProject.Connection

If Not (rst.EOF and rst.BOF) Then
'/now fill your textboxes
With Forms("YourOtherForm")
.Textbox1 = rst("Field1")
.Textbox2= rst("Field2")
.Textbox3= rst("Field3")
End With
End IF

Set rst = Nothing

I hope I have explained this clearly enough for understanding. Any help would
be apprecated.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[brian3d via AccessMonster.com]

Thank you John, apprecate the advice.

Information: Access2007
Requirement: I have four text box's. The first one I would like to auto fill

[quoted text clipped - 12 lines]

I hope I have explained this clearly enough for understanding. Any help would
be apprecated.

Two points:

1. You're not storing data in a Form. The form is JUST A WINDOW; the data is
stored in your Table, not in the textboxes.

2. If you're extracting these three fields from one table and storing them
redundantly in another table, you are probably making a big mistake.
Relational databases use the "Grandmother's Pantry Principle": "a place - ONE
place! - for everything, everything in its place". You may just want to store
a foreign key, a link to the object table to *display* the characteristics.
It's convenient to use a Combo Box to select from the list of objects; you can
include the other three fields in the combo's Row Source query and display
them on the form using textboxes with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field you want displayed.

If you have a good reason to store the data redundantly please explain - it
can be done, it's just that "best practices" would usually prohibit doing so.

John W. Vinson [MVP]