Locking Form based on Value of Field

I am going crazy. Tried some of the samples found here however does not work.
I have a form that is linked to a query. The form is used to enter
appointments. The users enter the account number of the client at the
beginning of the appointment form and the form auto populated some of the
fields and readies them to enter a new record (appointment) for an
appointment they had. In this process of autopopultion some fields for the
new appointment are populated as well. What I want to do is make sure they
can’t enter a new record if a certain logical field is equal to “-1�. It
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

Use the Before Update event of the text box where the user enters the
client's account number to see if the client is active. If the client is not
active, present a mesage box to the user, and undo the form:

Dim varIsActive As Variant

varIsActive = DLookup("[AccountNumber]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

If IsNull(varIsActive) Then
MsgBox "Client Not Fount"
Cancel = True
Me.Undo
ElseIf varIsActive = False then
MsgBox "Client Is Not Active"
Cancel = True
Me.Undo
End If

--
Dave Hargis, Microsoft Access MVP


"QuestionsAcc" wrote:

I am going crazy. Tried some of the samples found here however does not work.
I have a form that is linked to a query. The form is used to enter
appointments. The users enter the account number of the client at the
beginning of the appointment form and the form auto populated some of the
fields and readies them to enter a new record (appointment) for an
appointment they had. In this process of autopopultion some fields for the
new appointment are populated as well. What I want to do is make sure they
can’t enter a new record if a certain logical field is equal to “-1�. It
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

Where do I place the logical field information if it is = -1 then cancel

Klatuu wrote:
Use the Before Update event of the text box where the user enters the
client's account number to see if the client is active. If the client is not
active, present a mesage box to the user, and undo the form:

Dim varIsActive As Variant

varIsActive = DLookup("[AccountNumber]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

If IsNull(varIsActive) Then
MsgBox "Client Not Fount"
Cancel = True
Me.Undo
ElseIf varIsActive = False then
MsgBox "Client Is Not Active"
Cancel = True
Me.Undo
End If

I am going crazy. Tried some of the samples found here however does not work.
I have a form that is linked to a query. The form is used to enter
[quoted text clipped - 6 lines]
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

When you say logical field information, I assume you mean a boolean (Yes/No)
data type field in the table. And I just notices I made an error in the code.

Change to:
varIsActive = DLookup("[LogicalFieldName]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

That would be the name of the boolean field that indicates whether the
client is active. Also, change the table name and field name for the account
number to use your actual field names.

Pardon the error.

--
Dave Hargis, Microsoft Access MVP


"QuestionsAcc" wrote:

Where do I place the logical field information if it is = -1 then cancel

Klatuu wrote:
Use the Before Update event of the text box where the user enters the
client's account number to see if the client is active. If the client is not
active, present a mesage box to the user, and undo the form:

Dim varIsActive As Variant

varIsActive = DLookup("[AccountNumber]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

If IsNull(varIsActive) Then
MsgBox "Client Not Fount"
Cancel = True
Me.Undo
ElseIf varIsActive = False then
MsgBox "Client Is Not Active"
Cancel = True
Me.Undo
End If

I am going crazy. Tried some of the samples found here however does not work.
I have a form that is linked to a query. The form is used to enter
[quoted text clipped - 6 lines]
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

It comes back with an error message that "you cancelled the previous
Operation".

Klatuu wrote:
When you say logical field information, I assume you mean a boolean (Yes/No)
data type field in the table. And I just notices I made an error in the code.

Change to:
varIsActive = DLookup("[LogicalFieldName]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

That would be the name of the boolean field that indicates whether the
client is active. Also, change the table name and field name for the account
number to use your actual field names.

Pardon the error.

Where do I place the logical field information if it is = -1 then cancel

[quoted text clipped - 22 lines]
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

--
Message posted via http://www.accessmonster.com

That probably means there is a problem with a name in the query. When it
can't match a name, it cancels the operation and you get that misleading
error.
Check your names in your code and your query to be sure everything matched up
--
Dave Hargis, Microsoft Access MVP


"QuestionsAcc via AccessMonster.com" wrote:

It comes back with an error message that "you cancelled the previous
Operation".

Klatuu wrote:
When you say logical field information, I assume you mean a boolean (Yes/No)
data type field in the table. And I just notices I made an error in the code.

Change to:
varIsActive = DLookup("[LogicalFieldName]", "ClientTable","[AccountNumber]
= " & Me.txtAccountNumber)

That would be the name of the boolean field that indicates whether the
client is active. Also, change the table name and field name for the account
number to use your actual field names.

Pardon the error.

Where do I place the logical field information if it is = -1 then cancel

[quoted text clipped - 22 lines]
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

--
Message posted via http://www.accessmonster.com

Found the issue, you were correct. Missed the letter "d" on a field.
However, it states datatype mismatch?

Klatuu wrote:
That probably means there is a problem with a name in the query. When it
can't match a name, it cancels the operation and you get that misleading
error.
Check your names in your code and your query to be sure everything matched up
It comes back with an error message that "you cancelled the previous
Operation".
[quoted text clipped - 17 lines]
would be nice to have a message say sorry you can’t use this client because
they are no longer active. Help please.

--
Message posted via http://www.accessmonster.com