Help with Continuous Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All

Is there a way to set all checkboxes on a Continuous Form to true.
The code below only sets the first one to true
Me.Transfers1_subform.Controls("Completed") = -1

Thanks Mike
 
Execute an Update query statement to set the yes/no field to True in the
table. Then requery the form to show the result.

This kind of thing:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim strSql As String

If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET Completed = True " & _
"WHERE Completed = False;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
End If
Set db = Nothing
End Sub
 
Mr Browne
First thanks for your fast response.

I'm having trouble with your code, getting Run-time error 2455
Invalid reference to the property Dirty.

This is what is highlighted - If Me.Dirty Then

Does it matter if its Record Source is a query
 
Dirty means there are uncommitted edits in the form (i.e. the record needs
to be saved.)

The error could mean several things:

a) The current record cannot be saved, e.g. because a required field is not
filled in. The solution is to complete the record, or press Esc twice to
undo it.

b) The form is unbound, i.e. there is nothing in its RecordSource property.
This is unlikely if it's a continuous form, but the solution would be to
remove the offending line.

c) It is not editable. If so, you can also remove the line.
 
Mr. Browne
I have got it so it runs but, it change's all Completed to -1 in the table
and i'm only
needing to change the records on the form. know I'm getting Run time error
3061
Too few parameters. Expected 5.
I was reading and think it has to do with my params in my query. I have
tried to replace the where in the code that you have gave me but no luck.
Here is the SQL from the query. Maybe I'm just out of luck.

If you could help Great if not then thanks for all that you have done.

Mike

SELECT tblTransfers.Completed, tblTransfers.[Store#],
tblTransfers.[To/From], tblTransfers.Date, tblTransfers.PluNumber,
tblTransfers.PluDescription, tblTransfers.Qty, tblTransfers.TotalCost,
tblTransfers.TotalRetail, tblTransfers.BuydownAmount,
tblTransfers.BuyDownTotal
FROM tblTransfers
WHERE (((tblTransfers.Completed) Between
IIf([Forms]![TransferComparison]![CompletedOptions1]=3,-1,[Forms]![TransferComparison]![CompletedOptions1])
And [Forms]![TransferComparison]![CompletedOptions1]) AND
((tblTransfers.[Store#])=[Forms]![TransferComparison]![cboStoreName1]) AND
((tblTransfers.[To/From])=IIf([Forms]![TransferComparison]![cboStoreName2]="",0,Mid([Forms]![TransferComparison]![cboStoreName2],InStr([Forms]![TransferComparison]![cboStoreName2],"#")+1)))
AND ((UCase(Format$([Date],"mmmm")))=[Forms]![TransferComparison]![cboMonth])
AND ((Format$([Date],"yyyy"))=[Forms]![TransferComparison]![cboYear]))
ORDER BY tblTransfers.PluNumber;
 
What defines "the records in your form"?
Is it a filter? A link to the main form? Criteria in a query?
You will need to add similar criteria to the WHERE clause of the Update
query.

You will also need to concatenate the value form the controls on the form
into the query string. (Or perhaps that's the WHERE clause you are talking
about.)

If you can't figure out how to concatenate the values and use the right
delimiters, you could use RunSQL instead of Exeucte. Here's an explanation
of the difference:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Mr. Browne
I have got it so it runs but, it change's all Completed to -1 in the table
and i'm only
needing to change the records on the form. know I'm getting Run time error
3061
Too few parameters. Expected 5.
I was reading and think it has to do with my params in my query. I have
tried to replace the where in the code that you have gave me but no luck.
Here is the SQL from the query. Maybe I'm just out of luck.

If you could help Great if not then thanks for all that you have done.

Mike

SELECT tblTransfers.Completed, tblTransfers.[Store#],
tblTransfers.[To/From], tblTransfers.Date, tblTransfers.PluNumber,
tblTransfers.PluDescription, tblTransfers.Qty, tblTransfers.TotalCost,
tblTransfers.TotalRetail, tblTransfers.BuydownAmount,
tblTransfers.BuyDownTotal
FROM tblTransfers
WHERE (((tblTransfers.Completed) Between
IIf([Forms]![TransferComparison]![CompletedOptions1]=3,-1,[Forms]![TransferComparison]![CompletedOptions1])
And [Forms]![TransferComparison]![CompletedOptions1]) AND
((tblTransfers.[Store#])=[Forms]![TransferComparison]![cboStoreName1]) AND
((tblTransfers.[To/From])=IIf([Forms]![TransferComparison]![cboStoreName2]="",0,Mid([Forms]![TransferComparison]![cboStoreName2],InStr([Forms]![TransferComparison]![cboStoreName2],"#")+1)))
AND
((UCase(Format$([Date],"mmmm")))=[Forms]![TransferComparison]![cboMonth])
AND ((Format$([Date],"yyyy"))=[Forms]![TransferComparison]![cboYear]))
ORDER BY tblTransfers.PluNumber;



Allen Browne said:
Dirty means there are uncommitted edits in the form (i.e. the record
needs
to be saved.)

The error could mean several things:

a) The current record cannot be saved, e.g. because a required field is
not
filled in. The solution is to complete the record, or press Esc twice to
undo it.

b) The form is unbound, i.e. there is nothing in its RecordSource
property.
This is unlikely if it's a continuous form, but the solution would be to
remove the offending line.

c) It is not editable. If so, you can also remove the line.
Click to expand...
Click to expand...
 
Mr. Browne

I think the Run SQL in my way to go does this look ok.
I do need to add code for the cancel error but besides that is this the
correct way
or is there a more correct way

Thanks Mike

Private Sub cmdApplyAll_Click()
Dim strSql As String

If MsgBox("Confirm " & Me.cmdApplyAll.Caption _
& " to " & Form_TransferComparison.StoreNumber1.Value & ".", _
vbYesNoCancel + vbInformation, "Transfers") <> vbYes Then
Exit Sub
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Transfers1 SET Completed = -1 " & _
"WHERE Completed = 0;"

DoCmd.RunSQL strSql
Me.Requery

End If
End Sub

Allen Browne said:
What defines "the records in your form"?
Is it a filter? A link to the main form? Criteria in a query?
You will need to add similar criteria to the WHERE clause of the Update
query.

You will also need to concatenate the value form the controls on the form
into the query string. (Or perhaps that's the WHERE clause you are talking
about.)

If you can't figure out how to concatenate the values and use the right
delimiters, you could use RunSQL instead of Exeucte. Here's an explanation
of the difference:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Mr. Browne
I have got it so it runs but, it change's all Completed to -1 in the table
and i'm only
needing to change the records on the form. know I'm getting Run time error
3061
Too few parameters. Expected 5.
I was reading and think it has to do with my params in my query. I have
tried to replace the where in the code that you have gave me but no luck.
Here is the SQL from the query. Maybe I'm just out of luck.

If you could help Great if not then thanks for all that you have done.

Mike

SELECT tblTransfers.Completed, tblTransfers.[Store#],
tblTransfers.[To/From], tblTransfers.Date, tblTransfers.PluNumber,
tblTransfers.PluDescription, tblTransfers.Qty, tblTransfers.TotalCost,
tblTransfers.TotalRetail, tblTransfers.BuydownAmount,
tblTransfers.BuyDownTotal
FROM tblTransfers
WHERE (((tblTransfers.Completed) Between
IIf([Forms]![TransferComparison]![CompletedOptions1]=3,-1,[Forms]![TransferComparison]![CompletedOptions1])
And [Forms]![TransferComparison]![CompletedOptions1]) AND
((tblTransfers.[Store#])=[Forms]![TransferComparison]![cboStoreName1]) AND
((tblTransfers.[To/From])=IIf([Forms]![TransferComparison]![cboStoreName2]="",0,Mid([Forms]![TransferComparison]![cboStoreName2],InStr([Forms]![TransferComparison]![cboStoreName2],"#")+1)))
AND
((UCase(Format$([Date],"mmmm")))=[Forms]![TransferComparison]![cboMonth])
AND ((Format$([Date],"yyyy"))=[Forms]![TransferComparison]![cboYear]))
ORDER BY tblTransfers.PluNumber;



Allen Browne said:
Dirty means there are uncommitted edits in the form (i.e. the record
needs
to be saved.)

The error could mean several things:

a) The current record cannot be saved, e.g. because a required field is
not
filled in. The solution is to complete the record, or press Esc twice to
undo it.

b) The form is unbound, i.e. there is nothing in its RecordSource
property.
This is unlikely if it's a continuous form, but the solution would be to
remove the offending line.

c) It is not editable. If so, you can also remove the line.

Mr Browne
First thanks for your fast response.

I'm having trouble with your code, getting Run-time error 2455
Invalid reference to the property Dirty.

This is what is highlighted - If Me.Dirty Then

Does it matter if its Record Source is a query

:

Execute an Update query statement to set the yes/no field to True in
the
table. Then requery the form to show the result.

This kind of thing:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim strSql As String

If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET Completed = True " & _
"WHERE Completed = False;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
End If
Set db = Nothing
End Sub

Hello All

Is there a way to set all checkboxes on a Continuous Form to true.
The code below only sets the first one to true
Me.Transfers1_subform.Controls("Completed") = -1

Thanks Mike
Click to expand...
Click to expand...
Click to expand...
 
I suspect you still need to add the WHERE clause so you don't update every
row in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Mr. Browne

I think the Run SQL in my way to go does this look ok.
I do need to add code for the cancel error but besides that is this the
correct way
or is there a more correct way

Thanks Mike

Private Sub cmdApplyAll_Click()
Dim strSql As String

If MsgBox("Confirm " & Me.cmdApplyAll.Caption _
& " to " & Form_TransferComparison.StoreNumber1.Value & ".", _
vbYesNoCancel + vbInformation, "Transfers") <> vbYes Then
Exit Sub
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Transfers1 SET Completed = -1 " & _
"WHERE Completed = 0;"

DoCmd.RunSQL strSql
Me.Requery

End If
End Sub

Allen Browne said:
What defines "the records in your form"?
Is it a filter? A link to the main form? Criteria in a query?
You will need to add similar criteria to the WHERE clause of the Update
query.

You will also need to concatenate the value form the controls on the form
into the query string. (Or perhaps that's the WHERE clause you are
talking
about.)

If you can't figure out how to concatenate the values and use the right
delimiters, you could use RunSQL instead of Exeucte. Here's an
explanation
of the difference:
http://allenbrowne.com/ser-60.html

Mike said:
Mr. Browne
I have got it so it runs but, it change's all Completed to -1 in the
table
and i'm only
needing to change the records on the form. know I'm getting Run time
error
3061
Too few parameters. Expected 5.
I was reading and think it has to do with my params in my query. I have
tried to replace the where in the code that you have gave me but no
luck.
Here is the SQL from the query. Maybe I'm just out of luck.

If you could help Great if not then thanks for all that you have done.

Mike

SELECT tblTransfers.Completed, tblTransfers.[Store#],
tblTransfers.[To/From], tblTransfers.Date, tblTransfers.PluNumber,
tblTransfers.PluDescription, tblTransfers.Qty, tblTransfers.TotalCost,
tblTransfers.TotalRetail, tblTransfers.BuydownAmount,
tblTransfers.BuyDownTotal
FROM tblTransfers
WHERE (((tblTransfers.Completed) Between
IIf([Forms]![TransferComparison]![CompletedOptions1]=3,-1,[Forms]![TransferComparison]![CompletedOptions1])
And [Forms]![TransferComparison]![CompletedOptions1]) AND
((tblTransfers.[Store#])=[Forms]![TransferComparison]![cboStoreName1])
AND
((tblTransfers.[To/From])=IIf([Forms]![TransferComparison]![cboStoreName2]="",0,Mid([Forms]![TransferComparison]![cboStoreName2],InStr([Forms]![TransferComparison]![cboStoreName2],"#")+1)))
AND
((UCase(Format$([Date],"mmmm")))=[Forms]![TransferComparison]![cboMonth])
AND ((Format$([Date],"yyyy"))=[Forms]![TransferComparison]![cboYear]))
ORDER BY tblTransfers.PluNumber;



:

Dirty means there are uncommitted edits in the form (i.e. the record
needs
to be saved.)

The error could mean several things:

a) The current record cannot be saved, e.g. because a required field
is
not
filled in. The solution is to complete the record, or press Esc twice
to
undo it.

b) The form is unbound, i.e. there is nothing in its RecordSource
property.
This is unlikely if it's a continuous form, but the solution would be
to
remove the offending line.

c) It is not editable. If so, you can also remove the line.

Mr Browne
First thanks for your fast response.

I'm having trouble with your code, getting Run-time error 2455
Invalid reference to the property Dirty.

This is what is highlighted - If Me.Dirty Then

Does it matter if its Record Source is a query

:

Execute an Update query statement to set the yes/no field to True
in
the
table. Then requery the form to show the result.

This kind of thing:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim strSql As String

If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET Completed = True " & _
"WHERE Completed = False;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
End If
Set db = Nothing
End Sub

Hello All

Is there a way to set all checkboxes on a Continuous Form to
true.
The code below only sets the first one to true
Me.Transfers1_subform.Controls("Completed") = -1
Click to expand...
Click to expand...
Click to expand...
 
Mr Browne

I have the Where clause I Think ?
Well at least its not updating every row in the table

Moving on I have 2 Continuous forms on a main form
I have a txtboxs that = Sum([Qty]) in the footers of the subforms

I have a txtbox on main form that
=Nz(Transfers1_subform!SumOfQty1,0)-Nz(Transfers2_subform!SumOfQty2,0)
If the subforms have no data I'm getting the value #Error when main form
loads.

Thanks Again Mike



Allen Browne said:
I suspect you still need to add the WHERE clause so you don't update every
row in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Mr. Browne

I think the Run SQL in my way to go does this look ok.
I do need to add code for the cancel error but besides that is this the
correct way
or is there a more correct way

Thanks Mike

Private Sub cmdApplyAll_Click()
Dim strSql As String

If MsgBox("Confirm " & Me.cmdApplyAll.Caption _
& " to " & Form_TransferComparison.StoreNumber1.Value & ".", _
vbYesNoCancel + vbInformation, "Transfers") <> vbYes Then
Exit Sub
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Transfers1 SET Completed = -1 " & _
"WHERE Completed = 0;"

DoCmd.RunSQL strSql
Me.Requery

End If
End Sub

Allen Browne said:
What defines "the records in your form"?
Is it a filter? A link to the main form? Criteria in a query?
You will need to add similar criteria to the WHERE clause of the Update
query.

You will also need to concatenate the value form the controls on the form
into the query string. (Or perhaps that's the WHERE clause you are
talking
about.)

If you can't figure out how to concatenate the values and use the right
delimiters, you could use RunSQL instead of Exeucte. Here's an
explanation
of the difference:
http://allenbrowne.com/ser-60.html

Mr. Browne
I have got it so it runs but, it change's all Completed to -1 in the
table
and i'm only
needing to change the records on the form. know I'm getting Run time
error
3061
Too few parameters. Expected 5.
I was reading and think it has to do with my params in my query. I have
tried to replace the where in the code that you have gave me but no
luck.
Here is the SQL from the query. Maybe I'm just out of luck.

If you could help Great if not then thanks for all that you have done.

Mike

SELECT tblTransfers.Completed, tblTransfers.[Store#],
tblTransfers.[To/From], tblTransfers.Date, tblTransfers.PluNumber,
tblTransfers.PluDescription, tblTransfers.Qty, tblTransfers.TotalCost,
tblTransfers.TotalRetail, tblTransfers.BuydownAmount,
tblTransfers.BuyDownTotal
FROM tblTransfers
WHERE (((tblTransfers.Completed) Between
IIf([Forms]![TransferComparison]![CompletedOptions1]=3,-1,[Forms]![TransferComparison]![CompletedOptions1])
And [Forms]![TransferComparison]![CompletedOptions1]) AND
((tblTransfers.[Store#])=[Forms]![TransferComparison]![cboStoreName1])
AND
((tblTransfers.[To/From])=IIf([Forms]![TransferComparison]![cboStoreName2]="",0,Mid([Forms]![TransferComparison]![cboStoreName2],InStr([Forms]![TransferComparison]![cboStoreName2],"#")+1)))
AND
((UCase(Format$([Date],"mmmm")))=[Forms]![TransferComparison]![cboMonth])
AND ((Format$([Date],"yyyy"))=[Forms]![TransferComparison]![cboYear]))
ORDER BY tblTransfers.PluNumber;



:

Dirty means there are uncommitted edits in the form (i.e. the record
needs
to be saved.)

The error could mean several things:

a) The current record cannot be saved, e.g. because a required field
is
not
filled in. The solution is to complete the record, or press Esc twice
to
undo it.

b) The form is unbound, i.e. there is nothing in its RecordSource
property.
This is unlikely if it's a continuous form, but the solution would be
to
remove the offending line.

c) It is not editable. If so, you can also remove the line.

Mr Browne
First thanks for your fast response.

I'm having trouble with your code, getting Run-time error 2455
Invalid reference to the property Dirty.

This is what is highlighted - If Me.Dirty Then

Does it matter if its Record Source is a query

:

Execute an Update query statement to set the yes/no field to True
in
the
table. Then requery the form to show the result.

This kind of thing:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim strSql As String

If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET Completed = True " & _
"WHERE Completed = False;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
End If
Set db = Nothing
End Sub

Hello All

Is there a way to set all checkboxes on a Continuous Form to
true.
The code below only sets the first one to true
Me.Transfers1_subform.Controls("Completed") = -1
Click to expand...
Click to expand...
Click to expand...
 
Back
Top