Subform locked on 1st record

[PDH]

Can anyone help me?

I have developed a form and subform that looks for a given family name from one table, and returns all of the family member names (and corresponding attributes) from another table within the subform. The family name is selected via a combo box within the main form. The correct number of family members are always returned within the subform.

Though the correct corresponding family member's information is displayed, the cursor, once released, always "snaps back" to the 1st record. The subform only lets me edit the 1st record. No additions can be made either, even though I have chosen "yes" for edits, adds and deletes within subform properties.

I have checked all of the properties within the subform, and the field linked between the subform and main form. I can't find anything that would appear to be causing this problem.

What do I need to do to allow the user to edit all records within the subform?

Thanks in advance for your help.

 

[JerryDennison]

You need to insure that the subform's Recordsource property is based on an updatable recordset. This should be a query that is(typically) based on a single table. You can determine if the recordset is updatable by opening the recordsource property and viewing the resultant query in datasheet view. If the asteric<sp> is not grayed out the recordset is updatable. If it is grayed out then something about the SQL is preventing creating of an updatable recordset. This can sometimes be very ambiguous and often I've seen it cleared up by simply deleting the table from the query and relinking it(to the query, do not actually delete the table from the database).

 

[scking]

Sounds like your subform container doesn't have the 'Link Child Fields' and 'Link Master Fields' properly set. I've seen this on a number of occasions and have always been able to fix it by verifying the parent and child fields being used as the relational link were the same datatype, the same size. Then check the tools\relationships and ensuring that they are properly linked. By the way, don't link tables in the front end if they are linked to tables in the back end. They would have to be set in the back end. Then delete the subform contain and just drag and drop the subform onto the main form. This will start the wizard and should help you to connect the parent/child properly. Try it.

Steve King Growth follows a healthy professional curiosity

 

[PDH]

OK, I'm going to need a little more help on this. SCKing, I think you have the issue pinpointed in my instance a little better. The query underlaying the subform IS updateable.

I have one field - EntityID - which links the main form and the subform. I did notice that the EntityID in the main form was autonumber/Long Integer. In the subform, the format was double. I changed the format to the supporting subform table from double to Long Integer format.

I then deleted the subform and re-established it within the main form. Alas, the same problem of &quot;snapping&quot; back to the 1st record still exists.

SCKing, I'm not sure what you mean when you say that I shouldn't &quot;link tables in the front end if they are linked to tables in the back end&quot;. Any more suggestions?

Thanks

 

[scking]

If an application consists of two Microsoft Access .mdb databases, it is probably using a client/server design concept. Forms, queries, macros, modules are in the client (front end) and the data tables are in the server (back end). In this case the server tables are generally linked from the client and are represented as a table. Clicking on the link opens the data tables from the server and displays them in the client. HOWEVER, the relationships must be shown in the server, where the actual data tables exist, not the client. So open the server and go to tools\relationships. You will have a palette you can add tables to and link them together. Unless this is accomplished Access may not be able to determine exactly what the relationships between the main form tables and the subform tables. You took the first step by changing the type from double to long. An autonumber field can only link with a long integer. Give it a try.

Steve King Growth follows a healthy professional curiosity

 

[PDH]

Finally!

I was able to get the subform to work, though part of the problem was that my SQL was coded directly into the control source box, instead of being named as a query. Once I named the query, then established a relationship between the query and the table, the subform was no longer locked on the first record.

Unfortunately, I have uncovered another problem. When I attempt to add another record (or family name) to the family members table, each KEYSTROKE, rather than name, adds a new record. Quite bizarre. I haven't looked into it much, yet, but I may still have the family member name field set as a primary key in the originating table (should be a combination of Family Member ID and Entity ID). I'll have to check tonight.

Thanks a million for your help!