Subform not updatable - not sure why (1 Viewer)

[CBenfer]

OK, I'm having a lot of trouble with this today. Hopefully this is a simple fix.

I have a form (SPC_Data_Entry_Display_Form) based on a table (Data_Storage_Tbl). Each of these records has info about when and where a measurement was taken. I want to record the measurements in a separate table (Measurement_Storage_Tbl) with a key to link back to the Data_Storage_Tbl entry that these are tied to. All tables are physically in another DB but are linked from this one.

To do this, I created a subform on the SPC_Data_Entry_Display_Form, but somehow the subform is not updateable - if there are no records to display it is completely blank. If there are records, it shows them but does not allow new entries and the old entries cannot be edited. I tried recreating the subform from scratch, but no luck. I'm sure this is something dumb, but please help! Here are the steps I'm taking:

1) I insert a new subform/subreport from the design menu.
2) I select "Use Existing Tables/Queries"
3) I select Measurment_Storage_Tbl and include the fields "Measurement_Value" and "Parent_ID". There is another auto-numbered field that is in the table ("Auto_ID"), but I usually don't bring it in (I've tried and it doesn't seem to make any difference).
4) In the next dialog box, I select the Form field as "ID" and the subform field as "Parent_ID".
5) I name the subform.

That's about it. I've read up on non-updatable tables and it always seems like they are talking about joins and other factors that have nothing to do with my super-simple record set.

So, I hope this is something really dumb, but what am I missing? Thanks in advance for the help.

 

[SparklyPrincess6969]

If you try opening up the subform on its own, ie without the main form being open, is it editable?

 

[plog]

All tables are physically in another DB but are linked from this one

Can you edit the tables directly from the linked tables you have? It might have to do with the access (lower-case) you have to those tables and not Access (upper case).

 

[vbaInet]

I think you've set the DataEntry property of the form to Yes. Change that to No.

 

[CBenfer]

If you try opening up the subform on its own, ie without the main form being open, is it editable?

Yes. I can edit as needed in the subform only.

 

[CBenfer]

Can you edit the tables directly from the linked tables you have? It might have to do with the access (lower-case) you have to those tables and not Access (upper case).

Yes, I can edit the tables by opening them directly and typing in the data.

 

[CBenfer]

I think you've set the DataEntry property of the form to Yes. Change that to No.

It is set at No. For reference, during my first attempt at this I shut off access to some functions that I didn't want the user to have. After it didn't work, I played with those settings (Allow Additions, Allow Edits, etc.) to see if I could sort it out - no luck. Then I deleted the whole subform and started over - no luck. If I follow the steps above and make absolutely no other changes to the defaults, it still gives me the same response.

 

[vbaInet]

It's an underlying issue with the linkage between your subform and the parent form. I think you're going bottom up instead of top down (if that makes sense).

Let's see your db with the problem form.

 

[CBenfer]

Here's the DB. The data storage DB is just intended to hold the data, and the SPC Data DB is the one I'm having trouble with.

The particular form that is causing trouble is "SPC_Data_Entry_Display_Form". There is only one subform.

To test the main form correctly, it is intended to be opened from "Open_SPC_Entry_Form".

When you open the DB it will be obvious that this is still very much under construction, so if you see random placeholders and stuff or functions that don't seem to do anything don't be too surprised.

 

[RainLover]

Access is a Relational Database.

Each Table is related to another. Sort of like a spiders web with branches going from one Table to another.

Your Database does not do that.

Suggest that you look at a working database that has these relationships in place. Our own archives has heaps.

Do that then you will see that your tables are designed incorrectly.

Have another go at redesigning and ask more questions as you go.

It might be a good idea to look at your naming conventions as yours could do with some improvement.

 

[CBenfer]

Access is a Relational Database.

Each Table is related to another. Sort of like a spiders web with branches going from one Table to another.

Your Database does not do that.

Suggest that you look at a working database that has these relationships in place. Our own archives has heaps.

Do that then you will see that your tables are designed incorrectly.

Have another go at redesigning and ask more questions as you go.

It might be a good idea to look at your naming conventions as yours could do with some improvement.

OK, I guess I'm missing something. I have three tables:

The Data Storage table is going to keep the "header info" for the SPC data that is collected.

The Measurement Storage table has a Parent ID field that shows which Data Storage field it belongs to. The actual measurements are stored here so that I can keep as many or as few for each "header".

The Spec data stores the appropriate limits and whether we're using given SPC rules. This information is looked up based on the part, operation, and measurement name which is common to the Data Storage table.

While this might not be the most efficient architecture, I'm not sure why this would be a problem. If you can give me some idea of what you mean it might help. Also, are you saying that the table arrangement is causing my problem, or just that it isn't the best way to do things for other reasons?

 

[vbaInet]

It's a whole other topic altogether. If Rain says there are problems with your table design then I would believe and won't need to open your db.

Just so we don't misconstrue the objective of this thread can we ask you to create a new thread in the relevant section (i.e. Tables), we can deal with it there. Also, as Rain said have a look at this link for sample database:
http://www.access-programmers.co.uk/forums/forumdisplay.php?f=64

 

[RainLover]

VBA is correct.

Your tables require a complete rewrite.

You need to gather some basic information on Database design.

Normalization is important for you right now and a good naming convention would help.

There are plenty of tuts on the web for you to choose from.

After you have done some research you will understand better and we will help where possible.

 

[CBenfer]

OK - I'll post something in the Tables forum after lunch regarding the normalization. I don't write many DBs, so I'm sure the design isn't ideal.

However, I'm still puzzled as to why a simple function like this subform isn't working correctly for me. I've done similar things in the past without any trouble, but for some reason this is giving me problems. I've tried bringing in different fields and changing the form properties in various ways, but nothing is really making sense.

Thanks.

 

[vbaInet]

Once your design flaws are straightened out, everything (even the subform) will tie in nicely.

 

[RainLover]

OK - I'll post something in the Tables forum after lunch regarding the normalization. I don't write many DBs, so I'm sure the design isn't ideal.

However, I'm still puzzled as to why a simple function like this subform isn't working correctly for me. I've done similar things in the past without any trouble, but for some reason this is giving me problems. I've tried bringing in different fields and changing the form properties in various ways, but nothing is really making sense.

Thanks.

Try creating a Query that includes the Form Header and Form Sub Details. See if you can add edit and delete a few records. You may find this helpful to highlight any problem. This way you do not have the possibility of a mistake in the actual form design.

 

[CBenfer]

OK, I tried that and the query updates just fine. Sorry for being ignorant, but what does that now tell me?

For reference, I included the query design as a JPG here to make sure that I understood what you were asking me to do.

 

[RainLover]

Here is some help. I am using your JPEG IMAGE 1178 to help.

You have
Test_1_Fail
Test_1_Fail
Test_1_Fail
etc

When does this sequence end. This Year, next Year or in 10 Years time. Each time you need a new one you must call upon the Programer to add it because this Database when completed will be locked down.

What you have is called Hard Programing

Do a search in Access on Repeating objects and see if you can find a solution. For now I suggest your using just the one. You will eventually need a new table to place these in.

Lets look at the names you have used for your tables. Data_Storage_Tbl etc.
Try using a Prefix of tbl. Then for Queries use qry, frm for Forms etc.

For a Primary Key try DataStoragePK. Get rid of the underscores. I will give you a link to an article on Naming Conventions. Please have a read and adopt it or something similar.

In the tblMeasurementStorage your Foreign Key should be DataStorageFK. Much easier to understand.

Now in your Relationship Layout have a look at the line joining the two tables. Right click on it and select Enforce Referential Integrity. Nothing else.

This should give you a One to many Join.

Now enter some data in both tables. You should run into a problem if you don't know what you are doing. See if you can work out what the problem is. As a hint it is not in the design it is in your entry.

Now create your Query and test it then finally the form.

http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[RainLover]

OK, I tried that and the query updates just fine. Sorry for being ignorant, but what does that now tell me?

For reference, I included the query design as a JPG here to make sure that I understood what you were asking me to do.

If your query is working correctly then so should your form. Is this the case.

We still have to fix this Test_1_Fail. So when you are happy with every thing else I would suggest creating a new thread and simply ask what is wrong with the design and how do you fix if.

If you stay here your thread will be so long by the time you get to the end you won't know where you came from.

 

[vbaInet]

If your query is working correctly then so should your form. Is this the case.

We still have to fix this Test_1_Fail. So when you are happy with every thing else I would suggest creating a new thread and simply ask what is wrong with the design and how do you fix if.

If you stay here your thread will be so long by the time you get to the end you won't know where you came from.

CBenfer already started a thread ages ago. It just wasn't mentioned on this thread. Here's the link:
http://www.access-programmers.co.uk/forums/showthread.php?t=266421