Form --> SubForm - Populating AutoNumber Field Before the Switch

[MamadouNdaw]

I have a simple database that acts as a dictionary. There is a One to Many relationship between the word-list table and the definitions table. Each table has an AutoNumber field as the primary key.

I have setup a query to display all of the words in the dictionary and their corresponding definitions. This query is the record source for my dictionary editing form. I have added all the relevant fields without adding the primary key fields, as they are autonumber fields.

When adding a new word to the dictionary, the primary key field on the one side of the relationship does not append, causing an error (null value not allowed for the primary key of the word-list table) when switching to the subform for the 'many' side of the relationship. I would like for the autonumber field to update automatically upon making a new entry.

Using "DoCmd.RunCommand acSaveRecord" after update does not cause the autonumber field to append. I know there must be a simple snippet of code that lets appends to the autonumber field.

Note: This does work when using the corresponding tables as the record source. But, it creates an error when attempting to delete an entry. I prefer to pass through the query.

Thanks Guys

 

[Banana]

I'm pretty sure that if you leave the autonumber blank, it should be able to come up with a new autonumber for the new record.

Assuming your table structure is:

tblWords

WordID (A autonumber PK)
Word
Pronunciation

tblDefinition

DefinitionID (A autonumber PK)
WordID (long integer number)
Definition

And you've defined a relationship between tblWord.WordID and tblDefinition.WordID with Referential Integrity enforced, then the query you want to have would look like this:

SELECT tblDefinition.WordID, Word, Pronunciation, Definition 
FROM tblWord, tblDefinition;

The query should be updateable (you can test it in the query's datasheet view; if it works in query's datasheet, then it'll work likewise as a recordsource for the form you want to bind it to.

 

[MamadouNdaw]

That is exactly how my tables are set up. My query is as follows:

SELECT tblWolofWords.Word, tblWolofToEnglish.WordID, tblWolofToEnglish.DefinitionID, tblWolofToEnglish.PartOfSpeech, tblWolofToEnglish.Definition
FROM tblWolofWords INNER JOIN tblWolofToEnglish ON tblWolofWords.WordID = tblWolofToEnglish.WordID
ORDER BY tblWolofWords.Word;

It updates fine in datasheet view. But, with my form, entering the word does not update the autonumber field. Consequently, an error is produced due to allowing a null value in the key field.

 

[Banana]

Ok.

Try deleting that field with the key. See if you can update it.

 

[MamadouNdaw]

The field with the key is not on the form. Let me explain in greater detail.

2 Tables

tblWords
_____WordID (AutoNumber Key)
_____Word

tblDefinitions
_____DefinitionID (AutoNumber Key)
_____WordID (Long Integer)
_____PartOfSpeech
_____Definition

Referential Integrity is on.

2 Forms:

Mainform: frmAddEditWords
Subform: sfrmAddEditWords

Record Source: qryDictionary

The mainform has just one text box with the field "Word" as the control source. The subform is next to this field and consists of "PartofSpeech" and "Definition" fields. The subform has no problems and updates the Autonumber key "DefinitionID" when the "PartOfSpeech" box is filled in. The "PartofSpeech" text box is a drop down list referenced to a table.

The problem is on the mainform, where only a single text box is located. Upon completing a new entry, and clicking in the subform, I receive the error "You tried to assign a null value to a variable that is not a variant data type." Any additions to the subform cannot be saved and must be forced to close.

Going back to my tables, tblWords has the entry that I tried to enter complete with an AutoNumberID. The tblDefinitions has no corresponding entries as the integer WordID field cannot seem to be fill itself in.

I know I'm overlooking something simple.