Combo box with Unique field 1

[Woody666]

How can I use a combo box to display Primary Key data such that when a selection is made it goes to that record and doesnt display an error message saying that you cant duplicate that record.

Woody

 

[Steve101]

Woody,

(a) This should'nt be a problem, Suggest that you may have a problem with your existing code, which is attempting to ADD a new record after you select from the combo, as opposed to just navigating to it. Check that this is not the case.

(b) Assuming you have a combo called cmbField, the following code, when placed in the AfterUpdate event of the combo, will achieve the navigation to the selected record:

Dim F As Form: Set F = Me
F.RecordsetClone.findfirst "JobNo = " & cmbField
If Not F.RecordsetClone.nomatch Then
F.Bookmark = F.RecordsetClone.Bookmark
Else
MsgBox "No matching record"
End If

(c) In the above code snippet, replace the "JobNo = " with whatever your Primarykey data field is on the form. If the field is text (as opposed to numeric), then the line should read something like

F.RecordsetClone.findfirst "JobNo = '" & cmbField & "'"

ie. "surround" cmbField with single quotes.

Hope that this helps,
Cheers,
Steve

PS. If your selection based on the combo is more complex than just one field, then the expression after the findfirst becomes slightly more complex. If this is the case, let us know for further assistance.

 

[Woody666]

Thanks Steve101,

It was trying to create a new record each time. These things I suppose are sent to try us. Thanks
Woody