-----Original Message-----
Bill,
To do this in a friendly way you need to write VBA code that checks the
values the user has entered before the record is saved, and warns the
user at that stage. You have to be entering the data via a form, not
directly into the table.
Depending on when you want the user to be warned, the code goes into the
BeforeUpdate event procedure of the form (which lets the user fill in
all the fields and runs when they try to move to another record), or of
the control(s) bound to the fields in question (which warns them as soon
as they try to move to another control, but means writing more, and more
subtle code).
The general idea is to use the DCount() function to see if there is an
existing record that already has the values we are about to save.
For instance, if the fields are FlightNumber (text) and FlightDate
(date/time) and the controls that display them are txtFlightNumber and
txtFlightDate, the code in the Form_BeforeUpdate() procedure would be
something like this (note the extra quotes round the text value):
Dim strCriteria As String
strCriteria = "(FlightNumber=""" & Me.txtFlightNumber.Value _
& """) AND (FlightDate=" & Me.txtFlightDate.Value & ")")
If DCount("FlightNumber", "MyTable", strCriteria) > 0 Then
'A record with these values already exists so we are about to
'try to create a duplicate record!
Cancel = True 'cancel the update
MsgBox "This flight has already been entered on this date. " _
& vbCRLF _
& "Please check and try again.", vbExclamation + vbOKOnly
End If
To John Nurick
Thanks - works fine
The "Error" code tells me that there are fields which are
duplicated but it doesn't say which. I know which one but
is there a way to indicate automatically which field is
duplicated.
I have a validation rule for the specific entry required
for a Flight number (Like "XX####") which works but I
can't see how I can get "Can't duplicate flight/date" as
a message
Thanks Bill
-----Original Message-----
Bill,
You have to create an index on both fields, flight
number and date. With
the table in Design view, go to the View menu and select
Indexes. This
opens the Index design dialog.
Type a name for the index (e.g. "FlightsDates") in the
first column of
the grid, and in the next column select the flight
number field. Then in
the next row select the date field (but leave the Index
Name blank).
Then at the bottom of the dialog set Primary to No and
Unique to Yes.
This gives you one index incorporating the two fields,
so you can have
the same flight number many times, but only on different
days (and many
different flights on the same day).
On Wed, 8 Sep 2004 03:42:35 -0700, "bill"
John (& Dan)
Thanks for your response - much appreciated
Ref preventing duplicates.
Flight numbers are duplicated but never on the same day.
How can I restrict the entry to match that requirement?
I have tried yes indexing but after accepting the first
entry, it refuses the next.
Thanks Bill
-----Original Message-----
Hi Bill,
Number 1 can usually be handled with a validation rule
on the field (set
it in Table Design view), something like
Is Null Or Like "AB####"
which will accept empty (Null) or AB followed by 4
digits.
For number 2, try setting an index (duplicates not
allowed) on the two
fields that contain the flight number and the date.
On Tue, 7 Sep 2004 10:41:42 -0700, "Bill"
2 questions
I have a field called flight number inwhich I place a
carrier code and a 4 digit number
Can I arrange the entry so that only a specific 2
character code is entered?
and
is there a way of preventing someone entering the
same
flight number twice on a single day?
I thought it would be simple but ....
Thanks Bill
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
