Adding records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
 
In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:
 
Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

Klatuu said:
In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:
I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
 
It will cause a record to be written if changes have been made. Modify your
code to correct this problem:

Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
Else
If Me.Dirty And IsNull(Me.txtCustID) Then
Cancel = True
End If
End If

I am also curious about what syntax error there is in the DLookup. The only
thing I see is that if your Customer ID is text instead of numeric, then it
would need to be this:
DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'")

If that is the case, remember I said I was assuming a numeric value. If it
is something else, I don't see it.
tezza said:
Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

Klatuu said:
In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:
I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
Click to expand...
 
You are correct....the customer ID is text.
The code change I made marries to yours exactly.
I have amended the code for the extra bit but when I now hit the Close
button nothing happens.
It would appear that I can't close the form when the Customer ID is null.
HELP!!!!
Here is the code....

Private Sub Company_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Company ID]", "Customers", "[Company ID] = '" &
Me.Company_ID & "'")) Then
MsgBox "Duplicate Customer - Please use a unique code", vbOKOnly,
"Serious Error"
Cancel = True
Else
If Me.Dirty And IsNull(Me.Company_ID) Then
Cancel = True
End If
End If
End Sub

Klatuu said:
It will cause a record to be written if changes have been made. Modify your
code to correct this problem:

Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
Else
If Me.Dirty And IsNull(Me.txtCustID) Then
Cancel = True
End If
End If

I am also curious about what syntax error there is in the DLookup. The only
thing I see is that if your Customer ID is text instead of numeric, then it
would need to be this:
DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'")

If that is the case, remember I said I was assuming a numeric value. If it
is something else, I don't see it.
tezza said:
Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

Klatuu said:
In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:

I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
Click to expand...
 
HHHHHHMMMMMMMMMM
I think I understand the problem. The only other thing I can think of would
be to put the test for Me.Dirty and IsNull(Me.Company_ID) in the form's
Unload event. The Close event doesn't have a Cancel in it. So, try taking
that new part out, and putting it in the form Unload Event and see what
happens.

tezza said:
You are correct....the customer ID is text.
The code change I made marries to yours exactly.
I have amended the code for the extra bit but when I now hit the Close
button nothing happens.
It would appear that I can't close the form when the Customer ID is null.
HELP!!!!
Here is the code....

Private Sub Company_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Company ID]", "Customers", "[Company ID] = '" &
Me.Company_ID & "'")) Then
MsgBox "Duplicate Customer - Please use a unique code", vbOKOnly,
"Serious Error"
Cancel = True
Else
If Me.Dirty And IsNull(Me.Company_ID) Then
Cancel = True
End If
End If
End Sub

Klatuu said:
It will cause a record to be written if changes have been made. Modify your
code to correct this problem:

Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
Else
If Me.Dirty And IsNull(Me.txtCustID) Then
Cancel = True
End If
End If

I am also curious about what syntax error there is in the DLookup. The only
thing I see is that if your Customer ID is text instead of numeric, then it
would need to be this:
DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'")

If that is the case, remember I said I was assuming a numeric value. If it
is something else, I don't see it.
tezza said:
Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

:

In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:

I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
Click to expand...
 
Hi
Have done that.
Get an Access message telling me that the Close action was cancelled.
I can't close the form.
Funnily enough, if I click on the Add Customer button, all fields are
blanked as you would expect and if I press the Close button then the Form
closes. If I check the Customer table it is ok, there isn't an empty record.
However, if I click on the Add Customer button, type in a duplicate code,
get the error message about duplicates, highlight the customer code that I
entered, press Del followed by Close then when I check the Customer table it
now contains a blank record?
What is the difference between the two?


Klatuu said:
HHHHHHMMMMMMMMMM
I think I understand the problem. The only other thing I can think of would
be to put the test for Me.Dirty and IsNull(Me.Company_ID) in the form's
Unload event. The Close event doesn't have a Cancel in it. So, try taking
that new part out, and putting it in the form Unload Event and see what
happens.

tezza said:
You are correct....the customer ID is text.
The code change I made marries to yours exactly.
I have amended the code for the extra bit but when I now hit the Close
button nothing happens.
It would appear that I can't close the form when the Customer ID is null.
HELP!!!!
Here is the code....

Private Sub Company_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Company ID]", "Customers", "[Company ID] = '" &
Me.Company_ID & "'")) Then
MsgBox "Duplicate Customer - Please use a unique code", vbOKOnly,
"Serious Error"
Cancel = True
Else
If Me.Dirty And IsNull(Me.Company_ID) Then
Cancel = True
End If
End If
End Sub

Klatuu said:
It will cause a record to be written if changes have been made. Modify your
code to correct this problem:

Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
Else
If Me.Dirty And IsNull(Me.txtCustID) Then
Cancel = True
End If
End If

I am also curious about what syntax error there is in the DLookup. The only
thing I see is that if your Customer ID is text instead of numeric, then it
would need to be this:
DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'")

If that is the case, remember I said I was assuming a numeric value. If it
is something else, I don't see it.
:

Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

:

In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:

I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
Click to expand...
 
The difference is that in the first case, the form is not dirty, but in the
second case, you have entered data, and the form is dirty. Try adding the
Me.Undo below. I think that should cure the problem.

If Me.Dirty And IsNull(Me.Company_ID) Then
Cancel = True
Me.Undo
End If


tezza said:
Hi
Have done that.
Get an Access message telling me that the Close action was cancelled.
I can't close the form.
Funnily enough, if I click on the Add Customer button, all fields are
blanked as you would expect and if I press the Close button then the Form
closes. If I check the Customer table it is ok, there isn't an empty record.
However, if I click on the Add Customer button, type in a duplicate code,
get the error message about duplicates, highlight the customer code that I
entered, press Del followed by Close then when I check the Customer table it
now contains a blank record?
What is the difference between the two?


Klatuu said:
HHHHHHMMMMMMMMMM
I think I understand the problem. The only other thing I can think of would
be to put the test for Me.Dirty and IsNull(Me.Company_ID) in the form's
Unload event. The Close event doesn't have a Cancel in it. So, try taking
that new part out, and putting it in the form Unload Event and see what
happens.

tezza said:
You are correct....the customer ID is text.
The code change I made marries to yours exactly.
I have amended the code for the extra bit but when I now hit the Close
button nothing happens.
It would appear that I can't close the form when the Customer ID is null.
HELP!!!!
Here is the code....

Private Sub Company_ID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Company ID]", "Customers", "[Company ID] = '" &
Me.Company_ID & "'")) Then
MsgBox "Duplicate Customer - Please use a unique code", vbOKOnly,
"Serious Error"
Cancel = True
Else
If Me.Dirty And IsNull(Me.Company_ID) Then
Cancel = True
End If
End If
End Sub

:

It will cause a record to be written if changes have been made. Modify your
code to correct this problem:

Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
Else
If Me.Dirty And IsNull(Me.txtCustID) Then
Cancel = True
End If
End If

I am also curious about what syntax error there is in the DLookup. The only
thing I see is that if your Customer ID is text instead of numeric, then it
would need to be this:
DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'")

If that is the case, remember I said I was assuming a numeric value. If it
is something else, I don't see it.
:

Cheers..
The DLookup line had syntax errors.
I have corrected it and it works fine now apart from when I hit the Close
button.
When I do this I get an Access error message about "Cannot have a null value
in field".
Because this is the key field I have set it so that it does not allow zero
length.
Is there a way round this.
Presumably hitting the Close button causes the record to be written?

:

In the Before Update event oth the control where you enter the customer. For
example purposes, I will assume you are using a numeric customer ID. I will
also make up names to use:
Private Sub txtCustID_BeforeUpdate
If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " &
Me.txtCustID)) Then
MsgBox "This Customer Already Exitst"
Cancel = True
End If

The Cancel = True line cancels the update of the customer id and leaves the
focus in the customer ID text box.

"tezza" w
rote:

I have a form linked to a customer table. On the form is an Add New button. I
need to check for duplicated codes after user enters the customer and not
after all the form has been filled in.
Can anyone tell me which event I should do this on i.e. Before Update, After
Update etc etc.
If a duplicate is found I put out my own error message, spacefill the
customer code and go to the customer code control. Unfortunately though the
code is blanked out, control goes to the next field and I finish up writing
away a record with a blank customer code.
Any ideas or places where I could look at how this should be handled?
Thanks in anticipation.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top