Can't use Recordset Clone

I think it is RecordsetClone according to the intellisense. However, it still bombs if it's a new record. I'm trying to use the info the user is submitting to write values to other tables, so when i use RecordsetClone on an update event, it seems to work, but I can't use it when it's a new record. Doesn't seem to have anything to clone I suppose, but I still need to find out the data the user has entered and make appropriate entries on other table whether it's a new record or an update. So what would be the equivalent of RecordsetClone in the situation of a new record?

As it is a clone, I do not think you can edit it.  You can maniplulate/scroll it and select values, but not change or add.  What happens with:

Set objRSNew = Me.Recordset

I don't need to edit it, I just need to get it's values into an object and then walk through those values to tell me which records on other tables need entries.

Set objRSNew = Me. Recordset bombs also. The debugger shows objRSNew to have a value of Nothing. So does this have to do with the fact that I'm entering a new record?

This is how to get the values from unbound contros on a form into a table name myTable

strSQL = "INSERT INTO myTable (fld1, fld2, fld3) VALUES ( '" & Me!tbx1 & "','" & Me!tbx1 & "",'" & Me!tbx3 & "')"
DoCmd.RunSQL strSQL

this assumes text values in all three fields.  If any are numeric, omit the single quotes, if any are Date, replace the "'" with "#"

GRayL, what I didn't say is that I can't use what you are suggesting because the field values are multi valued (Access 2007) and therefore I need to create one recordset for the field and then a nested one for the multiple values within the field, which are the values that I really want to get at..

Just a couple of quick comments.

RecordsetClone
and
Recordset.Clone

are both valid and describe different objects (which have effectively the same content and structure - baased as they are on the same core Recordset).

The RecordsetClone is a persisted object during the instance of the form.
Recordset.Clone is a method which creates a new clone of the recordset - as many of these as required may be created but they're not persisted by default. Your object variable does that for you.
Neither is read only unless the underlying recordset is too.

Can you include more code that you're running (describing what actually works and where the problem lies)?

Here's the code. These are the only events I'm coding for. I used to update tblItems in the after insert event, but since it looks like the before update code always runs whether it's a new record or not, then I just put the after insert code into the before update code and then put conditionals to check if it's a new record or an update of an existing one.

In the case of both a new record and an update of an existing one, on the before update, I am walking through a number of numeric multi valued fields and retrieving their values and using them to mark the corresponding record numbers on another table by making some entries to that record. Basically, if a boardmodule is used inside of a system, then I have to go back to that boardmudule's table and mark which system it is now inside of. This prevents this boardmodule's number from showing back up in the query combo for subsequent record entries and/or changes. because it is now marked as already used. This is also why you see the requery of the combos in the form's current event.

If it is an update, then i have to do one more step. I have to compare the record ready to be submitted after edits, to what it orginally was. So I create and RSOld by querying the database for the record's state right before the changes are inserted and an RS that contains the changed values and if an RSOld value does not show up anywhere in the new value, then I need to go back to the record number contained in RSOld and clear the values that were entered earlier relating to what system it's in (because it's no longer in that system), so that it will now show up in the combo query and be available for addition to a system again. I'm sorry that I can't seem to find an easy way to communicate this, which is why I lose people. The fact that I'm walking through multi valued fields makes this a particular pain in the ass.

Option Compare Database
Public SystemsSN

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim tablesArray(7) As String
    Dim outerCount As Long
    outerCount = 0
    tablesArray(0) = "tblAirCooledModule"
    tablesArray(1) = "tblRFTMCCModule"
    tablesArray(2) = "tblBBTMCCModule"
    tablesArray(3) = "tblChassisModule"
    tablesArray(4) = "tblCPUNumbers"
    tablesArray(5) = "tblGPSNumbers"
    tablesArray(6) = "tblJ2BackplaneNumbers"
    Dim fieldsArray(7) As String
    fieldsArray(0) = "Air Cooled Modules"
    fieldsArray(1) = "RFTM CC Modules"
    fieldsArray(2) = "BBTM CC Modules"
    fieldsArray(3) = "Chassis Modules"
    fieldsArray(4) = "CPU Numbers"
    fieldsArray(5) = "GPS Numbers"
    fieldsArray(6) = "J2 Backplane Numbers"
   
    Dim bDimmed As Boolean
    Dim bflag As Boolean
    Dim objRSOld As Recordset
    Dim objRSNew As Recordset
    Dim objRSOld1 As Recordset
    Dim objRSNew1 As Recordset
        If Me.NewRecord Then
            Dim cnn As ADODB.Connection
            Dim strSQL As String
            Set cnn = CurrentProject.Connection
            strSQL = "INSERT INTO tblItems (SerialNumber,ItemType) VALUES (" & SystemsSN & ",'tblSystems')"
            cnn.Execute strSQL
            Set cnn = Nothing
        End If
        For outerCount = 0 To 6
        If Me.NewRecord Then
            Set objRSNew = Me.Recordset
        Else
            Set objRSNew = Me.RecordsetClone
        End If
        Set objRSNew1 = objRSNew.Fields(fieldsArray(outerCount)).Value
        If Not Me.NewRecord Then
            Set objRSOld = CurrentDb.OpenRecordset("select" & fieldsArray(outerCount) & " from" & tablesArray(outerCount) & "where SN =" & SN)
            Set objRSOld1 = objRSOld.Fields(fieldsArray(outerCount)).Value
            Do Until objRSOld1.EOF
                bflag = True
                Do Until objRSNew1.EOF
                    If objRSOld1.Fields(0).Value = objRSNew1.Fields(0).Value Then
                        bflag = False
                    End If
                objRSNew1.MoveNext
                Loop
                If bflag = True Then
                    strSQL = "UPDATE" & tablesArray(outerCount) & " SET [System Number] = NULL, [System Type] = '' WHERE SN = " & objRSOld1.Fields(0).Value
                    cnn.Execute strSQL
                End If
            objRSOld1.MoveNext
            Loop
        End If
        Do Until objRSNew1.EOF
            strSQL = "UPDATE" & tablesArray(outerCount) & " SET [System Number]=" & SN & ", [System Type] =" & tablesArray(outerCount) & "WHERE SN = " & objRSNew1.Fields(0).Value
            cnn.Execute strSQL
            objRSNew.MoveNext
        Loop
        Set objRSNew = Nothing
        Set objRSOld = Nothing
        Set objRSNew1 = Nothing
        Set objRSOld1 = Nothing
        Next outerCount
End Sub


Private Sub Form_Current()
    If Me.NewRecord Then
        SN = DMax("SerialNumber", "tblItems") + 1
    End If
    [Air Cooled Modules].Requery
    [RFTM CC Modules].Requery
    [BBTM CC Modules].Requery
    [Chassis Modules].Requery
    [CPU Numbers].Requery
    [GPS Numbers].Requery
    [J2 Backplane Numbers].Requery
    SystemsSN = SN
End Sub

As far as what is not working? So far, objRSNew is not getting populated by either Me.Recordset (in the case that it's a new record nor Me.RecordsetClone. Note, i have not been able to update yet because I can't enter a new record because those statements above fail on the before update event

In this line:

Set objRSOld1 = objRSOld.Fields(fieldsArray(outerCount)).Value

You have dimensioned objRSOld1 as a recordset yet the line provides a single value??

Somebody told me that this would put all of the values from a multi valued field into this nested recordset. nobody seems to really know a lot about multi valued fields and i guess because it's only in Access 2007. But the first problem is that objRSNew isn't even getting populated by either Me.Recordset or Me.Recordset.Clone. The debugger tells me it's empty. How can a simple statement like this in the before update event fail? This is my immediate problem

If Me.NewRecord Then
            Set objRSNew = Me.Recordset
        Else
            Set objRSNew = Me.RecordsetClone
        End If
        Set objRSNew1 = objRSNew.Fields(fieldsArray(outerCount)).Value

Since objRSNew comes up as empty or "Nothing", which it shouldn't, it bombs when trying to set objRSNew1 while using objRSNew.fields because objRSNew is empty or null "Nothing"

Yes, but you are trying to set a recordset equal to the value of a field ??  That should blow a fuse when the compiler gets to it ;-)

GRayL

Why does objRSNew = Me.RecordsetClone never populate objRSNew when a new record is being entered? That is my problem. The other stuff I'll deal with when i first get past this. As far as the compiler not blowing a fuse, it understands multi valued fields. Do you have an experience with Access 2007 multi value fields? I don't have much which is why I'm looki9ng for someone who does.

I see there's been some chatter on this. :-)
Right, so... what's been happening.

Well - even if there was type mismatch, the compiler wouldn't pick it up, it would be a runtime error at most.
However the code presented shouldn't be a problem. They key is that it's assigning the content of a field to a recordset object.
So we'd get a type mismatch (recordset <> field) if it were just
Set objRSNew1 = objRSNew.Fields(fieldsArray(outerCount))
However
Set objRSNew1 = objRSNew.Fields(fieldsArray(outerCount)).Value
is assigning a recordset object - as the field is multi-value.

It's not a new concept to DAO in 2007, ADO has been doing this with shaped recordsets for a long time.
Neither gets a lot of exposure in the light of day though ;-).

I see that your problem in this thread is just one of aquiring the parent recordset on a new record.
This isn't a peculiar to multi-value fields.
If you're on a new record then the record isn't yet committed and isn't accessible from the clones (as you've clearly found and hence the decision to use the actual form Recordset).

This is going to be messy at best, and at worst just fail (as you're finding).
It's where the discontinuity between the Recordset object and the actual form data differ.
You can't use the Recordset entirely as a standard recordset object. (For example navigating to a new record using recordset methods is fine - but try committing it and advancing like that and it fails).

For my money - if you're performing some function (subsequent additions of data based on entries in the currently displayed record) then it's worth committing that data before continuing.
Once committed you're free to navigate, access and rely upon that record's data.

Obviously committing the New Record's data is as easy as
Me.Dirty = False

Remember to hold the previously displayed PK value in a variable just as you have been doing as this commital won't necessarily see the recordset agree with what you perceive the current position to be. Then you can navigate to the recently added record and iterate through it's values as you would otherwise.
Something like:
objRSNew.FindFirst "PKField = " & lngPKField

If there's a reason why you don't want to commit the new record before acting upon its data then by all means offer it. I can't see how it would be a good idea as yet though.

Cheers.

LPurvis, it would certainly appear that you understand the issues. I'm getting pulled away at work and will need some time to digest what you're saying. Thanks

"If you're on a new record then the record isn't yet committed and isn't accessible from the clones (as you've clearly found and hence the decision to use the actual form Recordset)."

I did this, for that very possibility, but it does not work when entering a new record, and that's where it bombs on objRSNew=Me.Recordset, even though that's not the actual line where it really bombs as explained above.. Can't comment about updates until new ones can be added except to say that I moved my new record code that was in the after insert event to the before update section when i found out that before update code will execute prior to before insert or after insert events whether it';s a new record or not, so why not put it there at that point is the way I began to see it.

"For my money - if you're performing some function (subsequent additions of data based on entries in the currently displayed record) then it's worth committing that data before continuing.
Once committed you're free to navigate, access and rely upon that record's data."

I considered this. I can use the just added record and query it and use it as the new record. But I need to compare it to the old record on updates.

Would i have to create a recordset to save the original record during it's form current event? Seems kind of hokey, wouldn't it? I can't be the first person who's run into this problem and there has to be a completely proper solution.

But again, the problem is how to get the current record's data during the before update event and populate a new recordset

"If there's a reason why you don't want to commit the new record before acting upon its data then by all means offer it. I can't see how it would be a good idea as yet though."

If the data is already committed, then it's my understanding that the data has been lost according to my event script and then i would have to use the global variable to query for the just entered or updated record. That would mean i would have to grab the current values of the record during the current event (seems like a lot of unnecessary, repeated processing given how Access allows you to browse records through it's forms), and put them into a global or public recordset, and then also, grab the SN (PK) val and put that into a global variable and then, in the after update event rather than the before update event, I would query the database based on the criteria of the global variable's value to grab the record just entered and use that as the new values, so that i can compare the values of the old global recordset with the values of the new, properly queried recodset

It sounds like it will work, but do you think it's the only or best solution? Seems like you would know.

The main oddity or redundancy would be continually repopulating a recordset every time each record is the current one, whether the user is going to enter data or not. That's what seems to be not right to me, although maybe that's the answer if it's cheap in terms of memory, processing (etc....) to do anyway.

OK, I haven't really got time to sift through all your comments just now.
Can you summarise the actual intent of your process in a couple of lines?
I think that might be instructive in an overall solution suggestion rather than spending time pouring over the code (and not being familiar with your data objects anyway - for example I can't know if a recordset you open as the same source as the form you've been working on etc).

Cheers.

We have system and different types of subsystems. Both have numeric PKs that they are identified by Subsystems are  FK's of a system and their numeric SN values are accessed via multi value lookup. On entry or update of a system, user selects from a pool of available subsystem numbers and adds them. Each field on a system form represents a type of subsystem to be entered. In each there can be more than one subsystem of that type, hence the use of multi valued fields. Any subsystems used for any system must be marked as used and marked with the SN of the system using it, so that it does not continue to show up as an available subsytem for future systems entries or even edits. So when a subsystem number is used, i have to go to it's table, find that record number and mark it as used and then, if the subsystem number gets removed from a system upon edits, then i need to clear the system number entries from it's record, so that it will now be availble again in the lookup vals for future system entries.

I don't think what I'm trying to do is all that complex, heroic or hard to understand, and in fact, it's really necessary, but I can't seem to get across what I'm trying to do for the life of me to other people. It's really rather simple, conceptually. Maybe people see text and just don't even read it and go right to any code I have, get confused, and give up.

And the point of asking you for your description there was to explain what you're doing.
Not that it's hard to understand - but that you'd not done so previously.
(When the bigger picture is looked at it's usually easier to place the smaller puzzle piece - a little jigsaw analogy for you there :-).

I have to say though, if you'd place yourself at a reasonably advanced level I'm surprised you've chosen to go with MVF's.
The underlying structure is relational (we're assured as much by MS) but the access to that is programatic only.
For me - if there's an entity to be modelled, then model it. (i.e. in your table design schema).
MVFs can be a handy tool for the uninitiated to begin to think relationally but, unless you're planning on moving to Sharepoint, they don't have an external platform equivalent. i.e. to my mind they're a dead end.
However - I say again that this isn't the crux of your issue here, it's to do with Recordset access to the new record in a bound form.
None the less, that you need to perform this action entirely programatically is, again, a result of having implemented MVF's.

For example...
>> "So when a subsystem number is used, i have to go to it's table, find that record number and mark it as used "
Assigned entries in a table being unavailable for subsequent selection is a common action performed through SQL. There's no programatic requirement on updates for tagging items as used. (Such operations should really be wrapped up within a transaction even). A simple query would then always show you the subsystem numbers that haven't yet been selected.

Anyway - I dare say you're not about to abandon MVF's now.
So to your issue.
Accessing the data on a new record.
Don't.
It's in a buffer - the Recordset object can scarsely access it at best.
Commit those changes and navigate back.
You say there's no problem when operating from existing records - you'd then be in that exact position.

Each row on your form must have some primary key to uniquely identify that record.
Grabbing that value and returning to that row.
You then voice concerns about comparins against old values.
If that is something you need to do then I don't, for one second, feel that the memory overhead of a few variables to hold those values is excessive overhead.

For example consider your comment:
>> "Would i have to create a recordset to save the original record during it's form current event? Seems kind of hokey, wouldn't it? I can't be the first person who's run into this problem and there has to be a completely proper solution."

You're certainly not the first person wanting to compare updated values with those that existed previously.
However as part of that requirement, holding the previously entered values somewhere is not an unreasonable expectation.
I'd say you've implemented things the way you've implemented them.
That you're now having to jump through a hoop or two, well that's just a consequence of your design. Nobody made you design it that way.
If you're comparins all records loaded into the form - then you could even maintain a recordset in memory of all records on that form as it was when loaded. That's perhaps not ideal from a memory usage perspective - but if your requirements demand a comparison then relying on the values displayed in the BeforeUpdate event before commital, to me, isn't any more robust. Ordinarily I'd think - a disconnected ADO recordset held open for comparison, but your MVF implementation negates that as a possibility. So a Snapshot DAO recordset instead it is.

If your comparisons aren't form-wide but merely for the one specific record upon which you're located (and your use of the BeforeUpdate event certainly implies this is intended for each individual record only) then the process is simple. Either a single row recordset (established before the record commits) or, as you say, a set of variables.
To my mind, the recordset is easier, due to your MVF implementation (you'd need an array or collection to hold the MVF values if implementing variables). And a single row recordset in memory, honestly, is surely nothing compared to the manipulation you're performing otherwise here (to handle the scenario you're left with).

And in a new record it's surely trivial though - there were no previously entered values! A simple flag to indicate as much (that you were on a new record) should relieve you of any checking duties on that front.

Thoughts?

"For me - if there's an entity to be modelled, then model it. (i.e. in your table design schema)."

Does SharePoint understand data modeling and child tables? It understand MVFs very well. We want this to be al least partially viewable in Sharepoint lists. If you think i can do that with data modeling, then I might change everything

I have to say I'm not (thus far) a convert to the joys of using Sharepoint as a data repository.
MVF's do indeed exist (to one extent or another) to help service integration with Sharepoint (FWIW complex data types have also given us Attachment data types by necessesity, so it's great in that respect too).

AFAIK you can model what you want using different lists - though it would be up to your Access FE to then bind these together.
FWIW I wouldn't expect query performance to be blisteringly fast though using distinct lists through the Access FE. (Quite the reverse).
For a general example of selecting from lists and only making available unselected items have a look in the List Select demo in the examples page linked to in my profile.

(By the way - that Sharepoint is a big factor in this might have been worth mentioning earlier in the thread? ;-)

Either way, I don't think you're in a hard place. The suggestions presented to you require a bit of work, but not much.
(Consider the amount of code you've already written above to accomplish your current task).

Despite your previous explanation I can't offer much on suggested schema without being very much more familiar with the data objects in play (for example all those tables listed).

Cheers.

"Assigned entries in a table being unavailable for subsequent selection is a common action performed through SQL. There's no programatic requirement on updates for tagging items as used. (Such operations should really be wrapped up within a transaction even). A simple query would then always show you the subsystem numbers that haven't yet been selected."

So how would I do that in Access 2007?

"So to your issue.
Accessing the data on a new record.
Don't.
It's in a buffer - the Recordset object can scarsely access it at best.
Commit those changes and navigate back."

How do i know what values record had before editing? I can certainly query the record that I just entered and do so on the after update event rather than the before event and use that as the new recordset values, but how do I populate and another recordset with the original values before they were edited so that I can compare the two and make the appropriate entries when i find out what's been entered and what's been deleted.

As far as new records go, where I just need to mark records, not compare old and new values, are you saying that I merely submit the record and then query for that record it on the after insert event in order to get the values I need to look at and then take my actions in the after insert event?

"That you're now having to jump through a hoop or two, well that's just a consequence of your design. Nobody made you design it that way."

SharePoint?

"So a Snapshot DAO recordset instead it is."

So what exactly does that mean in terms of code?

"Either a single row recordset (established before the record commits) or, as you say, a set of variables."

I did the following and it bombs on a new record, so i can't even tell you about updates yet

And how to I establish that? That's mostly what my problem and question is. Me.Recordset or Me.RecordsetClone refuses to populate the object with anything

"To my mind, the recordset is easier, due to your MVF implementation (you'd need an array or collection to hold the MVF values if implementing variables). And a single row recordset in memory, honestly, is surely nothing compared to the manipulation you're performing otherwise here (to handle the scenario you're left with)."

If I could just figure out a way to get an old and new recordset, I wouldn't have to bother with global arrays, and also, Access forces them to be private rather than public. i don't know if that means it won't work or not, but arrays were the first thing I thought of, but loping through nested recordsets is far more elegant and less klunky, if, again, i could just find a way to get two recordsets. I think, for the time being, for new records, I'm going to mark a global variable on the current event as to the record form's pre populate SN and then use that to query the record I just entered on the after insert event. then, if i could get past the new Record entry, the Me.RecordsetClone might then start populating the recordset

>> "So how would I do that in Access 2007?"
You mean in a table schema design only?
Have you looked at the example I suggested? That's the "select from a list" which includes only "unselected" items for selection.
The old "Two Listboxes Demo".

>> "SharePoint?"
Well up until then I had no way of telling that Sharepoint was a factor in your situation. (You'd not mentioned it).
Still though, AFAIK there's no requirement that you use MVFs just because you're working in Sharepoint. Just the potential / liklihood of them.

>> Snapshot DAO recordset instead it is.
>> So what exactly does that mean in terms of code?
Well, thinking about it, a snapshot recordset is still connected to the database. Not a problem for main record data - as the data it contains is fixed until requeried. However the child records in the MVF would be a potential problem. (Since they're implemented relationally). Deleted records therein would be a problem.

I presume you're wanting to make note of the MVF values that have been changed for the given record?
Even a snapshot lacks the true discontinuity from the updated data that an ADO disconnected recordset could have offered if only the provider supported it :-s
You may have to then rely on an array or other variable to maintain the "previous" set of data prior to update through the form.

As a general functionality point though - you could open a recordset (and/or fill an array for the MVF data) in the form's Dirty event (whereby the form record commences editing) and then compare in the AfterUpdate when the changes have been committed.

>> and it bombs on a new record
There we come back to the "commit the record before attempting to access it" solution.
For example, consider just displaying the data to the immediate window...

Private Sub Form_AfterUpdate()

   Dim rstSub As DAO.Recordset
   Dim lngID As Long
   
   lngID = Me.ID  'Whatever your PK ID field is.

   With Me.RecordsetClone
       .FindFirst "ID = " & lngID  'Get to that row
       Debug.Print "New value: " & .Fields("WhateverNameField")
       Set rstSub = .Fields("YourMultiField").Value
       Do Until rstSub.EOF
           Debug.Print "  " & rstSub.Fields(0).Value
           rstSub.MoveNext
       Loop
   End With

End Sub

As I say though - the MVF is perhaps the problem in using recordsets only and not variables.
But there's little harm in a single, simple array for comparing the MVF data. (Since you're already this far into it, it doesn't seem a bigger leap to me). In for a penny, in for a pound - and all that.

Cheers.

>>>"Still though, AFAIK there's no requirement that you use MVFs just because you're working in Sharepoint. Just the potential / liklihood of them."

I don't think sharepoint is going to understand data modeling, but since it's a microsoft product, it understands MVFs.

Also, Here's another complexity that I haven't been able to get across which could be another reason to use MVFs. There are three types of BoardModules that can be entered into a System (BoardModule FK lookup multivalue). Call them Module A, B and C.  A system will always get at least one of each type of Module, so i have three columns in my System table and three fields on it's form, each representing one of the three types of Modules. However, there is an undetermined amount of Modules of each type that can be added. So suppose we can enter anywhere between 1 and 20 BoardModule ID values of module typeA. We never know how many. So the way I see it, and I could be naive, is that I would have to make 20 child tables, each with a varying amount of columns ranging from one to 20,  or create one child table with the maximum amount of fields and have many null values and wasted space in the records. Each option seems strange or hokey, so maybe there's another option?

>>>"I presume you're wanting to make note of the MVF values that have been changed for the given record?"

Absolutely, and this is where I have to do a little dancing. I didn't even need recordsets or anything in other situations, i would just walk through the .Fields and grab the field value

>>>"You may have to then rely on an array or other variable to maintain the "previous" set of data prior to update through the form."

Can one have global recordsets? Also, when i tried to create a public array, Access didn't like it and made me name it private. Can it still be used globally?


There we come back to the "commit the record before attempting to access it" solution.
For example, consider just displaying the data to the immediate window...

>>With Me.RecordsetClone

This is the problem and always has been, I cannot get this to work in the after insert event when I enter a new record, so i can't even tell whether it would work in the after update event. For new records, I use the global variable to find the record just entered, and fill a recordset to record which other tables need entries and then walk through it and do it. So untill I can get something like this to work during any other event besides the form's current event, i can't even say with recordset clones work after the after update event. My query is correct according to then debugger and the global variable is the correct one, but if fails to populate the recordset with anything almost as though the record hasn't been added yet, but when i stop it in the debugger and manually check that table to see if that record was added, it was.

So here's the plain and simple question. What events can you use RecordsetClone on? Otherwise, i can't figure out why this simple little statment returns nothing and then causes the statement that calls this supposedly populated recordset bombs.

Dim myDB As DAO.Database
    Dim objRSNew As DAO.Recordset
    Set myDB = CurrentDb
    Dim strRS
    strRS = "SELECT * FROM tblSystems WHERE SN=" & SystemsSN
    Set objRSNew = myDB.OpenRecordset(strRS)
    For outerCount = 0 To 6
        Set objRSNew1 = objRSNew.Fields(" & fieldsArray(outerCount) & ").Value