Duplicate Error

[Guest]

I am using ACCESS 2003, in my database I have a form to enter BargeNames,
Size, Type and so. My problem is BargeNames is my Primary Key in the table
(no two barges have the same name Per USCG REGS.)we track how many times a
barge has been at our dock, product and so on in a separate table. I have a
relationship with the tables. When we enter a barge name in the BN Table that
has already been used we get the dreaded "would create a duplicate" message
but not until the entire form is filled out, how can I have the message when
I leave the field instead of at the end. Probably with a Before UpDate but I
have no idea how to do it.

 

[Ken Snell \(MVP\)]

You would use the BeforeUpdate event of the textbox into which you are
entering the Barge Name value.

A generic code example:

Private Sub NameOfTextBox_BeforeUpdate(Cancel As Integer)
If DCount("BargeName", "BN", "BargeName='" & _
Me.NameOfTextBox.Value & "'") > 0 Then
Cancel = True
MsgBox "This barge name already exists in the database!", _
vbExclamation, "Duplicate Barge Name"
End If
End Sub

 

[Douglas J. Steele]

Put logic in the BeforeUpdate event of the control into which you're typing
the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're going to
need to use VBA for this. Don't worry, it's not that difficult.

Select the text box on the form in Design mode, and look at the Properties
window. Find the BeforeUpdate event, select [Event Procedure] from the box
and click on the ellipsis (...) to the right of the box. That will take you
into the VB Editor, in the midst of something like:

Private Sub Textbox1_BeforeUpdate(Cancel As Integer)

End Sub

(Textbox1 will be replaced by whatever the name of the control actually is)

You want to add code so that you end up with:

Private Sub Textbox1_BeforeUpdate(Cancel As Integer)

If DCount("*", "[NameOfTable]", "[BargeName] = '" & Me.Textbox1 & "'") > 0
Then
MsgBox "You've already entered " & Me.Textbox1
Cancel = True
End If

End Sub

(Make sure you use the correct field name rather than Textbox1 and replace
NameOfTable with the actual name of your table. If the field in your table
isn't actually named BargeName, replace that with the correct name as well)

 

[Ken Snell \(MVP\)]

Put logic in the BeforeUpdate event of the control into which you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're going to
need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent action
as the first action, and MsgBox as second action (with ellipsis in Condition
column), and StopMacro as third action (with ellipsis in Condition column).

 

[Ken Snell \(MVP\)]

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent action
as the first action, and MsgBox as second action (with ellipsis in Condition
column), and StopMacro as third action (with ellipsis in Condition column).


--

Ken Snell
<MS ACCESS MVP>

Put logic in the BeforeUpdate event of the control into which you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent
action as the first action, and MsgBox as second action (with ellipsis in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Guest]

Ken it works perfect once I got all of my textbox names corrected, I was able
to use this in several of my form just by changing the names. One of my forms
"EventsEntry" has a field "TicketID" which is the primary key in the my table
"Events". Instead of just stopping us from entering and giving us the msgbox
(which is great) can we some how be directed to the record that has the same
"TicketeID" field so we can be update it and we don't have to go looking for
it. Every event transaction has it's own TicketID but when Tickets are
brought in we don't know if it has been entered or not so your macro stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent action
as the first action, and MsgBox as second action (with ellipsis in Condition
column), and StopMacro as third action (with ellipsis in Condition column).


--

Ken Snell
<MS ACCESS MVP>

Put logic in the BeforeUpdate event of the control into which you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent
action as the first action, and MsgBox as second action (with ellipsis in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Ken Snell \(MVP\)]

Are you using an ACCESS macro or VBA code?

--

Ken Snell
<MS ACCESS MVP>

Ken it works perfect once I got all of my textbox names corrected, I was
able
to use this in several of my form just by changing the names. One of my
forms
"EventsEntry" has a field "TicketID" which is the primary key in the my
table
"Events". Instead of just stopping us from entering and giving us the
msgbox
(which is great) can we some how be directed to the record that has the
same
"TicketeID" field so we can be update it and we don't have to go looking
for
it. Every event transaction has it's own TicketID but when Tickets are
brought in we don't know if it has been entered or not so your macro
stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent
action
as the first action, and MsgBox as second action (with ellipsis in
Condition
column), and StopMacro as third action (with ellipsis in Condition
column).


--

Ken Snell
<MS ACCESS MVP>

message
Put logic in the BeforeUpdate event of the control into which you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're
going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent
action as the first action, and MsgBox as second action (with ellipsis
in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Guest]

I am using ACCESS 2003 and I used marco as you suggested

Are you using an ACCESS macro or VBA code?

--

Ken Snell
<MS ACCESS MVP>

Ken it works perfect once I got all of my textbox names corrected, I was
able
to use this in several of my form just by changing the names. One of my
forms
"EventsEntry" has a field "TicketID" which is the primary key in the my
table
"Events". Instead of just stopping us from entering and giving us the
msgbox
(which is great) can we some how be directed to the record that has the
same
"TicketeID" field so we can be update it and we don't have to go looking
for
it. Every event transaction has it's own TicketID but when Tickets are
brought in we don't know if it has been entered or not so your macro
stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent
action
as the first action, and MsgBox as second action (with ellipsis in
Condition
column), and StopMacro as third action (with ellipsis in Condition
column).


--

Ken Snell
<MS ACCESS MVP>



message
Put logic in the BeforeUpdate event of the control into which you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're
going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent
action as the first action, and MsgBox as second action (with ellipsis
in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Ken Snell \(MVP\)]

You could add an additional macro action -- FindRecord -- after your message
box (or before, whichever you prefer) that will let you move the form to the
appropriate record, based on the value of the unique key field that was
entered. This assumes that

-- you have not dirtied the current record (if you have, then I
don't believe a macro is going to be useful for you because it cannot "undo"
the dirtying of the record that will occur when the user first types the
value into the control); and

-- that your form's RecordSource contains all the records from the
table so that you can "find" that particular record in the form's recordset
data.

(I apologize for the delay in answering your question -- got tied up with
some other work...)

--

Ken Snell
<MS ACCESS MVP>

I am using ACCESS 2003 and I used marco as you suggested

Are you using an ACCESS macro or VBA code?

--

Ken Snell
<MS ACCESS MVP>

Ken it works perfect once I got all of my textbox names corrected, I
was
able
to use this in several of my form just by changing the names. One of my
forms
"EventsEntry" has a field "TicketID" which is the primary key in the my
table
"Events". Instead of just stopping us from entering and giving us the
msgbox
(which is great) can we some how be directed to the record that has the
same
"TicketeID" field so we can be update it and we don't have to go
looking
for
it. Every event transaction has it's own TicketID but when Tickets are
brought in we don't know if it has been entered or not so your macro
stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent
action
as the first action, and MsgBox as second action (with ellipsis in
Condition
column), and StopMacro as third action (with ellipsis in Condition
column).


--

Ken Snell
<MS ACCESS MVP>



message
Put logic in the BeforeUpdate event of the control into which
you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're
going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put
the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
Me.Textbox1 & "'") > 0) in the Condition column, and use CancelEvent
action as the first action, and MsgBox as second action (with
ellipsis
in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Ken Snell \(MVP\)]

Adding to my post --

You can undo a record that has been dirtied. You'd use the RunCommand
action, and use the Undo option for it.

--

Ken Snell
<MS ACCESS MVP>

You could add an additional macro action -- FindRecord -- after your
message box (or before, whichever you prefer) that will let you move the
form to the appropriate record, based on the value of the unique key field
that was entered. This assumes that

-- you have not dirtied the current record (if you have, then I
don't believe a macro is going to be useful for you because it cannot
"undo" the dirtying of the record that will occur when the user first
types the value into the control); and

-- that your form's RecordSource contains all the records from the
table so that you can "find" that particular record in the form's
recordset data.

(I apologize for the delay in answering your question -- got tied up with
some other work...)

--

Ken Snell
<MS ACCESS MVP>

I am using ACCESS 2003 and I used marco as you suggested

Are you using an ACCESS macro or VBA code?

--

Ken Snell
<MS ACCESS MVP>


Ken it works perfect once I got all of my textbox names corrected, I
was
able
to use this in several of my form just by changing the names. One of
my
forms
"EventsEntry" has a field "TicketID" which is the primary key in the
my
table
"Events". Instead of just stopping us from entering and giving us the
msgbox
(which is great) can we some how be directed to the record that has
the
same
"TicketeID" field so we can be update it and we don't have to go
looking
for
it. Every event transaction has it's own TicketID but when Tickets
are
brought in we don't know if it has been entered or not so your macro
stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent
action
as the first action, and MsgBox as second action (with ellipsis in
Condition
column), and StopMacro as third action (with ellipsis in Condition
column).


--

Ken Snell
<MS ACCESS MVP>



message
Put logic in the BeforeUpdate event of the control into which
you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're
going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put
the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '"
&
Me.Textbox1 & "'") > 0) in the Condition column, and use
CancelEvent
action as the first action, and MsgBox as second action (with
ellipsis
in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Guest]

Here is what I have done so far

Condition Action
"Your code" CancelEvent
.... MsgBox
.... StopMacro
.... FindRecord
.... RunCommand / w undo in
the bottom box

Is this how it should look, because this is how I put it and all I get is
the MsgBox
Thank you for your time and help.

Adding to my post --

You can undo a record that has been dirtied. You'd use the RunCommand
action, and use the Undo option for it.

--

Ken Snell
<MS ACCESS MVP>

You could add an additional macro action -- FindRecord -- after your
message box (or before, whichever you prefer) that will let you move the
form to the appropriate record, based on the value of the unique key field
that was entered. This assumes that

-- you have not dirtied the current record (if you have, then I
don't believe a macro is going to be useful for you because it cannot
"undo" the dirtying of the record that will occur when the user first
types the value into the control); and

-- that your form's RecordSource contains all the records from the
table so that you can "find" that particular record in the form's
recordset data.

(I apologize for the delay in answering your question -- got tied up with
some other work...)

--

Ken Snell
<MS ACCESS MVP>

I am using ACCESS 2003 and I used marco as you suggested

:

Are you using an ACCESS macro or VBA code?

--

Ken Snell
<MS ACCESS MVP>


Ken it works perfect once I got all of my textbox names corrected, I
was
able
to use this in several of my form just by changing the names. One of
my
forms
"EventsEntry" has a field "TicketID" which is the primary key in the
my
table
"Events". Instead of just stopping us from entering and giving us the
msgbox
(which is great) can we some how be directed to the record that has
the
same
"TicketeID" field so we can be update it and we don't have to go
looking
for
it. Every event transaction has it's own TicketID but when Tickets
are
brought in we don't know if it has been entered or not so your macro
stopped
us from that problem but now to find that "TicketID" and update. it.

Ken Snell (MVP)" wrote:

Aarrghh... error in my post -- corrected:

Actually this can be done with Macro and no VBA. Just need to put the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '" &
[Textbox1] & "'") > 0) in the Condition column, and use CancelEvent
action
as the first action, and MsgBox as second action (with ellipsis in
Condition
column), and StopMacro as third action (with ellipsis in Condition
column).


--

Ken Snell
<MS ACCESS MVP>



message
Put logic in the BeforeUpdate event of the control into which
you're
typing the BargeName to check whether the name already appears.

While I know you're posting in the Macros group, I believe you're
going
to need to use VBA for this. Don't worry, it's not that difficult.

Actually this can be done with Macro and no VBA. Just need to put
the
testing expression (DCount("*", "[NameOfTable]", "[BargeName] = '"
&
Me.Textbox1 & "'") > 0) in the Condition column, and use
CancelEvent
action as the first action, and MsgBox as second action (with
ellipsis
in
Condition column), and StopMacro as third action (with ellipsis in
Condition column).

 

[Ken Snell \(MVP\)]

Move the StopMacro action to after the RunCommand action.

 

[Guest]

After I made the changes I am getting the message "Find What" is blank. I
tried several different thing

=[TicketID]
=[Events], [TicketID]

Events is the table and TicketID is my field

 

[Ken Snell \(MVP\)]

Use the name of the control that contains the value that you want to find.

=[NameOfTheControl]

--

Ken Snell
<MS ACCESS MVP>

After I made the changes I am getting the message "Find What" is blank. I
tried several different thing

=[TicketID]
=[Events], [TicketID]

Events is the table and TicketID is my field

Move the StopMacro action to after the RunCommand action.

 

[Guest]

I'm getting the message that the undo is not available so I have put a button
on the form to go to a qry to get the TicketID and update it from there.

Thanks anyway for the help I truly appreciate the help.

Use the name of the control that contains the value that you want to find.

=[NameOfTheControl]

--

Ken Snell
<MS ACCESS MVP>

After I made the changes I am getting the message "Find What" is blank. I
tried several different thing

=[TicketID]
=[Events], [TicketID]

Events is the table and TicketID is my field

Move the StopMacro action to after the RunCommand action.

--

Ken Snell
<MS ACCESS MVP>


Here is what I have done so far

Condition Action
"Your code" CancelEvent
... MsgBox
... StopMacro
... FindRecord
... RunCommand / w undo
in
the bottom box

Is this how it should look, because this is how I put it and all I get
is
the MsgBox
Thank you for your time and help.