By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,864 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

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

100+
P: 176
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
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
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

100+
P: 176
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
Expert 2.5K+
P: 3,072
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

100+
P: 176
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
Expert 2.5K+
P: 3,072
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

100+
P: 176
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

100+
P: 176
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
Expert 2.5K+
P: 3,072
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

100+
P: 176
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
Expert 2.5K+
P: 3,072
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.