Hi all,
I have form Orders. In Header of form is 3 search combobox.In
first(cmbVesselID) you select Vessel bay this selection in second
one(cmbOrderID) you can select any order releted to Vessel from first
combobox.In threed one same like second just you can see order by Invoice
No.
First please look in code for each of comboBox :

1)cmbVesselID
Private Sub cmbVesselID_AfterUpdate()
If Not IsNull(Me.cmbVesselID) Then
    Me.Requery
    Me.FilterOn = False
End If
    Me.cmbInvoiceNo.Requery
    Me.cmbOrderID.Requery
    Me.cmbInvoiceNo = Null
End Sub
Private Sub cmbVesselID_Change()
    Me.cmbOrderID.RowSource = "SELECT DISTINCTROW Order.OrderID,
Order.OrderNo,Val([order].[Order]) AS Expr1, Order.CategoryID From [Order]
WHERE (((Order.VesselID) = [Forms]![Order].[cmbVesselID])) ORDER BY
Order.CategoryID,Val([order].[Order]);"
End Sub

2)cmbOrderID
Private Sub cmbOrderID_AfterUpdate()
If Not IsNull(Me.cmbOrderID) Then
    Me.RecordsetClone.FindFirst "OrderID=" & Me.cmbOrderID.Column(0)
    Me.Bookmark = Me.RecordsetClone.Bookmark

End If
    Me.cmbOrderID = Null
End Sub

3)cmbInvoiceNo

Private Sub cmbInvoiceNo_AfterUpdate()
If Not IsNull(Me.cmbInvoiceNo) Then

    Me.RecordsetClone.FindFirst "OrderID=" & Me.cmbInvoiceNo.Column(0)
    Me.Bookmark = Me.RecordsetClone.Bookmark

End If
    Me.cmbInvoiceNo = Null
End Sub

Form Record Source is :
SELECT Order.* FROM [Order] WHERE
((([Order].[VesselID])=[FORMS]![ORDER]![cmbVesselID])) ORDER BY
[Order].[CategoryID], Val([Order]);

In Second Combobox Row Source is :
SELECT DISTINCTROW Order.OrderID, Order.OrderNo,Val([order].[Order]) AS
Expr1, Order.CategoryID From [Order] WHERE (((Order.VesselID) 
[Forms]![Order].[cmbVesselID])) ORDER BY
Order.CategoryID,Val([order].[Order]);

Now in details of form is all field from order Table where orderId is PK and
OrderNo I get buy merge 4 field:VesselCode,CategoryID,Order and
orderYear.And hire i think is my mistake.
To create order NO (exp. ACD - DFM1a/02) user enter Category(DFM),Order(1a)
and OrderYear(02) but first part ACD is from VesselID what is Hiden on form
but is selected when I select Vessel from first combobox(cmbVesselID)
I have this code on different event for each of tree
field(Category,Order,orderYear) and code is:
 Me.OrderNo = Me.VesselID.Column(1) & " - " & Me.CategoryID.Column(1) &
Me.Order & "\" & Me.OrderYearID.Column(1)
And to check if is order No duplicated i Have this code :

Private Sub OrderYearID_AfterUpdate()
Me.OrderNo = Me.VesselID.Column(1) & " - " & Me.CategoryID.Column(1) &
Me.Order & "\" & Me.OrderYearID.Column(1)
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Order.OrderNo FROM [Order] WHERE
Order.OrderNo = '" & Me.OrderNo & "' AND OrderID <> " & Me.OrderID)
If Not rst.EOF Then
    MsgBox "Please choose another Order Number.", vbOKOnly, "Duplicate
OrderNo"
 Me.OrderNo = Null
 Me.Order = Null
 Me.OrderYearID = Null
 Me.CategoryID.SetFocus
End If

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Can any one tell me where i make mistake because  in my beck-end i have
duplicated orders (completely duplicated - same OrderID and all field are
populate same and the mystery is Access same how remove PK from OrderID) I
can send you part of order table with duplicated record and you can see!!


Please hepl !!!!!


Thanks