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.

"Ken Sheridan" wrote:

> 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];

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.


"Will_Harris_ZA" wrote:

> 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.
> 
> "Ken Sheridan" wrote:
> 
> > 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];

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

"Dale Fye" wrote:

> 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.
> 
> 
> "Will_Harris_ZA" wrote:
> 
> > 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.
> > 
> > "Ken Sheridan" wrote:
> > 
> > > 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];