AfterUpdate event procedure

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

I have an AfterUpdate procedure that populates values from a subform into
fields on my main data entry form. The forms/subforms were created using
Access 2000 and worked correctly in Access 2003. When I upgraded to Access
2007, the code no longer works....unless I enter the STUDYID value twice.
Any ideas as to why this is occurring? And how do I fix it?

Private Sub STUDYID_AfterUpdate()
[LNAME] = [frmsubIsol07].Form![Lname2]
[MI] = [frmsubIsol07].Form![MI2]
[FNAME] = [frmsubIsol07].Form![Fname2]
[COUNTY] = [frmsubIsol07].Form![County2]
[HOSPID] = [frmsubIsol07].Form![Hospid2]
[HOSPID1] = [frmsubIsol07].Form![Txhosp2]
[Accessno] = [frmsubIsol07].Form![Accessno2]
End Sub
 
I may not be understanding your situation well enough ...

The standard use of main form/subform construction is to allow for the one
(main)-to-many (sub) relationship.

Thus, there would be no need to "populate values from a subform (i.e., a
"many" record) into fields on main ... form (the "one" record)".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,

The situation is as follows: We have two data collection forms that have a
few common fields: fname, lname, mi, state, county, etc. Ideally, we would
have structured the tables with a one to many relationship, but the end-user
requested a flat-file view with all data elements contained in a single
record. To prevent duplicating entry of common fields across tables, we
created a subform to serve as a placeholder for values of the variables
listed above. Upon entry of the studyid in form2, if this value is found in
form1, the lname, fname, etc. is captured in the subform. The afterupdate
procedure grabs the data from the subform and populates the related fields in
form2, which worked fine in previous versions of Access.

Jeff Boyce said:
I may not be understanding your situation well enough ...

The standard use of main form/subform construction is to allow for the one
(main)-to-many (sub) relationship.

Thus, there would be no need to "populate values from a subform (i.e., a
"many" record) into fields on main ... form (the "one" record)".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CJ said:
I have an AfterUpdate procedure that populates values from a subform into
fields on my main data entry form. The forms/subforms were created using
Access 2000 and worked correctly in Access 2003. When I upgraded to
Access
2007, the code no longer works....unless I enter the STUDYID value twice.
Any ideas as to why this is occurring? And how do I fix it?

Private Sub STUDYID_AfterUpdate()
[LNAME] = [frmsubIsol07].Form![Lname2]
[MI] = [frmsubIsol07].Form![MI2]
[FNAME] = [frmsubIsol07].Form![Fname2]
[COUNTY] = [frmsubIsol07].Form![County2]
[HOSPID] = [frmsubIsol07].Form![Hospid2]
[HOSPID1] = [frmsubIsol07].Form![Txhosp2]
[Accessno] = [frmsubIsol07].Form![Accessno2]
End Sub
Click to expand...


.
Click to expand...
 
See comments in-line below ...


CJ said:
Jeff,

The situation is as follows: We have two data collection forms that have
a
few common fields: fname, lname, mi, state, county, etc. Ideally, we
would
have structured the tables with a one to many relationship, but the
end-user
requested a flat-file view with all data elements contained in a single
record.
Click to expand...

If you were limited to displaying the data only in the tables, you might as
well be using Excel! It sounds like you understand that you need to use
forms to display/add/edit data, in part, to keep the users from muckin'
about in the tables!

Just because the user wants to SEE a flat-file view, you are not required to
store the data in Access that way. Access queries work quite nicely to
"flatten" a view for the users.
To prevent duplicating entry of common fields across tables, we
created a subform to serve as a placeholder for values of the variables
listed above. Upon entry of the studyid in form2, if this value is found
in
form1, the lname, fname, etc. is captured in the subform. The afterupdate
procedure grabs the data from the subform and populates the related fields
in
form2, which worked fine in previous versions of Access.
Click to expand...

You've worked out this fairly complicated process as a work-around, to deal
with the "flat-file view" requirement.

If you go back to basics and normalize your data structure, you can use
queries and forms to display it any way you need to, without all the
gyrations...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Jeff Boyce said:
I may not be understanding your situation well enough ...

The standard use of main form/subform construction is to allow for the
one
(main)-to-many (sub) relationship.

Thus, there would be no need to "populate values from a subform (i.e., a
"many" record) into fields on main ... form (the "one" record)".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CJ said:
I have an AfterUpdate procedure that populates values from a subform
into
fields on my main data entry form. The forms/subforms were created
using
Access 2000 and worked correctly in Access 2003. When I upgraded to
Access
2007, the code no longer works....unless I enter the STUDYID value
twice.
Any ideas as to why this is occurring? And how do I fix it?

Private Sub STUDYID_AfterUpdate()
[LNAME] = [frmsubIsol07].Form![Lname2]
[MI] = [frmsubIsol07].Form![MI2]
[FNAME] = [frmsubIsol07].Form![Fname2]
[COUNTY] = [frmsubIsol07].Form![County2]
[HOSPID] = [frmsubIsol07].Form![Hospid2]
[HOSPID1] = [frmsubIsol07].Form![Txhosp2]
[Accessno] = [frmsubIsol07].Form![Accessno2]
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks Jeff. I will need quite a bit of luck!

However, I don't think the design is the issue as the same problem occurs in
another of my Access databases with the afterupdate procedure. This database
contains a single table with no relationships. The event is called by data
elements in an option group. A Select Case statement is used to populate
assigned values into fields based on certain conditions. It's all done in
VB; no subforms are used. The code worked fine in Access 2003, but no longer
works in Access 2007...unless the user enters the value, sets focus to the
next data entry field, returns focus to the previous field, and reenters the
value.

Thanks again...CJ

Jeff Boyce said:
See comments in-line below ...


CJ said:
Jeff,

The situation is as follows: We have two data collection forms that have
a
few common fields: fname, lname, mi, state, county, etc. Ideally, we
would
have structured the tables with a one to many relationship, but the
end-user
requested a flat-file view with all data elements contained in a single
record.
Click to expand...

If you were limited to displaying the data only in the tables, you might as
well be using Excel! It sounds like you understand that you need to use
forms to display/add/edit data, in part, to keep the users from muckin'
about in the tables!

Just because the user wants to SEE a flat-file view, you are not required to
store the data in Access that way. Access queries work quite nicely to
"flatten" a view for the users.
To prevent duplicating entry of common fields across tables, we
created a subform to serve as a placeholder for values of the variables
listed above. Upon entry of the studyid in form2, if this value is found
in
form1, the lname, fname, etc. is captured in the subform. The afterupdate
procedure grabs the data from the subform and populates the related fields
in
form2, which worked fine in previous versions of Access.
Click to expand...

You've worked out this fairly complicated process as a work-around, to deal
with the "flat-file view" requirement.

If you go back to basics and normalize your data structure, you can use
queries and forms to display it any way you need to, without all the
gyrations...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Jeff Boyce said:
I may not be understanding your situation well enough ...

The standard use of main form/subform construction is to allow for the
one
(main)-to-many (sub) relationship.

Thus, there would be no need to "populate values from a subform (i.e., a
"many" record) into fields on main ... form (the "one" record)".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an AfterUpdate procedure that populates values from a subform
into
fields on my main data entry form. The forms/subforms were created
using
Access 2000 and worked correctly in Access 2003. When I upgraded to
Access
2007, the code no longer works....unless I enter the STUDYID value
twice.
Any ideas as to why this is occurring? And how do I fix it?

Private Sub STUDYID_AfterUpdate()
[LNAME] = [frmsubIsol07].Form![Lname2]
[MI] = [frmsubIsol07].Form![MI2]
[FNAME] = [frmsubIsol07].Form![Fname2]
[COUNTY] = [frmsubIsol07].Form![County2]
[HOSPID] = [frmsubIsol07].Form![Hospid2]
[HOSPID1] = [frmsubIsol07].Form![Txhosp2]
[Accessno] = [frmsubIsol07].Form![Accessno2]
End Sub


.
Click to expand...
Click to expand...


.
Click to expand...
 
I've not run into the situation you describe ... perhaps another newsgroup
reader can offer assistance.

My comment about the table structure was intended to point out that Access'
relationally-oriented features and functions are optimized to work with
well-normalized data.

If your data is not, you and Access will have to work overtime to overcome
the non-relational data structure.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CJ said:
Thanks Jeff. I will need quite a bit of luck!

However, I don't think the design is the issue as the same problem occurs
in
another of my Access databases with the afterupdate procedure. This
database
contains a single table with no relationships. The event is called by
data
elements in an option group. A Select Case statement is used to populate
assigned values into fields based on certain conditions. It's all done in
VB; no subforms are used. The code worked fine in Access 2003, but no
longer
works in Access 2007...unless the user enters the value, sets focus to the
next data entry field, returns focus to the previous field, and reenters
the
value.

Thanks again...CJ

Jeff Boyce said:
See comments in-line below ...


CJ said:
Jeff,

The situation is as follows: We have two data collection forms that
have
a
few common fields: fname, lname, mi, state, county, etc. Ideally, we
would
have structured the tables with a one to many relationship, but the
end-user
requested a flat-file view with all data elements contained in a single
record.
Click to expand...

If you were limited to displaying the data only in the tables, you might
as
well be using Excel! It sounds like you understand that you need to use
forms to display/add/edit data, in part, to keep the users from muckin'
about in the tables!

Just because the user wants to SEE a flat-file view, you are not required
to
store the data in Access that way. Access queries work quite nicely to
"flatten" a view for the users.
To prevent duplicating entry of common fields across tables, we
created a subform to serve as a placeholder for values of the variables
listed above. Upon entry of the studyid in form2, if this value is
found
in
form1, the lname, fname, etc. is captured in the subform. The
afterupdate
procedure grabs the data from the subform and populates the related
fields
in
form2, which worked fine in previous versions of Access.
Click to expand...

You've worked out this fairly complicated process as a work-around, to
deal
with the "flat-file view" requirement.

If you go back to basics and normalize your data structure, you can use
queries and forms to display it any way you need to, without all the
gyrations...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


:

I may not be understanding your situation well enough ...

The standard use of main form/subform construction is to allow for the
one
(main)-to-many (sub) relationship.

Thus, there would be no need to "populate values from a subform (i.e.,
a
"many" record) into fields on main ... form (the "one" record)".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an AfterUpdate procedure that populates values from a subform
into
fields on my main data entry form. The forms/subforms were created
using
Access 2000 and worked correctly in Access 2003. When I upgraded to
Access
2007, the code no longer works....unless I enter the STUDYID value
twice.
Any ideas as to why this is occurring? And how do I fix it?

Private Sub STUDYID_AfterUpdate()
[LNAME] = [frmsubIsol07].Form![Lname2]
[MI] = [frmsubIsol07].Form![MI2]
[FNAME] = [frmsubIsol07].Form![Fname2]
[COUNTY] = [frmsubIsol07].Form![County2]
[HOSPID] = [frmsubIsol07].Form![Hospid2]
[HOSPID1] = [frmsubIsol07].Form![Txhosp2]
[Accessno] = [frmsubIsol07].Form![Accessno2]
End Sub


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top