Update Parent form Control

[EddWood]

I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of the
order. If a user selects 3 items from a order consisting of 6 items, we
require them to update the status to 'Part Dispatched (ID2) and if all items
have been dispatched to update the status to 'Completed' (ID3)

My concern is that the users might not update the status before they close
the form. So am looking to see how I can update this status by code rather
than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions would
be welcome

Thanks

(Access 2007 FE with SQL2005 BE)

 

[Arvin Meyer [MVP]]

You can count the records in the subform with something like this from the
parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.

 

[EddWood]

Hi Arvin,

The selected items are marked with a checkbox on the subform, so 0 if not
selected -1 is selected.

Regards
Edd

You can count the records in the subform with something like this from the
parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of the
order. If a user selects 3 items from a order consisting of 6 items, we
require them to update the status to 'Part Dispatched (ID2) and if all
items have been dispatched to update the status to 'Completed' (ID3)

My concern is that the users might not update the status before they
close the form. So am looking to see how I can update this status by code
rather than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions
would be welcome

Thanks

(Access 2007 FE with SQL2005 BE)

 

[Arvin Meyer [MVP]]

That would imply that you add items to a table which are not selected,
almost like a paper form. That is grossly inefficient for a database. Never
the less here's your solution:

Add a footer to your subform and add a textbox to the footer. Set them both
to a 0" height so they aren't seen. Set the controlsource of the textbox to:

= Sum([Selection Field Name])

Now let's assume that textbox is named txtSelected. In the form's before
update event use the following code (This is untested air code):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

If x - Me.txtSelected = 0 Then
Me.ComboboxName = 3
Else If x - Me.txtSelected = x Then
Me.ComboboxName = 1
Else
Me.ComboboxName = 2
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

The selected items are marked with a checkbox on the subform, so 0 if not
selected -1 is selected.

Regards
Edd

You can count the records in the subform with something like this from
the parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of the
order. If a user selects 3 items from a order consisting of 6 items, we
require them to update the status to 'Part Dispatched (ID2) and if all
items have been dispatched to update the status to 'Completed' (ID3)

My concern is that the users might not update the status before they
close the form. So am looking to see how I can update this status by
code rather than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions
would be welcome

Thanks

(Access 2007 FE with SQL2005 BE)

 

[EddWood]

No, items are added to the tblinvoicelineitems via a combo box to choose
which products to add to the order. The check box is to select the items to
be dispatched, not to create the order.

That would imply that you add items to a table which are not selected,
almost like a paper form. That is grossly inefficient for a database.
Never the less here's your solution:

Add a footer to your subform and add a textbox to the footer. Set them
both to a 0" height so they aren't seen. Set the controlsource of the
textbox to:

= Sum([Selection Field Name])

Now let's assume that textbox is named txtSelected. In the form's before
update event use the following code (This is untested air code):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

If x - Me.txtSelected = 0 Then
Me.ComboboxName = 3
Else If x - Me.txtSelected = x Then
Me.ComboboxName = 1
Else
Me.ComboboxName = 2
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

The selected items are marked with a checkbox on the subform, so 0 if not
selected -1 is selected.

Regards
Edd

You can count the records in the subform with something like this from
the parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of
the order. If a user selects 3 items from a order consisting of 6
items, we require them to update the status to 'Part Dispatched (ID2)
and if all items have been dispatched to update the status to
'Completed' (ID3)

My concern is that the users might not update the status before they
close the form. So am looking to see how I can update this status by
code rather than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions
would be welcome

Thanks

(Access 2007 FE with SQL2005 BE)

 

[Arvin Meyer [MVP]]

OK, that makes more sense.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

No, items are added to the tblinvoicelineitems via a combo box to choose
which products to add to the order. The check box is to select the items
to be dispatched, not to create the order.

That would imply that you add items to a table which are not selected,
almost like a paper form. That is grossly inefficient for a database.
Never the less here's your solution:

Add a footer to your subform and add a textbox to the footer. Set them
both to a 0" height so they aren't seen. Set the controlsource of the
textbox to:

= Sum([Selection Field Name])

Now let's assume that textbox is named txtSelected. In the form's before
update event use the following code (This is untested air code):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

If x - Me.txtSelected = 0 Then
Me.ComboboxName = 3
Else If x - Me.txtSelected = x Then
Me.ComboboxName = 1
Else
Me.ComboboxName = 2
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

The selected items are marked with a checkbox on the subform, so 0 if
not selected -1 is selected.

Regards
Edd


You can count the records in the subform with something like this from
the parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of
the order. If a user selects 3 items from a order consisting of 6
items, we require them to update the status to 'Part Dispatched (ID2)
and if all items have been dispatched to update the status to
'Completed' (ID3)

My concern is that the users might not update the status before they
close the form. So am looking to see how I can update this status by
code rather than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions
would be welcome

Thanks

(Access 2007 FE with SQL2005 BE)

 

[EddWood]

Hi Arvin,

I have tried this but it does not work.

The issue I have is that the = Sum([check85]) throws an error. and as I have
group totals in the footer already they fail if I put this text box in. Also
as the value stored is either 0 or -1 how can it sum the value?

If x - Me.txtSelected = 0 Then ( Is that meant to be a negative '-' after
the x?)
Me.ComboboxName = 3 (I get this)
Else If x - Me.txtSelected = x Then (but don't get this line and throws an
error stating that the 'Then' should be on first line )
Me.ComboboxName = 1
Else
Me.ComboboxName = 2
End If

If you could advise
Thanks

That would imply that you add items to a table which are not selected,
almost like a paper form. That is grossly inefficient for a database.
Never the less here's your solution:

Add a footer to your subform and add a textbox to the footer. Set them
both to a 0" height so they aren't seen. Set the controlsource of the
textbox to:

= Sum([Selection Field Name])

Now let's assume that textbox is named txtSelected. In the form's before
update event use the following code (This is untested air code):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

If x - Me.txtSelected = 0 Then
Me.ComboboxName = 3
Else If x - Me.txtSelected = x Then
Me.ComboboxName = 1
Else
Me.ComboboxName = 2
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

The selected items are marked with a checkbox on the subform, so 0 if not
selected -1 is selected.

Regards
Edd

You can count the records in the subform with something like this from
the parent form:

Dim x
x = Me.SubformName.Form.RecordsetClone.RecordCount

Before I can suggest a more complete answer, I'd need to know how you
determine which items have been selected in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a form [frmInvoice] with a subform [frmInvoiceLineItems]

On frmInvoice there is a combo box for users to update the status of
the order. If a user selects 3 items from a order consisting of 6
items, we require them to update the status to 'Part Dispatched (ID2)
and if all items have been dispatched to update the status to
'Completed' (ID3)

My concern is that the users might not update the status before they
close the form. So am looking to see how I can update this status by
code rather than relying on the users to remember to do this.

My frmInvoiceLineItems has a checkbox for items that are marked as
dispatched, but am unsure of how to go about this, so any suggestions
would be welcome

Thanks

(Access 2007 FE with SQL2005 BE)