Clone Previous Record

  • Thread starter Thread starter DD
  • Start date Start date
D

DD

Thanks in advance! I have a simple form which consists of
only about 10 fields. I want the user to be able to click
a command button and generate a new record with all of the
data from the most recent previous record as default. My
primary key is an ID field.
 
If you know how to execute queries, this should be what you need:

Insert into tblTable (field1, field2, ... fieldx)
Select Top 1 (field1, field2, ... fieldx)
From tblTable
Order By IDField Desc

When the user presses the command button, execute the query. This will push
the previous record into the table. This happens because it takes the top
record (Top 1) from the table where you order the records in descending
order based on the Autonumber field.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Hi,

I am very interested in this query. However, there seems
to be an error with this query syntax. Access is
saying "Syntax error (comma)in query expression"; then it
list the fields. Can anybody help with this, please?
 
Here it is:

INSERT INTO HIPlanNames ( STATE, PlanName, FFS, HMO, PPO,
POS, [DEFAULT], SOURCE, CODE_TEST, DEFAULT_TEST,
ENROLLMENT, PPOInd, STAFF, IPA, NETWORK, [GROUP],
MEDICARE, MEDICAID, LastUpdated )
SELECT TOP 1 HIPlanNames.STATE, HIPlanNames.PlanName,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.CODE_TEST, HIPlanNames.DEFAULT_TEST,
HIPlanNames.ENROLLMENT, HIPlanNames.PPOInd,
HIPlanNames.STAFF, HIPlanNames.IPA, HIPlanNames.NETWORK,
HIPlanNames.GROUP, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanID)=[Please enter the record
number you want to duplicate]));

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

Thanks.
 
Some immediate things I see:

HIPlanNames.DEFAULT .... should be HIPlanNames.[DEFAULT]
HIPlanNames.GROUP .... should be HIPlanNames.[GROUP]

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?
Click to expand...

You could make a slight tweak to the query you have below and have the user
enter a list of numbers separated by commas, but it would be difficult to
validate.

There are other means that you could do this.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Luther said:
Here it is:

INSERT INTO HIPlanNames ( STATE, PlanName, FFS, HMO, PPO,
POS, [DEFAULT], SOURCE, CODE_TEST, DEFAULT_TEST,
ENROLLMENT, PPOInd, STAFF, IPA, NETWORK, [GROUP],
MEDICARE, MEDICAID, LastUpdated )
SELECT TOP 1 HIPlanNames.STATE, HIPlanNames.PlanName,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.CODE_TEST, HIPlanNames.DEFAULT_TEST,
HIPlanNames.ENROLLMENT, HIPlanNames.PPOInd,
HIPlanNames.STAFF, HIPlanNames.IPA, HIPlanNames.NETWORK,
HIPlanNames.GROUP, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanID)=[Please enter the record
number you want to duplicate]));

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

Thanks.
-----Original Message-----
Post the query that you had created so we can examine what Access is seeing.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



.
Click to expand...
Click to expand...
 
I have corrected these names, and it works now...(didn't
realized they were access keywords. About the parameter,
can you show me the code that would do that? I would like
to experiment with that.

Many Thanks.
-----Original Message-----
Some immediate things I see:

HIPlanNames.DEFAULT .... should be HIPlanNames.[DEFAULT]
HIPlanNames.GROUP .... should be HIPlanNames.[GROUP]

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?
Click to expand...

You could make a slight tweak to the query you have below and have the user
enter a list of numbers separated by commas, but it would be difficult to
validate.

There are other means that you could do this.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Here it is:

INSERT INTO HIPlanNames ( STATE, PlanName, FFS, HMO, PPO,
POS, [DEFAULT], SOURCE, CODE_TEST, DEFAULT_TEST,
ENROLLMENT, PPOInd, STAFF, IPA, NETWORK, [GROUP],
MEDICARE, MEDICAID, LastUpdated )
SELECT TOP 1 HIPlanNames.STATE, HIPlanNames.PlanName,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.CODE_TEST, HIPlanNames.DEFAULT_TEST,
HIPlanNames.ENROLLMENT, HIPlanNames.PPOInd,
HIPlanNames.STAFF, HIPlanNames.IPA, HIPlanNames.NETWORK,
HIPlanNames.GROUP, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanID)=[Please enter the record
number you want to duplicate]));

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

Thanks.
-----Original Message-----
Post the query that you had created so we can examine what Access is seeing.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Hi,

I am very interested in this query. However, there seems
to be an error with this query syntax. Access is
saying "Syntax error (comma)in query expression";
Click to expand...
then
it
list the fields. Can anybody help with this, please?

-----Original Message-----
If you know how to execute queries, this should be what
you need:

Insert into tblTable (field1, field2, ... fieldx)
Select Top 1 (field1, field2, ... fieldx)
From tblTable
Order By IDField Desc

When the user presses the command button, execute the
query. This will push
the previous record into the table. This happens because
it takes the top
record (Top 1) from the table where you order the records
in descending
order based on the Autonumber field.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message
Thanks in advance! I have a simple form which consists
of
only about 10 fields. I want the user to be able to
click
a command button and generate a new record with
Click to expand...
all
of
the
data from the most recent previous record as default. My
primary key is an ID field.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Set the following WHERE clause:

WHERE (((HIPlanNames.PlanID) IN ([Please enter the record
number you want to duplicate])));
Click to expand...
Click to expand...

When prompt, you have to enter a comma deliminted list: 3, 4, 5, 6

I have not tested this but, in theory, it should work.

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Luther said:
I have corrected these names, and it works now...(didn't
realized they were access keywords. About the parameter,
can you show me the code that would do that? I would like
to experiment with that.

Many Thanks.
-----Original Message-----
Some immediate things I see:

HIPlanNames.DEFAULT .... should be HIPlanNames.[DEFAULT]
HIPlanNames.GROUP .... should be HIPlanNames.[GROUP]

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?
Click to expand...

You could make a slight tweak to the query you have below and have the user
enter a list of numbers separated by commas, but it would be difficult to
validate.

There are other means that you could do this.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Here it is:

INSERT INTO HIPlanNames ( STATE, PlanName, FFS, HMO, PPO,
POS, [DEFAULT], SOURCE, CODE_TEST, DEFAULT_TEST,
ENROLLMENT, PPOInd, STAFF, IPA, NETWORK, [GROUP],
MEDICARE, MEDICAID, LastUpdated )
SELECT TOP 1 HIPlanNames.STATE, HIPlanNames.PlanName,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.CODE_TEST, HIPlanNames.DEFAULT_TEST,
HIPlanNames.ENROLLMENT, HIPlanNames.PPOInd,
HIPlanNames.STAFF, HIPlanNames.IPA, HIPlanNames.NETWORK,
HIPlanNames.GROUP, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanID)=[Please enter the record
number you want to duplicate]));

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

Thanks.
-----Original Message-----
Post the query that you had created so we can examine
what Access is seeing.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message
Hi,

I am very interested in this query. However, there seems
to be an error with this query syntax. Access is
saying "Syntax error (comma)in query expression"; then
it
list the fields. Can anybody help with this, please?

-----Original Message-----
If you know how to execute queries, this should be what
you need:

Insert into tblTable (field1, field2, ... fieldx)
Select Top 1 (field1, field2, ... fieldx)
From tblTable
Order By IDField Desc

When the user presses the command button, execute the
query. This will push
the previous record into the table. This happens
because
it takes the top
record (Top 1) from the table where you order the
records
in descending
order based on the Autonumber field.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic &
Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message
Thanks in advance! I have a simple form which
consists
of
only about 10 fields. I want the user to be able to
click
a command button and generate a new record with all
of
the
data from the most recent previous record as
default. My
primary key is an ID field.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi,

The WHERE clause gave 2 messages:
1. when you enter 3, 4 (notice space after comma), access
says "you're about to append 0 row(s)"
2. when you enter 3,4 (no space after comma), access
says "you're about to append 1 row(s)"

Please help !!!
-----Original Message-----

Set the following WHERE clause:

WHERE (((HIPlanNames.PlanID) IN ([Please enter the record
number you want to duplicate])));
Click to expand...
Click to expand...

When prompt, you have to enter a comma deliminted list: 3, 4, 5, 6

I have not tested this but, in theory, it should work.

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I have corrected these names, and it works now...(didn't
realized they were access keywords. About the parameter,
can you show me the code that would do that? I would like
to experiment with that.

Many Thanks.
-----Original Message-----
Some immediate things I see:

HIPlanNames.DEFAULT .... should be HIPlanNames. [DEFAULT]
HIPlanNames.GROUP .... should be HIPlanNames.[GROUP]


One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

You could make a slight tweak to the query you have
Click to expand...
below
and have the user
enter a list of numbers separated by commas, but it
Click to expand...
would
be difficult to
validate.

There are other means that you could do this.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Here it is:

INSERT INTO HIPlanNames ( STATE, PlanName, FFS, HMO, PPO,
POS, [DEFAULT], SOURCE, CODE_TEST, DEFAULT_TEST,
ENROLLMENT, PPOInd, STAFF, IPA, NETWORK, [GROUP],
MEDICARE, MEDICAID, LastUpdated )
SELECT TOP 1 HIPlanNames.STATE, HIPlanNames.PlanName,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.CODE_TEST, HIPlanNames.DEFAULT_TEST,
HIPlanNames.ENROLLMENT, HIPlanNames.PPOInd,
HIPlanNames.STAFF, HIPlanNames.IPA, HIPlanNames.NETWORK,
HIPlanNames.GROUP, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanID)=[Please enter the record
number you want to duplicate]));

One other thing. Is it possible to allow the user to put
multiple record numbers in the parameter for PlanID?

Thanks.
-----Original Message-----
Post the query that you had created so we can examine
what Access is seeing.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message
Hi,

I am very interested in this query. However, there seems
to be an error with this query syntax. Access is
saying "Syntax error (comma)in query expression"; then
it
list the fields. Can anybody help with this, please?

-----Original Message-----
If you know how to execute queries, this should
Click to expand...
be
what
you need:

Insert into tblTable (field1, field2, ... fieldx)
Select Top 1 (field1, field2, ... fieldx)
From tblTable
Order By IDField Desc

When the user presses the command button, execute the
query. This will push
the previous record into the table. This happens
because
it takes the top
record (Top 1) from the table where you order the
records
in descending
order based on the Autonumber field.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic &
Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message
Thanks in advance! I have a simple form which
consists
of
only about 10 fields. I want the user to be
Click to expand...
able
to
click
a command button and generate a new record with all
of
the
data from the most recent previous record as
default. My
primary key is an ID field.


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Luther said:
Hi,

The WHERE clause gave 2 messages:
1. when you enter 3, 4 (notice space after comma), access
says "you're about to append 0 row(s)"
2. when you enter 3,4 (no space after comma), access
says "you're about to append 1 row(s)"

Please help !!!
Click to expand...


What a pain.
You would think after all these years MSFT would expose the contents of the
previous record with something other than a "ctrl :"
When I switched from DOS and Revelation to Windows and Access, it was the
first thing I missed.
Well, the second, but mbasic is slowly catching up with Pick Basic wrt
string handling.
 
Back
Top