Solved

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

Posted on 2013-01-20
13
766 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL profiler equivalent in MS-Access 3 42
Is it possible to reset DSum? 12 41
MS-Access 2002 error (Win XP on Win7Pro) 19 40
Access 2010 Query Syntax 5 21
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 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

20 Experts available now in Live!

Get 1:1 Help Now