469,362 Members | 2,450 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,362 developers. It's quick & easy.

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

176 100+
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.
Jan 14 '07 #1
10 12661
nico5038
3,080 Expert 2GB
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)
Jan 14 '07 #2
Michael R
176 100+
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.
Jan 14 '07 #3
nico5038
3,080 Expert 2GB
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)
Jan 15 '07 #4
Michael R
176 100+
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.
Jan 15 '07 #5
nico5038
3,080 Expert 2GB
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)
Jan 15 '07 #6
Michael R
176 100+
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.
Jan 15 '07 #7
Michael R
176 100+
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.
Jan 15 '07 #8
nico5038
3,080 Expert 2GB
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)
Jan 16 '07 #9
Michael R
176 100+
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.
Jan 17 '07 #10
nico5038
3,080 Expert 2GB
I wrote OnCurrent and coded the MouseUp sorry.... :-)

Success with your application !

Nic;o)
Jan 17 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Kunal | last post: by
1 post views Thread by sofakingfree | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.