Problem with Autonumber

[Hutch]

I am new so sorry if this is basic. I am designing a larger database, but was
able to reproduce the problem with a smaller one. Here is the problem.

I have two tables, the first is Main which has four fields,
ID (autonumber), Date, Name and OrderID (number).

The second table is Order which has two fields,
OrderID (autonumber) and ProductName.

I created a query and only included all fields from Main and only
ProductName from Order.

The problem I am having is that I do not use the ProductName field on every
data entry that is done. I get an error that it cannot locate the OrderID. I
know that this error is because I did not enter any data in the Order Table,
which did not generate the autonumber for the Order Table. I have seen this
done in the Northwind Database, but am not sure how it was done.

Can I have tables that include auto numbering, but only use the tables when
needed?

 

[Steve Schapel]

Hutch,

The purpose of your tables is not really clear. Assuming that the
OrderID field in both tables is the "data in common" on which the
relationship between the tables is based, then the fact that your
OrderID in the Order table is an autonumber (and presumably primary key
field), this would imply a 0ne-to-many relationship between the tables.
In other words, there can be more than one Main table record for any
given Order table record. Is that correct? If so, it would be unusual
to have a field like ProductName in the table on the "one" side of such
a relationship. And it would be usual to expect that there would not be
a Main table record with an OrderID that does not exist in the Order
table... in fact it would be common practice in such a table design to
define a Relationship between the tables enforcing Referential
Integrity. Does that make sense? If you need more help with this,
maybe you could explain a bit more about the data you are working with here.

By the way, on a complete tangent to your question, Date and Name are
both Reserved Words (i.e. have a special meaning) in Access, and as such
should not be used as the names of fields or controls.

 

[Hutch]

Sorry for that, I will try to explain it a little better.

I have two tables, the first is Main
ID (Autonumber) (Primary Key)
EmployeeName (text)
HoursWorked (number)
BoatID (number)

The second table is Boat
BoatID (Autonumber) (Primary Key)
Boat# (number)

I have setup a one-to-many relationship between Main and Boat Table with
BoatID. When I enter the HoursWorked and Boat# everything works fine. The
problem is not every day an employee will use a boat so no Boat# would be
entered. If I do not put in a Boat# I will get an error that the data could
not be saved because it was unable to find BoatID.

Can I enter data into one table and save it without having to enter data
into a second table that has a autonumber as the primary key.

The above database was just created to reproduce the error. My real database
has four tables and I do not want to have numerous boxes on a form that are
needed to generate a autonumber in a table that may not be needed if no data
will be going into that table.

I hope this explains wait I am looking to do.

 

[Steve Schapel]

Hutch,

At the moment I am unable to see what you might be having trouble with.
There should be no problem with entering a record in the Main table
with nothing entered in the BoatID field. If you have the field's
Required property set to Yes in the design of the table, you would have
to put one in... but that doesn't seem likely. Other than that, the
only time I would expect Access to object would be if you tried to enter
a value in the BoatID field that does not exist in the Boat table. So I
am missing something about what you are doing. You are entering the
data via a form, right? And the form is based on the Main table, right?
Probably you have a combobox, whose Row Source is the Boat table, for
the entry of the BoatID? Maybe you could give some examples of the data
you are entering, and at which point in the process you getr the error
message, and exactly what the error message says.

 

[Hutch]

Steve thanks for your help so far. I am still confused on this issue.

Maybe I need to ask this question. I have a multiple table query for a
single form. Both tables have autonumber primary keys. Can I enter data into
one table without entering data into the second table, which would not create
the autonumber.

I setup a database to test this theory. This is not a real database so I am
not using it for anything then to see if this can work.

I created the two tables, one table has:
Main (Table)
ID (primary key, autonumber)
EmployeeName (text)
BoatID (Long Integer, Relationship with Boat Table to field BoatID) One to
many

Boat (Table)
BoatID (Primary key, autonumber, Relationship with Main Table to field BoatID)
Boat# (Long Integer)

I created a query, which included,
ID, EmployeeName, BoatID from Main table
Boat# from Boat table

I created a form, which used the query. If I enter data into the form using
both fields I don't have any problems. If I just enter data only into the
employee name field and leave the Boat# field blank and try to close the form
or go to the next form I get the following error, "The Microsoft Jet database
engine cannot find a record in the table 'Boat' with key matching field(s)
'BoatID'."

Am I missing something somewhere or am I setting it up wrong. I have gone
through the Northwind database, which uses this setup very well. I entered
only one field into the form and was about to go to the next form without an
error. When I looked at the tables only one table had a entry and the next
two tables were empty. I cannot find out what is different from this table to
mine.

 

[Steve Schapel]

Hutch,

Do you have a Default Value set for the BoatID field in the Main table?

 

[Hutch]

No the settings are:
Long Integer, Increment, and Yes (No Duplicates). Format and Caption are
blank.
The database is 135kb. Is there a way I could e-mail it to you so you could
see the problem?

Thanks again

 

[Steve Schapel]

Hutch,

I was referring to the BoatID in the Main table, not the Boat table.
Are you too? If so, it sounds like you have it set as an Autonumber
data type. This should not be. It should be a Number data type, with
nothing in the Default Values property, and Indexed property should be
either No, or Yes (Duplicates Ok).

 

[Hutch]

Sorry about that, here is the settings in the Main table for BoatID:
Long Integer, Default Value 0, Required No, Indexed Yes (Duplicates OK).
I've taken the Default Value out with the same error code.

 

[Steve Schapel]

Hutch,

You will need to have a Left Join in your query, in order to allow a
Main table record without a corresponding Boat table record. The SQL of
the query will be something like this...
SELECT Main.ID, Main.EmployeeName, Main.BoatID, Boat.BoatNumber
FROM Main LEFT JOIN Boat ON Main.BoatID = Boat.BoatID
Is that what you've got?

As an aside, you will see in my example I used BoatNumber instead of
Boat#, since it is not a good idea to use a # as part of the name of a
field.

 

[Hutch]

Thank you very much. I think that solved my problem. My real database seems
to be working fine with those changes.