How do I fix my form to allow addition of new blank record?

I used to be able to add new blank record while in the form, but I have been playing with the form to add a new field and now I can't add a new blank record.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
go to the design view of the form

turn on the Property Sheet if it is not showing by pressing Alt-Enter

click in the upper left where the rulers intersect to select the form

on the DATA tab of the Property Sheet, set Allow Additions to Yes


if, however, the RecordSource uses more than one table, it may be that you have created a recordset that cannot have new records added.  There are other ways to show information from related tables besides putting them in the RecordSource.  If this is your case, we can help you with suggestions.

Does this additional field you added come from a related table where you have a foreign key (FK) in the RecordSource?  If so, the FK can be a combobox that shows the column you need and a calculated control can then show it ~
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why would you add a new BLANK record? Generally speaking that doesn't really make much sense.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
lol, Scott ~ probably just wants a new record to fill out :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Probably ... but the wording sounds a bit odd.
DesMoinesOfficeAuthor Commented:
Thank you for your help Crystal. Yes, I do just want a new blank form to show up to fill in the information.  Sorry, if my wording sounded odd, just a newbie trying to figure out what to do and how to explain what I am trying to accomplish.
DesMoinesOfficeAuthor Commented:
I followed your directions and the property sheet data tab, allow additions is already set to yes.  Yes, this new field is from a related table.
So, your RecordSource includes more than one table.  Please post the SQL.  Make sure that both tables have primary keys defined.  It is also best to define the relationship using the relationship window where you can enforce referential integrity (RI).  It is possible that your join is incorrect or that you have done something to make the query not updateable.  To check, simply open the query in datasheet view.  Can you add a row?  If not the query is not updateable.  We may be able to solve the problem.  If not, we can offer alternatives.
DesMoinesOfficeAuthor Commented:
Pat, thanks for your help. Yes, I can add a row to the query and I can add a new record to the table, just not while in the form.  I'm sorry, but I don't know how to post the SQL. Both tables do have primary keys defined.
Open the query in design view.  Then switch to SQL view.  Copy the string and paste it here.
DesMoinesOfficeAuthor Commented:
SELECT [Session Details].ID, [Session Details].Printed, [Session Details].[Last name], [Session Details].[First name], [Session Details].[Client name], [Session Details].[Session number], [Session Details].[Session date], [Session Details].[Start time], [Session Details].[End time], [Session Details].[Elapsed Time], [Session Details].[Client appeared], [Session Details].[Client reported], [Session Details].[Focus of Session], [Session Details].[Therapeutic interventions], [Session Details].Other, [Session Details].Plan, [Session Details].[Danger to Self], [Session Details].[Danger to Others], [Session Details].[Refer to], [Session Details].[Next session], [Session Details].Therapist, [Session Details].[Psychiatric Meds Prescribed], [Session Details].[Psychiatric Meds Changed], [Session Details].[Crisis Plan in Place], [Session Details].Signature, [Session Details].[Justification text], [Session Details].[Client Number], [Session Details].[Medicaid number] AS [Session Details_Medicaid number], [Client Treatment Plan].[Client code#], [Client Treatment Plan].[Medicaid Number] AS [Client Treatment Plan_Medicaid Number], [Client Treatment Plan].[Date of Birth]
FROM [Client Treatment Plan] INNER JOIN [Session Details] ON [Client Treatment Plan].[Client name] = [Session Details].[Client name];

The new field was the Date of Birth
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:
Open your Form in design view, bring up the Form Property Sheet.  

Change the Recordset Type to what is shown in the screenshot below.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Instead of adding in the Client Treatment Plan table, do this:

make a combobox on the form

DATA tab of Property Sheet
1. RowSource
SELECT ctp.[Client name], ctp.[Client code#], ctp.[Medicaid Number], ctp.[Date of Birth]
FROM [Client Treatment Plan] as ctp
2. ControlSource = Client name

FORMAT tab of Property Sheet
1. ColumnCount = 4
2. ColumnWidths = 2;1;1;1
3. ListWidth = 5.2
4. Visible = No  'wait to change this though

OTHER tab of Property Sheet
1. Name = txtClientName

then, make textbox controls to show the information.  Set ControlSource to:
= txtClientName.Column(#)

# is the column index.  Column indexes start with 0 ... so column index 1 is really column 2.


now that is said, I used Client name because that is what you used in your SQL.  However, isn't there  a better field to link these tables together? Names are not good to use for linking because:
1. they might be duplicated
2. they have lots of characters so they are not efficient
DesMoinesOfficeAuthor Commented:
THANK YOU!!!!!  I can't tell you how happy this makes me!  I was seriously stressing about this.  "How am I gonna tell my boss I messed up the form" etc.  Thanks again for your help.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Note:  You have to be very careful with that setting. When you have more than one table in a Recordsource,  you need to be sure you are using the correct field - where that same field exists in more than one table. For example say two tables with a One to Many relationship (main table and lookup table)... and you use a (common) field from the One table on the Form for the Control Source of an object (text box, combo box, etc) - instead of the same field from the Many table (main table) - you will get some unintended results :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.