Update textboxes based on entry of a texbox

G

Guest

I currently use Access97 but will be upgrading to 2003 within a couple of
months.

I currently have a subform called "frmHistory". It has the following fields:

"Date" (auto date)
"LeadSource" (combo box)

"frmHistory" is a subform of "frmLeads". It has the following fields:

"Territory"
"RBM"
"TSS"
"Distributor"
"HouseRtrnBy" (text)
"HouseRtrnDate" (date)
"DistribRtrnBy" (text)
"DistribRtrnDate" (date)

Depending on the territory entered, fields "RBM", "TSS", and "Distributor"
auto fill in.

The "LeadSource" combo box is bound to "tblLeadSources" and has several
records including "Phone", "Magazine Ad", "Distrib LR", "RBM LR", "TSS LR"
and many others. When I choose "RBM LR" or "TSS LR", I want "HouseRtrnBy" to
autofill with either the "RBM" or "TSS" and "HouseRtrnDate" to autofill with
the "Date"

Same thing with "Distrib LR", I want "DistribRtrnBy" and "Distrib RtrnDate"
to autofill in.

I'm a novice, please be detailed in your directions. Thanks sooo much!
 
G

Guest

Hi.

To assign controls a value based on an entry, use the AfterUpdate event
procedure. If you've never used one of these, edit the form in design view,
select the LeadSource combo box, and show the Properties by selecting View,
Properties. Click the Event tab, and click on the AfterUpdate field, then
the ellipsis to the right of it. Choose Code Builder if given a choice.

Access will create the shell of an AfterUpdate event procedure for this
control, which will be run automatically when the Update event occurs, i.e.,
when you change the value in this field. Because you wish to update controls
in the subform, you must use a more complex syntax to refer to them than
those in the main form.

Referring to a Form Control From A Main Form Event Procedure
---------------------------------------------------------------------------
Main Form Control Me![NameOfControl]
Subform Control Me![NameofSubform].Form![NameofSubformControl]

So, in your case, the following code will do as you ask. Place it between
the Sub and End Sub lines:

Select Case Me![LeadSource]
Case "RBM LR"
Me![NameofSubform].Form![HouseRtrnBy] = "RBM"
Me![NameofSubform].Form![HouseRtrnDate] = Me![Date]
Case "TSS LR"
Me![NameofSubform].Form![HouseRtrnBy] = "TSS"
Me![NameofSubform].Form![HouseRtrnDate] = Me![Date]
Case "Distrib LR"
Me![NameofSubform].Form![DistribRtrnBy] = "TSS"
Me![NameofSubform].Form![DistribRtrnDate] = Me![Date]

End Select

Two other things.

You should rename the Date field in the table underlying the main form to
something like LeadDate. "Date" is an Access reserved word, the use of which
as a field name can produce unpredictable behavior. A Google Search on
"Access Reserved Words" will get you to a comprehensive list.

Also, while I can't know for sure without fully understanding the purpose of
your application and your tables, I have a strong suspicion based on what
you're trying to do, and because you have parallel sets of fields
("HouseRtrnBy"/"HouseRtrnDate" and "DistribRtrnBy"/"DistribRtrnDate"), that
your tables are not properly normalized, which will make your application
more difficult to maintain. I suggest you read and understand this topic
thoroughly before making these or other changes to your application.

As an example, an application I inherited included the parallel fields:
HVACBidAmount
ElectricBidAmount
PlumbingBidAmount
SiteworkBidAmount
etc.

If more categories were desired, a field would have to be added to the
table. This is avoided by breaking this information into a new detail table
of the structure:

BidID AutoNumber Primary Key
ProjectID Foreign Key to Tie the Detail Records to Its Parent Record
BidType
Amount

This structure reflects the one-to-many relationship, and any arbitrary
number of Bid Detail records may be added.

Hope this helps.
Sprinks
 
G

Guest

Thank you! I tried it today, and it worked perfectly. I changed the "date"
field to "HistoryDate". Thank you for your advice!

Sprinks said:
Hi.

To assign controls a value based on an entry, use the AfterUpdate event
procedure. If you've never used one of these, edit the form in design view,
select the LeadSource combo box, and show the Properties by selecting View,
Properties. Click the Event tab, and click on the AfterUpdate field, then
the ellipsis to the right of it. Choose Code Builder if given a choice.

Access will create the shell of an AfterUpdate event procedure for this
control, which will be run automatically when the Update event occurs, i.e.,
when you change the value in this field. Because you wish to update controls
in the subform, you must use a more complex syntax to refer to them than
those in the main form.

Referring to a Form Control From A Main Form Event Procedure
---------------------------------------------------------------------------
Main Form Control Me![NameOfControl]
Subform Control Me![NameofSubform].Form![NameofSubformControl]

So, in your case, the following code will do as you ask. Place it between
the Sub and End Sub lines:

Select Case Me![LeadSource]
Case "RBM LR"
Me![NameofSubform].Form![HouseRtrnBy] = "RBM"
Me![NameofSubform].Form![HouseRtrnDate] = Me![Date]
Case "TSS LR"
Me![NameofSubform].Form![HouseRtrnBy] = "TSS"
Me![NameofSubform].Form![HouseRtrnDate] = Me![Date]
Case "Distrib LR"
Me![NameofSubform].Form![DistribRtrnBy] = "TSS"
Me![NameofSubform].Form![DistribRtrnDate] = Me![Date]

End Select

Two other things.

You should rename the Date field in the table underlying the main form to
something like LeadDate. "Date" is an Access reserved word, the use of which
as a field name can produce unpredictable behavior. A Google Search on
"Access Reserved Words" will get you to a comprehensive list.

Also, while I can't know for sure without fully understanding the purpose of
your application and your tables, I have a strong suspicion based on what
you're trying to do, and because you have parallel sets of fields
("HouseRtrnBy"/"HouseRtrnDate" and "DistribRtrnBy"/"DistribRtrnDate"), that
your tables are not properly normalized, which will make your application
more difficult to maintain. I suggest you read and understand this topic
thoroughly before making these or other changes to your application.

As an example, an application I inherited included the parallel fields:
HVACBidAmount
ElectricBidAmount
PlumbingBidAmount
SiteworkBidAmount
etc.

If more categories were desired, a field would have to be added to the
table. This is avoided by breaking this information into a new detail table
of the structure:

BidID AutoNumber Primary Key
ProjectID Foreign Key to Tie the Detail Records to Its Parent Record
BidType
Amount

This structure reflects the one-to-many relationship, and any arbitrary
number of Bid Detail records may be added.

Hope this helps.
Sprinks
ILoveAccess said:
I currently use Access97 but will be upgrading to 2003 within a couple of
months.

I currently have a subform called "frmHistory". It has the following fields:

"Date" (auto date)
"LeadSource" (combo box)

"frmHistory" is a subform of "frmLeads". It has the following fields:

"Territory"
"RBM"
"TSS"
"Distributor"
"HouseRtrnBy" (text)
"HouseRtrnDate" (date)
"DistribRtrnBy" (text)
"DistribRtrnDate" (date)

Depending on the territory entered, fields "RBM", "TSS", and "Distributor"
auto fill in.

The "LeadSource" combo box is bound to "tblLeadSources" and has several
records including "Phone", "Magazine Ad", "Distrib LR", "RBM LR", "TSS LR"
and many others. When I choose "RBM LR" or "TSS LR", I want "HouseRtrnBy" to
autofill with either the "RBM" or "TSS" and "HouseRtrnDate" to autofill with
the "Date"

Same thing with "Distrib LR", I want "DistribRtrnBy" and "Distrib RtrnDate"
to autofill in.

I'm a novice, please be detailed in your directions. Thanks sooo much!
Click to expand...
Click to expand...
 
Top