manually updating text box causes primary key problems

[Joe Farage]

I have a form that has several subforms on it. They are all linked to the main table by use of a field called Patient ID.
On this form, if I use the buttons for first record, previous record, next record, last record; there is no problem and everything works correctly.
But if I try to type in an ID number in the box and then hit tab, it gives me the following dialog box.

"The changes that you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I think that it's trying to change the Patient ID of the current patient to whatever number i type in, and it can't because the ID is the primary key. But i don't know how to fix this.

Or if there's a different way to ensure that the user cannot enter duplicate values in the ID field, that works too.

Appreciate the help, Joe.

[Rabbit]

I think that it's trying to change the Patient ID of the current patient to whatever number i type in, and it can't because the ID is the primary key. But i don't know how to fix this.

That's exactly what it's trying to do. There's nothing to fix because that's what it's supposed to do if you try to change the value of a unique field to an existing value.

[Joe Farage]

Ok that makes sense, but then how do I set it up so that the user can type in a number and that record will load? without changing any other records or removing the primary key?

[Rabbit]

You have to create an unbound text box and in the after update event of that box, find and goto that record.

[Joe Farage]

ok so i got that to work, and i still want to have buttons that will go to the next,prev, last, first, etc. records. I've already set up the buttons so that the first one goes to record 1, and next and previous go to their records (prev also gives a message box if it's already at record 1). how do i set up the Last button and get a message box for the Next button if it's already at the last record?

[Rabbit]

A recordset's .bof and .eof properties will tell you if you're at the beginning or end respectively.

[NeoPa]

I have found, when trying similar record navigation logic, that there are certain things that don't work as I would expect them to (I hesitate to say Access has it wrong, as we see that so many times in questions on here and 99 times out of 100 the poster is simply illustrating their own lack of understanding - so I think it's wrong, but there may just be a reason I'm not seeing).

Anyway, the code snippet below shows what I came up with and comments why certain (non-intuitive) choices were made. I hope this helps.

Select Case intKeyCode
Case vbKeyUp
    'Bug where .AbsolutePosition can be 0 even when on NewRecord
    If (.Recordset.AbsolutePosition > 0) _
    Or (.NewRecord) Then _
    Call DoCmd.GoToRecord(Record:=acPrevious)
Case vbKeyDown
    'No reliable way to determine if we're already on the last record.
    On Error Resume Next
    If Not .NewRecord Then Call DoCmd.GoToRecord(Record:=acNext)
    On Error GoTo 0
Case vbKeyHome
    Call DoCmd.GoToRecord(Record:=acFirst)
Case vbKeyEnd
    Call DoCmd.GoToRecord(Record:=acLast)
End Select

PS. I'm open to others' suggestions on ways to handle this better myself. I find it hard to believe that such clumsy code is the best available, but nothing else I tried worked for me. I'm using Access 2003.

[NeoPa]

Just to be clear, I did try looking at the values of Me.Recordset.EOF and Me.Recordset.BOF (For the Last and First records respectively), and neither showed as TRUE in either circumstance. That was one of the issues that I struggled with when I first developed this code.

[OldBirdman]

One of my pet peeves is to have a button which, when pressed, gives a message saying "You can't do that now". Not only have I gone to the effort of selecting and pressing the button, but I must now press "OK" to clear the error message. Hopefully, I don't forget which button I had selected, and select it again.

I'm using Access 2003, and if I open any table in 'datasheet' view, I will have a set of navigation controls at the bottom left of that view. The same buttons, actually, that Access will supply if I set a form's properties to have navigation buttons.

Notice that the Next and/or Previous buttons are greyed out (Enabled=False) if necessary. I cannot display the Previous record if there isn't one. I can display the First record if I am at the first record, and my brain does has trouble with that. Maybe because I think of it as "GoTo First Record" and I can't 'Go' anywhere, but Access thinks of it as "Display First Record" which can be done even if already being displayed. "Next"/"Last" are not so neat as the "Add New" is sort of put between the last record and the end-of-file, but that is another issue.

NeoPa's code snippit appears to be part of a common function called when a navigation button/key is pressed. I would call a common function after the button/key is pressed and the navigation is complete. This would (dis-)Enable appropriate navigation buttons. I would also call this same function from any other function that affected navigation, such as an "Add New", "Delete Current", or ReQuery.

Admittedly, this line of logic is somewhat more time-consuming to set up. Because I usually use this type of logic, I have several "Tidy..." functions other than "TidyNav". I feel this makes my forms more intuitave, and therefore subject to fewer user errors.

Navigation is too important not to give it extra attention. Locating and displaying a desired record is what most users do.

[NeoPa]

Some very good points there OB, most of which I agree with wholeheartedly, and none of which do I disagree with (Ambivalent on a couple maybe).

I feel I should state, for the record and to ensure anyone using the same code knows what they're letting themselves in for, that I don't use buttons for record navigation. I use keystrokes instead. Buttons, if they were to be used, should be greyed out when inactive in my view too (where possible of course). As this concept is not appropriate for keystrokes there is nothing in my code to support that.