[Webinar] Streamline your web hosting managementRegister Today

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
?
489 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

611 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