Check box creates a new record when clicked??? Huh???

[JK]

I’m going to repost this because I don’t think my last post was clear.

In Access03 I have a workorders database. I originally used the MS
Workorders Example database to start with. The layout is still much the same.
I have a form, “Workorders by Customer†that displays some customer
information with a continuous subform listing the workorders assigned to that
customer. On the form “Workorders by Customer†there is a View All button
that opens the “Workorders†form without a filter – on that same form; there
is an Add New button that opens the “Workorders†form in Data Entry mode.
Each field/record in the subform has a double-click event that opens the
“Workorders†form to a specific record.

That said; let me explain the “Workorders†form. There is another Add New
button. There is also a check box call FieldRepair. If the workorder is for
work performed by a technician off-site (in the field) this check box is
selected (selected by default.) If the work is done in-house, the user must
deselect the check box. When the check box is deselected, another filed
called DatePickedUp is enabled and cleared of its default value.

The DatePickedUp field has a default value set to #1/1/1900#. This is only
because I have a report that when run displays all work completed in-house
with equipment that has not been picked up by the customer yet. So, if
DateFinished is not null (the actual work is done) and the DatePickedUp field
is null then display the record in my report.

Someone helped me with the following code some time ago. If the check box
FieldRepair is selected on the Workorders form (which again – it is by
default), then DatePickedUp is disabled and the default value (#1/1/1900#) is
set to that field. If the check box FieldRepair is deselected, the
DatePickedUp field is enabled and the default value is cleared allowing the
user to enter a date when the equipment is actually picked up by the
customer.

The code that makes it work is posted below.

The check box FieldRepair has an after update event and an On Click Event.
And, the Workorders form has a Current Event which calls the
FieldRepair_Afterupdate Event. All code is posted below.

My problem, when I select either the Add New button on the Workorders by
Customer form OR the Add New button on the Workorders form and then attempt
to deselect the FieldRepair check box, it automatically creates a new record
and the check box never deselects. If I click the check box four times, I’ll
find four new workorders assigned to the customer. I don’t get it – and it’s
driving me nuts. No one is probably going to read this because it’s so long,
but I don’t know what else to do… ïŒ Please help… Thx….

‘==============================
Private Sub FieldRepair_AfterUpdate()

'Grey Date Pickedup Field if Checked (FieldRepair)

Dim bLock As Boolean
bLock = Nz(Me.FieldRepair, True)
Me.[DatePickedUp].Locked = bLock

If Me!FieldRepair = False Then
Forms!Workorders![DatePickedUp] = Null

Else
End If
End Sub
‘==============================
Private Sub FieldRepair_Click()

' Declare a variable to hold the current record's key.
Dim vMyControlValue As Variant

' Save the current record's key.
vMyControlValue = Me.WorkorderID

' Requery the form.
Me.Requery

' Find the record whose key we saved.
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

' Auto insert 1/1/1900 into the DatePickedUp Field if (FieldRepair)is
checked...
If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
Else
' If you need code to achieve your other alternative,
' put it here. If not, you can delete the "Else" statement.
End If
End Sub
‘==============================

 

[Klatuu]

You are never returning to the record you were on before the Requery
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

Does not reposition you to the record, it only finds it. What you need is:
With Me.RecordsetClone
.FindFirst "WorkorderID=" & vMyControlValue
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

I’m going to repost this because I don’t think my last post was clear.

In Access03 I have a workorders database. I originally used the MS
Workorders Example database to start with. The layout is still much the same.
I have a form, “Workorders by Customer†that displays some customer
information with a continuous subform listing the workorders assigned to that
customer. On the form “Workorders by Customer†there is a View All button
that opens the “Workorders†form without a filter – on that same form; there
is an Add New button that opens the “Workorders†form in Data Entry mode.
Each field/record in the subform has a double-click event that opens the
“Workorders†form to a specific record.

That said; let me explain the “Workorders†form. There is another Add New
button. There is also a check box call FieldRepair. If the workorder is for
work performed by a technician off-site (in the field) this check box is
selected (selected by default.) If the work is done in-house, the user must
deselect the check box. When the check box is deselected, another filed
called DatePickedUp is enabled and cleared of its default value.

The DatePickedUp field has a default value set to #1/1/1900#. This is only
because I have a report that when run displays all work completed in-house
with equipment that has not been picked up by the customer yet. So, if
DateFinished is not null (the actual work is done) and the DatePickedUp field
is null then display the record in my report.

Someone helped me with the following code some time ago. If the check box
FieldRepair is selected on the Workorders form (which again – it is by
default), then DatePickedUp is disabled and the default value (#1/1/1900#) is
set to that field. If the check box FieldRepair is deselected, the
DatePickedUp field is enabled and the default value is cleared allowing the
user to enter a date when the equipment is actually picked up by the
customer.

The code that makes it work is posted below.

The check box FieldRepair has an after update event and an On Click Event.
And, the Workorders form has a Current Event which calls the
FieldRepair_Afterupdate Event. All code is posted below.

My problem, when I select either the Add New button on the Workorders by
Customer form OR the Add New button on the Workorders form and then attempt
to deselect the FieldRepair check box, it automatically creates a new record
and the check box never deselects. If I click the check box four times, I’ll
find four new workorders assigned to the customer. I don’t get it – and it’s
driving me nuts. No one is probably going to read this because it’s so long,
but I don’t know what else to do… ïŒ Please help… Thx….

‘==============================
Private Sub FieldRepair_AfterUpdate()

'Grey Date Pickedup Field if Checked (FieldRepair)

Dim bLock As Boolean
bLock = Nz(Me.FieldRepair, True)
Me.[DatePickedUp].Locked = bLock

If Me!FieldRepair = False Then
Forms!Workorders![DatePickedUp] = Null

Else
End If
End Sub
‘==============================
Private Sub FieldRepair_Click()

' Declare a variable to hold the current record's key.
Dim vMyControlValue As Variant

' Save the current record's key.
vMyControlValue = Me.WorkorderID

' Requery the form.
Me.Requery

' Find the record whose key we saved.
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

' Auto insert 1/1/1900 into the DatePickedUp Field if (FieldRepair)is
checked...
If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
Else
' If you need code to achieve your other alternative,
' put it here. If not, you can delete the "Else" statement.
End If
End Sub
‘==============================

 

[JK]

Thank you for taking the time to read my first post. Painful, I'm sure.
I'm not able to make your suggestion work - although I'm sure you're correct
as far as the problem is concerned.

I read a little in the help files about recordsetcode, findfirst, etc and I
changed my code to look like this. When I try to check the check box on the
Workorders form, I get the Record not found message.

Am I way off base here? Am I coding this incorrectly. I tried to insert your
suggested code into my existing code as many possible ways as I could and was
still unable to make it work?


Private Sub FieldRepair_Click()

Dim rst As Recordset
Dim strSearchName As Variant

Set rst = Me.RecordsetClone
strSearchName = Me.WorkorderID

Me.Requery

rst.FindFirst "WorkorderID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
End If
End Sub

You are never returning to the record you were on before the Requery
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

Does not reposition you to the record, it only finds it. What you need is:
With Me.RecordsetClone
.FindFirst "WorkorderID=" & vMyControlValue
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

I’m going to repost this because I don’t think my last post was clear.

In Access03 I have a workorders database. I originally used the MS
Workorders Example database to start with. The layout is still much the same.
I have a form, “Workorders by Customer†that displays some customer
information with a continuous subform listing the workorders assigned to that
customer. On the form “Workorders by Customer†there is a View All button
that opens the “Workorders†form without a filter – on that same form; there
is an Add New button that opens the “Workorders†form in Data Entry mode.
Each field/record in the subform has a double-click event that opens the
“Workorders†form to a specific record.

That said; let me explain the “Workorders†form. There is another Add New
button. There is also a check box call FieldRepair. If the workorder is for
work performed by a technician off-site (in the field) this check box is
selected (selected by default.) If the work is done in-house, the user must
deselect the check box. When the check box is deselected, another filed
called DatePickedUp is enabled and cleared of its default value.

The DatePickedUp field has a default value set to #1/1/1900#. This is only
because I have a report that when run displays all work completed in-house
with equipment that has not been picked up by the customer yet. So, if
DateFinished is not null (the actual work is done) and the DatePickedUp field
is null then display the record in my report.

Someone helped me with the following code some time ago. If the check box
FieldRepair is selected on the Workorders form (which again – it is by
default), then DatePickedUp is disabled and the default value (#1/1/1900#) is
set to that field. If the check box FieldRepair is deselected, the
DatePickedUp field is enabled and the default value is cleared allowing the
user to enter a date when the equipment is actually picked up by the
customer.

The code that makes it work is posted below.

The check box FieldRepair has an after update event and an On Click Event.
And, the Workorders form has a Current Event which calls the
FieldRepair_Afterupdate Event. All code is posted below.

My problem, when I select either the Add New button on the Workorders by
Customer form OR the Add New button on the Workorders form and then attempt
to deselect the FieldRepair check box, it automatically creates a new record
and the check box never deselects. If I click the check box four times, I’ll
find four new workorders assigned to the customer. I don’t get it – and it’s
driving me nuts. No one is probably going to read this because it’s so long,
but I don’t know what else to do… ïŒ Please help… Thx….

‘==============================
Private Sub FieldRepair_AfterUpdate()

'Grey Date Pickedup Field if Checked (FieldRepair)

Dim bLock As Boolean
bLock = Nz(Me.FieldRepair, True)
Me.[DatePickedUp].Locked = bLock

If Me!FieldRepair = False Then
Forms!Workorders![DatePickedUp] = Null

Else
End If
End Sub
‘==============================
Private Sub FieldRepair_Click()

' Declare a variable to hold the current record's key.
Dim vMyControlValue As Variant

' Save the current record's key.
vMyControlValue = Me.WorkorderID

' Requery the form.
Me.Requery

' Find the record whose key we saved.
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

' Auto insert 1/1/1900 into the DatePickedUp Field if (FieldRepair)is
checked...
If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
Else
' If you need code to achieve your other alternative,
' put it here. If not, you can delete the "Else" statement.
End If
End Sub
‘==============================

 

[Klatuu]

What data type is WorkorderID?
As written, you are trying to search for a numeric value.
If WorkorderID is a text field, the syntax should be:

rst.FindFirst "WorkorderID = """ & strSearchName & """"

And you should dim strSearchName the same data type as the workodrderID field.

Actually, the code you have in the Click event really belongs in the After
Update event with your other code.
--
Dave Hargis, Microsoft Access MVP

Thank you for taking the time to read my first post. Painful, I'm sure.
I'm not able to make your suggestion work - although I'm sure you're correct
as far as the problem is concerned.

I read a little in the help files about recordsetcode, findfirst, etc and I
changed my code to look like this. When I try to check the check box on the
Workorders form, I get the Record not found message.

Am I way off base here? Am I coding this incorrectly. I tried to insert your
suggested code into my existing code as many possible ways as I could and was
still unable to make it work?


Private Sub FieldRepair_Click()

Dim rst As Recordset
Dim strSearchName As Variant

Set rst = Me.RecordsetClone
strSearchName = Me.WorkorderID

Me.Requery

rst.FindFirst "WorkorderID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
End If
End Sub

You are never returning to the record you were on before the Requery
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

Does not reposition you to the record, it only finds it. What you need is:
With Me.RecordsetClone
.FindFirst "WorkorderID=" & vMyControlValue
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

I’m going to repost this because I don’t think my last post was clear.

In Access03 I have a workorders database. I originally used the MS
Workorders Example database to start with. The layout is still much the same.
I have a form, “Workorders by Customer†that displays some customer
information with a continuous subform listing the workorders assigned to that
customer. On the form “Workorders by Customer†there is a View All button
that opens the “Workorders†form without a filter – on that same form; there
is an Add New button that opens the “Workorders†form in Data Entry mode.
Each field/record in the subform has a double-click event that opens the
“Workorders†form to a specific record.

That said; let me explain the “Workorders†form. There is another Add New
button. There is also a check box call FieldRepair. If the workorder is for
work performed by a technician off-site (in the field) this check box is
selected (selected by default.) If the work is done in-house, the user must
deselect the check box. When the check box is deselected, another filed
called DatePickedUp is enabled and cleared of its default value.

The DatePickedUp field has a default value set to #1/1/1900#. This is only
because I have a report that when run displays all work completed in-house
with equipment that has not been picked up by the customer yet. So, if
DateFinished is not null (the actual work is done) and the DatePickedUp field
is null then display the record in my report.

Someone helped me with the following code some time ago. If the check box
FieldRepair is selected on the Workorders form (which again – it is by
default), then DatePickedUp is disabled and the default value (#1/1/1900#) is
set to that field. If the check box FieldRepair is deselected, the
DatePickedUp field is enabled and the default value is cleared allowing the
user to enter a date when the equipment is actually picked up by the
customer.

The code that makes it work is posted below.

The check box FieldRepair has an after update event and an On Click Event.
And, the Workorders form has a Current Event which calls the
FieldRepair_Afterupdate Event. All code is posted below.

My problem, when I select either the Add New button on the Workorders by
Customer form OR the Add New button on the Workorders form and then attempt
to deselect the FieldRepair check box, it automatically creates a new record
and the check box never deselects. If I click the check box four times, I’ll
find four new workorders assigned to the customer. I don’t get it – and it’s
driving me nuts. No one is probably going to read this because it’s so long,
but I don’t know what else to do… ïŒ Please help… Thx….

‘==============================
Private Sub FieldRepair_AfterUpdate()

'Grey Date Pickedup Field if Checked (FieldRepair)

Dim bLock As Boolean
bLock = Nz(Me.FieldRepair, True)
Me.[DatePickedUp].Locked = bLock

If Me!FieldRepair = False Then
Forms!Workorders![DatePickedUp] = Null

Else
End If
End Sub
‘==============================
Private Sub FieldRepair_Click()

' Declare a variable to hold the current record's key.
Dim vMyControlValue As Variant

' Save the current record's key.
vMyControlValue = Me.WorkorderID

' Requery the form.
Me.Requery

' Find the record whose key we saved.
Me.Recordset.FindFirst "WorkorderID=" & vMyControlValue

' Auto insert 1/1/1900 into the DatePickedUp Field if (FieldRepair)is
checked...
If Me!FieldRepair = True Then
Forms!Workorders![DatePickedUp] = #1/1/1900#
Else
' If you need code to achieve your other alternative,
' put it here. If not, you can delete the "Else" statement.
End If
End Sub
‘==============================