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
476 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
  • 7
  • 5
  • 4
16 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
 
LVL 61

Expert Comment

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

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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

18 Experts available now in Live!

Get 1:1 Help Now