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

E

Eric

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.
 
J

John W. Vinson

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.
Click to expand...

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]
 
E

Eric

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.
 
J

John W. Vinson

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.
Click to expand...

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]
 
E

Eric

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.
 
J

John W. Vinson

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.
Click to expand...

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]
 
D

David W. Fenton

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.
Click to expand...

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

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.
 
E

Eric

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.
 
J

John W. Vinson

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.
Click to expand...

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]
 
E

Eric

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.
 
J

John W. Vinson

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;
Click to expand...

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]
 
E

Eric

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.

John W. Vinson said:
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;
Click to expand...

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]
Click to expand...
 
Top