Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
484 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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