[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

Access - DAO Recordset - Tabbed Form SubForm's SubForm update for Master/Child Link update

Hello All

Just learning anything about the coding of DAO syntax....... (Thanks to MBiz & Irog!!!!)

So pardon if my terms here are incorrect....

I have a tabbed form.  On that tabbed form Each Tab has at least 1 sub form.  Now then on one of the forms, appreciation to Mbiz & Irog, I have a DAO recordset that creates some input fields all correctly!

However, I now am attempting to take that instruction and go to a Tab that has a subform (I'll refer here to as [S1]) and on that [S1] it has it's own Subform [S1-A] which also has a subform [S1-A-1].  

Since each of the (form & their subform's) are linked Master/Child in my coding I am getting an error that says a required field is not completed.  When I open the tables that the DAO Recordset is creating records in they are there and it looks like the info is in fact completed.  

So my question is this (at least I think it is).  How do I get the form & subform to update during the nested creation of the records.....so that the Master/child links are updated....

I am going to show the code because the db is too large to post.
My code is
    Dim lngCtCoin As Long, lngCtCurrency As Long
    Dim iDenomType As Integer, iDenomination As Integer
    Dim lngNbrActiveDenominations As Integer
    Dim lngTempDenomTypeID As Long
    Dim rst As Recordset, rst2 As Recordset
    
    Me.tsfrm_ctldta_MachPullCurrencyTypeCoins.Visible = True
    Me.tsfrm_ctldta_MachPullCurrencyTypeCurrency.Visible = True
    
    Me.MachPullTtl = 0
    Me.Dirty = False
    
    Me.cboActiveDenominations.RowSource = "SELECT [CurrencyDenominationsID], [CurrencyDenominations], [CurrencyDenominationInactive] FROM [sysctl_CurrencyDenominations] WHERE [CurrencyDenominationInactive]=" & 0
    lngCtCoin = DCount("CurrencyDenominationsID", "sysctl_CurrencyDenominations", "CurrencyTypeID= " & 1 & " AND CurrencyDenominationInactive = " & 0)
    lngCtCurrency = DCount("CurrencyDenominationsID", "sysctl_CurrencyDenominations", "CurrencyTypeID= " & 2 & " AND CurrencyDenominationInactive = " & 0)
    
    Set rst = CurrentDb.OpenRecordset("Select * From ctldta_MachPullCurrencyType")
    For iDenomType = 1 To 2
        rst.AddNew
        rst!MachPullID = Me.MachPullID
        rst!CurrencyTypeID = iDenomType
        rst.Update
        lngTempDenomTypeID = rst!MachPullCurrencyTypeID
        
        Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM dta_MachPullDetails")
        For iDenomination = 1 To lngNbrActiveDenominations
            rst2.AddNew
            rst2!CurrencyDenominationsID = iDenomination
            rst2!MachPullCountAmt = 0
            rst2!MachPullCurrencyTypeID = lngTempDenomTypeID
            rst2.Update
        Next
        rst2.Close
        Set rst2 = Nothing
    
    Next
    rst.Close
    Set rst = Nothing
    
    
    Me.tsfrm_ctldta_MachPullCurrencyTypeCoins.Requery
    Me.tsfrm_ctldta_MachPullCurrencyTypeCurrency.Requery
    Forms!tfrm_MachPullReporting!tsfrm_ctldta_MachPullCashCount!tsfrm_ctldta_MachPullCurrencyTypeCoins!tsfrm_dta_MachPullDetails_Coins.Requery
    Forms!tfrm_MachPullReporting!tsfrm_ctldta_MachPullCashCount!tsfrm_ctldta_MachPullCurrencyTypeCurrency!tsfrm_dta_MachPullDetails_Currency.Requery

Open in new window

0
wlwebb
Asked:
wlwebb
  • 7
  • 5
1 Solution
 
Helen FeddemaCommented:
One possibility in such cases is to put DoCmd.RunCommand acCmdSaveRecord statements throughout your code to ensure that each record is saved after a value is set.  Sometimes this works.  The other option is to set the subforms' source objects to "", run your code, then reset the source objects to their previous settings.  This will ensure that all the new values are available for linking.
0
 
wlwebbAuthor Commented:
Helen
Thank you for the input....
I am wondering though... Where do I put that save?  Can it be in the middle of the for statement?
before the rst.close
or before the rst=nothing???????

Not knowing DAO I don't want it to lockup the whole db when I undoubtedly put it in the wrong place.....
0
 
IrogSintaCommented:
You really need to think about renaming the subform controls to something shorter rather than leaving them with their default names.  It would make it much more readable.  Using the Me keyword would help as well when referring to the same form.  Compare the following:

Forms!tfrm_MachPullReporting!tsfrm_ctldta_MachPullCashCount!tsfrm_ctldta_MachPullCurrencyTypeCurrency!tsfrm_dta_MachPullDetails_Currency.Requery
Me!sf_CashCount!sf_TypeCurrency!sf_Currency.Requery

They're both the same, but isn't the latter easier to read?  
As for your error, what line does it fall on?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
wlwebbAuthor Commented:
Irog...

1st...... error is on line 23
lngTempDenomTypeID = rst!MachPullCurrencyTypeID


I know it is a code issue because I can load a new record for this whole tabbed form and manually select all of the fields.... no error......

PS>..........
I know you're right......regarding name issue...... I was SOOOOOO far into it I just left it consistent with how I started..... simply because I can't imagine how much coding it would take to go back thru all the code and change all the names........
0
 
IrogSintaCommented:
What is the exact error message?  Do you have a field called MachPullCurrencyTypeID in the table ctldta_MachPullCurrencyType?
0
 
wlwebbAuthor Commented:
Irog
Copy and paste of table and field name.........
table= ctldta_MachPullCurrencyType
field = MachPullCurrencyTypeID
0
 
IrogSintaCommented:
So what is the exact error message you're getting?
0
 
wlwebbAuthor Commented:
Ok I have figured out exactly what it does...........

The error message I'm getting is when I would go to input info into the Grand Child form.

Essentially
I have a Parent - Child - Grand Child (not the overall access Parent - Just the Parent-child grandchild relative to this particular tab)..........  The forms for the Parent and the Child have all the fields hidden and that is what is being created in the code.........  My input happens on the Grand CHild form....  Since there wasn't anything being populated in the fields I figured my code for prepopulating that form with the Denominations so I tried "selecting" a Denomination in the dropdown box on that form.... That is when I get the message:
"You must enter a value in the 'dta_MachPullDetails.MachPullCurrencyTypeID' field.

SO WHAT I HAVE DONE NOW......
I looked at the 3 table that are effected here.  

1st effected table - ctldta_MachPull (Parent -NOT overall tabbed form parent)
2nd effected table - ctldta_MachPullCurrencyType (Child)
3rd effected table - dta_MachPullDetails (Grandchild)

For the 1st table, the table is in fact getting populated correctly and the form does show the info correctly... it's ok

For the 2nd table, the table is in fact getting populated correctly HOWEVER
1) the Form doesn't visually show that CurrencyTypeID gets updated and the MachPullCurrencyID field still shows {New}  However the field MachPullID gets set to the Parent form's MachPullID.
2) the TABLE actually gets updated with records.... just not showing them on the screen form.


For the 3rd table -  Nothing happens in the table or in the form...
0
 
wlwebbAuthor Commented:
Irog.........
Is my problem that I am opening two recordsets?????????  Are you allowed to do that????????

If so have I coded that right........

I'm asking because I am altering another form where I'll actually open 3 recordsets......
0
 
IrogSintaCommented:
Opening two recordsets is not problem.  But you still haven't posted the exact error message that Access gives.  Can you post a screenshot of it which includes the line that is highlighted?  

By the way, you don't need to separate the values in your Where clauses in lines 13 to15.  Line 14 for example could be written this way:
lngCtCoin = DCount("CurrencyDenominationsID", "sysctl_CurrencyDenominations", "CurrencyTypeID= 1 AND CurrencyDenominationInactive = 0")

Open in new window

0
 
wlwebbAuthor Commented:
Irog....

"That is when I get the message:
"You must enter a value in the 'dta_MachPullDetails.MachPullCurrencyTypeID' field."
0
 
IrogSintaCommented:
Can you post a screen shot showing this message along with the highlighted line?
0
 
wlwebbAuthor Commented:
NOt the answer but guided me in the correct direction......  problem was it wasn't passing the new ID # to the grandchild........  Had to add code......
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now