?
Solved

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

Posted on 2013-01-20
13
Medium Priority
?
777 Views
Last Modified: 2013-01-21
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
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 38798763
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
 

Author Comment

by:wlwebb
ID: 38799029
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38799047
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:wlwebb
ID: 38799130
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38799219
What is the exact error message?  Do you have a field called MachPullCurrencyTypeID in the table ctldta_MachPullCurrencyType?
0
 

Author Comment

by:wlwebb
ID: 38799874
Irog
Copy and paste of table and field name.........
table= ctldta_MachPullCurrencyType
field = MachPullCurrencyTypeID
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38799880
So what is the exact error message you're getting?
0
 

Author Comment

by:wlwebb
ID: 38799911
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
 

Author Comment

by:wlwebb
ID: 38799958
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38801170
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
 

Author Comment

by:wlwebb
ID: 38801934
Irog....

"That is when I get the message:
"You must enter a value in the 'dta_MachPullDetails.MachPullCurrencyTypeID' field."
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38802174
Can you post a screen shot showing this message along with the highlighted line?
0
 

Author Closing Comment

by:wlwebb
ID: 38802199
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question