autonumber on form??

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

Guest

I have a form used to enter data into 4 tables. For some reason unknown to
me, the autonumber for the primary key (order details : order number) doesn't
change from "autonumber" to an actual number. I must have done something to
it at some point to change this because it used to work. I'm lost as usual.
 
I have also discovered that the autonumber will work if the field for
comments (part of the same table) is filled in then the autonumber works. The
comment field is not required. If I pass over the comment then the
autonumber never converts and the record is not saved.
 
Perhaps a little misunderstanding here. "Autonumber" is a type of table
field (actually an integer that increments itself). If you have a control
on your form bound to that autonumber type field (from one of your tables),
then a integer value will be created each time a new record is added. That
is what is happening, when you enter any values into any other field on your
form.

That said, autonumber fields are best used to define a unique primary key,
NOT to generate a meaningful sequence/order number. They cannot be edited,
or overwritten. They can be deleted, but not reused. In short, they are
really for internal database use and are not commonly even shown on most
entry forms; they certainly can NOT be "converted" - though not sure what
you mean by that phrase.

Look at the table structure and relationships in the sample Northwinds
database to get a better understanding of how to organize the info in your 4
tables.
-Ed
 
Thank you Ed for your reply:
Hopefully this will explain my predicament better.

I created the form using a wizard from 4 tables. After the fact I added a
comment field to the table and then to the form. I can enter new or exitsing
customer information, then tab to another part of the form where I can enter
order details. When entering new items the autonumber(order number) control
continues to display "autonumber" and is not converted or changed to an
actual number. Because of this the order details are not saved. This
doesn't happen if I enter something into the comment control, which by the
way is not a required field for the table. I can't figure out what is wrong.
I am new to Access and require explicit directions for anything in this
program.
Thanks again,
 
I'm guessing that you have (or should have) a main form that is designed to
show a single record (like customer name and address) and has tblCustomer or
similar query as its record source. A subform control, designed as a
datasheet or continuous form (and having tblOrders, or similar query as its
record source) would then be placed on the main form, and used to show all
related orders for that customer.

Those two forms are kept synchronized by setting the master link property to
the primary key of the record source of the main form, and the child link
property to the foreign key (same data type) of the record source for the
subform. This setup will allow a single customer to have many related
orders, distinguished by date and/or order#. If all that sounds confusing
(as it should to a novice) then use the subform wizard when rebuilding your
form and it will walk you through all the steps.

Again though, your data tables need the correct structure and
relationship(s). You can copy ideas and objects from the Northwind sample
database or better still, use the inventory template to create a new
database. It will set up your tables, forms and reports from info you
pick/supply. Modify them as necessary, copy in your data, and your in
business!
Good luck
-Ed
 
You are correct about the subforms etc. Little details I forgot to mention.

So you are telling me, to fix the subform I need to rebuild it now that I
have added a field?

I don't want to trash this form because it looks much like the paper forms
that were being used by employees, it also has tab controls to links to
shipping and payment details. I've only tinkered with Northwind to try and
get ideas for macros etc. but still haven't used any. You think this would
be beneficial to copy and edit these forms and other objects?
Would I have to again rebuild the forms (subforms) as I added new fields, or
changed the formating?
Our database is not actually for a "business" so to speak, I am trying to
get it to be used for the tracking of insect releases by the state of
colorado and the requests for those insects; all while interfacing it to
GIS-not done this yet. It needs to be used for reporting and general best
practices as far as our insectary goes.
 
Well, when a form/report doesn't work for me (as I would like it) one
troubleshooting technique is to make a copy of it and simplify the copy down
to the point when it does work.

I realize that you have time invested in your app to date, but there is no
substitute for good basic design. Make sure you have the table
relationships set correctly (experiment, if you're not sure). Make sure
that any query you use as an entry form data source is updateable. Try a
simple form/subform setup (preferably made by the wizards).

If you're still stuck, zip up a copy of your mdb and send it to me.
-Ed
 
Back
Top