Solved

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

Posted on 2013-01-20
13
761 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now