when save produces an error

  • Thread starter Thread starter maureen
  • Start date Start date
M

maureen

I have a macro that I would like to ad a condition to on
the RUNCOMMAND - SAVERECORD line. Or where ever it makes
sense to --

What's happening on this form is I am creating, sometimes
recreating, product item numbers. The PRODITEMNUM field
index property is set to 'yes - no duplicates' so all
product item numbers will be unique.

Since the likelyhood of duplicate product numbers
attempting to be created by the users is great, I need to
first do a "TEST SAVE" or a comparison to see if the
current PRODITEMNUM will generate a duplicate values
error upon saving the record. I need to do this "test"
before or immediately after the users LEAVES the
PRODITEMNUM field and BEFORE he/she can do anything else
on the form. I can not put a SAVERECORD command on the
On Change nor on the on exit events for other reasons.

Can you help?
 
suggest you add a macro to the PRODITEMNUM control's AfterUpdate event
property, as

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = " & [PRODITEMNUM]) >
0
Action: Msgbox
(next line)
Condition: ...
Action: GoToControl
Control Name: PRODITEMNUM

if you were doing this in VBA, i would suggest the BeforeUpdate event, where
you can cancel the update and the cursor remains in the control - but AFAIK,
you can't cancel an event from within a macro.
 
This could do the trick -

I'm having trouble creating the condition correctly -
Here's what I have in there now:

DCount(1,"tblProducts","[PRODITEMNUM] = " & [PRODITEMNUM])
0
Click to expand...

The name of the table where the possible duplicate
ProdItemNum may already be stored/saved is tblProducts.
The name of the field in that table is ProdItemNum and it
is the 2nd field in there. The name of the field on the
new Products Form is also ProdItemNum

I am getting the following error message -

syntax error missing operator in query
expression '[PRODITEMNUM]=0ABW

where 0awb is the ProdItemNum field value

What am I missing? What's wrong?
THANKS!
-----Original Message-----
suggest you add a macro to the PRODITEMNUM control's AfterUpdate event
property, as

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = " & [PRODITEMNUM]) >
0
Action: Msgbox
(next line)
Condition: ...
Action: GoToControl
Control Name: PRODITEMNUM

if you were doing this in VBA, i would suggest the BeforeUpdate event, where
you can cancel the update and the cursor remains in the control - but AFAIK,
you can't cancel an event from within a macro.


I have a macro that I would like to ad a condition to on
the RUNCOMMAND - SAVERECORD line. Or where ever it makes
sense to --

What's happening on this form is I am creating, sometimes
recreating, product item numbers. The PRODITEMNUM field
index property is set to 'yes - no duplicates' so all
product item numbers will be unique.

Since the likelyhood of duplicate product numbers
attempting to be created by the users is great, I need to
first do a "TEST SAVE" or a comparison to see if the
current PRODITEMNUM will generate a duplicate values
error upon saving the record. I need to do this "test"
before or immediately after the users LEAVES the
PRODITEMNUM field and BEFORE he/she can do anything else
on the form. I can not put a SAVERECORD command on the
On Change nor on the on exit events for other reasons.

Can you help?
Click to expand...


.
Click to expand...
 
You need to delimit the text value with ' characters:

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = '" & [PRODITEMNUM] &
"'") > 0

--

Ken Snell
<MS ACCESS MVP>

Maureen said:
This could do the trick -

I'm having trouble creating the condition correctly -
Here's what I have in there now:

DCount(1,"tblProducts","[PRODITEMNUM] = " & [PRODITEMNUM])
0
Click to expand...

The name of the table where the possible duplicate
ProdItemNum may already be stored/saved is tblProducts.
The name of the field in that table is ProdItemNum and it
is the 2nd field in there. The name of the field on the
new Products Form is also ProdItemNum

I am getting the following error message -

syntax error missing operator in query
expression '[PRODITEMNUM]=0ABW

where 0awb is the ProdItemNum field value

What am I missing? What's wrong?
THANKS!
-----Original Message-----
suggest you add a macro to the PRODITEMNUM control's AfterUpdate event
property, as

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = " & [PRODITEMNUM]) >
0
Action: Msgbox
(next line)
Condition: ...
Action: GoToControl
Control Name: PRODITEMNUM

if you were doing this in VBA, i would suggest the BeforeUpdate event, where
you can cancel the update and the cursor remains in the control - but AFAIK,
you can't cancel an event from within a macro.


I have a macro that I would like to ad a condition to on
the RUNCOMMAND - SAVERECORD line. Or where ever it makes
sense to --

What's happening on this form is I am creating, sometimes
recreating, product item numbers. The PRODITEMNUM field
index property is set to 'yes - no duplicates' so all
product item numbers will be unique.

Since the likelyhood of duplicate product numbers
attempting to be created by the users is great, I need to
first do a "TEST SAVE" or a comparison to see if the
current PRODITEMNUM will generate a duplicate values
error upon saving the record. I need to do this "test"
before or immediately after the users LEAVES the
PRODITEMNUM field and BEFORE he/she can do anything else
on the form. I can not put a SAVERECORD command on the
On Change nor on the on exit events for other reasons.

Can you help?
Click to expand...


.
Click to expand...
Click to expand...
 
BRILLANT! That did it - Thank you very much for your
assistance!!
-----Original Message-----
You need to delimit the text value with ' characters:

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = '" & [PRODITEMNUM] &
"'") > 0

--

Ken Snell
<MS ACCESS MVP>

This could do the trick -

I'm having trouble creating the condition correctly -
Here's what I have in there now:

DCount(1,"tblProducts","[PRODITEMNUM] = " & [PRODITEMNUM])
0
Click to expand...

The name of the table where the possible duplicate
ProdItemNum may already be stored/saved is tblProducts.
The name of the field in that table is ProdItemNum and it
is the 2nd field in there. The name of the field on the
new Products Form is also ProdItemNum

I am getting the following error message -

syntax error missing operator in query
expression '[PRODITEMNUM]=0ABW

where 0awb is the ProdItemNum field value

What am I missing? What's wrong?
THANKS!
-----Original Message-----
suggest you add a macro to the PRODITEMNUM control's AfterUpdate event
property, as

Condition: DCount(1, "MyTableName","[PRODITEMNUM]
Click to expand...
= "
& [PRODITEMNUM]) >
0
Action: Msgbox
(next line)
Condition: ...
Action: GoToControl
Control Name: PRODITEMNUM

if you were doing this in VBA, i would suggest the BeforeUpdate event, where
you can cancel the update and the cursor remains in
Click to expand...
the
control - but AFAIK,
you can't cancel an event from within a macro.


"maureen" <[email protected]> wrote
Click to expand...
in
message
I have a macro that I would like to ad a condition
Click to expand...
to
on
the RUNCOMMAND - SAVERECORD line. Or where ever it makes
sense to --

What's happening on this form is I am creating, sometimes
recreating, product item numbers. The PRODITEMNUM field
index property is set to 'yes - no duplicates' so all
product item numbers will be unique.

Since the likelyhood of duplicate product numbers
attempting to be created by the users is great, I
Click to expand...
need
to
first do a "TEST SAVE" or a comparison to see if the
current PRODITEMNUM will generate a duplicate values
error upon saving the record. I need to do this "test"
before or immediately after the users LEAVES the
PRODITEMNUM field and BEFORE he/she can do anything else
on the form. I can not put a SAVERECORD command on the
On Change nor on the on exit events for other reasons.

Can you help?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Ken, i'm glad you helped her, but how did you know PRODITEMNUM was text not
numeric? i'm always trying to do better at understanding posts so i can
answer them better; i can't figure out what clue i missed in Maureen's post.
help me out? :)


Ken Snell said:
You need to delimit the text value with ' characters:

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = '" & [PRODITEMNUM] &
"'") > 0

--

Ken Snell
<MS ACCESS MVP>

Maureen said:
This could do the trick -

I'm having trouble creating the condition correctly -
Here's what I have in there now:

DCount(1,"tblProducts","[PRODITEMNUM] = " & [PRODITEMNUM])
0
Click to expand...

The name of the table where the possible duplicate
ProdItemNum may already be stored/saved is tblProducts.
The name of the field in that table is ProdItemNum and it
is the 2nd field in there. The name of the field on the
new Products Form is also ProdItemNum

I am getting the following error message -

syntax error missing operator in query
expression '[PRODITEMNUM]=0ABW

where 0awb is the ProdItemNum field value

What am I missing? What's wrong?
THANKS!
-----Original Message-----
suggest you add a macro to the PRODITEMNUM control's AfterUpdate event
property, as

Condition: DCount(1, "MyTableName","[PRODITEMNUM] = " & [PRODITEMNUM]) >
0
Action: Msgbox
(next line)
Condition: ...
Action: GoToControl
Control Name: PRODITEMNUM

if you were doing this in VBA, i would suggest the BeforeUpdate event, where
you can cancel the update and the cursor remains in the control - but AFAIK,
you can't cancel an event from within a macro.


I have a macro that I would like to ad a condition to on
the RUNCOMMAND - SAVERECORD line. Or where ever it makes
sense to --

What's happening on this form is I am creating, sometimes
recreating, product item numbers. The PRODITEMNUM field
index property is set to 'yes - no duplicates' so all
product item numbers will be unique.

Since the likelyhood of duplicate product numbers
attempting to be created by the users is great, I need to
first do a "TEST SAVE" or a comparison to see if the
current PRODITEMNUM will generate a duplicate values
error upon saving the record. I need to do this "test"
before or immediately after the users LEAVES the
PRODITEMNUM field and BEFORE he/she can do anything else
on the form. I can not put a SAVERECORD command on the
On Change nor on the on exit events for other reasons.

Can you help?


.
Click to expand...
Click to expand...
Click to expand...
 
Tina,

Since Ken is out of town, I'll presume to answer on his behalf...

I think Maureen gave it away when she said: "where 0awb is the
ProdItemNum field value".
 
ohhhh, there it is! i was rereading the original post, rather than Maureen's
reply to my post - which i didn't see until after Ken had already helped
her. well, at least i didn't miss such an obvious clue in the original
post - i don't feel quite so dumb now. <g>
thanks bunches, Steve! :)
 
And now that I'm back, Steve is absolutely correct! The value 0awb is what
gave me the tip.
 
Back
Top