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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Move it or copy it? Please post the VBA for the onclick event for the form
button.

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.
 
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

vnormth said:
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 said:
Move it or copy it? Please post the VBA for the onclick event for the
form
button.

UpRider
Click to expand...
Click to expand...
 
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#]

UpRider said:
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

vnormth said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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

vnormth said:
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#]

UpRider said:
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

vnormth said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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#]
Click to expand...
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
 
Back
Top