Help editing a record from form textbox field

[cqljohn]

New to creating forms in access...

I have made a form to add new records that opens in full screen. The first textbox on the form is my DB Primary Key. When I enter a value that is already in my DB, I get a message "can't add data because it would create a duplicate". I understand why I am getting the message, but should I not be able to edit an existing record in this same form? If not, how can I do this?

The Property Sheet for the form I have created is set to "Yes" for Data Entry, Allow Additions, Allow Deletions, and Allow Edits. Record Locks is set to "No Locks".

[zmbd]

Is the form bound to the record-set?
Sounds as if it is not and that you have code behind the scenes saving the record; however, you've not provided that information.

If it is bound, and you alter the primary key and attempt to save, then the error is by design. You have to move the form to the record to edit, change the fields of interest and then save.

If the form is unbound and you are using code to save the fields then do not save the edited primary key, you need to open the the record for editing. The error in your code cannot be resolved until you post your code.

PLEASE
Do NOT attach your database
Follow this link > Before Posting (VBA or SQL) Code

[NeoPa]

John,

It seems you need to make your own mind up first about what you want the form to do for you. At one stage you say you want the form to add new records, yet later you wonder that it doesn't enable you to edit existing ones.

Well done for including the properties. One is particularly relevant. The .DataEntry property specifically restricts the form from editing existing records. Even with that reset though, a form will not navigate to a record at the point (which you describe) of entering the data unless you put specific code in there to tell it to do that. I wouldn't advise that, as it would be confusing for users. They expect values entered onto the form to update any existing values. Not to be interpreted as a request to select a different record.

To select a specific record, you can either filter to cause the record to show or navigate to the record. See Example Filtering on a Form for the filtering side. Filtering can be general or specific. IE. Matching a pattern or a specific value, including that of a unique index.

[cqljohn]

zmbd, it is a bound form. There is no code there.

NeoPa, you are correct on making my mind up. When I created the form, I just wanted to add records. Once I got it up and going, I realized I need to edit records also. All I really want the form to do is add a record if it doesn't exist. If it does exist, when I put a value in the first field of the form, I would like for it to show the values in my textboxes and allow me to edit them, just as I can do in a table. I would like to at least be able edit the primary key, even if I have to retype other related textboxes. If it were for me I'd just use the table (I'm having to do that now when I have a value that needs editing), but I have other users that will be using the DB.

[NeoPa]

CQLJohn
"I would like for it to show the values in my textboxes and allow me to edit them, just as I can do in a table."

You are mistaken. What you do using a table is analogous to what a form does. You simply don't appreciate the first step as it is so automatic.

If you are on the new record of a table and type in an existing index value it does not navigate you to the matching record. On the contrary, it does exactly what the form was doing. It reports the error.

Being an intelligent human, though, what you actually do in the table is you first determine which record to start with. For a new record it will be at the bottom and for an existing one it will be where that one currently exists in the list. This is the part that you've overlooked as it's so simple when done by the human brain. This is what I was suggesting you duplicate in code by allowing the operator first to select the item they're interested in.

Once you (the VBA code) understand where the operator wants to work you can make that happen for them. Simples!

[zmbd]

. I would like to at least be able edit the primary key, even if I have

Normally, one would not alter the primary key:
> Database Normalization and Table Structures.

[cqljohn]

zmbd, Agreed. I'm not actually wanting to edit the PK, just edit the fields associated with it.

NeoPa, I read the link. Me will have to think on it as I have little VBA skills. I did d/l your example, and I could probably make it work similar to that. Since I can't understand all of the code, I'll have to do a little training to break it down to make it work for me. It would be nice if one could enter a value in a textbox, have it checked to see if it exist and allow the record to be edited, if does not exist, add the record. That's my end goal (I think).

[NeoPa]

CQLJohn
"That's my end goal (I think)."

May I suggest you think again. I see how that would be nice, but it would actually be counter-intuitive for the operator. It's not how things normally work, for various good reasons. It may work for this limited situation, but then when you want to do something similar but different, where it would be inappropriate, you are left with an expectation of similarity which you couldn't sensibly fulfil.

I recommend you look at a separate control that is used to specify the record you want to work on. This covers existing as well as new records. From there enter the rest of the data.

Whenever I filter on an exact match for any field I make sure the .DefaultValue for the related control is set to the filtered value. This can make data entry easier for the operator and brings the functionality very close to what you hope to do.

[cqljohn]

NeoPa, I haven't told you the whole story on the DB, just what I thought was important for what I needed.
What I am using it for is to add records for a lab.
I have different forms for each test.
The first 12 fields are the same on each form (item information).
Most of the time we only do one test per item (The DB is working for this).
We could do up to 10 or so tests on the same item (MY problem).
We would rarely have the results for more than one test at a time for an item.
I have the forms updating the appropriate fields in the DB.
When I say I want to edit a record, I'm not actually wanting to change any existing info, but add new test data with the use of a different form.
The people working for me are not too computer savvy, so I wanted to keep it in one format, with all the forms looking the same, so it's just a repetitive thing they have to do, no thinking about what to do.
I know this is not the idea way to do this, but I think it would just be real user friendly for my users.

Imagine getting a list of 50 items that need to be entered in the DB. There is no way to know if one of them has been tested and entered already under a different test method (which would cause a record lock). You wouldn't want to have to do a search on each item before you entered them. I don't really want them typing data that is already in the DB (item info). I want them to type the item number (my PK), tab to the next field (beforeupdate), if the record doesn't exist, write it. If the record exist, allow me to edit it. The edit would actually allow me to update new info in field non populated in the DB.

I'm sure there is a better way, but again, I'm a newbie...

[NeoPa]

John, you seem to be saying one thing in one sentence and another in the next. Easily done when describing logic.

I understood you to be saying that you only ever enter new records. However, at no point do you explain you have separate tables for the items and the tests. Without this laid out clearly your explanations make little sense in any reliable way.