UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 1st Usage Works, Subsequent Usage, - 'no Current Record' Error, Access 2010    
 
   
Yana
post Aug 4 2017, 11:43 AM
Post#1



Posts: 33
Joined: 14-February 14



Haven't worked in Access in 3 years, and have forgotten much of what I had previously learned. Don't know whether to post this in 'Forms', 'Errors', or 'Records'. Since I'm trying to manipulate and add records, I will start here. The origins of the code below was taken from a response to a post on this site (8/6/2010, Gustav, http://www.UtterAccess.com/forum.index.php...wtopic=1950102). I modified the code for my purposes. The code works as desired the first time I run it. Any subsequent attempts to run the code, I receive a 'Run-time error 3021: No Current Record." If I close the form and re-open, the code runs the first time but not subsequent attempts. The code is posted below.

valRounds = Me.txtRounds

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblWorkoutSessionPlanningData-000-Entry")
Set frmRS = Forms!Form1.RecordsetClone

For valRoundsCounter = 1 To valRounds
With rs
.AddNew
For Each fld In rs.Fields
With fld
strFld = .Name
If strFld = "LdUnits" Then
Exit For
Else
.Value = frmRS.Fields(strFld).Value (Note: error occurs here)
End If
End With
Next
.Update
End With
Next valRoundsCounter

rs.Close
frmRS.Close

Set strFld = Nothing
Set rs = Nothing
Set frmRS = Nothing
Set db = Nothing

Me.Requery


The error occurs where indicated above. In the debug window:
I have a value for: strFld = .Name
I have no value for: .Value = frmRS.Fields(strFld).Value (.Value = frmRS.Fields(strFld).Value=<No current Record>


The purpose of the code is to duplicate and mass fill activities that re-occur over a period of time with little/minor variations. Additional code (to be added later) will make the minor changes to the various fields and then will add a record key after the bulk of the duplication and changes are made.

Any suggests on previous posts where I may find an answer. Or, any suggestions to try.

Thank you in advance for your time and consideration of inquiry.
This post has been edited by Yana: Aug 4 2017, 11:44 AM
Go to the top of the page
 
BruceM
post Aug 4 2017, 02:21 PM
Post#2


UtterAccess VIP
Posts: 6,995
Joined: 24-May 10
From: Downeast Maine


I'm having a little trouble following this, but since it doesn't seem you are navigating through the Clone recordset it looks as if every record you are adding is identical. Is that the intent?

Could you explain the intent of Exit For if the field is LdUnits? Isn't every record going to have a field of that name? It appears to me, then, that the For loop will invariably be exited before there is a chance to update any record.
Go to the top of the page
 
ADezii
post Aug 4 2017, 02:57 PM
Post#3



Posts: 1,847
Joined: 4-February 07
From: USA, Florida, Delray Beach


I'm with Bruce on this one, the Code makes little or no sense in it's context.
Go to the top of the page
 
Yana
post Aug 6 2017, 09:42 PM
Post#4



Posts: 33
Joined: 14-February 14



Thank you for your quick response. My apologies for the delay in answering your questions seeking clarification.

In response to your observations and question:

1. The code in my initial post is attached to a command button on a form. The desire/hope is to be able input a single record that will serve as a general format/template for a series of subsequent records. (a sample is included toward the end)

2. As of now, yes, every record being added is identical. Eventually, each of the added records will be modified as it is added through additional coding to be written and added to the basic loop. Right now, I’m just trying to replicate the record for prescribed amount of times.

3. The Exit For (if the field is LdUnits) was the result of a previous error. When I initially created the form, I didn’t want to deal with adding, placing and formatting all the 40+ fields. I added enough of a representation to see if the main idea would work. Thus, my initial form did not have all the fields that were called/available when the rs record set (Set rs = db.OpenRecordset("tblWorkoutSessionPlanningData-000-Entry") was created in the code. As the Loop progressed it would eventually hit a field that was not available and would produce an error code. I found the first field that would produce the error and inserted it as the exit (If strFld = "LdUnits" Then Exit For) so that it would complete the code. Once I inserted this Exit, the code would run it’s intended purpose: copy a given record and paste it the prescribed amount of times. Thus, it was a temporary fix for a problem.

Since your inquiry, I have added all the fields to the form. I have also removed the ‘If Then Exit For’ in question to see if it was the cause of my existing error or if it changed how the code worked. Adding the missing fields and removing the 'If Then Exist For' did not change any observable results. The code will run smoothly the first time it is called -- it copies and pastes the desired record the prescribed amount of times and enters it into the tables recordset. It produces an error on any subsequent attempt to run the code.

Hopefully that helps, but my attempts at clarifying have probably only caused additional confusion.

Given that I want to enter a single record into my form; that I want to select that record and duplicate it a prescribed amount of times (determined on the form); how do I go from

A1|name|bb|r|1|1|8|2|30

to this

A1|name|bb|r|1|1|8|2|30
A1|name|bb|r|2|1|8|4|30
A1|name|bb|r|3|1|8|6|30
A1|name|bb|r|4|1|8|8|45
A1|name|bb|r|5|1|8|8|45
A1|name|bb|r|6|1|8|8|45
A1|name|bb|l|7|1|8|2|30
A1|name|bb|l|8|1|8|4|30
A1|name|bb|l|9|1|8|6|30
A1|name|bb|l|10|1|8|8|45
A1|name|bb|l|11|1|8|8|45
A1|name|bb|l|12|1|8|8|45

What is the best way to go about copying and pasting the general record? (I know how to make all the minor incremental changes through coding and how to add a 'key' to make each record unique. What I’m having problems with is duplicating/copying and pasting the records in such a way that they can also be modified during the process.

Should I create the recordset off the same qry that the form is built on? Should I duplicate the forms record set? Should I create the record set from the table that the qry is built on?

Bruce, you asked about navigating through the clone recordset. As you can see, I don't fully understand all of that. I tried to look at other examples and work from that. My thoughts were:
1) in creating the first recordset (rs) I was pulling all the fields from the table where the data would be eventually stored. (The desired record to be duplicated and later modified would not be in the table yet -- it was the destination.)
2) in creating the second recordset (frmRS), I could access the data to be duplicated from the form and the necessary unbound controls.
3) by using the .AddNew and .Update the new records would always be pasted at the end of the recordset so there was no need to navigate through the recordset.

Sorry for the long post. I've been working on this for some time trying to write clearly and succinctly.

Thank you again for taking your time to considering this inquiry and for slogging through my attempts to explain. No matter the outcome I appreciate your attempts to assist.


The code with the removal of the IF THEN EXIT FOR

valRounds = Me.txtRounds

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblWorkoutSessionPlanningData-000-Entry")
Set frmRS = Forms!Form1.RecordsetClone

For valRoundsCounter = 1 To valRounds
With rs
.AddNew
For Each fld In rs.Fields
With fld
strFld = .Name
.Value = frmRS.Fields(strFld).Value (Note: error occurs here)
End If
End With
Next
.Update
End With
Next valRoundsCounter

rs.Close
frmRS.Close

Set strFld = Nothing
Set rs = Nothing
Set frmRS = Nothing
Set db = Nothing

Me.Requery

As it stands, the above code works one time. It will copy and paste a given record x times (whatever valRounds has been set to) into the underlying table the first time it is called. It produces an error code on any subsequent attempt until the form is closed and reopened.
This post has been edited by Yana: Aug 6 2017, 09:43 PM
Go to the top of the page
 
BruceM
post Aug 7 2017, 06:59 AM
Post#5


UtterAccess VIP
Posts: 6,995
Joined: 24-May 10
From: Downeast Maine


Did you compile the code? The reason I ask is that you have an End If without an If, which should not have been able to compile.

QUOTE
Since your inquiry, I have added all the fields to the form.

Is that Form1? You refer to unbound controls. These are not part of the recordset or its clone. A bound form has a Record Source property (or a record source specified via code). The fields in that recordset are the fields available to the clone.

QUOTE
A1|name|bb|r|1|1|8|2|30

to this

A1|name|bb|r|1|1|8|2|30
A1|name|bb|r|2|1|8|4|30
A1|name|bb|r|3|1|8|6|30
A1|name|bb|r|4|1|8|8|45 (etc.)

You aren't duplicating a record here. You are duplicating five fields and altering four others, by my count. To do that you need to make provisions to add the changed values. At some point the fourth field changes from "r" to "l". The fifth field increments by 1 in each successive record. The eighth field increments by 2 until it reaches 8, at which point it stays the same until the fourth field changes from "r" to "l", when it reverts to 8. The ninth field is 30 as long as the eighth field is less than 8. When the eighth field is 8 the ninth field is 45.

The underlying business rules are not clear. My observations may not reflect the actual need. Here is an example of how you could approach the incrementing fifth field:

CODE
For valRoundsCounter = 1 To valRounds
  With rs
    .AddNew
    !FirstField = Forms!Form1!FirstField
    !SecondField = Forms!Form1!SecondField
    !ThirdField = Forms!Form1!ThirdField
    !FourthField = Forms!Form1!FourthField
    !FifthField = valRoundsCounter
    .Update
  End With
Next valRoundsCounter

BTW, when posting code, please select the code and click the Code Tags icon in the UA message window, to preserve the indents.

You mention running the code from a command button on "a form". If that form is Form1 (the one with the RecordsetClone you are trying to use), instead of Forms!Form1!FirstField you could do Me.FirstField, etc. If FirstField, etc. are in the recordset you can use the RecordsetClone, particularly if you are running the code from a form other than Form1, but again, remember that unbound controls are not part of the clone.

My main point here is that if you are copying some fields and altering others, you need to make provisions to write the altered values.
Go to the top of the page
 
Yana
post Aug 7 2017, 09:58 AM
Post#6



Posts: 33
Joined: 14-February 14



Thank you again for your assistance.

Based on your suggestions I have:

1. Compiled my code. Every thing is/was fine.
2. Thank you for the new information and insight about the unbound controls. I will explore this more.
3. Thank you for your observation and the resulting insight and clarification about my intended process (duplication vs. alteration)
4. Your observation (#3 above) and your example have given food for thought and possible modifications that I haven't tried and need to explore.
5. Thank you for the BTW and how to post code to preserve indents. That was driving me crazy! (Myopic focus on my problem resulted in laziness in researching a solution that would have resulted in an easier read for others. My thanks and apologies).
6. I'm pretty confident that I can write the code for the altercations. What you have prompted me to reconsider is whether or not creating a record set is really necessary. Can I accomplish my tasks without that (rhetorical)?

It may be several days before I re-post with any conclusions or follow-up questions. Thank you again.
Go to the top of the page
 
BruceM
post Aug 7 2017, 10:15 AM
Post#7


UtterAccess VIP
Posts: 6,995
Joined: 24-May 10
From: Downeast Maine


I still can't find the If to go with the End If, but I will assume a typo or something, otherwise the code would not have compiled. I'm glad to hear my comments may have helped set you on the right track.

If you post again it would be best to start a new thread, as it is likely to get more attention than adding to this thread. Include a link to this thread, though, if the general topic is similar.

Until then, best of luck with the project!
Go to the top of the page
 
Yana
post Aug 8 2017, 04:03 PM
Post#8



Posts: 33
Joined: 14-February 14



Call me stubborn! I had some time to kill before getting back on the project in earnest, so I decided to do some more trial and error on the code in my initial post. Through my searches I ran across something that I tried (RecordsetClone vs. Recordset.Clone). It works but I don't know why. It seems that it has something to do with the difference between ADO and DAO. Now, each time I run the event, it works ... no 'Run-time error 3021: No Current Record' on subsequent calls. Any guesses why Recordset.clone worked when RecordsetClone did not?


CODE
Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblWorkoutSessionPlanningData-000-Entry")
        'Set frmRS = Forms!form1.RecordsetClone      [Note: original]
        Set frmRS = Me.Recordset.Clone          '[Note: New addition]  

        valRounds = Me.txtRounds      
        
        For valRoundsCounter = 1 To valRounds
            With rs
                .AddNew
                    For Each fld In rs.Fields
                        With fld
                            strFld = .Name
                             .Value = frmRS.Fields(strFld).Value
                        End With
                    Next
                .Update
            End With
        Next valRoundsCounter
        
        rs.Close
        frmRS.Close
        
        Set rs = Nothing
        Set frmRS = Nothing
        Set db = Nothing
        
        Me.Requery


Bruce, Thank you again for your time, assistance and patience.
Go to the top of the page
 
Yana
post Aug 8 2017, 04:03 PM
Post#9



Posts: 33
Joined: 14-February 14



Call me stubborn! I had some time to kill before getting back on the project in earnest, so I decided to do some more trial and error on the code in my initial post. Through my searches I ran across something that I tried (RecordsetClone vs. Recordset.Clone). It works but I don't know why. It seems that it has something to do with the difference between ADO and DAO. Now, each time I run the event, it works ... no 'Run-time error 3021: No Current Record' on subsequent calls. Any guesses why Recordset.clone worked when RecordsetClone did not?


CODE
Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblWorkoutSessionPlanningData-000-Entry")
        'Set frmRS = Forms!form1.RecordsetClone      [Note: original]
        Set frmRS = Me.Recordset.Clone          '[Note: New addition]  

        valRounds = Me.txtRounds      
        
        For valRoundsCounter = 1 To valRounds
            With rs
                .AddNew
                    For Each fld In rs.Fields
                        With fld
                            strFld = .Name
                             .Value = frmRS.Fields(strFld).Value
                        End With
                    Next
                .Update
            End With
        Next valRoundsCounter
        
        rs.Close
        frmRS.Close
        
        Set rs = Nothing
        Set frmRS = Nothing
        Set db = Nothing
        
        Me.Requery


Bruce, Thank you again for your time, assistance and patience.
Go to the top of the page
 
BruceM
post Aug 9 2017, 12:18 PM
Post#10


UtterAccess VIP
Posts: 6,995
Joined: 24-May 10
From: Downeast Maine


The Me prefix means that what follows is a property of the form. The bang (!) means that what follows is a member of a collection. A dot means that what follows is a property. There's more to it than that, but close enough for now.

Forms!Form1 means that Form1 is a member of the Forms collection. If you were referencing a control on a form it would be Forms!Form1!TextBox1, because the controls are the default collection for a form.

You changed Forms!Form1!RecordsetClone to Me.Recordset.Clone. You didn't just change from RecordsetClone to Recordset.Clone. If the current form is not Form1, the RecordsetClone will not be the same as the recordset for the current form. Also, even if the current form is Form1, the syntax should be Forms!Form1.RecordsetClone because the RecordsetClone is a property of the form, not a member of a collection. I don't know for sure that using a bang instead of a dot will cause it to be interpreted other than as you intend, but I should have noticed the potential problem sooner.
Go to the top of the page
 
Yana
post Aug 9 2017, 09:35 PM
Post#11



Posts: 33
Joined: 14-February 14



Thanks for the explanation and the possible cause.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd November 2017 - 10:50 PM