Unable To Add Records Via Form

[CT]

Hello,

I'm fairly new to Access so I'm not sure if this is an easy one for all of
you...

I'm running Access 2003. I have two tables and created a relationship
between 1 field in table1 and 1 field in table2. The type of relationship
is to only display rows where the fields are equal.

I created a form using fields from table1 and table2. The data displays
correctly in the form, however when I try to add a new record the add record
arrow is greyed out. Also, if I try to edit any existing fields, it does
not allow me to. No error, just unable to edit the fields.

Is there some type of protection that I need to modify in order to
add/modify the tables via this form?

Thanks

 

[Ken Snell \(MVP\)]

An updatable query allows you to edit existing data or to add new records or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you build
a form based on such a query the form also will not let you do such things.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc

 

[CT]

Thanks Ken, but my form is not build off of a query, it's built off of 2
tables.

I need to be able to add/edit fields in those tables via a form. But, when
I try to type in a field on the form, nothing happens. And I'm not able to
add a new record via the form. It seems like I'm only able to view existing
data in the tables via this form.

 

[Rick Brandt]

Thanks Ken, but my form is not build off of a query, it's built off
of 2 tables.

Um, that IS a query. If you didn't build it then the form wizard did.

I need to be able to add/edit fields in those tables via a form.
But, when I try to type in a field on the form, nothing happens. And
I'm not able to add a new record via the form. It seems like I'm only
able to view existing data in the tables via this form.

Multi-table queries can often be made editable if only a couple tables are
involved, but generally it is a bad idea. Better to use a form with a
subform (one table each).

 

[CT]

Thanks..like I said..fairly new to this..

Ok ..so I'm trying to create a subform now but having a different problem!
I'm following these directions from the HELP in Access.. I've added the
fields from each table that I want in the form (using the Form Wizard)..now
I get to this part in the directions:

"When you click Next, if you set up the relationships correctly before
starting the wizard, the wizard asks which table or query you want to view
by. "

I'm not being asked by the wizard which table or query I want to view by. I
have a relationship set up where :Include ALL records from 'Table - HW Info'
and only those records from 'Table - Software' where the joined fields are
equal. (only one field from each table is related)

Thanks all.

 

[Rick Brandt]

Thanks..like I said..fairly new to this..

Ok ..so I'm trying to create a subform now but having a different
problem! I'm following these directions from the HELP in Access..
I've added the fields from each table that I want in the form (using
the Form Wizard)..now I get to this part in the directions:

"When you click Next, if you set up the relationships correctly before
starting the wizard, the wizard asks which table or query you want to
view by. "

I'm not being asked by the wizard which table or query I want to view
by. I have a relationship set up where :Include ALL records from
'Table - HW Info' and only those records from 'Table - Software'
where the joined fields are equal. (only one field from each table is
related)

Thanks all.

Screw the wizard. All it is doing is slowing your learning curve. I
suggest starting over.

Select the main table in the db window and press the AutoForm button on the
toolbar. You now have a form bound to that table (save it). Now do the
same thing with the child table. Then open the main form in design view and
drag onto it the second form from the db window. Now you have a form with a
subform in it. All that is left is the cosmetics of the layout.

Two important properties on the subform control are the MasterLink and Child
Link properties. That is what synchronizes the subform records to the main
form record.

If you have proper relationships set up between your tables then Access
should automatically set those properties for you, but you should check them
anyway.

To get the above *working* should take less than a minute. Getting both
forms to look like you want should be all you have to do after that.

 

[CT]

Rick! Thanks a lot!

Just one last thing (I hope)...

I noticed the subform has it's own set of controls (to scroll through
records)...Can I remove those controls? This way the only controls that are
being used are on the "master" form. If I can remove them, how do I do it?

Thanks.

 

[Rick Brandt]

Rick! Thanks a lot!

Just one last thing (I hope)...

I noticed the subform has it's own set of controls (to scroll through
records)...Can I remove those controls? This way the only controls
that are being used are on the "master" form. If I can remove them,
how do I do it?

Open that form in design view and set the NavigationButtons property to No.

 

[Larry Linson]

Open that form in design view and set the NavigationButtons property to

No.

But don't expect the navigation controls on the main form to move through
records in the subform!

Larry Linson
Microsoft Access MVP