How to update selected records on a datasheet subform using seltop and selheight?

[Michael R]

Hello to all.

In what way can I update a certain selected records' values on a datasheet subform using a command button which is located on the main form?

Thanks and cheers.

[nico5038]

You can construct an UPDATE query in the mainform buttons' OnClick event, using an additional textfield on the mainform named txtNewValue, like:

currentdb.execute ("Update tblX Set TextFieldA='" & me.txtNewValue & "' where ID=" & me.subformname.form.ID)

or for a number:

currentdb.execute ("Update tblX Set NumberFieldB=" & me.txtNewValue & " WHERE ID=" & me.subformname.form.ID)

The ".subformname.form" is needed to refer to the unique key of the current record of the subform. Ofcourse you need to change the name to the name of your subform....
Getting the idea?

Nic;o)

[Michael R]

You can construct an UPDATE query in the mainform buttons' OnClick event, using an additional textfield on the mainform named txtNewValue, like:

currentdb.execute ("Update tblX Set TextFieldA='" & me.txtNewValue & "' where ID=" & me.subformname.form.ID)

or for a number:

currentdb.execute ("Update tblX Set NumberFieldB=" & me.txtNewValue & " WHERE ID=" & me.subformname.form.ID)

The ".subformname.form" is needed to refer to the unique key of the current record of the subform. Ofcourse you need to change the name to the name of your subform....
Getting the idea?

Nic;o)

Nope, I'm talking about several records selection.
When I make a selection using the mouse on the datasheet subform, I need a button to be able to update the selected values. Problem is, as I press button on the main form, the focus dissaperas from the selection and the selection drops.

[nico5038]

You'll need the subform's OnCurrent event with code like:

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error Resume Next

Parent.txtSelLength = Me.SelHeight
Parent.txtSelstart = Me.SelTop

Dim rs As Recordset
Dim intI As Integer

Set rs = Me.RecordsetClone

rs.MoveFirst
intI = 1
While intI < Me.SelTop
intI = intI + 1
rs.MoveNext
Wend
'now positioned on the first

'Init resultfield
Parent.txtSelected = ""

intI = 0

While intI < Me.SelHeight
Parent.txtSelected = Parent.txtSelected & " " & rs!Field1
intI = intI + 1
rs.MoveNext
Wend

Set rs = Nothing

End Sub

When you place the Parent.txt... fields on your mainform and change rs!field1 into the name of your keyfield, then you'll see the effect. (Make sure you have a reference set to the DAO library)

Nic;o)

[Michael R]

You'll need the subform's OnCurrent event with code like:

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error Resume Next

Parent.txtSelLength = Me.SelHeight
Parent.txtSelstart = Me.SelTop

Dim rs As Recordset
Dim intI As Integer

Set rs = Me.RecordsetClone

rs.MoveFirst
intI = 1
While intI < Me.SelTop
intI = intI + 1
rs.MoveNext
Wend
'now positioned on the first

'Init resultfield
Parent.txtSelected = ""

intI = 0

While intI < Me.SelHeight
Parent.txtSelected = Parent.txtSelected & " " & rs!Field1
intI = intI + 1
rs.MoveNext
Wend

Set rs = Nothing

End Sub

When you place the Parent.txt... fields on your mainform and change rs!field1 into the name of your keyfield, then you'll see the effect. (Make sure you have a reference set to the DAO library)

Nic;o)

Thanks Nico, I've tried that, and it works, but I was asking about how can I update a selection of several records from a datasheet subform in the following way:

subform:.....field1.....field2
......................1..........true
......................2..........true
> ...................3..........true
> ...................4..........true

so, if the selection will is 3-4 then by using the command button from the main form, i need the subform to be changed to this:

subform:.....field1.....field2
......................1..........true
......................2..........true
......................3..........false
......................4..........false

the main reason I can't figure out how to do it, except the fact that I don't understand very well the mechanics of working with recordsets, is because as I select several records from the subform, and press the command button, the records are being dis-selected because the subform losts the focus.

[nico5038]

The above code can be used to collect the unique ID's of the subform and these can be used to issue UPDATE queries like:

dim arr
dim intI as integer

ass = split (me.txtKeyFields,",")

for intI = 0 to UBOUND(arr)
currentdb.execute ("update tblX set YesNo=Not(YesNo) where KeyField=" & arr(intI))
next

Getting the idea ?

Nic;o)

[Michael R]

The above code can be used to collect the unique ID's of the subform and these can be used to issue UPDATE queries like:

dim arr
dim intI as integer

ass = split (me.txtKeyFields,",")

for intI = 0 to UBOUND(arr)
currentdb.execute ("update tblX set YesNo=Not(YesNo) where KeyField=" & arr(intI))
next

Getting the idea ?

Nic;o)

Yep, it works using a public array for collecting unique id's (I didn't understand how to do it using Parent.txt....),

Thanks very much.

[Michael R]

Nico, something additional on this issue

While this method works great on a subform, it doesn't work well with a regular continous form. It doesn't always recognize the selection. Maybe it is has something to do with timing issues? Do you know how can I work this out?

Thanks again.

[nico5038]

Do you have a timer coded somewhere?
If so, that could be the reason as a timer will disturb the focussing and event sequence.

Nic;o)

[Michael R]

Do you have a timer coded somewhere?
If so, that could be the reason as a timer will disturb the focussing and event sequence.

Nic;o)

No, the code is timer-free
I change the subform event of the collecting id's code to On Mouse Up, and it works fine.

Cheers, Michael.

[nico5038]

I wrote OnCurrent and coded the MouseUp sorry.... :-)

Success with your application !

Nic;o)