Automatically create records in subform and nested subform (1 Viewer)

DataBass · Jun 13, 2018

I have a main form called frmItemDetail. Inside frmItemDetail, I have a subform called frmActivity. Nested within frmActivity is a subform called frmCategory. frmActivity is linked to the frmItemDetail by ItemID and frmCategory is linked to both its parents with ItemID and StageID and things work as they should.

Here is what I would like to do:

When a user selects a 'type' from the combo box called Type, I would like to automatically generate records in both the subform and the nested subform, as shown in the attached picture.

For example, if the user selects "Test" from the Type combo box, I would like to generate 8 records in the Activity subform with only the 'Stage' field filled out.

There would be 8 records as follows:
1. Stage 1
2. Stage 2
3. Stage 3
4. Stage 4
5. Stage 5
6. Stage 6
7. Stage 7
8. Stage 8.

Here is the really difficult part: For each of these records, I would like to generate 1 or more records in the nested datasheet with these exact strings in the Category field
1. Stage 1
Plan

2. Stage 2
Draw

3. Stage 3
Consult
Review

4. Stage 4
Edit
Estimate

5. Stage 5
Model
Render
Review

6. Stage 6
Consult

7. Stage 7
Final

8. Stage 8.
Show

I found a way to crudely add the records in the frmActivity subform by setting the focus on the frmActivity subform, setting the value of the 'Stage' field and then using DoCmd.GoToRecord acNewRec. It isn't pretty but it worked. I'm sure it isn't the best way to do it. However, I am at a complete loss as to how to create the records in the nested subform, frmCategory and link them to the parent records. Should this be done within the form? Should it be done behind the scenes with something like 'AddNew? I have no idea.

Any help with this would be greatly appreciated. Seriously. I'm stuck

JHB · Jun 13, 2018

I would use an append query for both.

Uncle Gizmo · Jun 13, 2018

DataBass said:
I'm sure it isn't the best way to do it. However, I am at a complete loss as to how to create the records in the nested subform, frmCategory and link them to the parent records.

The best way to solve this sort of problem is to realise that your forms are for viewing/editing/adding the data. The data itself is actually stored in tables.

Instead of attacking the problem from the form angle you attack the problem from the table angle. In other words you need to add records to the tables.

To add the records, first you need somewhere to store the records you want to add, and the best place is another table!

I explain something similar with regard to creating a checklist in my blog here:-

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

There's text, YouTube videos and a google presentation.

Sent from my SM-G925F using Tapatalk

DataBass · Jun 13, 2018

JHB, Thanks for the suggestion. I looked into Append/Insert Queries and gave it a shot. Believe it or not, it actually worked! Amazing!

I used an SQL Insert query for each new record in the After Update event of the cboType combo box.

Uncle Gizmo, since I was frantically typing code when you made your post, I didn't see it in time. I will check out your blog as soon as I'm done here.

Would anyone mind looking at my code and telling me if there is a better way to do this? Or if there is a way I could better re-write what I have and/or make it more compact, or more DRY?

Thanks again for the suggestions!

Code:

Private Sub cboType_AfterUpdate()

Dim ItemID As Long

Dim Stage1ID As Long
Dim Stage2ID As Long
Dim Stage3ID As Long
Dim Stage4ID As Long
Dim Stage5ID As Long
Dim Stage6ID As Long
Dim Stage7ID As Long
Dim Stage8ID As Long

ItemID = Me.ItemID

If Me.cboType = "Test" Then

    'Insert 8 new records into tbl Activity with correct stages 

    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '1. Stage 1')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '2. Stage 2')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '3. Stage 3')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '4. Stage 4')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '5. Stage 5')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '6. Stage 6')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '7. Stage 7')"
    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '8. Stage 8')"

    Me.frmActivity.Requery

    'Lookup primary keys of records just added above and assign them to variables

    Stage1ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '1. Stage 1'")
    Stage2ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '2. Stage 2'")
    Stage3ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '3. Stage 3'")
    Stage4ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '4. Stage 4'")
    Stage5ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '5. Stage 5'")
    Stage6ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '6. Stage 6'")
    Stage7ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '7. Stage 7'")
    Stage8ID = DLookup("StageID", "tblActivity", "ItemID=" & [Forms]![frmItemDetail]![ItemID] & "AND Stage = '8. Stage 8'")

    'Insert 12 new records into tblCategory as sub-records to the records added above using variables
    
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage1ID & "', '1. Stage 1', 'Plan')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage2ID & "', '2. Stage 2', 'Draw')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage3ID & "', '3. Stage 3', 'Consult')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage3ID & "', '3. Stage 3', 'Review')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage4ID & "', '4. Stage 4', 'Edit')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage4ID & "', '4. Stage 4', 'Estimate')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage5ID & "', '5. Stage 5', 'Model')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage5ID & "', '5. Stage 5', 'Render')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage5ID & "', '5. Stage 5', 'Review')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage6ID & "', '6. Stage 6', 'Consult')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage7ID & "', '7. Stage 7', 'Final')"
    DoCmd.RunSQL "INSERT INTO tblCategory(ItemID, StageID, Stage, Category)" & "VALUES (ItemID, '" & Stage8ID & "', '8. Stage 8', 'Show')"
        
    Me.frmCategory.Requery
  
End If

End Sub

Galaxiom · Jun 13, 2018

Uncle Gizmo said:
The best way to solve this sort of problem is to realise that your forms are for viewing/editing/adding the data.

Forms are for adding records too and a form is how this should be done.

Instead of attacking the problem from the form angle you attack the problem from the table angle. In other words you need to add records to the tables.

Not really. Proforma virtual records that don't actually exist in a table can be made to appear in a form by putting them in the form's recordset using an outer join query. They only become real records after entering values into other bound controls and allowing the record to save.

See the sample in Post 3 of this thread.

The crux of the technique is the subform RecordSource query. No code is required. (The code in the example is just to write another value chosen in the main form as the record is saved.)

No insert queries required. And importantly, no records saved unless they are actually used. This allows the subform to present any number of potential records without cluttering up the table with essentially empty records. It is ideal where a range of optional attributes are to be recorded in a subform.

The next part is not in the sample database and I have not tried it.

In the case of the current thread, the records in the subform may be able to be filtered using the subformcontrol LinkFields with the LinkMasterField being the main form's unbound combo used to select what DataBass has called the Type. (BTW Type is reserved so don't use it as an object name. Let's call it RecordType)

The LinkChildField would be a field in the subform SourceObject's recordset. It need not be displayed on the form. In the sample, RecordType would be a field included in the query from the Student table. In that case it would be used to select the appropriate Student records (probably the Stage records in the current thread).

If this doesn't work, then use a RecordSource query for the subform with a Where clause based on the RecordType selection combo on the main form and Requery the subform after the selection. I'm sure this would work.

This structure should be able to be extended to a second level subform or further.

This technique is remarkably simple and powerful but I have never seen it suggested by anyone else let alone documented. Everyone seems to always suggest the brute force Insert route for these situations.

I have suggested it on a few other threads but I am not sure that anyone understood it well enough to implement in their situation. It is easier than it looks and would be an excellent subject for one of Tony's tutorial videos.

Tony, I'm sure you will be able to understand this technique but let me know if I need to clarify anything.

Uncle Gizmo · Jun 13, 2018

Galaxiom said:
Proforma virtual records that don't actually exist in a table can be made to appear in a form by putting them in the form's recordset using an outer join query. They only become real records after entering values into other bound controls and allowing the record to save.

I like the sound of that Greg, thanks for mentioning it.

Galaxiom said:
It would be an excellent subject for one of Tony's tutorial videos.

Yes, it would! It would follow on nicely from the checklist tutorial.

Sent from my SM-G925F using Tapatalk

pbaldy · Jun 13, 2018

Post 4 was moderated, now approved. I'm posting to trigger email notifications.

DataBass · Jun 13, 2018

Galaxiom,

Thanks for the reply. I read your post, I read the thread that you referred to and I looked at the sample grades.mdb.

I'm not seeing how these approaches would allow me to automatically generate the records I need. It appears that the user still needs to manually enter the data on the form. This is what I am trying to avoid.

The code I posted, while it may not be pretty, did exactly what I need. Is there really a way to add these 20 records automatically without code and insert queries?

Could you please enlighten me if I am missing something here?

Mark_ · Jun 13, 2018

@ OP,
Taking ONE step back, what happens if they change the value in your combo box?

For myself, I would NEVER add the child records UNTIL the parent has been saved and the user KNOWS it.

For program flow, I'd have them create the parent, hit the "SAVE" button (which saves the record and creates children) THEN get moved to an update form that has the child records on it and NO combo box.

This will avoid the headache you have with orphan/excess records if the user decides to up and change your combo box on you.

Uncle Gizmo · Jun 13, 2018

Mark_ said:
I would NEVER add the child records UNTIL the parent has been saved

Galaxiom's solution very neatly avoids the usual child record problems...

Sent from my SM-G925F using Tapatalk

DataBass · Jun 13, 2018

Mark_ said:
@ OP,
Taking ONE step back, what happens if they change the value in your combo box?

For myself, I would NEVER add the child records UNTIL the parent has been saved and the user KNOWS it.

Mark_, Good question and good point. The code that adds the records will only execute if there are zero records in the subforms. If they try to change to value of the combo box and records exist in the subforms, they get a MsgBox explaining that they can't change it unless they manually remove the records from the subforms. Also, I have added a confirmation MsgBox before the records are added so the user can be sure he selected the correct value in the combo box. It may not be ideal but everyone involved is ok with this.

I'm still at a loss for how I might apply Galaxiom's approach.

DataBass · Jun 13, 2018

Even if my code above is not the ideal method, it does work.

Just for my own understanding, would anyone be willing to give me some ideas for how I could compact/improve what I already have? It seems that there is so much repetition in there, but I'm not sure how best to avoid that.

Thanks

JHB · Jun 13, 2018

Open the form in the attached database, put in an ItemId then click the button.
The result is going into tables tblActivity and tblCategory.

Uncle Gizmo · Jun 14, 2018

DataBass said:
Even if my code above is not the ideal method, it does work.

Just for my own understanding, would anyone be willing to give me some ideas for how I could compact/improve what I already have? It seems that there is so much repetition in there, but I'm not sure how best to avoid that.

Thanks

Well first off you're doing the right thing you've got it working!

Secondly you've recognised that there's probably a better way of doing it.

In my experience there's usually three ways of doing the same thing.

It's very important that you've got it working in the way you have because by doing it in what, for want of a better term is the brute force way you can immediately see that there's a pattern.

Code:

DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '1. Stage 1')"
DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '2. Stage 2')"
DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, '3. Stage 3')"
etc.............

Once you see a pattern, then generally there's a way of iterating through a pattern to get the same results without having to copy and paste many lines of code.

I don't know if you've messed around with for next Loops yet, if not, it might be an idea to look them up and run through some examples.

Basically the for next loop allows you to specify a start number and an end number and then iterate through a block of code.

So in your particular case you could change the code to something like this:-

Code:

Private Sub CreateRecords_Click()
Dim X As Integer


For X = 1 To 8

    DoCmd.RunSQL "INSERT INTO tblActivity(ItemID, Stage)" & "VALUES (ItemID, 'X. Stage X')"

Next X


End Sub

Now, that's not going to work with out a bit of fiddling around, it's a quick pseudo example often referred to as AIR code just to point you in the right direction.

Stepping it up a bit you could achieve the same results, (remember I said this usually three ways of doing the same thing) by using a recordset loop.

A record set loop is somewhat similar to a for next loop, but instead of moving from 1 to 8 you move through a set of Records (let's say eight records...) the records are stored in a table a "set of Records" or a "record set" hence the term record set loop. So imagine now you had a table with the single column with just the numbers 1 to 8 in it your record set loop would go through the list from 1 to 8. Now, you might well be thinking why put them in a table when you can just use a for next loop? The first thing is the user can add things to that table for instance they can add a number nine if required. Secondly you could have two columns three columns four columns, each of them providing more information for your insert into SQL statement.

DataBass · Jun 14, 2018

Wow.

JHB, That's really something. Sooo much simpler!

It never occurred to me to make separate tables and append the records this way.

Great idea.

I am trying to apply this to my project, but I'm having difficulty with one particular issue.

ItemID is the top level ID for the main form. In your example, we generate this ID manually, not a problem.

In tblActivity, each record needs to have an ItemID AND a StageID. I have added StageID as an autonumber PK.

So far so good, everything still works

Here is the difficulty: Each record in tblCategory needs to have the ItemID, StageID and it's own CategoryID so that each Category record will be tied to a specific StageID. I have added these fields and made CategoryID an autonumber PK.

I have also set up a one-to-many relationship between tblActivity and tblCategory, using StageID.

Using append queries, how would I link the Category to the correct StageID? Should I use Dlookup to get the StageID?

Thanks again for your response.

DataBass · Jun 14, 2018

Uncle Gizmo,

Thanks for taking the time to spell that out. I have not used For Next loops in VBA, but I have used For Each loops in javascript and they appear to be the same thing essentially. Great idea.

I have very limited experience with recordsets. I'm playing with the idea a bit now for setting bookmarks.

Yes, I knew there has to be a better way to make my code work but I just didn't know where to start. Loops and recordsets, will give me plenty to experiment with.

Thanks for your input

Mark_ · Jun 14, 2018

If categories belong to stages, then looking up the category will get you the stage ID. You don't need to save it.

JHB · Jun 15, 2018

Do the attached database now fulfilled you requirement?
Else explain where it is wrong, (printscreen)!

DataBass · Jun 15, 2018

JHB,

Amazing. That did it. I'm going to dig into your sample and figure out how you did that.

This is great.

THANK YOU!!

Where should I send the beer?

JHB · Jun 16, 2018

DataBass said:
Amazing. That did it. I'm going to dig into your sample and figure out how you did that.
..THANK YOU!!

Where should I send the beer?

The StageID is coming from the tblActivity, (so no magic at all)!

You're welcome!

Send it to Denmark, (prefer a cold Tuborg from the bottle).

Automatically create records in subform and nested subform (1 Viewer)

Registered User.

Attachments

Have been here a while

Nifty Access Guy

Registered User.

Super Moderator

Nifty Access Guy

Wino Moderator

Registered User.

Longboard on the internet

Nifty Access Guy

Registered User.

Registered User.

Have been here a while

Attachments

Nifty Access Guy

Registered User.

Registered User.

Longboard on the internet

Have been here a while

Attachments

Registered User.

Have been here a while

Similar threads

Users who are viewing this thread