Allen Brownes Copying forms/Subforms

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

Guest

I am a newby with access, but have actually created a Customer/Orders
program that works well. Tables are: Orders, Products, Order Details, and
Customers. I inserted Allen's code found at
"http://allenbrowne.com/ser-57.html" into the Northwind data base and it
worked perfectly. I followed his instructions as best I could but the code
hung up at the ".addNew". When I put the "OrderID" in there the error says
that it can't duplicate the field and since it is a primary field, I can't
click the Duplicate OK in the Table Design Field. When I remove the "OrderID"
from the .addNew, the code proceeds to the 1ngID=OrderID and stops because
there is a null?? I hope that maybe Allen or someone out there can give me
some insight on this. This is all I need to finish my program. I know I'm
over my head here. Thanks for any help.
Ron
 
If the AddNew line fails, choose References on the Tools menu (from the code
window), and make sure you have the box checked next to:
Microsoft DAO 3.6 Libraray

The code uses a variable named LNGID, not 1NGID.

The code assumes OrderID is an AutoNumber. If it is, and the Update
succeeded, it will not be Null. If it is not an autonumber, you need to work
on your table, as you seem to have a number field that is (presumably
primary key) but the save worked without assigning a value to it.

HTH
 
Allen
Thanks for your help. The reference was set to 2.1. I changed it to DOA 3.6.
I also looked at my Orders Table "OrderID" to be sure it was on auto number.
It was. I am still getting the error message #3022: Not successful because it
would create duplicate values in the index, primary key, or relationship. I
will double check everything again tomorrow. I did have LNGID in there.
Thanks again for your help.
Ron
 
Duplicate suggests Access is trying to assign the same autonumber again, or
perhaps the duplicate is on another field where you specified a "No
Duplicates" index?

Presumably you are not assigning a value to the OrderID. Try adding a new
record to the table manually. If this also fails, post back for a fix for
this bug.

If it is not the autonumber, open the table in design view, and open the
Indexes dialog (View menu). Look through the list in the dialog to see what
else might be marked Unique.
 
I checked my tables. All tables are "Duplicates OK", except for the primary
keys like "OrderID". All of my primary keys are autonumber.

I entered a new order manually. That works fine.

The only things marked unique in the indexes were the primary keys.

I copied your code to my program and put all the fields from the main form:
!OrderID=Me.OrderID
!FirstName=Me.FirstName
!LastName=Me.LastName
Etc.

I don't know what I've done wrong here.
Thanks for trying
 
Is OrderID the primary key?

If so, omit the line:
!OrderID=Me.OrderID

You want to duplicated order to have a new number, don't you?
 
The "OrderID" is the primary key. I omitted it. I also found a spelling error
I made in the code on one of the"Duplicate the releted records" fields. Now
the code runs to line "DBEngine(0) (0).Execute strSl,. The error code is 3346
"Number of Query Values & Destination Fields Are Not The Same". My
OrderDetails table, OrderDetails query, Orders subform, and code all match.
The code runs fine up to this point, inserting the duplicated information
into the Customer and OrderDetails tables, creating a new OrderID number,
which is what I want. I don't understand why I'm hung up here.
Thanks for your help------and patience.
 
In the lines above this Execute line, check the SQL statement. Count the
number of commas inside the brackets, and compare to the number of commas in
the SELECT clause. Also, if you have spaces or other odd characters in the
field or table names, enclose the names in square brackets.

The miscount is between the number of fields in brackets (3 in the example
below), and the number in the field list of the 2nd SELECT (which must be
the same number):
SELECT INTO Table1 ([F1], [F2], [F3])
SELECT [F1], [F2], [F3] FROM Table2
WHERE ...
 
THAT DID IT!!!
I'm so excited. Again, thanks for your help and patience with me.

Ron Weaver

Allen Browne said:
In the lines above this Execute line, check the SQL statement. Count the
number of commas inside the brackets, and compare to the number of commas in
the SELECT clause. Also, if you have spaces or other odd characters in the
field or table names, enclose the names in square brackets.

The miscount is between the number of fields in brackets (3 in the example
below), and the number in the field list of the 2nd SELECT (which must be
the same number):
SELECT INTO Table1 ([F1], [F2], [F3])
SELECT [F1], [F2], [F3] FROM Table2
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ron Weaver said:
The "OrderID" is the primary key. I omitted it. I also found a spelling
error
I made in the code on one of the"Duplicate the releted records" fields.
Now
the code runs to line "DBEngine(0) (0).Execute strSl,. The error code is
3346
"Number of Query Values & Destination Fields Are Not The Same". My
OrderDetails table, OrderDetails query, Orders subform, and code all
match.
The code runs fine up to this point, inserting the duplicated information
into the Customer and OrderDetails tables, creating a new OrderID number,
which is what I want. I don't understand why I'm hung up here.
Thanks for your help------and patience.
Click to expand...
Click to expand...
 
http://www.microsoft.com/office/com...g=microsoft.public.access.modulesdaovba&fltr=

Hi Ron above is a link to my thread. I was hoping you could help me out. I
am trying to do the smae thing as you however I can't get Allens code to work
for me. I was hoping you could help me.

I look forward to hearing from you. Thanks.

Ron Weaver said:
THAT DID IT!!!
I'm so excited. Again, thanks for your help and patience with me.

Ron Weaver

Allen Browne said:
In the lines above this Execute line, check the SQL statement. Count the
number of commas inside the brackets, and compare to the number of commas in
the SELECT clause. Also, if you have spaces or other odd characters in the
field or table names, enclose the names in square brackets.

The miscount is between the number of fields in brackets (3 in the example
below), and the number in the field list of the 2nd SELECT (which must be
the same number):
SELECT INTO Table1 ([F1], [F2], [F3])
SELECT [F1], [F2], [F3] FROM Table2
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ron Weaver said:
The "OrderID" is the primary key. I omitted it. I also found a spelling
error
I made in the code on one of the"Duplicate the releted records" fields.
Now
the code runs to line "DBEngine(0) (0).Execute strSl,. The error code is
3346
"Number of Query Values & Destination Fields Are Not The Same". My
OrderDetails table, OrderDetails query, Orders subform, and code all
match.
The code runs fine up to this point, inserting the duplicated information
into the Customer and OrderDetails tables, creating a new OrderID number,
which is what I want. I don't understand why I'm hung up here.
Thanks for your help------and patience.

:

Is OrderID the primary key?

If so, omit the line:
!OrderID=Me.OrderID

You want to duplicated order to have a new number, don't you?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I checked my tables. All tables are "Duplicates OK", except for the
primary
keys like "OrderID". All of my primary keys are autonumber.

I entered a new order manually. That works fine.

The only things marked unique in the indexes were the primary keys.

I copied your code to my program and put all the fields from the main
form:
!OrderID=Me.OrderID
!FirstName=Me.FirstName
!LastName=Me.LastName
Etc.

I don't know what I've done wrong here.
Thanks for trying
:

Duplicate suggests Access is trying to assign the same autonumber
again,
or
perhaps the duplicate is on another field where you specified a "No
Duplicates" index?

Presumably you are not assigning a value to the OrderID. Try adding a
new
record to the table manually. If this also fails, post back for a fix
for
this bug.

If it is not the autonumber, open the table in design view, and open
the
Indexes dialog (View menu). Look through the list in the dialog to see
what
else might be marked Unique.

Allen
Thanks for your help. The reference was set to 2.1. I changed it to
DOA
3.6.
I also looked at my Orders Table "OrderID" to be sure it was on auto
number.
It was. I am still getting the error message #3022: Not successful
because
it
would create duplicate values in the index, primary key, or
relationship.
I
will double check everything again tomorrow. I did have LNGID in
there.
Thanks again for your help.
Ron

:

If the AddNew line fails, choose References on the Tools menu (from
the
code
window), and make sure you have the box checked next to:
Microsoft DAO 3.6 Libraray

The code uses a variable named LNGID, not 1NGID.

The code assumes OrderID is an AutoNumber. If it is, and the Update
succeeded, it will not be Null. If it is not an autonumber, you
need
to
work
on your table, as you seem to have a number field that is
(presumably
primary key) but the save worked without assigning a value to it.

I am a newby with access, but have actually created a
Customer/Orders
program that works well. Tables are: Orders, Products, Order
Details,
and
Customers. I inserted Allen's code found at
"http://allenbrowne.com/ser-57.html" into the Northwind data base
and
it
worked perfectly. I followed his instructions as best I could but
the
code
hung up at the ".addNew". When I put the "OrderID" in there the
error
says
that it can't duplicate the field and since it is a primary
field, I
can't
click the Duplicate OK in the Table Design Field. When I remove
the
"OrderID"
from the .addNew, the code proceeds to the 1ngID=OrderID and
stops
because
there is a null?? I hope that maybe Allen or someone out there
can
give
me
some insight on this. This is all I need to finish my program. I
know
I'm
over my head here. Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top