Shrinking recordset 1

[tekkyun]

Hi,
I have application which runs as follows:-
1. Create global ADODB.recordset variable RsSt10
2. Append fields to RsSt10
3. Open form/subform (where subform is continuous form)
4. copy subform recordset to RsSt10
5. Set RsSt10 as subform recordset

I'm all ok to here
If I then click on subform record, then recordset loses all records apart from the one I have selected!

Any ideas why?

 

[TheAceMan1]

How are ya tekkyun . . .

Even though you've posted with quite a bit of gray area, [blue]your going around the block for what you can do at home![/blue]

You should be using [blue]dynamic SQL[/blue] where the fields (including your additions) are simply concatenated proper and the [blue]RowSource[/blue] of your subform is replaced with the new SQL.

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

 

[tekkyun]

Aceman,
I've done it this way because I don't want changes that users make in the subform reflected in the source tables.
...maybe you know simpler way???
I have narrowed down the problem now. If I select a textbox on the subform I can make changes ok and all my records stay. Its only when I select a checkbox that my records disappear!

My code:-
1. M_ordersDue Module:-
Global RsSt10 As ADODB.Recordset

Sub SetGlobals()

Set RsSt10 = New ADODB.Recordset
With RsSt10
.Fields.Append "Buyer", adVarWChar, 20
.Fields.Append "OrdersPlaced", adInteger
.Fields.Append "Tick", adBoolean
.Fields.Append "CalcTemp", adInteger
End With
End Sub

2. On open event of main form:-
Dim FrmTempRs As Recordset
Dim Frm As Form
Dim ctl As Control

DoCmd.Maximize

SetGlobals

Set Frm = Forms("FrmViewWeeklyData")
Set ctl = Frm.Controls("FrmViewWeeklyDataBuyerAddData")
Set FrmTempRs = ctl.Form.RecordsetClone

If Not FrmTempRs.EOF Then FrmTempRs.MoveFirst
'then copy data from FrmTempRs to new temp recordset RsSt10
With M_ordersDue.RsSt10
M_ordersDue.RsSt10.Open , , adOpenKeyset, adLockOptimistic
Do Until FrmTempRs.EOF
.AddNew
.Fields("Buyer") = FrmTempRs.Fields("Buyer")
.Fields("OrdersPlaced") = FrmTempRs.Fields("OrdersPlaced")
.Fields("Tick") = FrmTempRs.Fields("Tick")
.Fields("CalcTemp") = FrmTempRs.Fields("CalcTemp")
.Update
FrmTempRs.MoveNext
Loop

End With

Set ctl.Form.Recordset = M_ordersDue.RsSt10
etc..etc....


Its the "Tick" field that's causing me grief!
After debugging, I find that the records are still there after entering subform, but they disppear before update of "tick" field.

Funny thing is, is that I have almost identical code running on another form/subform and it works fine!

Any clues?

 

[TheAceMan1]

OK tekkyun . . .

So whats preventing you from using an [blue]UnBound Form?[/blue]

See Ya! . . . . . .

 

[tekkyun]

Aceman,
Suppose that's what I'm trying to do. Bind the form to display data, then unbind it so that changes are not written back to the tables.

Just realised that my comment on the end of my previous post is wrong. I have same problem on my other form/subform, its just that I didn't test it on multiple records.

 

[TheAceMan1]

[blue]I've done it this way because [purple]I don't want changes that users make in the subform reflected in the source tables.[/purple][/blue]

With no other additions the only way to accomplish this is thru an [blue]Unbound Form.[/blue] This presents a problem in that an [blue]Unbound Form[/blue] can only be a [blue]Single View[/blue] form, not continuous . . . as there's no recordsource and I'm not gonna mention the somewhat staggering amount of needed code to handle the data.

What you may want to consider is [blue]a seperate independent local table for holding the data[/blue] . . . this would allow binding of the form. Before opening the form you would clear the table and append as needed.

As for the problem with the checkbox . . . still too much gray area to tell. In any case 1st things 1st. Perhaps solving the form issue will clear this up.

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

 

[tekkyun]

Aceman,
Hey, thanks for your suggestions, really appreciate the help.
You mention using independent local table, but I have a problem with this in that I have concurrent users of the data so that one users changes would be overwritten by anothers. This is why I am trying to hold the data as local recordset variable.
In effect, all I am trying to do is to allow users to select multiple records from variable list of data (hence continuous form), then use their individual selections to display further detailed data. My original method of holding data in table, and using indicator field to hold selections works great for single users, but is a pain for multi-users.
If there is an easier way, please put me out of my misery!

 

[PHV]

allow users to select multiple records from variable list of data
Have you considered a MultiSelect ListBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tekkyun]

PHV,
No, didn't know multiselect option existed! I've just tried it and can only get it to return a value when multi select is set to none. If I change multiselect to either simple or extended then it returns Null.
How does it work?

 

[PHV]

Have a look at the ItemsSelected collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tekkyun]

Ah I've sussed how from another post.
I reckon that's what I've been looking for!
Thanks PHV


Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In List0.ItemsSelected
stDocCriteria = stDocCriteria & "[ID] = " & List0.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If

 

[tekkyun]

I'd still be intersted to know what causes the disappearing records using checkbox and continuous form though.....

 

[tekkyun]

Yeah, just had a play with listbox idea and would still be really interested to find the cause, as list box doesn't have the facilities I need. It looks a bit naff in my application, so I have abandoned that idea.