Insert a default auto-number field

[hollyneal]

I've got a call tracking system. I've got one table, Calls, that has a field Quote Number. In a separate table, Notes, I've got the field Quote Number and the field Notes. It's in a separate table because each Quote Number can have more than one Notes. The problem is that every entry in Calls has to have a Notes entry, but not every entry has to have a Quote Number. If the quote number is blank I can't tie the Notes to the particular call that they go with. I tried putting the word 'none' in as default in the Quote Number field but that's not unique either. Is there a way to insert the primary key from Calls as the primary key for the row in the Notes table? And if I can do that can I still tie multiple Notes records to each Calls record? Or is there another way to do this?

I hope this makes sense...

 

[jzwp22]

Is there a way to insert the primary key from Calls as the primary key for the row in the Notes table?

The primary key of the calls table HAS to be a foreign key in the notes table not the primary key.

You will need to create a new field in the notes table that is of the same datatype as the primary key field of the calls table. If the primary key of the call table is autonumber, the foreign key field in the notes table must be a long integer number datatype. (the autonumber is just a special case of the long integer number). For records that are already entered in the notes table, you will have to somehow determine the appropriate value to enter into the foreign key field. Once you have them all filled in, you can establish the relationship in the relationship window.

You would then create a form (based on the calls table) with a subform (based on the notes table). Access will automatically join the two. Thereafter, when you add a new record to the subform, Access will automatically fill in the key value from the main form in the appropriate control in the subform.

BTW, didn't we work on this already in the attached database?