Update This Record command button

[Uschi via AccessMonster.com]

I am working on a database (Assess 2002) which keeps a history of ownership
for a homeowners association. Each homeowner owns a share in this corporation.
The fields on the form are as follows: Space No., Certificate No., Date
Issued, Membership Name, Current Member Listing (checkbox), Reason for Change
in Ownership (list box), Maintenance Fee, Rent and Date Certificate
Surrendered.

I have an Update This Record (Add Record) command button. When a record is
updated the new record already contains certain carryover information: Space
No., Current Member Listing (check box is checked), Maintanance Fee and Rent.


The user needs to do 3 things with a record when that owner surrenders a
certificate: 1) fill in the Date Certificate Surrendered field, 2) click the
Update This Record command button and 3) fill in the new owner information on
the new record.

This problem is this: it is far too easy to go straight to the Update This
Record command button which, of course, leaves the Date Certificate
Surrendered field blank.

When the user clicks the Update This Record command button without completing
the Date Certificate Surrendered field, I would like a message box to pop up
telling the user that the record cannot be updated until the Date Certificate
Surrendered field is completed. Is this possible?

Your help will be greatly appreciated.

 

[Bob Hairgrove]

I am working on a database (Assess 2002) which keeps a history of ownership
for a homeowners association. Each homeowner owns a share in this corporation.
The fields on the form are as follows: Space No., Certificate No., Date
Issued, Membership Name, Current Member Listing (checkbox), Reason for Change
in Ownership (list box), Maintenance Fee, Rent and Date Certificate
Surrendered.

I have an Update This Record (Add Record) command button. When a record is
updated the new record already contains certain carryover information: Space
No., Current Member Listing (check box is checked), Maintanance Fee and Rent.


The user needs to do 3 things with a record when that owner surrenders a
certificate: 1) fill in the Date Certificate Surrendered field, 2) clickthe
Update This Record command button and 3) fill in the new owner information on
the new record.

This problem is this: it is far too easy to go straight to the Update This
Record command button which, of course, leaves the Date Certificate
Surrendered field blank.

When the user clicks the Update This Record command button without completing
the Date Certificate Surrendered field, I would like a message box to pop up
telling the user that the record cannot be updated until the Date Certificate
Surrendered field is completed. Is this possible?

Your help will be greatly appreciated.

If you tell us what code is run when the button is clicked, we can probably add
a line or three to pop up a message and exit the procedure early when the"Date
Certificate Surrendered" control is empty. However, it would also be niceto see
the table and query structures behind the form's data (look in the form's
Recordsource property for that).

 

[Uschi via AccessMonster.com]

The code for the Update This Record button when clicked is as follows:

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Current.Value = False
tmpMaint = [Maintenance Fee].Value
tmpRent = Rent
tmpSpNo = SpNo.Value
DoCmd.GoToRecord , , acNewRec
SpNo.Value = tmpSpNo
Current.Value = True
[Maintenance Fee].Value = tmpMaint
Rent = tmpRent

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Also, there is only one table behind this form.

I hope this information is what you need.

 

[Bob Hairgrove]

The code for the Update This Record button when clicked is as follows:

Try this (additions inline):

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

If Len([Date Certificate Surrendered] & "") = 0 Then
MsgBox "Please enter the date this dertificate was surrendered " & _
"before updating the record.", vbExclamation, "No Date Surrendered"
Exit Sub
End If

Current.Value = False
tmpMaint = [Maintenance Fee].Value
tmpRent = Rent
tmpSpNo = SpNo.Value
DoCmd.GoToRecord , , acNewRec
SpNo.Value = tmpSpNo
Current.Value = True
[Maintenance Fee].Value = tmpMaint
Rent = tmpRent

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Also, there is only one table behind this form.

The reason for this convoluted syntax:

If Len([Date Certificate Surrendered] & "") = 0 Then

is because you haven't told us whether or not that field is of type text,
date/time, numeric or maybe something else, and whether or not it is nullable.
Concatenating a null value to an empty string is one of the few operations which
does not propagate null, as the result is an empty string. If [Date Certificate
Surrendered] is numeric, however, it will fail if it contains the value 0(which
it might depending on what the default value is).

The equivalent result could also be obtained by using the Nz() function:

If Nz([Date Certificate Surrendered], 0) = 0 Then
'etc.

If we knew that [Date Certificate Surrendered] might be null (i.e., the column
is nullable, or the "Required" property is set to "No"), then you could also
write:

If IsNull([Date Certificate Surrendered]) Then
'etc.

HTH

 

[Uschi via AccessMonster.com]

Dear Bob,

You did it! I am mad with joy (Doctor Zhivago).

Both of your codes worked. The Date Surrendered field is Date/Time; that
being the case, should I be using one code over the other for the long run?

Thank you so much for helping me. I am very grateful.

Uschi

 

[Bob Hairgrove]

Dear Bob,

You did it! I am mad with joy (Doctor Zhivago).

One of my all-time favorite movies!

Both of your codes worked. The Date Surrendered field is Date/Time; that
being the case, should I be using one code over the other for the long run?

It doesn't really matter.

Thank you so much for helping me. I am very grateful.

Uschi

You're welcome!