Unique fields won't notify before form is complete

[Andrea001]

Can anyone help, I have a form where the primary key is an ID number. The ID number is the first field to be entered on the form. I would like the user to know if that particular ID is already entered before they go through and fill in the rest of the fields in the form. I've been trying to do this with the Validation property but I'm not sure how to specify "not equal to anything that has been used before".

I'd also like to know if there is a way to update a form so that it can pick up new values entered into a combo box (via a table) without closing and re-opening the form.

Thanks,
Andrea

 

[MichaelRed]

there are MANY approaches to this. For entry level, I suggests that you just use an autonumber or GUID value as one of the db fields.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[gusbrunston]

Dear Andrea:

I've had good luck with this expression as the default ID; it finds the highest number previously used, adds 1, and that becomes the default new ID. Users can then accept the next ID number.

=DMax("[WhateverID]","tblWhatever&quot+1
Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[JerichoJ]

Andrea001,

Ok, I think this is what you are looking for.

For our example, the name of the ID field in the table will be "FieldID" and the name of the text box control on the form that is bound to this field will be txtFieldID. Of course, you will substitute your actual field and control names specific to your situation.

Create an AfterUpdate event for the txtFieldID control:

Private Sub txtFieldID_AfterUpdate()

Dim mssql as String

If Not IsNull(Me.txtFieldID) Then
mssql = "[FieldID] = " & Me.txtFieldID
Me.RecordSetClone.FindFirst mssql
If Not Me.RecordSetClone.NoMatch Then
'The ID is a duplicate
msgbox "You have entered a duplicate value"
Me.txtFieldID.SetFocus
End If
End If

End Sub

Of course, you will want to add appropriate error handling to this procedure. Also, you can do something other than inform the user that the value is a duplicate. For instance, maybe you want to take them to the record that has the duplicate ID value.

If so, just add the following lines to the Not ...NoMatch section:

Me.Undo
Me.BookMark = Me.RecordSetClone.BookMark

This will cancel the changes that have been made to this record and immediately wisk the user to the record that has the ID in question.

As far as your Combo box concern. After your code runs that enters the new values into the table, issue the following:

Me.ComboBoxControlName.Requery

This will pickup the new value(s).

 

[Andrea001]

Dear JerichoJ,

Thanks so much for your help on both issues, the other two solutions were not working for me because it was possible that the number being entered is actually lower than one used previously. Your solution makes more sense for my applications ... can't wait to try it!

Andrea

 

[Andrea001]

Dear JerichoJ,

Just tried both of your suggestions in my database and it worked perfectly. Once again THANK YOU!

Andrea