Access:How can a form button moves the record to another table?

[vnormth]

I'm trying to use an Action Query with the INSERT INTO functionality but I
can't seem to get it to use the current record displayed in the form (no
row(s) get appended into the destination db).

Thanks!

 

[UpRider]

Move it or copy it? Please post the VBA for the onclick event for the form
button.

UpRider

 

[vnormth]

I'm trying to use an Append Query whose SQL looks like this:

INSERT INTO Trades ( [Month], Commodity, [Year], Pattern, [Trend Seeker
Direction], [Trigger Price] )
SELECT Radar.Month, Radar.Commodity, Radar.Year, Radar.Pattern, Radar.[TS
Direction], Radar.[Trigger Price]
FROM Radar;


but it copies ALL the rows in the Radar table into the Trades table, when I
want only the row of what's displayed in the Form.

 

[UpRider]

You need a WHERE clause to specify a unique identifier (the PK would be
good) for the record in Radar that you want to transfer.

UpRider

I'm trying to use an Append Query whose SQL looks like this:

INSERT INTO Trades ( [Month], Commodity, [Year], Pattern, [Trend Seeker
Direction], [Trigger Price] )
SELECT Radar.Month, Radar.Commodity, Radar.Year, Radar.Pattern, Radar.[TS
Direction], Radar.[Trigger Price]
FROM Radar;


but it copies ALL the rows in the Radar table into the Trades table, when
I
want only the row of what's displayed in the Form.

Move it or copy it? Please post the VBA for the onclick event for the
form
button.

UpRider

 

[vnormth]

Ok UpRider (hey, you're my new best friend, btw - THANKS for all this!)...I
had actually played around with that but I'm clearly not spcificying the
right param. [Contract#] is the PK in the Radar table, but how do I furnish
the Contract# value currently being displayed in the form?

I keep ending up doing something dumb like WHERE [Contract#] = [Contract#]

You need a WHERE clause to specify a unique identifier (the PK would be
good) for the record in Radar that you want to transfer.

UpRider

I'm trying to use an Append Query whose SQL looks like this:

INSERT INTO Trades ( [Month], Commodity, [Year], Pattern, [Trend Seeker
Direction], [Trigger Price] )
SELECT Radar.Month, Radar.Commodity, Radar.Year, Radar.Pattern, Radar.[TS
Direction], Radar.[Trigger Price]
FROM Radar;


but it copies ALL the rows in the Radar table into the Trades table, when
I
want only the row of what's displayed in the Form.

Move it or copy it? Please post the VBA for the onclick event for the
form
button.

UpRider

I'm trying to use an Action Query with the INSERT INTO functionality
but I
can't seem to get it to use the current record displayed in the form
(no
row(s) get appended into the destination db).

Thanks!

 

[UpRider]

If you are transferring all the fields, you don't need to name each one,
just use *
I ASKED you to post the code in the update button, but you DID NOT, so I
have to ASSUME you are building the SQL string for the query. Like this:

strSQL = "INSERT INTO Trades select * from Radar " _
& "WHERE Contract# = " & [Contract#]

If Contract# is a text field you have to say

& "WHERE Contract# = " & chr$(39) & [Contract#] & chr$(39)

UpRider

Ok UpRider (hey, you're my new best friend, btw - THANKS for all
this!)...I
had actually played around with that but I'm clearly not spcificying the
right param. [Contract#] is the PK in the Radar table, but how do I
furnish
the Contract# value currently being displayed in the form?

I keep ending up doing something dumb like WHERE [Contract#] = [Contract#]

You need a WHERE clause to specify a unique identifier (the PK would be
good) for the record in Radar that you want to transfer.

UpRider

I'm trying to use an Append Query whose SQL looks like this:

INSERT INTO Trades ( [Month], Commodity, [Year], Pattern, [Trend Seeker
Direction], [Trigger Price] )
SELECT Radar.Month, Radar.Commodity, Radar.Year, Radar.Pattern,
Radar.[TS
Direction], Radar.[Trigger Price]
FROM Radar;


but it copies ALL the rows in the Radar table into the Trades table,
when
I
want only the row of what's displayed in the Form.

:

Move it or copy it? Please post the VBA for the onclick event for the
form
button.

UpRider

I'm trying to use an Action Query with the INSERT INTO functionality
but I
can't seem to get it to use the current record displayed in the form
(no
row(s) get appended into the destination db).

Thanks!

 

[Bob Quintal]

Ok UpRider (hey, you're my new best friend, btw - THANKS for all
this!)...I had actually played around with that but I'm clearly
not spcificying the right param. [Contract#] is the PK in the
Radar table, but how do I furnish the Contract# value currently
being displayed in the form?

I keep ending up doing something dumb like WHERE [Contract#] =
[Contract#]

You need to tell the query how to find the contract number to use on
each side of the equal sign
WHERE [RADAR].[contract#] = Forms!formname![Contract#]

Change formname to the actual name of your form