Adding new records to tables via a form

[Megacitizen]

After countless efforts, I have finally managed to get a form that displays my Teams as from the correct Bases and Nations, and for those allocated regions, I have been able to input the correct info afterwards. Hallelujah!

My next query is to be able to use the form to add new teams not currently in our tables. On testing my form, I was unable to add in a new team, getting instead a warning macro "you must enter a value in the 't_Teams.BaseID' field". I was under the impression that if I tried to key in a new team, it would automatically be assigned a new ID which would become its PK.

Is there a code I need to use to be able to add new records, and would it be an AfterEvent based code?

Sorry for asking so many stupid questions, but I really honestly have little clue what I'm letting myself in for.

Using MS AccDB 2010

 

[PNGBill]

Should be a simple task.
If your dtabase is new, small and with non sensitive data can you post a copy and someone can check it.

 

[Megacitizen]

Should be a simple task.
If your dtabase is new, small and with non sensitive data can you post a copy and someone can check it.

Here it is: - I realise some of the fields SHOULD be in seperate tables, but I've only just discovered a video about joining 3 or more tables in a single sql join and haven't got round to sorting my tables out again yet.

View attachment Database1.accdb

 

[PNGBill]

Study your tables and their relationships. Research Normalisation.

t_Teams should not have fields BaseName, BaseLocation and Region.
This data can be queried from the relevant tables as required.

Before you can add a t_Teams record you do need to first have any relevant records in t_Bases as this field is Required in t_Teams.

Because field BaseID is Required in t_Teams, you must enter data in this field when creating a new record.
You can do this by either adding the field to StatsForm and or giving a Default value to BaseID. You would not blindly use a default value if it would mean your data would be corrupeted.

I have added the field to your form in a combo box control.

 

[Megacitizen]

Managed to get a newer form set up, but I still have the problem of not being able to get new data entered in. The form will accept data keyed in the first form but won't save it when I try to move onto the next form. Our intention is that the user should be able to key in all info as though it was all new, on each page, with the comboboxes for the more common occurrances of Bases and Nations. I was under the impression that all new data when entered on the form, would automatically be added to it's relevant table, complete with Autonumber ID.

All PK/FK IDs in the tables are set up to be Autonumber and thus are not shown on the main form itself.

Any ideas?

 

[Geotch]

Is the control source of the form set to the table? Go to form properties, all tab very first property is the control source. You should see the table name.

 

[Megacitizen]

Control source is set to the correct table.

The idea is for the user to key the info into the field and the combo box will select the correct name if it's already in the table or if not, add it to the table, automatically allocating a new PK etc. At the moment, it's not accepting any new information not already in the table.