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" <(E-Mail Removed)> wrote in message
news:6FF66501-93F0-4399-A085-(E-Mail Removed)...
> 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" wrote:
>
>> See comments in-line below ...
>>
>>
>> "CJ" <(E-Mail Removed)> wrote in message
>> news:98C182A9-9487-418B-8124-(E-Mail Removed)...
>> > 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.
>>
>> 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.
>>
>> 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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:0002F9C6-D95B-4A75-AEED-(E-Mail Removed)...
>> >> >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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>
|
Similar Threads
