Edit record in form with original entry shown

[erknoebe]

Hi, I’m trying to edit a table using a form that allows the user to choose the record they want to edit. The form should display the record’s original data and allow the user to enter the new data in data entry fields. The table connected to the form has two columns, one being the primary key called Abbr and the other a description. Both fields are text. An example of the data contained is Abbr “CEO”, description “Chief Executive Officer”. I plan on using a save record command button to finish the edit. I do not know programming so I’ve been struggling with available options in Access.
What I’ve been able to do so far is use a form with a subform. In the form I created a text box with the Abbr being displayed. In order to change the value in the Abbr text box I created a combo box that allows me to choose the Abbr values in the table. I do not know why this works. The subform has a parent child relationship set to the Abbr field. I could not get the combo box to change the subform records so I had to create the Abbr field in addition to the combo box. In the sub form the record displays but I cannot edit the fields because an error displays saying “The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, …”. I set the form properties to not allow data entry and not allow new additions. All I want the user to be able to do is edit records in a form environment.


I think the combo box alone should be sufficient to change the record in the subform. I could also use the combo box to display the original data and edit or delete the data in the subform. Thanks in advance for the help.

[erknoebe]

After further trouble shooting this is what I’ve found. If I remove the primary key and index off of the Abbr field I am allowed to edit the Abbr and Description of the record I’ve selected in the combo box. The reason the error is being generated is because when I click in the field in the subform to edit it, the Abbr field in the first record of the table is over written with the Abbr I’ve chosen to edit. Hence the duplicate error entry since it over writes the Abbr data with the Abbr data I’m trying to edit. This happens as soon as I click in a field to start my edit. I am able to edit the field I’ve chosen with the combo box. What I need to know is how to prevent the first record in the table from being over written when I click in a field in the subform.

[NTC]

wanting to see the record, without editing it directly - yet editing it - and then a save function is all kind of convoluted. (sorry if that's a bit blunt)

a client/server db is 'live' - one is working directly with the table data; unlike a web app which has a browser UI that stages info and then is sent to the web server. have had plenty of clients ask "where's the save button?"... not needed - you are live in the table when you use a form. the web experience has set a level of UI expectation....

so to set up what you want; rather than a subform approach you might consider just a set of unbound textboxes...that serve to hold your 'pre-edit' info.... then one can use a command button to insert those into the real record.....

[erknoebe]

You're right, I’m looking at this from a web app perspective. The reason I want to use the save button is to prevent the user from accidently entering garbage into the database without the “are you sure button.” It would be nice to forgo the subform, but I don’t know how to choose the record to edit without it. I don’t want the user scrolling through all the entries in the table in a table format. I thought I saw a post about choosing a record with a combo box that ended up overwriting the first record in the table, but I can’t find the post. The post may have been about entering a new record that over wrote the first record because the pointer was not pointed to a new record field, I don’t remember. I guess my basic question is, can you select a record with a combo box and edit the record, and how do you do it since I don’t seem to be able?

[erknoebe]

I think I found a solution. It does require some coding but the links provide the code so I didn’t have to come up with it myself. One link uses a combo box to select records from a table. The combo box is housed in the header of the form and the fields to edit are in the detail section. The other link tells how to display the first record in the table in the combo box when the form is first loaded. This will match the default values displayed in the text fields in the detail section of the form. The third link tells how to use the tab order to keep the current record active until a save command button is pressed. I have tested the solution and it appears to work. I will have to further test to make sure everything works as expected. For now I think this is the solution.
There is a link to have a combo box show multi columns after selecting the record by using some code. I cannot remember where this link is, but that would display the original entry while editing the fields in the detail section.
Links:
http://allenbrowne.com/ser-03.html
http://www.dbforums.com/microsoft-access/1038304-automatically-select-first-row-combo-box.html
http://office.microsoft.com/en-gb/access-help/set-the-tab-order-for-controls-HA010285217.aspx

[NTC]

The combo box is housed in the header of the form and the fields to edit are in the detail section.

This is the classic set up. The form is sourced on a table/query of course. When you add the combobox the wizard will launch and prompt you on the set up to look up a value.

To design a db one really should have a textbook, easily found at Amazon or a big book store. googling around to find stuff on the web is ok - - but a book will have examples and give you a better overall understanding.