Cant Set Values

D

DS

I have a subform with a Checkbox on it, whenever I close the form I have
this code on it...

Forms!Sales.SalesDetails!Sent = True

the problem is it only sets the value on the last record, I need it to
set the value on all of the records showing on the subform at the time
that aren't already set to that value. How do you do that or this :)
Thanks
DS
 
K

Ken Snell [MVP]

You need to loop through the subform's recordset and set the value of each
record. The following code assumes that the field to which the Sent checkbox
is bound is also named Sent; if it's a different field name, then change it
to the correct name:

With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
 
J

John Vinson

I have a subform with a Checkbox on it, whenever I close the form I have
this code on it...

Forms!Sales.SalesDetails!Sent = True

the problem is it only sets the value on the last record, I need it to
set the value on all of the records showing on the subform at the time
that aren't already set to that value. How do you do that or this :)
Thanks
DS
Click to expand...

Well, you're assuming - incorrectly - that data is stored in a
subform. It's not! A form is only a tool, a window to display data
which is stored in a table.

If you want to update multiple records in a Table, then you need an
Update Query. You'll need to create a query selecting whichever
records need to be updated in the SalesDetails table (or whatever
table is the basis for this subform).

I do have to question whether this field properly belongs in this
table. What is the Recorsource for the subform? What Entity -
real-life thing, person or event - does it represent, and is it in
fact appropriate for each record in this table to be Sent? Might it be
possible for some records on the subform to be Sent = True and others
Sent = False? If not, then perhaps the Sent field belongs in the Sales
table, not the SalesDetails table.

John W. Vinson[MVP]
 
D

DS

Ken said:
You need to loop through the subform's recordset and set the value of each
record. The following code assumes that the field to which the Sent checkbox
is bound is also named Sent; if it's a different field name, then change it
to the correct name:

With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
Click to expand...

Hi, Thanks it works!!!! I incorperated it into the rest of the
code...Not to sloppy is it?
Thanks
DS



Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

If Forms!Sales!Guests = Forms!Sales.SalesDetails!Text58 Then
With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
DoCmd.Close
DoCmd.OpenForm "LogOn"
ElseIf Forms!Sales!Guests > Forms!Sales.SalesDetails!Text58 Then
DoCmd.OpenForm "NotEnuf"
ElseIf Forms!Sales!Guests < Forms!Sales.SalesDetails!Text58 Then
DoCmd.OpenForm "TooMany"
End If

Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub
 
D

DS

John said:
Well, you're assuming - incorrectly - that data is stored in a
subform. It's not! A form is only a tool, a window to display data
which is stored in a table.

If you want to update multiple records in a Table, then you need an
Update Query. You'll need to create a query selecting whichever
records need to be updated in the SalesDetails table (or whatever
table is the basis for this subform).

I do have to question whether this field properly belongs in this
table. What is the Recorsource for the subform? What Entity
real-life thing, person or event - does it represent, and is it in
fact appropriate for each record in this table to be Sent? Might it be
possible for some records on the subform to be Sent = True and others
Sent = False? If not, then perhaps the Sent field belongs in the Sales
table, not the SalesDetails table.

John W. Vinson[MVP]
Click to expand...
Well....the SalesDetails field has items on it that get sent, the thing
is that sometime, wel alot of times one needs to go back and order more
stuff but the stuff that has been sent can't be edited when this
happens. So there can be some that are true and some that are false.
See my post to Ken. Along with the code.
Thanks
DS
 
D

DS

Ken said:
You need to loop through the subform's recordset and set the value of each
record. The following code assumes that the field to which the Sent checkbox
is bound is also named Sent; if it's a different field name, then change it
to the correct name:

With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
Click to expand...
This works fine if you already have records attached to the sales. But
if you open a new sale, then add a new item and hit the button to mark
sent as true, it shows all of the records? Any suggestions.?
Thanks
DS
 
A

adsl

DS said:
I have a subform with a Checkbox on it, whenever I close the form I have
this code on it...

Forms!Sales.SalesDetails!Sent = True

the problem is it only sets the value on the last record, I need it to set
the value on all of the records showing on the subform at the time that
aren't already set to that value. How do you do that or this :)
Thanks
DS
Click to expand...
 
A

adsl

Ken Snell said:
You need to loop through the subform's recordset and set the value of each
record. The following code assumes that the field to which the Sent
checkbox is bound is also named Sent; if it's a different field name, then
change it to the correct name:

With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
Click to expand...
 
A

adsl

DS said:
Hi, Thanks it works!!!! I incorperated it into the rest of the code...Not
to sloppy is it?
Thanks
DS



Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

If Forms!Sales!Guests = Forms!Sales.SalesDetails!Text58 Then
With Forms!Sales.SalesDetails.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
!Sent = True
.Update
.MoveNext
Loop
End With
DoCmd.Close
DoCmd.OpenForm "LogOn"
ElseIf Forms!Sales!Guests > Forms!Sales.SalesDetails!Text58 Then
DoCmd.OpenForm "NotEnuf"
ElseIf Forms!Sales!Guests < Forms!Sales.SalesDetails!Text58 Then
DoCmd.OpenForm "TooMany"
End If

Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub
Click to expand...
 
A

adsl

John Vinson said:
Well, you're assuming - incorrectly - that data is stored in a
subform. It's not! A form is only a tool, a window to display data
which is stored in a table.

If you want to update multiple records in a Table, then you need an
Update Query. You'll need to create a query selecting whichever
records need to be updated in the SalesDetails table (or whatever
table is the basis for this subform).

I do have to question whether this field properly belongs in this
table. What is the Recorsource for the subform? What Entity -
real-life thing, person or event - does it represent, and is it in
fact appropriate for each record in this table to be Sent? Might it be
possible for some records on the subform to be Sent = True and others
Sent = False? If not, then perhaps the Sent field belongs in the Sales
table, not the SalesDetails table.

John W. Vinson[MVP]
Click to expand...
 
A

adsl

DS said:
Well....the SalesDetails field has items on it that get sent, the thing is
that sometime, wel alot of times one needs to go back and order more stuff
but the stuff that has been sent can't be edited when this happens. So
there can be some that are true and some that are false. See my post to
Ken. Along with the code.
Thanks
DS
Click to expand...
 
K

Ken Snell [MVP]

Your question probably is meaningful within the context of your database,
but I have no idea what you're doing on the form or subform, what your setup
conditions are, when you're running this code (such as, do you try to run
this code before you save a new record, or while you're entering the new
record), what discrimination the code is supposed to have vs. what it's
doing, etc.

More details, please...
 
D

DS

Ken said:
Your question probably is meaningful within the context of your database,
but I have no idea what you're doing on the form or subform, what your setup
conditions are, when you're running this code (such as, do you try to run
this code before you save a new record, or while you're entering the new
record), what discrimination the code is supposed to have vs. what it's
doing, etc.

More details, please...
Click to expand...
Ken, I found the problem. I added a requery to the bottom of it by
accident. Thats what I get for not going to bed! Nap Time! Once again
thank you for your help.
Sncerely
DS
 
Top