Referring to a record set from an unbound form

E.g.

Option Compare Database
Option Explicit

Private Sub Form_Load()

  Set Me.Form.Recordset = Forms("baseForm").Form.RecordsetClone

End Sub

Select allOpen in new window

Depending on the use-case, the form name of the base form can be passed as an OpenArgs value.

I prefer an approach that isn't dependent on a specific form name. I mean, after all perhaps you want to open that popup form from different forms.
So in your popup form, declare a form object at the top

Private OpenedFromForm as form

Select allOpen in new window

And in the popup forms OPEN event:

Set OpenedFormForm=Screen.ActiveControl.Parent

Select allOpen in new window

Then when you need anything from the OpenedFromForm, you can get it by that reference, eg.

Set RS=OpenedFromForm.recordsetclone

Select allOpen in new window

(Note that while testing/designing the open event will not fire if the form is already open in design view. So if you make changes to the popup form, save it, and close it before testing)

Thanks Anders

When you say "And in the popup forms OPEN event:" do you mean "on open event" of the pop up or the command button that opens the form?

My word why everything access developer is such hard work I do not know.

I get error with "Set rs = OpenedFromForm.RecordsetClone"  Object variable with block not set

If I use  Set Me.Form.Recordset = Forms("Forms![ProjectForm]![Details].Form ").Form.RecordsetClone I am obviously not specifying the form name correctly

Correct.

Set Me.Form.Recordset = Forms("ProjectForm").Form.Controls("Details").Form.RecordsetClone

Select allOpen in new window

See all possibilites in the sample. I'd prefer

Option Compare Database
Option Explicit

Private Sub btnOpenArgs_Click()

  DoCmd.OpenForm "OpenArgs", , , , , , Me.Form.Name & ";" & sfmData.Name

End Sub

Select allOpen in new window

with

Option Compare Database
Option Explicit

Private Sub Form_Load()

  Dim Args() As String

  Args() = Split(Me.Form.OpenArgs, ";")
  If UBound(Args()) = 0 Then
    Set Me.Form.Recordset = Forms(Args(0)).Form.RecordsetClone
  Else
    Set Me.Form.Recordset = Forms(Args(0)).Form.Controls(Args(1)).Form.RecordsetClone
  End If

End Sub

Select allOpen in new window

btw, it's not Screen.ActiveControl, but Screen.PreviousControl.
EE29167487.accdb

I'm sorry chaps I just keep getting error no matter what I try. On this I will have to just give up. You would have thought that there would be a way of just saying

Set rs = RecordsetClone "Query Name"

I've posted a working sample.. Just keep in mind, the the name of subform control is required, not the name of the form in that control.

I did try that but I will open another computer with Access 16 and check it out. I'm still working with Access 2002. It's great

*COUGH*
EE29167487.mdb

If you can tell us what you are trying to do, we might be able to suggest an alternative solution.  So far, I can't visualize a reason for wanting to manipulate the recordset of one form from another but that doesn't mean that there isn't one.

Like your sense of humor Ste5an!!  2002 no ribbon....Yeeeerrrrr

A user uses a main form with a subform to enter information. In the background a date Created field is set to current date and time.

All reports are then ordered in date created order as the customer expects. Sometimes an order need moving to a different position.(Maybe they forgot a record). So I created an unbound pop up form that has two combo boxes the first chooses the record to move and the second chooses the record that the previously selected record must move above.

I grab the dateCreated of the second combo, find the record above, grab that DateCreated tthe two dates together and multiply by 0.5 to give me a date between the two. This all works great except where the difference between the second combo record selected date is less than two seconds different in which case I cannot squeeze the new record in.  

So I want to change all DateCreated fields so that I have one hour difference between records. But these records are on the form and subform below the unbound pop up form

If I could just use something like I want to update the subforms query using Ste5an's code all would be bright and beautiful""

I get error with:
Set Me.Form.Recordset = Forms("ProjectForm").Form.Controls("Details").Form.RecordsetClone but have not figured out why. Also if you look at precious posts I ger an error "No current record" when the record count says 11 records?????

Post a sample database...

Will do ASAP

"My word why everything access developer is such hard work I do not know."

Having followed several of your posts, your plight reminds me of that scene in the movie "Animal House", where the pledge is getting whacked in the fanny by a fraternity brother, and all he can say is "Thank you sir, can I have another".  

You're bringing it on yourself.  What you are trying to do is actually quite simple, but your inexperience at trying to code by following instructions without knowing what seems like anything about Access/VBA coding, including basic syntax, or how to look up Access VBA questions & solutions with Google, is really beating yourself up.

Your "PreviousControl" button on your "BaseForm1" opens the "PreviousControl" form which make references to objects that may not be valid when the user clicks the "PreviousControl" button on the Base1 form.  For example, when you close the pop-up and don't click on another form or record, you get an error if you try to click the "PreviousControl" button again.
"No current record" means the recordset cursor or form's recordset cursor is NOT on a record in a recordset when you try to use the recordset (either EOF, BOF, or between records after a record .edit and .update attempt).  You need to know how to determine where you are and keep track of where you are in your recordset.

By the way, you can add fractional seconds to date/time values, but you can't see them with the date/time format - you need to use a numerical format to manipulate.

Help us try to un-paint you out of the corner you have gotten yourself into....

You are probably correct Mark. I've just been looking on google search to find access answers to try to learn it for myself but it's difficult to find people outside of EE that understand a problem. I did find a couple of User Groups with video. I'll check them out. I have books but it's not easy finding what you need.

Hey Derek. One reason you may be having trouble finding help with what you are trying to do is that I don't think anybody else has tried to do what  you want to do the way you are trying to do it.  There are much better ways.  You're going around your backside to get from thumb to forefinger, so there's not much help out there for doing that.  Your submitting the overall process that you are trying to do in small snapshots, so nobody has the big picture of what you are trying to do or what's been discussed in previous posts, so it makes it harder to give good advice and keep you out of that painted corner.

One Google research tip I've found helpful is to enter "Access VBA" or just "VBA" at the beginning of my Google search text.  It gives anything else I type after that relativity to Access and VBA.

There is always reasons why I do that Mark. I try to write stuff  that does not interfere with existing data. Some of it 10 years old. Then updates go usually without a hitch. Also I do get answers from experts that send me in a different direction direction Not deliberately of course. I am sure someone told me that you can only order a date field to the second. So all that work had to stop. Then I was looking at updating the records by adding  a gap of 1 hour into the back end data but could not get the code to work and to be fair to me no one told me why. Also an answer to this would have helped

If you look at previous posts I get an error "No current record" when the record count says 11 records?????

So no reply I have to try another way.

And why can you not just ask for a recordset clone, (QueryName) I still don't know.

Then I am told that you can split seconds in a date and use date as the sort field (I think that was you) which means I can go back to the original idea and forget trying to separate date gaps by 1 hour.

Also I tend not to give up.
Sample attached
db2.zip

Derek:  We'll get you taken care of.  Glad you don't give up.  It's just a process of working thru the stuff that we experts need to know about your program so that we can give you what you need.  The more details we have, the more detailed the solution we can provide.  The less info we have (the more general our understanding of what you are doing), the more general the help provided.

Also, not everybody has the same answers or level of expertise.  The answers you get depend a lot on by who and when your post is looked at.  Easy ones are answered quickly.  More complex/twisted ones (in an experts eyes) take awhile.

You can get a recordsetclone, you just have to make sure you are asking the right object and make sure that it has a recordset to clone.  Should be no problem, unless your code to reference the object is bad, then you have to fix it, and fixing it is not easy if you don't know how to properly write it and the experts don't have a db to look at so they can create it for you.  A situation like that dissolves into a game of 20 Questions, which can take several days....

Also "No Current Record" has nothing to do with how many records are in a recordsource/recordset, but on where the cursor is that's operating on the recordset.  If the cursor is not on a record (EOF (after End Of File (last record)), or BOF (before Beginning Of File (before 1st record)), or in-between records, such as after a .Update to a .Edit, but before a .MoveNext, or the cursor has just not been placed on a record because of some process that left the focus on something that didn't have a recordset, then you will get that error when you try to do anything that requires an active record.  That's why you need to know how to keep track of your cursor and how to make sure it is in place properly.  Usually that is automatic so it is set for you so you don't have to think about it, but in those cases where it isn't, you get this error message.

I'll take a look at db2.zip tonight and see what we can do.

I'm pretty sure I posted code in one of Derek's other threads to resequence a set of data that used sequence numbers.  I do this in a couple of apps where the user needs to move stuff around.  When an item gets added, I assign the next available sequence number incrementing by 100.  Then if a user wants to move 10200 to be after 9100,  He changes 10200 to 9140.  If at some point, the set gets too messy, there is code to renumber the items so they return to their original gap of 100 using the adjusted sequence numbers to order the data rather than the autonumbers which would order them by data entry sequence..

I agree with Mark.  This shouldn't be as complicated as it has become.  When you are generating the "sequence" using Now(), you don't have any control over the gap.  If the user is fast, the gap is very small.  If the user is slow, the gap is large.  My original impression was that this was a rare occurrence so what was suggested in other threads (a way to calculate the midpoint)  should have been fine.  Now, it seems like many changes have to be made constantly and the smallness of the gaps is what is causing a problem.  Perhaps it is time to abandon the time sequence since it appears to have become meaningless anyway and go with a sequence number where you can use an increment as large or as small as you want in order to control the gap to allow for movement and inserts.

Pat's on the right track.  I've always found a single or double data type where you can add a digit after a decimal point to insert a number between two other numbers always works, no matter how many times you have to try to move a record between two others.  Especially if all you need to use it for is sorting or positioning a record somewhere in a record stack.

The conversion of datetime to CDbl presents an ability to do this nicely.  Let the datetime inserted at the time the record is created be a start.  You can have a separate number column that has the converted datetime if you need a manipulatable column for sorting while you keep the datetime column for datetime use.  Usually sorts are based on numeric, text, or datetime data that is in the table and is not adjusted, so not many folks ever come across a need to work something like this out.

Derek:  While I can open your db2.mdb, I'm getting the following error when I try to run your code under your controls:
What do you need/use this for?  What versions of Access and Windows are you running?

Nevermind… I changed the reference to a later one - it works...

OK Derek.  Ran your db2.mdb order change forms and all looks like it did the job - ran ok and reordered the records.  I don't see any problems..
What's next?

Thanks Pat. I wanted to use existing field to save adding a field to customers data then running a query to update what would be an empty field set and then have to reorder many of the 120 odd reports in the application. Your code always works but I could not use it for this problem. I was very interested in the subquery you suggested but I would still have to update dozens of reports for a new sort order,

Hi Mark. I have no idea what OWC10.dll is for either. What did you change it too? Currently still on 2002 with windows 8
The only issue with the sample, that curiously isn't a problem in my app is if you choose an item to move and then in the second combo choose the top record it works only the first time. Move another row to the the top of the list and I get an error.

The principles for the sample db were based on your idea, So thanks for that.

Great solution at last. Only took me a week!

So you're done?  Got what you needed?  Glad I was able to help.

Sometimes the most aggravating part of trying to get a solution is having to wade thru all the stuff folks throw out there based on a misunderstanding of the problem or what you are trying to do.  Sometimes you get into a game of 20-Questions before you finally get things worked out.

The better the author is at stating the question and providing all the technical details that folks unfamiliar with his work need, the quicker things come together.
Also, the better attention all the "experts" pay to properly reading and interpreting the issue and trying to get the author the answer he needs would help a lot too.  
The wide range of experiences and level of expertise (from novices who just want a quick-and-dirty no-code solution because they don't know VBA, to application development experts who are more interested in trying to teach proper overall design instead of answering the issue) creates an environment with such a broad spectrum that it can take a while for minds to finally come together.

Do I miss something or where two threads merged?

Yes inadvertently this thread merged. I hope you had recognition of your efforts Ste5an. Really don't see what points are allocated any more so I have no way of knowing