DLookup in Form

[Guest]

Hi,

I have a form called "Accos" on which I have a text box to enter a "Form
Number" (numerical only). It is the 1st text box on the form and is a No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box saying
"Duplicate Entry" or something similar with an OK button. When you click the
OK button I want it to go to a "New Record" as it is the 1st field on the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number]. Given
that I'm pedantic about usability, I would be inclined to check the validity
of [Form Number] in the control's BeforeUpdate event. In that case, use the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Thanks Graham,

It does complain when it saves, which is great. However, there are about 20
fields to fill in before it saves. They get rather annoyed after entering all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number]. Given
that I'm pedantic about usability, I would be inclined to check the validity
of [Form Number] in the control's BeforeUpdate event. In that case, use the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi,

I have a form called "Accos" on which I have a text box to enter a "Form
Number" (numerical only). It is the 1st text box on the form and is a No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box saying
"Duplicate Entry" or something similar with an OK button. When you click
the
OK button I want it to go to a "New Record" as it is the 1st field on the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

What I'd do in that case, is write a function to validate the data on the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham,

It does complain when it saves, which is great. However, there are about
20
fields to fill in before it saves. They get rather annoyed after entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case, use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi,

I have a form called "Accos" on which I have a text box to enter a
"Form
Number" (numerical only). It is the 1st text box on the form and is a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box saying
"Duplicate Entry" or something similar with an OK button. When you
click
the
OK button I want it to go to a "New Record" as it is the 1st field on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other way.

TIA,

Kevin

 

[Guest]

Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a Syntax error
in the 1st 2 lines. I'm checking the form names etc to make sure I didn't
stuff it up.
Thanks,
Kevin

Kevin,

What I'd do in that case, is write a function to validate the data on the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham,

It does complain when it saves, which is great. However, there are about
20
fields to fill in before it saves. They get rather annoyed after entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case, use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to enter a
"Form
Number" (numerical only). It is the 1st text box on the form and is a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box saying
"Duplicate Entry" or something similar with an OK button. When you
click
the
OK button I want it to go to a "New Record" as it is the 1st field on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

Rather than go back and forth dealing with single questions one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of every
control involved in this activity. If any of these are combos or listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range of
values they must have)
* The name of the table to look up (using DLookup), and name of the field to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I didn't
stuff it up.
Thanks,
Kevin

Kevin,

What I'd do in that case, is write a function to validate the data on the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks Graham,

It does complain when it saves, which is great. However, there are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to enter a
"Form
Number" (numerical only). It is the 1st text box on the form and is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box
saying
"Duplicate Entry" or something similar with an OK button. When you
click
the
OK button I want it to go to a "New Record" as it is the 1st field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other
way.

TIA,

Kevin

 

[Guest]

Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is "Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a table
called "Accidents". This is where it saves to when the form is complete. I
have set the field in the table as a "No Duplicates" entry. It is a required
field and there is no range set, except any default value set by Access.
Apart from the ID field, which is an autonumber field, it is the only "No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no effect on
the activity. If you think it is important let me know and I'll send them.
The next field after the "Form_Number" field is "AccoDate", which of course
is set to date/time and is required.

Thanks,

Kevin

Kevin,

Rather than go back and forth dealing with single questions one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of every
control involved in this activity. If any of these are combos or listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range of
values they must have)
* The name of the table to look up (using DLookup), and name of the field to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I didn't
stuff it up.
Thanks,
Kevin

Kevin,

What I'd do in that case, is write a function to validate the data on the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access should
complain if you try to save a record with a duplicate [Form Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to enter a
"Form
Number" (numerical only). It is the 1st text box on the form and is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main table
[Accidents] for duplicate entries before moving to the next field.
If a duplicate entry exists I want it to display a message box
saying
"Duplicate Entry" or something similar with an OK button. When you
click
the
OK button I want it to go to a "New Record" as it is the 1st field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other
way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

Sorry for not getting back to you sooner. I've been very busy finishing off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you don't need
a function to validate anything. I only suggested that because I thought you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number), all you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you know if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = " &
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better way
would be to automatically generate it when the record is created. To do
that, forget the above code and place the following text (including the =)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value + 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is "Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a table
called "Accidents". This is where it saves to when the form is complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by Access.
Apart from the ID field, which is an autonumber field, it is the only "No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no effect
on
the activity. If you think it is important let me know and I'll send them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range of
values they must have)
* The name of the table to look up (using DLookup), and name of the field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the data on
the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to enter
a
"Form
Number" (numerical only). It is the 1st text box on the form and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message box
saying
"Duplicate Entry" or something similar with an OK button. When
you
click
the
OK button I want it to go to a "New Record" as it is the 1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other
way.

TIA,

Kevin

 

[Guest]

Graham,

I appreciate the time that you've spent on me and the fact that you have
other work to do, i.e. making a quid, and I hate to be a pain in the you know
where, but I keep getting an error message when I hit the TAB key to move to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name, acNewRec", do
I replace "acDataForm" with the correct name of the form ie "Accidents Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number" name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin

Kevin,

Sorry for not getting back to you sooner. I've been very busy finishing off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you don't need
a function to validate anything. I only suggested that because I thought you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number), all you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you know if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = " &
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better way
would be to automatically generate it when the record is created. To do
that, forget the above code and place the following text (including the =)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value + 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is "Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a table
called "Accidents". This is where it saves to when the form is complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by Access.
Apart from the ID field, which is an autonumber field, it is the only "No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no effect
on
the activity. If you think it is important let me know and I'll send them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range of
values they must have)
* The name of the table to look up (using DLookup), and name of the field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the data on
the
form, and initiate it in the AfterUpdate event of each of the relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to check the
validity
of [Form Number] in the control's BeforeUpdate event. In that case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to enter
a
"Form
Number" (numerical only). It is the 1st text box on the form and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message box
saying
"Duplicate Entry" or something similar with an OK button. When
you
click
the
OK button I want it to go to a "New Record" as it is the 1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any other
way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

Sorry, my bad! Change the second-last line from "End Sub" to "End If". Then,
from the Debug menu, select Compile. If it still reports an error, let me
know what it is.

<<...do I replace "acDataForm" with...>>
No, acDataForm is a constant.

<<..."Name" with the "Form_Number" name?>>
No, don't change that either. Me.Name will return the form name.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

I appreciate the time that you've spent on me and the fact that you have
other work to do, i.e. making a quid, and I hate to be a pain in the you
know
where, but I keep getting an error message when I hit the TAB key to move
to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted
your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name, acNewRec",
do
I replace "acDataForm" with the correct name of the form ie "Accidents
Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number"
name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin

Kevin,

Sorry for not getting back to you sooner. I've been very busy finishing
off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you don't
need
a function to validate anything. I only suggested that because I thought
you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number), all
you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you know
if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = " &
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better
way
would be to automatically generate it when the record is created. To do
that, forget the above code and place the following text (including the
=)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value + 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is
"Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a
table
called "Accidents". This is where it saves to when the form is
complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by
Access.
Apart from the ID field, which is an autonumber field, it is the only
"No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no
effect
on
the activity. If you think it is important let me know and I'll send
them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

:

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of
every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range
of
values they must have)
* The name of the table to look up (using DLookup), and name of the
field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a
Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the data
on
the
form, and initiate it in the AfterUpdate event of each of the
relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there
are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to check
the
validity
of [Form Number] in the control's BeforeUpdate event. In that
case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to
enter
a
"Form
Number" (numerical only). It is the 1st text box on the form
and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message
box
saying
"Duplicate Entry" or something similar with an OK button. When
you
click
the
OK button I want it to go to a "New Record" as it is the 1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any
other
way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

Actually, I just recalled. In my previous post, I recommended that you
abandon the BeforeUpdate code in favour of the following, which you should
put into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

Do that, and the syntax error will go away.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

I appreciate the time that you've spent on me and the fact that you have
other work to do, i.e. making a quid, and I hate to be a pain in the you
know
where, but I keep getting an error message when I hit the TAB key to move
to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted
your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name, acNewRec",
do
I replace "acDataForm" with the correct name of the form ie "Accidents
Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number"
name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin

Kevin,

Sorry for not getting back to you sooner. I've been very busy finishing
off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you don't
need
a function to validate anything. I only suggested that because I thought
you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number), all
you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you know
if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = " &
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better
way
would be to automatically generate it when the record is created. To do
that, forget the above code and place the following text (including the
=)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value + 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is
"Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a
table
called "Accidents". This is where it saves to when the form is
complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by
Access.
Apart from the ID field, which is an autonumber field, it is the only
"No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no
effect
on
the activity. If you think it is important let me know and I'll send
them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

:

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of
every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range
of
values they must have)
* The name of the table to look up (using DLookup), and name of the
field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a
Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the data
on
the
form, and initiate it in the AfterUpdate event of each of the
relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there
are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to check
the
validity
of [Form Number] in the control's BeforeUpdate event. In that
case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to
enter
a
"Form
Number" (numerical only). It is the 1st text box on the form
and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message
box
saying
"Duplicate Entry" or something similar with an OK button. When
you
click
the
OK button I want it to go to a "New Record" as it is the 1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any
other
way.

TIA,

Kevin

 

[Guest]

I wish it was that simple.
The form numbers that are used are not incremental, they come of an actual
printed form, which are unique but are used by a number of people to enter
data.

I tried your suggestion with the End If, but got the Compile Error: Syntax
Error again, with the 1st 2 lines in red.

Kevin,

Actually, I just recalled. In my previous post, I recommended that you
abandon the BeforeUpdate code in favour of the following, which you should
put into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

Do that, and the syntax error will go away.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

I appreciate the time that you've spent on me and the fact that you have
other work to do, i.e. making a quid, and I hate to be a pain in the you
know
where, but I keep getting an error message when I hit the TAB key to move
to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted
your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name, acNewRec",
do
I replace "acDataForm" with the correct name of the form ie "Accidents
Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number"
name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin

Kevin,

Sorry for not getting back to you sooner. I've been very busy finishing
off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you don't
need
a function to validate anything. I only suggested that because I thought
you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number), all
you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you know
if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = " &
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better
way
would be to automatically generate it when the record is created. To do
that, forget the above code and place the following text (including the
=)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value + 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is
"Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a
table
called "Accidents". This is where it saves to when the form is
complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by
Access.
Apart from the ID field, which is an autonumber field, it is the only
"No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no
effect
on
the activity. If you think it is important let me know and I'll send
them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

:

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of
every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the range
of
values they must have)
* The name of the table to look up (using DLookup), and name of the
field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a
Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the data
on
the
form, and initiate it in the AfterUpdate event of each of the
relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there
are
about
20
fields to fill in before it saves. They get rather annoyed after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to check
the
validity
of [Form Number] in the control's BeforeUpdate event. In that
case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to
enter
a
"Form
Number" (numerical only). It is the 1st text box on the form
and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message
box
saying
"Duplicate Entry" or something similar with an OK button. When
you
click
the
OK button I want it to go to a "New Record" as it is the 1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any
other
way.

TIA,

Kevin

 

[Graham R Seach]

Kevin,

Functionally, there's nothing wrong with the code. If the lines are red,
then my guess is that your newsreader has incorrectly wrapped the lines.
Let's try again. Copy the following:

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", _
"Accidents", "[Form Number] = " & _
Nz(Me.Form_Number, 0)), 0) <> 0 Then

Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End Sub

If the code still turns red, then I'll have to send it to you in a text
file.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I wish it was that simple.
The form numbers that are used are not incremental, they come of an actual
printed form, which are unique but are used by a number of people to enter
data.

I tried your suggestion with the End If, but got the Compile Error: Syntax
Error again, with the 1st 2 lines in red.

Kevin,

Actually, I just recalled. In my previous post, I recommended that you
abandon the BeforeUpdate code in favour of the following, which you
should
put into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

Do that, and the syntax error will go away.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

I appreciate the time that you've spent on me and the fact that you
have
other work to do, i.e. making a quid, and I hate to be a pain in the
you
know
where, but I keep getting an error message when I hit the TAB key to
move
to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines
of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted
your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name,
acNewRec",
do
I replace "acDataForm" with the correct name of the form ie "Accidents
Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number"
name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin


:

Kevin,

Sorry for not getting back to you sooner. I've been very busy
finishing
off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you
don't
need
a function to validate anything. I only suggested that because I
thought
you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number),
all
you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you
know
if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = "
&
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better
way
would be to automatically generate it when the record is created. To
do
that, forget the above code and place the following text (including
the
=)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value +
1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is
"Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a
table
called "Accidents". This is where it saves to when the form is
complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by
Access.
Apart from the ID field, which is an autonumber field, it is the
only
"No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no
effect
on
the activity. If you think it is important let me know and I'll send
them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

:

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of
every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the
range
of
values they must have)
* The name of the table to look up (using DLookup), and name of the
field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a
Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure
I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the
data
on
the
form, and initiate it in the AfterUpdate event of each of the
relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there
are
about
20
fields to fill in before it saves. They get rather annoyed
after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to
check
the
validity
of [Form Number] in the control's BeforeUpdate event. In that
case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to
enter
a
"Form
Number" (numerical only). It is the 1st text box on the
form
and
is
a
No
Duplicates field.
What I want it to do is lookup the [Number] field in the
main
table
[Accidents] for duplicate entries before moving to the next
field.
If a duplicate entry exists I want it to display a message
box
saying
"Duplicate Entry" or something similar with an OK button.
When
you
click
the
OK button I want it to go to a "New Record" as it is the
1st
field
on
the
form anyway.
If there is no duplicate entry then continue as normal.
Sorry if it sounds confusing, but I couldn't explain it any
other
way.

TIA,

Kevin

 

[Guest]

Yippee,

We're finally getting somewhere. I think it was the newsreader as well. The
code you just sent looks different in the word wrapping.
The red has gone and it works fine, until after clicking the OK button for
the "Duplicate Entry".
After that it displays a "Run Time Error: 2105. You can't go to the
specified record" with End and Debug. The DoCmd line is in yellow.
I tried changing a few things in the line, me.form_number etc, but that
didn't work. I then deleted the line entirely and it somehow WORKED (YESSS).
I think because the focus was already on the text box that after I click the
OK button it just stays there.

Everything is fine now. Thanks heaps for your continued efforts, on top of
your normal work, in resolving this issue that was causing me grief.

Kevin

Kevin,

Functionally, there's nothing wrong with the code. If the lines are red,
then my guess is that your newsreader has incorrectly wrapped the lines.
Let's try again. Copy the following:

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", _
"Accidents", "[Form Number] = " & _
Nz(Me.Form_Number, 0)), 0) <> 0 Then

Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End Sub

If the code still turns red, then I'll have to send it to you in a text
file.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I wish it was that simple.
The form numbers that are used are not incremental, they come of an actual
printed form, which are unique but are used by a number of people to enter
data.

I tried your suggestion with the End If, but got the Compile Error: Syntax
Error again, with the 1st 2 lines in red.

Kevin,

Actually, I just recalled. In my previous post, I recommended that you
abandon the BeforeUpdate code in favour of the following, which you
should
put into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

Do that, and the syntax error will go away.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,

I appreciate the time that you've spent on me and the fact that you
have
other work to do, i.e. making a quid, and I hate to be a pain in the
you
know
where, but I keep getting an error message when I hit the TAB key to
move
to
the next field.
It comes up with a "Compile Error: Syntax Error" with the 1st 2 lines
of
code in red.
I done as you suggested in the BeforeUpdates event. I copied and pasted
your
code so it can't be a typo.
Question: In the line "DoCmd.GoToRecord acDataForm, Me.Name,
acNewRec",
do
I replace "acDataForm" with the correct name of the form ie "Accidents
Data
Entry Form" in [] or "" or otherwise, and "Name" with the "Form_Number"
name?
I tried all ways, including replacing just "DataForm" and leaving "ac".
Nothing seems to work. Dohhh.
Does the fact that I'm using Access 2000 have anything to do with it?

Fix this and Ill shout you a Pure Blonde, or whatever.

Kevin


:

Kevin,

Sorry for not getting back to you sooner. I've been very busy
finishing
off
a project, which I did yesterday.

If Form_Number is the only unique field (aside from ID), then you
don't
need
a function to validate anything. I only suggested that because I
thought
you
had more than one field in the unique index.

Well, given that you only have to validate one field (Form_Number),
all
you
need to do is put the following code into the BeforeUpdate event for
Form_Number. This will check that Form_Number is unique, and let you
know
if
it isn't.

Private Sub Form_Number_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("[Form Number]", "Accidents", "[Form Number] = "
&
Nz(Me.Form_Number, 0)), 0) <> 0 Then
Cancel = True
Me.Form_Number.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

But, if I am correct in assuming that Form_Number is numeric, a better
way
would be to automatically generate it when the record is created. To
do
that, forget the above code and place the following text (including
the
=)
into Form_Number's [DefaultValue] property:
=Nz(DMax("[Form Number]", "Accidents"), 0) + 1

This makes Form_Number's default value = the largest current value +
1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,

There is only 1 control involved in this problem.
It is a text box, and is a number data type. It's name is
"Form_Number".
It needs to lookup a field called "Form Number" (no underscore) in a
table
called "Accidents". This is where it saves to when the form is
complete. I
have set the field in the table as a "No Duplicates" entry. It is a
required
field and there is no range set, except any default value set by
Access.
Apart from the ID field, which is an autonumber field, it is the
only
"No
Duplicates" field on the form.
Do you need all the other fields on the form? They really have no
effect
on
the activity. If you think it is important let me know and I'll send
them.
The next field after the "Form_Number" field is "AccoDate", which of
course
is set to date/time and is required.

Thanks,

Kevin

:

Kevin,

Rather than go back and forth dealing with single questions
one-at-a-time,
how about you let us know the following:

* The names, control types (textbox, combo, etc) and datatypes of
every
control involved in this activity. If any of these are combos or
listboxes,
what are their RowSources?
* The business rules (which controls must have values, and the
range
of
values they must have)
* The name of the table to look up (using DLookup), and name of the
field
to
lookup (including its datatype)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Silly question Graham, but what would this "function" look like?

By the way, I tried the code you gave me and it came up with a
Syntax
error
in the 1st 2 lines. I'm checking the form names etc to make sure
I
didn't
stuff it up.
Thanks,
Kevin

:

Kevin,

What I'd do in that case, is write a function to validate the
data
on
the
form, and initiate it in the AfterUpdate event of each of the
relevent
controls is changed. That way, as soon as the user violates the
uniqueness
rule, they're advised of it.

MVPs in Sydney - yep, there are 3 of us.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

It does complain when it saves, which is great. However, there
are
about
20
fields to fill in before it saves. They get rather annoyed
after
entering
all
the data, only to have an error message come up at the end.
Will try your suggestion.
Didn't know there was a MVP in Sydney. Padstow myself.

Thanks.

:

Kevin,

If it's marked as a unique (no duplicates) field, then Access
should
complain if you try to save a record with a duplicate [Form
Number].
Given
that I'm pedantic about usability, I would be inclined to
check
the
validity
of [Form Number] in the control's BeforeUpdate event. In that
case,
use
the
following pseudo code:

Private Sub txtFormNumber_BeforeUpdate(Cancel As Integer)
If Nz(DLookup("Number", "Accidents", "Number = " &
Nz(Me.txtFormNumber, 0)), 0) <> 0 Then
Cancel = True
Me.txtFormNumber.Undo
MsgBox "Duplicate Entry"
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi,

I have a form called "Accos" on which I have a text box to