Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
486 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

824 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