[Beginner Question] Trying to enter data into an AutoNumber Control

[Shivers1029]

Database - Copy.zipHey!
I'm trying to create a database that involves issuing a copy of a game to a member.


My form looks something like this: https://gyazo.com/09095616a19ab7640eb37221252a9520
Here the "Stock Number" is the primary key.
The user is entering the "Catalogue Number" to use the Sub Form to search for available copies of that game. They can then enter it's "Stock Number" in the control.
What I would like this to do would be to update the data for that stock number with what the user has input in the other fields, but when I try to edit the "Stock Number" control I'm told that I can't edit it as it's an AutoNumber.
What can I do to either get around this, or how should I instead be structuring it so that I don't get this error?
Also please ignore the horrible layout...
Thanks.

Database - Copy.zip

[June7]

I've never seen an Access autonumber with alpha characters. Is there formatting to show GC and filler zero as prefix? Why would you edit a PK?

This is a form/subform arrangement? Controls on main form are BOUND? Controls used to input filter criteria should be UNBOUND.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Shivers1029]

I've attached the database (the form in question is "Issue Copy").

I'm using characters in it just to allow me to distinguish it from other keys (Member being "MB"000, game being "GM"000).

I also seem to have deleted the original post, and I have no idea how to get it back...

Feel free to pick apart my DB

[June7]

The original post should now be visible. It was 'moderated', maybe because of the URL link. I had to 'approve' it.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Member ID* as a field name is not good.

Trying to understand data relationships.

Why is MemberID* in Game Copy table?

What is purpose of GameCopy table?

I doubt Reservation table should have MemberID as a primary key.

[Shivers1029]

Sorry about the naming thing. I'd copied the names right from a set of normalized data and forgot to get rid of some of the *'s for foreign keys.

The Member ID is in the Game Copy table as the Game Copy can be assigned to a certain member (when it's issued to them).

The basic layout is that for each game there can be many copies of that game. So the Game table will have all of the details about specific game, and the Game Copy table will have the details about that specific copy of the Game.

I couldn't decide if the Member ID should be the primary key for the Reservations table, or if it should be a compound key with the Catalogue Number, but that's what I was trying last, although I'm not sure if it's right.

As you may have guessed this is the first database I've ever tried to take a crack at, so I expect much of it to be inefficient or incorrect, so any constructive criticism would be gratefully accepted.

[June7]

I am thinking Reservations should be a junction table between GameCopy and Members because it is the individual copies that are issued.

Reservations doesn't really need a primary key (compound or not). If anything, a compound index on MemberID, StockID, ResDate. Surely member can reserve same game more than once but not in same day.

No need for MemberID in GameCopy as Reservations table has history of games issued to members. Whether or not a game (by StockID) is currently issued can be determined by a query of Reservations - have a field for DateReturned.

You might want to take a look at the MS Lending Library database template.

[Shivers1029]

Thanks for all the help. I'll try out the suggestions above tomorrow and see what I come up with!