Checkbox and button

[Guest]

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a subform of
frmFollow.

At the moment, I am able to create record in frmProducts, exit frmFollow, re
enter, uncheck and recheck Collect, then click on cmdCollect and the correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re enter,
uncheck and recheck....I just want to be able to create the record, check the
box, press the button and correct associated record appears in frmCollections.

Any idea how I can do this? Should the code be on a different event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID, CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Allen Browne]

Assuming this is a bound form, it makes no sense to execute this in the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record (e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no longer
have access to the OldValue of the controls. Therefore you need module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press Delete. The
Delete event fires for each record, but you don't know at that stage whether
the Delete will occur or not. Therefore you need to record an array of all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the deletion is
available.

In short, this kind of approach is probably not going to work well for you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be anything.

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

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

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a subform of
frmFollow.

At the moment, I am able to create record in frmProducts, exit frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re enter,
uncheck and recheck....I just want to be able to create the record, check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID, CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Guest]

Thanks Allen.

The people using this database won't need to delete any records.

Also, the record WILL be saved. Once the person has checked the check box,
the record definitely needs to exist. The checkbox is after a question "Does
this item need to be collected?". If the box is checked (answer=yes) then
the record is required in both tblClaims and tblReturns. frmProducts is based
on tblClaims and frmCollections is based on tblReturns.

Can you suggest anything that may work?

Thanks for the side note, will get rid of the *useless* bit of code!

Assuming this is a bound form, it makes no sense to execute this in the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record (e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no longer
have access to the OldValue of the controls. Therefore you need module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press Delete. The
Delete event fires for each record, but you don't know at that stage whether
the Delete will occur or not. Therefore you need to record an array of all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the deletion is
available.

In short, this kind of approach is probably not going to work well for you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be anything.

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

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

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a subform of
frmFollow.

At the moment, I am able to create record in frmProducts, exit frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re enter,
uncheck and recheck....I just want to be able to create the record, check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID, CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Allen Browne]

I'm not really clear about what aim you need to achieve.

Presumably you are handling returns of some kind. Might there be a simple
solution such as entering a negative value into the same table as where the
items are ordered from, so that summing them still gives the correct results
when the negatives represent returns?

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

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

Thanks Allen.

The people using this database won't need to delete any records.

Also, the record WILL be saved. Once the person has checked the check
box,
the record definitely needs to exist. The checkbox is after a question
"Does
this item need to be collected?". If the box is checked (answer=yes) then
the record is required in both tblClaims and tblReturns. frmProducts is
based
on tblClaims and frmCollections is based on tblReturns.

Can you suggest anything that may work?

Thanks for the side note, will get rid of the *useless* bit of code!

Assuming this is a bound form, it makes no sense to execute this in the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record (e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no
longer
have access to the OldValue of the controls. Therefore you need
module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press Delete.
The
Delete event fires for each record, but you don't know at that stage
whether
the Delete will occur or not. Therefore you need to record an array of
all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the deletion
is
available.

In short, this kind of approach is probably not going to work well for
you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be
anything.

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a subform
of
frmFollow.

At the moment, I am able to create record in frmProducts, exit
frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re
enter,
uncheck and recheck....I just want to be able to create the record,
check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID,
CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Guest]

This is not an ordering database, just a database to handle returns.

A customer calls, we log the call using frmFirst. We follow up the query
(after investigation) using frmFollow and organise collection using
frmCollections. We then analyse the returned product using frmReturns.

I need to create a record using frmFirst, follow it up using frmFollow,
check the box IF the item needs to be collected, then press the Arrange
Collection button to open frmCollections at the associated record.

I'm not really clear about what aim you need to achieve.

Presumably you are handling returns of some kind. Might there be a simple
solution such as entering a negative value into the same table as where the
items are ordered from, so that summing them still gives the correct results
when the negatives represent returns?

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

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

Thanks Allen.

The people using this database won't need to delete any records.

Also, the record WILL be saved. Once the person has checked the check
box,
the record definitely needs to exist. The checkbox is after a question
"Does
this item need to be collected?". If the box is checked (answer=yes) then
the record is required in both tblClaims and tblReturns. frmProducts is
based
on tblClaims and frmCollections is based on tblReturns.

Can you suggest anything that may work?

Thanks for the side note, will get rid of the *useless* bit of code!

Assuming this is a bound form, it makes no sense to execute this in the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record (e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no
longer
have access to the OldValue of the controls. Therefore you need
module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press Delete.
The
Delete event fires for each record, but you don't know at that stage
whether
the Delete will occur or not. Therefore you need to record an array of
all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the deletion
is
available.

In short, this kind of approach is probably not going to work well for
you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be
anything.

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a subform
of
frmFollow.

At the moment, I am able to create record in frmProducts, exit
frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re
enter,
uncheck and recheck....I just want to be able to create the record,
check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID,
CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Allen Browne]

Okay, that makes sense. As it proceeds through your process, you log the
details, and later generate the return record.

It is therefore important to know which return record(s) came from which
frmFirst record, so you can track which ones have not been done, and whether
it has been done multiple times. The table behind frmFollow will therefore
have a foreign key to the frmFirst table's record. This means you don't need
a check box in frmFirst: the approval *is* the presence of a matching record
in frmFollow, and you are free of the maintenance issues that were bothering
me.

Once you are free of that check box, you are also free of the original
problem of having to close and re-enter the form.

If frmCollections is open at the time, you can Requery it so the new record
shows up.

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

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

This is not an ordering database, just a database to handle returns.

A customer calls, we log the call using frmFirst. We follow up the query
(after investigation) using frmFollow and organise collection using
frmCollections. We then analyse the returned product using frmReturns.

I need to create a record using frmFirst, follow it up using frmFollow,
check the box IF the item needs to be collected, then press the Arrange
Collection button to open frmCollections at the associated record.

I'm not really clear about what aim you need to achieve.

Presumably you are handling returns of some kind. Might there be a simple
solution such as entering a negative value into the same table as where
the
items are ordered from, so that summing them still gives the correct
results
when the negatives represent returns?

Thanks Allen.

The people using this database won't need to delete any records.

Also, the record WILL be saved. Once the person has checked the check
box,
the record definitely needs to exist. The checkbox is after a question
"Does
this item need to be collected?". If the box is checked (answer=yes)
then
the record is required in both tblClaims and tblReturns. frmProducts is
based
on tblClaims and frmCollections is based on tblReturns.

Can you suggest anything that may work?

Thanks for the side note, will get rid of the *useless* bit of code!

:

Assuming this is a bound form, it makes no sense to execute this in
the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record
(e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no
longer
have access to the OldValue of the controls. Therefore you need
module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press
Delete.
The
Delete event fires for each record, but you don't know at that stage
whether
the Delete will occur or not. Therefore you need to record an array of
all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the
deletion
is
available.

In short, this kind of approach is probably not going to work well for
you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be
anything.

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a
subform
of
frmFollow.

At the moment, I am able to create record in frmProducts, exit
frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re
enter,
uncheck and recheck....I just want to be able to create the record,
check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different
event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID,
CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Guest]

Okay thanks!

I have seen this requery about before, but don't know how to do it.

Okay, that makes sense. As it proceeds through your process, you log the
details, and later generate the return record.

It is therefore important to know which return record(s) came from which
frmFirst record, so you can track which ones have not been done, and whether
it has been done multiple times. The table behind frmFollow will therefore
have a foreign key to the frmFirst table's record. This means you don't need
a check box in frmFirst: the approval *is* the presence of a matching record
in frmFollow, and you are free of the maintenance issues that were bothering
me.

Once you are free of that check box, you are also free of the original
problem of having to close and re-enter the form.

If frmCollections is open at the time, you can Requery it so the new record
shows up.

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

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

This is not an ordering database, just a database to handle returns.

A customer calls, we log the call using frmFirst. We follow up the query
(after investigation) using frmFollow and organise collection using
frmCollections. We then analyse the returned product using frmReturns.

I need to create a record using frmFirst, follow it up using frmFollow,
check the box IF the item needs to be collected, then press the Arrange
Collection button to open frmCollections at the associated record.

I'm not really clear about what aim you need to achieve.

Presumably you are handling returns of some kind. Might there be a simple
solution such as entering a negative value into the same table as where
the
items are ordered from, so that summing them still gives the correct
results
when the negatives represent returns?


Thanks Allen.

The people using this database won't need to delete any records.

Also, the record WILL be saved. Once the person has checked the check
box,
the record definitely needs to exist. The checkbox is after a question
"Does
this item need to be collected?". If the box is checked (answer=yes)
then
the record is required in both tblClaims and tblReturns. frmProducts is
based
on tblClaims and frmCollections is based on tblReturns.

Can you suggest anything that may work?

Thanks for the side note, will get rid of the *useless* bit of code!

:

Assuming this is a bound form, it makes no sense to execute this in
the
AfterUpdate event of the control.

The record has not been saved yet, and it may never be saved, e.g.: a
validation rule may not be met, or the user might undo the record
(e.g.
press Esc.)

If you are going to use this kind of approach you need to use the
AfterUpdate event of the *form*, not control. In that event, you no
longer
have access to the OldValue of the controls. Therefore you need
module-level
variables that are set in Form_BeforeUpdate, so you can read them in
Form_AfterUpdate.

You will also need to handle the deletion of records. This gets more
invovled, as a user can select multiple records at once an press
Delete.
The
Delete event fires for each record, but you don't know at that stage
whether
the Delete will occur or not. Therefore you need to record an array of
all
the details of the records being deleted in the Delete event, and then
process them in the AfterDelConfirm event when the Status of the
deletion
is
available.

In short, this kind of approach is probably not going to work well for
you.

On a side note, the line:
DoCmd.Save acDefault, tblReturns
does not save the record in the form.
It saves design changes.
To whatever object is the default.
And as tblReturns seems to be an undefined variable, that could be
anything.

I have posted code below that I am using on my checkbox(Collect) and
associated button(cmdCollect) that are both in frmProducts, a
subform
of
frmFollow.

At the moment, I am able to create record in frmProducts, exit
frmFollow,
re
enter, uncheck and recheck Collect, then click on cmdCollect and the
correct
record appears in frmCollections.

However, that is the ONLY way. I don't want to have to exit , re
enter,
uncheck and recheck....I just want to be able to create the record,
check
the
box, press the button and correct associated record appears in
frmCollections.

Any idea how I can do this? Should the code be on a different
event?

TIA

Private Sub Collect_AfterUpdate()
If Me.Collect = True Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblReturns ( ClaimID,
CollectAndReplace )SELECT
tblClaims.pkClaimID, tblClaims.ReplacementOrder FROM tblClaims"
DoCmd.SetWarnings True
End If
Me.cmdCollect.Enabled = Nz(Me.Collect.Value, True)
End Sub

Private Sub cmdCollect_Click()

On Error GoTo Err_cmdCollect_Click

Dim intCurrentContact
intCurrentContact = Me![pkClaimID]

DoCmd.Save acDefault, tblReturns
DoCmd.Close acForm, "frmFollow"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCollections"
stLinkCriteria = "[pkClaimID]=" & intCurrentContact

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCollect_Click:
Exit Sub

Err_cmdCollect_Click:
MsgBox Err.Description
Resume Exit_cmdCollect_Click

End Sub

 

[Allen Browne]

Main form:
Forms!Form1.Requery

Subform:
Forms!Form1.Form2.Form.Requery