Help with form code - If / then statement

[p-rat]

I have an Access application someone else created and now have a
requirement from an end-user. I currently have a field called
"dbo_Field_Ticket_Header_Field_Ticket_Number". It's datatype is
integer. On the before update event there is this code:


Private Sub
dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate(Cancel As
Integer)
Dim lngField_Ticket_Number As Long, strCriteria As String
If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
lngField_Ticket_Number =
Me.dbo_Field_Ticket_Header_Field_Ticket_Number
strCriteria = "[Field_Ticket_Number] = " &
lngField_Ticket_Number
If SearchTableByCriteria("dbo_Field_Ticket_Header",
strCriteria) Then
MsgBox "That ticket number already exists in the database.
Please enter another ticket number.", vbExclamation
Cancel = True
End If
Else
MsgBox "INVALID TICKET NUMBER.", vbExclamation
Cancel = True
End If

End Sub

I'm want to be able to either enter in a string "SHOP TIME" to collect
our shop time or down time of production. If I can't enter in this
string I would like to be able to enter in a universal Field Ticket
Number such as '500' for this data collection. Currently if you try to
enter in a string it comes back with error. Also, if '500' is already
in the database and you try to re-enter it; it comes back with an
error.

Would anyone know how to revise this code to either accept a string
such as Shop Time or more than one ticket of the same number?

I guess one more thing I would like to happen is once this is revised
and I can enter one of these two things is that once updated it would
'black out' or not allow entry into about 10 other fields. Does anyone
know how to accomplish this?

 

[Steve Sanford]

Comments in-line

I have an Access application someone else created and now have a
requirement from an end-user. I currently have a field called

What is the requirement?

"dbo_Field_Ticket_Header_Field_Ticket_Number". It's datatype is
integer. On the before update event there is this code:


Private Sub
dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate(Cancel As
Integer)
Dim lngField_Ticket_Number As Long, strCriteria As String
If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
lngField_Ticket_Number =
Me.dbo_Field_Ticket_Header_Field_Ticket_Number
strCriteria = "[Field_Ticket_Number] = " &
lngField_Ticket_Number
If SearchTableByCriteria("dbo_Field_Ticket_Header",
strCriteria) Then
MsgBox "That ticket number already exists in the database.
Please enter another ticket number.", vbExclamation
Cancel = True
End If
Else
MsgBox "INVALID TICKET NUMBER.", vbExclamation
Cancel = True
End If

End Sub

This code only validates the ticket number entered. It checks that a number
was entered (although you can't enter text in a number field) and that the
number is a unique number.

I'm want to be able to either enter in a string "SHOP TIME" to collect
our shop time or down time of production. If I can't enter in this

Again, you cannot enter a string (text) into a field defined as an integer.

string I would like to be able to enter in a universal Field Ticket
Number such as '500' for this data collection. Currently if you try to
enter in a string it comes back with error. Also, if '500' is already
in the database and you try to re-enter it; it comes back with an
error.

You really need to rethink what you are trying to do. If you allow non
unique ticket numbers, you will probably have to edit your queries, reports
and possibly the forms.

Would anyone know how to revise this code to either accept a string
such as Shop Time or more than one ticket of the same number?

I would use a different field - either one that is in the database already
or add another field that would be empty or have "SHOP TIME" entered.

You could create a query to find a field with entries of "SHOP TIME" for
forms/reports.

I guess one more thing I would like to happen is once this is revised
and I can enter one of these two things is that once updated it would
'black out' or not allow entry into about 10 other fields. Does anyone
know how to accomplish this?

In the form open event and the form current event, set the field's enabled
property to false for each field you want to be "locked".