Append New Records in Subform

[ridgerunner]

Is it possible to append new records in a subform, as it opens, to an
underlying table using only certain fields from an existing table?

 

[strive4peace]

hi ridgerunner (what is your name?)

yes, of course <smile>

can you describe a bit more about why you want to do this and where the
data would come from?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[ridgerunner]

I would rather not put my name in a public place if that is OK.

The form would open and autopopulate two fields, same data every time, and
then the data entry person would add the data required for the other fields
in the underlying table. This would speed things up tremendously.
The data would come from two fields in a table located in the same database.

 

[ridgerunner]

There will be 34 records created everytime the form is used. One of the
fields will contain one of five categories and the other field will contain
one of 34 questions. Since the fields reside in a master reference table in
the database I thought it would be easier to append the fields from that
table into new records underlying the subform. From there it would be a
simple matter to add data to the remaining fields.

 

[strive4peace]

Hi ridgerunner

yes, it is fine ... but I like to ask anyway so hope you don't mind

If the data is the same every time, how about using the DefaultValue
property of the respective controls? Also, if the user will not
normally change it, then make the TabStop property = No. the user can
always click in to make a change

If the data changes depending on conditions, will those conditions be
the same for that edit session (ie: are they dependent on date?) or will
they change for different records?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[ridgerunner]

I created an append query that will work when run from only the subform. I
do not know how to get this to work when the subform is embedded in the main
form. I tried a button to run the query on the main form but nothing
happened. I cannot see the button if I place it on the subform, since the
subform must be viewed in data sheet view. If I need to call the query from
an event I do not know how to do that and would really appreciate help. The
main form creates a record with an ID that is copied into the subform for
linking purposes.

 

[strive4peace]

creating question records for a survey
~~~

Hi ridgerunner,

thanks for the additional information

since you did not specify fieldnames, I will use what I consider good,
generic names and you will have to change them

I am assuming your database includes tables with a structure similar to
the following:

Participants
- ParticID, autonumber, PK
- Lastname, text
- firstname, text

Questions
- QuestionID, autonumber, PK
- Question, text

Surveys
- SurveyID, autonumber, PK
- ParticID, long, FK to Participants
- SurvDate, date/time

SurveyAnswers
- SurvAnsID, autonumber
- SurveyID, long, fk to Surveys
- QuestionID, long, FK to Questions
- Answer

PK is Primary Key
FK is Foreign Key

this is a simplified example. It does not take into account that you
may have multiple types of surveys with different sets of questions

In SurveyAnswers, make a multi-field unique index on the combination of
SurveyID
QuestionID

this will protect you in case questions are created twice so you do not
get duplicates.

(multi-field unique indexes are covered in Access Basics, link in my siggy)

I am assuming that you have a main form/subform situation where the main
form is based on Surveys and the subform based on SurveyAnswers, which
is the table you wish to automatically fill records in

create a command button on the main form to create the questions

'~~~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

'if we are on a new record, give user a message
if me.newrecord then
msgbox "You are not on a current record" _
,, "Cannot create questions"
exit sub
end if

if isnull(me.ParticID) then
msgbox "You must fill out who you are" _
,, "Cannot create questions"
me.ParticID.setFocus
exit sub
end if

dim strSQL as string

strSQL = "INSERT INTO SurveyAnswers (SurveyID, QuestionID) " _
& " SELECT " & me.surveyID _
& ", QuestionID " _
& " FROM Questions;"

'remove this line once everything works ok
debug.print strSQL

currentdb.execute strSQL

'make the new records show up on the subform
me.subform_controlname.requery
'~~~~~~~~~~~~~~~~~

substitute the Name property of your subform control for subform_controlname

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~~~~~~~~~~~~~~
** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[ridgerunner]

Thanks, but this isn't a survey; it is an inspection form that is completed
exactly the same way every time an inspection is completed. Does the "INSERT
INTO" add only one record at a time?

 

[strive4peace]

Hi ridgerunner,

"this isn't a survey"

the analogy is the same. I made guesses since you did not specify much.

"Does the "INSERT INTO" add only one record at a time?"

no, it will add all the records from the questions table since there is
no criteria to limit it. I was assuming you would want to fill
everything out...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[ridgerunner]

Yes, I do want to fill everything out. Thanks. Will this work if I have
referential integrity "on" for the relationship between the master and child
links?

 

[strive4peace]

Hi ridgerunner,

having RI relationships will make things faster and, yes, creating
related record in code will work based on the fact that you are saving
the parent record before you create the related records.

Unless you have a valid reason NOT to enforce RI (like it will create >
32 indexes, which is the table limit, or you are linking and can't, or
you are importing data and creating parent records from children), IMO,
it is always a good idea to do so.

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[ridgerunner]

Thanks for the advice on compile. I have had a few problems to work through
and I thought I had everything fixed, but now when I click the button, the
master record is saved but none of the questions are displayed or added to
the table. Below is the code I placed into the command button 'on click'
event. Your help is greatly appreciated.

Private Sub AddQsts_Click()

Dim strSQL As String

strSQL = "INSERT INTO tblDMInspecDet (InspID, DMCatID, QstID) " _
& " SELECT " & Me.InspID _
& ", DMCatID " _
& ", QstID " _
& " FROM tblQuestions;"

'remove this line once everything works ok
Debug.Print strSQL

CurrentDb.Execute strSQL

'make the new records show up on the subform
Me.TestsubfrmDMInspDet.Requery

End Sub

 

[ridgerunner]

IT WORKS after adding the
if me.dirty then me.dirty = false

Since I do not know much about programming, it is often difficult for me to
understand how all this works. I apologize for not doing this before.

This is such a huge accomplishment for this to work; I can't thank you enough.

 

[strive4peace]

you're welcome, ridgerunner

while it is fresh in your mind, add comments to the code. Start your
comment with a single quote

' this is a comment

read the Access Basics document referenced in my siggy


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*