Repost from 6/18: New record in subform for info on main form

[LeAnne]

Gurus, please help! I’m struggling to accomplish a task that seems to be me
should be fairly straightforward. I won’t say my job hangs in the balance,
but I am under intense pressure to resolve this.

In my database (Candidate Project Evaluations), Project Sites are related
1:m to Projects. I have a form, frmEnterNewProjects, which is the Main data
entry form and displays site info filtered by location. This form contains a
subform, sfrmProjectEval, which is used to enter project information for a
site, including Project Number (a unique alphanumeric ID assigned by the
user). The master/child field is Site_ID. sfrmProjectEval is invisible and
disabled until the user clicks a control button on the Main form to enable (&
make visible) the sfrm. The sfrm also contains a sub-subform,
sfrmProjectSurvey, linked to sfrmProjectEval on Proj_ID. This form is
invisible and disabled until the user enters a Project Number and clicks a
ctrl on the sfrm; this runs an append query which “loads†survey questions
for the project and makes the sub-subform visible (& enabled). The intention
is for the user to complete the entire data entry process for a project
(enter site info/click ctrl to save site data and show sfrm/enter Project
Number for this site/click ctrl to load questions & show sub-subform/take
survey) before moving on. At the bottom of the MAIN form, there is a series
of buttons to enable the user to either enter a new site for the (filtered)
location (new record in MAIN form), enter a new project for the same site at
the (filtered) location (new record in SUBFORM), or exit the data entry form.


Everything seems to be working EXCEPT for the “Enter a New Project for this
Site†ctrl button. I’ve tried Me!sfrmProjectEval.Requery; I’ve tried

DoCmd.GoToControl "sfrmProjectEval"
DoCmd.GoToRecord , , acNewRec

…plus numerous other permutations, to no avail. sfrmProjectEval is based on
an updateable query, and the AllowAdditions property is set to Yes. I have
hidden the navigation buttons on sfrmProjectEval to “force†the user to
follow the complete data entry process. However, if I unhide them, they are
not greyed out, so I should be able to enter a new Project in the subform. I
just can’t seem to find the right procedures. ANY assistance at all would be
GREATLY appreciated!!

Tia,

LeAnne Astin
Ecologist
Fairfax County Stormwater Planning Div.

 

[BruceM]

Just to clarify, some table names:

tblProjectSites (Project Sites table; source for frmEnterNewProjects)
Site_ID (Primary key, or PK)
Location
etc.

tblProjEval (Project Information table; source for sfrmProjectEval)
Proj_ID (PK)
Site_ID (foreign key, or FK)
ProjectNumber
TextField
etc.

tblProjSurvey (Survey table; source for sfrmProjectSurvey)
Survey_ID (PK)
Proj_ID (FK)
TextField
etc.

Please verify this is correct, or amend it if not. I am confused that you
say you are trying to enter a new site for the filtered location, yet Site
seems to be at the top of the hierarchy. Is there another table somewhere?
Or are you trying to add a new record to tblProjEval.

Please, when describing the database, start with the tables. When
mentioning a form, state its Record Source. Break the description into
paragraphs or sections, as a single paragraph is difficult to sift through.

Try unhiding the forms/subforms for now, and comment out any code that is
for user convenience only. In other words, reduce it to the essentials
needed for data entry.

When you unhide the built-in navigation buttons, can you add a new record?
If not, what happens when you try? In what way does it not let you enter a
new record with your current set-up? Does it just sit there smirking at
you, or is there an error message, or what?

 

[LeAnne]

OMG, I think I've found the answer! This approach (found at
http://www.pcreview.co.uk/forums/thread-1147729.php) seems to work:

Me.sfrmProjectEval.Form.AllowAdditions = True 'just in case
Me.sfrmProjectEval.Form.Recordset.AddNew

Woohoo!

LeAnne Astin
Ecologist
Fairfax County Stormwater Planning Div.