Autonumber field not populating

[Pete-RM]

Hi Everyone



I have attached my database that allows me to create meal plans and assign them to certain days.

I am having a problem with my form called 'frmMainMeal'.

When I try to add a row in a NEW record (a new meal) I get a message saying "You cannot add or change a record because a related record is required in table 'Meal'". This does not happen when adding rows in existing records.

I think the issue is that my MealID field on my main form does not populate. It just says "(New)" instead of showing the next available number (it is set to autonumber).

Therefore I get that error message because there is no record in my table to apply the new row to.

Please help me get around this problem and make my MealID populate properly.

[ItsMe]

The Autonumber will not be generated until there is a value assigned to one of the bound controls associated with that table. At that time the Autonumber will appear. However, it will not be committed to the table until after the record is saved. You will have to create a record within the form's recordset and you may even have to commit this record to the table before committing a record in another table where referential integrity is enforced.


Another approach may be to remove the relationships and any rules for enforcing referential integrity. Using this approach will remove the benefit of cascading deletes, etc.

[Pete-RM]

Is there a way to have the autonumber appear when I go into a new record in my form and also have it populate in tables / queries?

I tried removing referential integrity but I still could not save new records in my form

[ItsMe]

The table named Meal only has one column. I added a second column and named it AttributeOne. I then went to design view of frmMainMeal. I added a bound control for the AttributeOne field and saved the form. I then opened frmMainMeal in form view and went to a new record. In the AttributeOne field, I placed a value and then successfully created a new record from within the subform.

[Pete-RM]

Thanks! that worked like a charm!

I have another question to help me simplify the process:

I would like to create a command button on frmMainMeal that, when clicked, will fill the AttributeOne field with a desired text and thus "activate" the autonumber in the MealID field.

Also, is it possible to have all items on the mainform an subform "greyed out" until I click the command button?

[ItsMe]

I would like to create a command button on frmMainMeal that, when clicked, will fill the AttributeOne field with a desired text and thus "activate" the autonumber in the MealID field.

I am not sure what you mean by 'desired text'. You can use VBA behind a click event of a command button. The VBA would look like this.

Me.AttributeOne.Value = "Some desirable text"
'or
Me!AttributeOne = "Some desirable text"

The VBA code goes within a procedure in the Form's Module. You can go to the Event tab under the property sheet while your command button is selected. There, you will find a list of Events. Beside the Click Event, to the right, is an ellipses(...). Click the ellipses and, then, double click "Code Builder" to launch the VBA editor. When the VBA editor opens, your cursor will be in the middle of your new click event handler/procedure. This is where your code goes.

Also, is it possible to have all items on the mainform an subform "greyed out" until I click the command button?

There are two properties for most controls that will, when set to the correct combination, cause the control to be greyed out and unavailable for the user. The two properties are Enabled and Locked. These properties are located under the Data tab within the Property Sheet. The defaults are Enabled = Yes and Locked = No. To grey out a control, set its properties (within the Properties Sheet) to the following ...
Enabled = No
Locked = No

You can adjust the properties of controls via VBA. For instance, you can place the following within a Click Event of a command button.

Me.Text19.Enabled = True

So far, I have answered your specific questions without offering my opinion on what a Best Practice may or may not be. I feel I should mention that your example DB you uploaded does not seem to be following a standard convention. A typical Main Form/Subform arrangement for data entry would use the main form to create a new record and the subform would show the summary of relevant records. I see that you have a desire for datasheet view and a summary. In this case, an unbound main form may be the best approach.

You can have an unbound main form and place a subform control in it. The subform's Source Object can be a bound form (like in your example). Unbound controls within your Main Form can have expressions that reference the subform and display summaries of the subform's recordset.