Solved

Access - Form with Subform that has its own Continuous subform - Going to new Record on the Continuous Form

Posted on 2013-01-15
16
483 Views
Last Modified: 2013-01-15
Hello all...

Have a Form that has Master Child relationship to a Subform (Call it Sub1) ..
Main Form is Single Form...  Sub1 is Single Form

That Sub1 has a Subform (call it SubSub2).  It has a Master Child relationship to Subsub2.  Subsub2 is a Continuous Form.  

Now then through code I want to create a specific number of new records on SubSub2.  I am thru code putting a zero in the field CountAmt.

On that Subsub2 form I have an unbound combobox that get's its data from a query. I am using the list of items in that combobox to populate a field in my subsub2 form.

Everything works (no errors)  however when I step thru the code it sits in the field where it puts the info from the unbound combobox and just keeps changing the one record... Doesn't go to create record 2 , 3, 4 etc.....

My code is.........
    
    lngCtCurrency = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ListCount

j=1

With Forms![sfrm_ctldta_MachPull]!sfrm_ctldta_MachPullCurrencyTypeCurrency
    .Form.AllowAdditions = True
    .Form!CurrencyTypeID = 2
    .Form.Dirty = False
    End With
    
    With Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency]
    .Visible = True
    .SetFocus
    .Form.AllowAdditions = True
    .Form![CurrencyDenominationsID].SetFocus
    
    For j = j To lngCtCurrency
        Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form![MachPullCountAmt] = 0
        Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form![CurrencyDenominationsID] = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ItemData(j - 1)
    Next
    End With
    
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].SetFocus
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.AllowAdditions = False
    Me.Form.Recalc

Open in new window


If I step through it the field [CurrencyDenominationsID] changes from one type to the next but no new record.......

I ATTEMPTED ADDING just before the "Next" stmt
DoCmd.RunCommand acCmdRecordsGoToNew

but that adds a whole new record to the Main Form not this subsub2.


?????????????
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
  • 4
16 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38781232
try
    lngCtCurrency = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ListCount

j=1

With Forms![sfrm_ctldta_MachPull]!sfrm_ctldta_MachPullCurrencyTypeCurrency
    .Form.AllowAdditions = True
    .Form!CurrencyTypeID = 2
    .Form.Dirty = False
    End With
    
    With Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency]
    .Visible = True
    .SetFocus
    .Form.AllowAdditions = True
    .Form![CurrencyDenominationsID].SetFocus
    
    For j = j To lngCtCurrency
        .Form![MachPullCountAmt] = 0
        .Form![CurrencyDenominationsID] = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ItemData(j - 1)

'ADD these
        .Form.Dirty = False 
        .Form.DataEntry=True


    Next
    End With
    
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].SetFocus
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.AllowAdditions = False
    Me.Form.Recalc
                   

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38781240
Give this a try:

    dim rs as dao.recordset
    
    lngCtCurrency = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ListCount

j=1

With Forms![sfrm_ctldta_MachPull]!sfrm_ctldta_MachPullCurrencyTypeCurrency
    .Form.AllowAdditions = True
    .Form!CurrencyTypeID = 2
    .Form.Dirty = False
    End With
    
    With Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency]
    .Visible = True
    .SetFocus
    .Form.AllowAdditions = True
    .Form![CurrencyDenominationsID].SetFocus
    
 set rs = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.RecordsetClone
    For j = j To lngCtCurrency
        rs.addnew
        rs![MachPullCountAmt] = 0
        rs![CurrencyDenominationsID] = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ItemData(j - 1)
       rs.update
    Next
    End With
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.requery
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].SetFocus
    Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.AllowAdditions = False
    Me.Form.Recalc

Open in new window

0
 

Author Comment

by:wlwebb
ID: 38781256
Cap1

That works!!! However on my continuous form the ONLY record that shows on subsub2 is the last record.....????????

When I look at the table, the new records are there but why wouldn't they display on a continuous form?????????

When I step through... they disappear on the .form.dataentry=true statement......????
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 61

Expert Comment

by:mbizup
ID: 38781264
Try the  method  in my last post too...
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 38781270
add this before   Me.Form.Recalc
                   
Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.DataEntry=False

Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.Requery


 Me.Form.Recalc
0
 

Author Comment

by:wlwebb
ID: 38781274
Mbiz...
Attempted that

I get an Error 3314
You must enter a value in the
'dta_MachPullDetails.MachPullCurrencyTypeID' field.

when it hits the rs.update line
0
 

Author Comment

by:wlwebb
ID: 38781280
Cap 1.........  it was the Me.Form.Recalc.........
When I remark that out then the fields stay!!!!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38781283
correction, use this lines instead of the  ones in my last post

add this before   Me.Form.Recalc

Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form.DataEntry=False

Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form.Requery
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38781284
ok cool..
the reason why the other records doesn't show is,

the subform DataEntry property is still true..
0
 

Author Comment

by:wlwebb
ID: 38781287
Keeping open ....

waiting on MBiz .....  I have only used DAO once before from someone else's suggestion and didn't understand it at all......

Trying to learn..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38781288
In the code I posted, in the rs!fieldname lines, make sure the field name is that from the table or query behind your subform, as opposed to textbox names.
0
 

Author Comment

by:wlwebb
ID: 38781294
Mbiz........

They are the same........  I looked at each field on the actual form....

The Control Source and the Name are both identical.....
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38781303
Also if MachPullCurrencyTypeID is a Foreign Key linking the subform to a parent record, you might need to do something like this to populate it:


 set rs = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency]![sfrm_dta_MachPullDetails_Currency].Form.RecordsetClone
    For j = j To lngCtCurrency
        rs.addnew
        ' Get the value of MachPullCurrencyTypeID  from the parent form
        rs!MachPullCurrencyTypeID  =  Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form.MachPullCurrencyTypeID  
        rs![MachPullCountAmt] = 0
        rs![CurrencyDenominationsID] = Forms![sfrm_ctldta_MachPull]![sfrm_ctldta_MachPullCurrencyTypeCurrency].Form![sfrm_dta_MachPullDetails_Currency].Form![cboActiveDenominations].ItemData(j - 1)
       rs.update
    Next

Open in new window


Anyhow no worries if you want to close this - bedtime here :-)

Its just an alternate solution which *usually* works smoothly.  I might have a brainstorm about it tomorrow
0
 

Author Comment

by:wlwebb
ID: 38781307
Mbiz......

OK thanks!!!!!  BTW It is a Foreign Key
0
 

Author Closing Comment

by:wlwebb
ID: 38781322
Thanks.........It has been busting my chops for hours!!!!!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38781323
Okay then try that last suggestion, populating  the foreign key from the parent. Yo may need to correct the field names.

If you can get that method working you can streamline your overall code a bit... for starter's, you wouldn't need to toggle the allowadditions property.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

624 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