Append query - current record only (follow on from Ken Stafford su

[Guest]

Hi

I wish to append a new record to a separate table (before clearing the field
contents for re-entry) using a command box. There are no other unique fields
(the record entry form is a subform).

Ie: Append new record in [TableA] to [TableB] using [MyID] (autonumber in
[TableA]) to determine whether the current record is already in [TableB].

Per recommendation (appended below after this msg), the query runs perfectly.
Unfortunately however, [MyID] in [TableA] is an autonumber, and I believe it
is causing me to get the error message:
"Data type mismatch in criteria expression".

None of my cells are Null. Is there something I can do to make the query
read MyID not as an autonumber?
Is this the issue here? Is there a more efficient means of performing this?

Thanks in advance
Will

PS I have yet to go on VBA courses so please be gentle.

You'll need to run ... an append query to insert the new rows. ...For the sake of this example I'll assume a single column non-autonumber key called MyID, so the query would go like this:

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

 

[Guest]

Will,

The query below is missing a closing parenthesis. Does your query have that?

Is the [MyID] field in table B a long integer, that is how Autonumber fields
are defined? If not, change it and try your query again. It cannot be an
Autonumber field in Table B!

You might also want to check the parameters of the fields you are importing
into in Table B to make sure they are the same type as in Table A. If they
are different, it will cause this error.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi

I wish to append a new record to a separate table (before clearing the field
contents for re-entry) using a command box. There are no other unique fields
(the record entry form is a subform).

Ie: Append new record in [TableA] to [TableB] using [MyID] (autonumber in
[TableA]) to determine whether the current record is already in [TableB].

Per recommendation (appended below after this msg), the query runs perfectly.
Unfortunately however, [MyID] in [TableA] is an autonumber, and I believe it
is causing me to get the error message:
"Data type mismatch in criteria expression".

None of my cells are Null. Is there something I can do to make the query
read MyID not as an autonumber?
Is this the issue here? Is there a more efficient means of performing this?

Thanks in advance
Will

PS I have yet to go on VBA courses so please be gentle.

You'll need to run ... an append query to insert the new rows. ...For the sake of this example I'll assume a single column non-autonumber key called MyID, so the query would go like this:

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

 

[Guest]

Thank you Dale

You were on the money in changing [MyID] field in receptacle [TableB] to
long integer. Query subsequently ran perfectly.

Closing parenthesis was sorted in my query, think Ken inadvertently left it
out.

Many thanks for your help
Will

Will,

The query below is missing a closing parenthesis. Does your query have that?

Is the [MyID] field in table B a long integer, that is how Autonumber fields
are defined? If not, change it and try your query again. It cannot be an
Autonumber field in Table B!

You might also want to check the parameters of the fields you are importing
into in Table B to make sure they are the same type as in Table A. If they
are different, it will cause this error.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi

I wish to append a new record to a separate table (before clearing the field
contents for re-entry) using a command box. There are no other unique fields
(the record entry form is a subform).

Ie: Append new record in [TableA] to [TableB] using [MyID] (autonumber in
[TableA]) to determine whether the current record is already in [TableB].

Per recommendation (appended below after this msg), the query runs perfectly.
Unfortunately however, [MyID] in [TableA] is an autonumber, and I believe it
is causing me to get the error message:
"Data type mismatch in criteria expression".

None of my cells are Null. Is there something I can do to make the query
read MyID not as an autonumber?
Is this the issue here? Is there a more efficient means of performing this?

Thanks in advance
Will

PS I have yet to go on VBA courses so please be gentle.

You'll need to run ... an append query to insert the new rows. ...For the sake of this example I'll assume a single column non-autonumber key called MyID, so the query would go like this:

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];