Append Query Error

[Guest]

I am using an append query to pull data from a form and ad it to the table.
The following data can be entered via the form but the append query trying to
ad the same data causes the debugger to pop up.

2/1/06 OKI S8711243-Dir CAROL S/S -- S/S 2/22/06
OKI -- BECKY S/S 1/15/06 OKI -- KIM --S/S
11/5/05--CALL 11/2/05 TO CHECK ON THEM

Query SQL

UPDATE (AGENT INNER JOIN [MAIN FILTERED] ON (AGENT.ALIAS = [MAIN
FILTERED].AGNT_NAME_) AND (AGENT.ALIAS = [MAIN FILTERED].AGNT_NAME_)) INNER
JOIN [MAIN RAW] ON ([MAIN FILTERED].LINNUM_PL = [MAIN RAW].LINNUM_PL) AND
([MAIN FILTERED].ITEM_NUM_P = [MAIN RAW].ITEM_NUM_P) AND ([MAIN
FILTERED].SUP_PL = [MAIN RAW].SUP_PL) AND ([MAIN FILTERED].DATE_PL = [MAIN
RAW].DATE_PL) AND ([MAIN FILTERED].P_O_NUM_PH = [MAIN RAW].P_O_NUM_PH) AND
(AGENT.ALIAS = [MAIN RAW].AGNT_NAME_) SET [MAIN FILTERED].[Exp Note] =
[Forms]![FrmNotesDFUAll]![Text0], [MAIN FILTERED].[Exp Note Date] = Date()
WHERE ((([MAIN RAW].ApplyNote)=Yes) AND (([MAIN
FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[OfficeDev18 via AccessMonster.com]

That's an update query you copied, not an append query.

Anyway, the proper way to add data via a form is to create the Recordset to
be added to, and set that as the form's RecprdSource object. The user fills
in one textbox (or whatever) at a time, and when the user hits the Enter or
<CR> button, Access automatically adds the record to the RecordSource,
without needing any query whatsoever to append or update it. There are other
ways to skin the cat, of course, but this is the basic approach.

Sam

I am using an append query to pull data from a form and ad it to the table.
The following data can be entered via the form but the append query trying to
ad the same data causes the debugger to pop up.

2/1/06 OKI S8711243-Dir CAROL S/S -- S/S 2/22/06
OKI -- BECKY S/S 1/15/06 OKI -- KIM --S/S
11/5/05--CALL 11/2/05 TO CHECK ON THEM

Query SQL

UPDATE (AGENT INNER JOIN [MAIN FILTERED] ON (AGENT.ALIAS = [MAIN
FILTERED].AGNT_NAME_) AND (AGENT.ALIAS = [MAIN FILTERED].AGNT_NAME_)) INNER
JOIN [MAIN RAW] ON ([MAIN FILTERED].LINNUM_PL = [MAIN RAW].LINNUM_PL) AND
([MAIN FILTERED].ITEM_NUM_P = [MAIN RAW].ITEM_NUM_P) AND ([MAIN
FILTERED].SUP_PL = [MAIN RAW].SUP_PL) AND ([MAIN FILTERED].DATE_PL = [MAIN
RAW].DATE_PL) AND ([MAIN FILTERED].P_O_NUM_PH = [MAIN RAW].P_O_NUM_PH) AND
(AGENT.ALIAS = [MAIN RAW].AGNT_NAME_) SET [MAIN FILTERED].[Exp Note] =
[Forms]![FrmNotesDFUAll]![Text0], [MAIN FILTERED].[Exp Note Date] = Date()
WHERE ((([MAIN RAW].ApplyNote)=Yes) AND (([MAIN
FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[Guest]

Actually your right it is supposed to be an update query. Based on a
selection in the form the query finds all records equal to that selection and
then updates a field.

That's an update query you copied, not an append query.

Anyway, the proper way to add data via a form is to create the Recordset to
be added to, and set that as the form's RecprdSource object. The user fills
in one textbox (or whatever) at a time, and when the user hits the Enter or
<CR> button, Access automatically adds the record to the RecordSource,
without needing any query whatsoever to append or update it. There are other
ways to skin the cat, of course, but this is the basic approach.

Sam

I am using an append query to pull data from a form and ad it to the table.
The following data can be entered via the form but the append query trying to
ad the same data causes the debugger to pop up.

2/1/06 OKI S8711243-Dir CAROL S/S -- S/S 2/22/06
OKI -- BECKY S/S 1/15/06 OKI -- KIM --S/S
11/5/05--CALL 11/2/05 TO CHECK ON THEM

Query SQL

UPDATE (AGENT INNER JOIN [MAIN FILTERED] ON (AGENT.ALIAS = [MAIN
FILTERED].AGNT_NAME_) AND (AGENT.ALIAS = [MAIN FILTERED].AGNT_NAME_)) INNER
JOIN [MAIN RAW] ON ([MAIN FILTERED].LINNUM_PL = [MAIN RAW].LINNUM_PL) AND
([MAIN FILTERED].ITEM_NUM_P = [MAIN RAW].ITEM_NUM_P) AND ([MAIN
FILTERED].SUP_PL = [MAIN RAW].SUP_PL) AND ([MAIN FILTERED].DATE_PL = [MAIN
RAW].DATE_PL) AND ([MAIN FILTERED].P_O_NUM_PH = [MAIN RAW].P_O_NUM_PH) AND
(AGENT.ALIAS = [MAIN RAW].AGNT_NAME_) SET [MAIN FILTERED].[Exp Note] =
[Forms]![FrmNotesDFUAll]![Text0], [MAIN FILTERED].[Exp Note Date] = Date()
WHERE ((([MAIN RAW].ApplyNote)=Yes) AND (([MAIN
FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[OfficeDev18 via AccessMonster.com]

Unfortunately, you didn't mention (and I neglected to ask), what is the exact
text of the error message that displays when the debugger comes up?

Sam

Actually your right it is supposed to be an update query. Based on a
selection in the form the query finds all records equal to that selection and
then updates a field.

That's an update query you copied, not an append query.

[quoted text clipped - 28 lines]

FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[Guest]

That might help, sorry about that.

I get a runtime error '3001' Invalid argument.

Unfortunately, you didn't mention (and I neglected to ask), what is the exact
text of the error message that displays when the debugger comes up?

Sam

Actually your right it is supposed to be an update query. Based on a
selection in the form the query finds all records equal to that selection and
then updates a field.

That's an update query you copied, not an append query.

[quoted text clipped - 28 lines]

FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[Guest]

I also noticed that if I shrink the text down to 124 characters it goes fine.
The field it goes into on the table is set to text at 255 characters.

That might help, sorry about that.

I get a runtime error '3001' Invalid argument.

Unfortunately, you didn't mention (and I neglected to ask), what is the exact
text of the error message that displays when the debugger comes up?

Sam

Actually your right it is supposed to be an update query. Based on a
selection in the form the query finds all records equal to that selection and
then updates a field.

That's an update query you copied, not an append query.

[quoted text clipped - 28 lines]
FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[OfficeDev18 via AccessMonster.com]

Rob,

Is [MAIN FILTERED].[Exp Note] a Note field? If it is, that may be part of
your problem. If it's not, and you can get away with it, you can always use
the LTrim() function to remove extraneous (read: blank) spaces to the right
of your data. In other words, you might say [Exp Note] = LTrim(YourDataField).
See the LTrim() function in the help file.

Sam

I also noticed that if I shrink the text down to 124 characters it goes fine.
The field it goes into on the table is set to text at 255 characters.

That might help, sorry about that.

[quoted text clipped - 14 lines]

FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[OfficeDev18 via AccessMonster.com]

Oops, I meant RTrim(), not LTrim().

Sorry about that,

Sam

Rob,

Is [MAIN FILTERED].[Exp Note] a Note field? If it is, that may be part of
your problem. If it's not, and you can get away with it, you can always use
the LTrim() function to remove extraneous (read: blank) spaces to the right
of your data. In other words, you might say [Exp Note] = LTrim(YourDataField).
See the LTrim() function in the help file.

Sam

I also noticed that if I shrink the text down to 124 characters it goes fine.
The field it goes into on the table is set to text at 255 characters.

[quoted text clipped - 4 lines]

FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));

 

[Guest]

=/ No worries I figured as much. Thanks for taking the time to give me ideas
=)

Oops, I meant RTrim(), not LTrim().

Sorry about that,

Sam

Rob,

Is [MAIN FILTERED].[Exp Note] a Note field? If it is, that may be part of
your problem. If it's not, and you can get away with it, you can always use
the LTrim() function to remove extraneous (read: blank) spaces to the right
of your data. In other words, you might say [Exp Note] = LTrim(YourDataField).
See the LTrim() function in the help file.

Sam

I also noticed that if I shrink the text down to 124 characters it goes fine.
The field it goes into on the table is set to text at 255 characters.

[quoted text clipped - 4 lines]

FILTERED].ITEM_NUM_P)=[Forms]![FrmNotesDFUAll]![Combo2]) AND
((AGENT.AGNT_NAME_)=[Forms]![Frm Agent Select]![Combo5]));