indexing in Access; primary key

[shepherdess]

I am trying to accomplish what is probably very easy. I have a primary key
established. If I enter all the data on the form and then try to save, I get
an appropriate error message. How do I make that error message apply as soon
as I move away from the primary key field so that all the other data entry
associated with that field will not be entered beyond that field and time is
saved?

Question 2: How then do I create the situation in which the enterer will be
then taken back to the first use of that primary key and better still have a
form popup that will accept extra data for the first record using that key.

Example, the company has a work order number as primary key; that is a
unique number, but the data entry clerk will not know that nor is he, she
proficient in Access. If there were an error message when that primary key
number is entered on a new record, with the accompanying return to the record
for which it is the key and he/she encounters a simple popup form which will
accept the data, it would be the best of all worlds.

One more for a newbie: Can Access record macro keystrokes as they as
executed and save that?

Thanks in advance for any and all help on this.

Shepherdess (yes, in my other life, I really am)

 

[Jason Lepack]

Check out the "On Exit" event of the textbox.

Cheers,
Jason Lepack

 

[John W. Vinson]

I am trying to accomplish what is probably very easy. I have a primary key
established. If I enter all the data on the form and then try to save, I get
an appropriate error message. How do I make that error message apply as soon
as I move away from the primary key field so that all the other data entry
associated with that field will not be entered beyond that field and time is
saved?

Question 2: How then do I create the situation in which the enterer will be
then taken back to the first use of that primary key and better still have a
form popup that will accept extra data for the first record using that key.

Let's roll these into one.

You can do this using VBA code in the BeforeUpdate event of the workorder
number textbox. Try code like this. I'm assuming a field named WONumber, Text
datatype (leave off the ' and the "'" if it's number), and a textbox named
txtWONumber bound to it.

Private Sub txtWONumber_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[WONumber] = '" & Me!txtWONumber & "'"
If rs.NoMatch Then
' new record, don't do anything
Else
' duplicate WONumber
iAns = MsgBox("This WO Number already in. Go to record (click Yes)" _
& " or erase and start over (click Cancel)?", vbYesCancel)
Cancel = True ' don't enter this record
Me.Undo ' erase the form
If iAns = vbYes Then
Me.Bookmark = rs.Bookmark ' jump to the found record
End If
End If
End Sub

One more for a newbie: Can Access record macro keystrokes as they as
executed and save that?

Not in any way that I've ever found to be useful.

Shepherdess (yes, in my other life, I really am)

Cool... my next-door neighbor has sheep, and I've found them wandering in my
flowerbeds at times!



John W. Vinson [MVP]