A form I made in access 2007 won't let me ad new records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 25 Jul 2007 02:46 GMT

I have a form I've made won't let me click add a new record. I'm not sure
what I changed because it used to work.

Reply to this Message

John W. Vinson - 25 Jul 2007 03:13 GMT

>I have a form I've made won't let me click add a new record. I'm not sure
>what I changed because it used to work.

We're not sure either, since we cannot see the form or the query upon which it
is based. My guess is that the query is not updateable.

Care to post some more information, so someone might have at least a chance of
coming up with an answer?

John W. Vinson [MVP]

Reply to this Message

Eric - 25 Jul 2007 04:42 GMT

Ok, I'm sorry I'm kind of new to access. I thought this might be something
very easy that I'm missing. The form is based on a table not a query. The
form is to enter rental properties into a table(Properties table). It is
linked to 3 other tables. I have a table for applications, owners, and
management companies. Each of these tables is linked to the properties table
and displays in my form. Everything was working fine until (I think) I was
messing with the relationships. I think I changed everything back but for
whatever reason I still can't add any new records. I can view the records I
have entered and they look fine. I can enter new records directly into the
properties table but not in the form.

> >I have a form I've made won't let me click add a new record. I'm not sure
> >what I changed because it used to work.
[quoted text clipped - 6 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 25 Jul 2007 06:31 GMT

>Ok, I'm sorry I'm kind of new to access. I thought this might be something
>very easy that I'm missing. The form is based on a table not a query. The
[quoted text clipped - 6 lines]
>have entered and they look fine. I can enter new records directly into the
>properties table but not in the form.

If the Form has values from three tables then it IS based on a Query. Open the
form in design view and view its Properties; what is its Recordsource
property? How are the tables related? How - in the real world - are Owners,
Applications, Management Companies, and Properties related? Think about
sentences like

Each Property must have one or more owners; each Owner may own one or more
properties

Each Owner contracts with one and only one management company; each Management
Company may have contracts with one or more owners

I'm quite certain you need more tables, and that you need to modify your Form
to use Subforms rather than trying to incorporate all your tables onto the
same form.

John W. Vinson [MVP]

Reply to this Message

Eric - 25 Jul 2007 12:52 GMT

Ok, I'll work on this a little more and get back to you. I do have seperate
forms for each of the tables and only have one field from each (Owners,
Management Companies, and Applications) in the Property entry form. I have
this so I can associate the owner to the property or a management company to
the property. maybe I have to rethink my design. What is so frustrating it
that it was working.

> >Ok, I'm sorry I'm kind of new to access. I thought this might be something
> >very easy that I'm missing. The form is based on a table not a query. The
[quoted text clipped - 24 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 25 Jul 2007 16:21 GMT

>Ok, I'll work on this a little more and get back to you. I do have seperate
>forms for each of the tables and only have one field from each (Owners,
>Management Companies, and Applications) in the Property entry form. I have
>this so I can associate the owner to the property or a management company to
>the property. maybe I have to rethink my design. What is so frustrating it
>that it was working.

You say you "have one field from each" in the property entry form. That
suggests that you have controls on the form referencing that field. What
controls? What is the control's Control Source property (i.e. where is it
putting the data)? And again - what is the Recordsource of the form? *That's*
what's almost surely causing your inability to update the form.

We'll be glad to help you straighten out the design! Just remember that the
form is just the superstructure; the foundation of your application is a set
of properly designed Tables, and you need to get that foundation right first!

John W. Vinson [MVP]

Reply to this Message

David W. Fenton - 25 Jul 2007 18:03 GMT

>>Ok, I'll work on this a little more and get back to you. I do
>>have seperate forms for each of the tables and only have one field
[quoted text clipped - 10 lines]
> the Recordsource of the form? *That's* what's almost surely
> causing your inability to update the form.

John, he pretty plainly said that the other table fields are on
subforms:

>> do have seperate forms for each of the tables and only have one
>> field from each

At least, that's the way I read it. It would explain why he can say
that his main form is based on a table and not a query.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Eric - 26 Jul 2007 12:50 GMT

Ok let me see if I can explain it a little better. My 'properties' table has
fields that relate to the id fields in the 'management', 'owners', and 'lease
signing' tables. I have also put those fields from the 'properties' table on
the 'property entry' form. This way when I enter a new property I can add a
new management company, owner, and tenant to the property.

I looked at the record source from the 'properties' form and I see what you
mean, it did create a query.

> >>Ok, I'll work on this a little more and get back to you. I do
> >>have seperate forms for each of the tables and only have one field
[quoted text clipped - 19 lines]
> At least, that's the way I read it. It would explain why he can say
> that his main form is based on a table and not a query.

Reply to this Message

John W. Vinson - 26 Jul 2007 18:31 GMT

>Ok let me see if I can explain it a little better. My 'properties' table has
>fields that relate to the id fields in the 'management', 'owners', and 'lease
[quoted text clipped - 4 lines]
>I looked at the record source from the 'properties' form and I see what you
>mean, it did create a query.

Please open that query in SQL view and post it here.

We cannot fix what we cannot see.

What you probably should be doing is basing your Form directly on the
Properties table - not on any other tables! - and put Combo Boxes on the form
to select the appropriate ManagementID, OwnerID etc.; and you may need a
Subform, if a given property can have multiple signings. (Bear in mind I do
not know anything about the structure of your data!)

You can use the "Not In List" event of the OwnerID combo box to pop up a
*separate* form to enter new owner data if that is appropriate.

John W. Vinson [MVP]

Reply to this Message

Eric - 26 Jul 2007 23:28 GMT

SELECT Properties.*, Owners.FirstName, Owners.LastName,
PropertyManagement.ManagementCompany, [Lease Signing Table].Resident1,
Owners.MobilePhone, PropertyManagement.MobilePhone AS
MobilePhone_PropertyManagement
FROM [Lease Signing Table] INNER JOIN (PropertyManagement INNER JOIN (Owners
INNER JOIN Properties ON Owners.OwnerID = Properties.OwnerID) ON
PropertyManagement.ManagementID = Properties.ManagementID) ON [Lease Signing
Table].LeaseID = Properties.LeaseID;

Here it is.

> >Ok let me see if I can explain it a little better. My 'properties' table has
> >fields that relate to the id fields in the 'management', 'owners', and 'lease
[quoted text clipped - 19 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 27 Jul 2007 00:24 GMT

>SELECT Properties.*, Owners.FirstName, Owners.LastName,
>PropertyManagement.ManagementCompany, [Lease Signing Table].Resident1,
[quoted text clipped - 4 lines]
>PropertyManagement.ManagementID = Properties.ManagementID) ON [Lease Signing
>Table].LeaseID = Properties.LeaseID;

This will almost certainly not be updateable. You're using too many tables!

Again... base the Form on a query like

SELECT Properties.*
FROM Properties
ORDER BY <some reasonable field>;

as the Recordsource for the form. If you need to see the owner name and other
owner data, put a combo box on the form with a Row Source query like

SELECT OwnerID, LastName & ", " & FirstName AS OwnerName FROM Owners ORDER BY
LastName, FirstName;

Set the column count to 2 and the columnwidths property to

0;1

to store the ID but display the name. You can include multiple fields from
PropertyManagement in another combo's row source, and put textboxes on the
form

=cboPropertyManagement.Column(n)

where cboPropertyManagement is the name of the combo box and n is the zero
based index of the field you want to see (that is, if the MobilePhone field is
the fifth field in the query use (4).

John W. Vinson [MVP]

Reply to this Message

Eric - 27 Jul 2007 12:24 GMT

Thanks for all the info. I will try a little redesign and get back to you.
It may take me a while. Thanks again for all the help.

> >SELECT Properties.*, Owners.FirstName, Owners.LastName,
> >PropertyManagement.ManagementCompany, [Lease Signing Table].Resident1,
[quoted text clipped - 34 lines]
>
> John W. Vinson [MVP]

Reply to this Message