Hi,all
Please I realy need help whit this. First I will explain my Db.
I have Form (Orders) and in Header of this form is tree combo search box. So from first comboBox (cmbVessel )I select Vessel and by selection of vessel from second comboBox (cmbOrderNo) I can select Order Number related to vessel. Treed is Invoice Numbers related to vessel.
So when I select Vessel then OrderNo in Details of form I can see order, and go to next order and est. est.…..Now just to explain how I make Order Number because I thin is here my mystery.OrderNo is created from 4 field:
1. When I select Vessel from first combo box in Hidden field of form I get Vessel Code (NIC)
2. Then in Ditel of form is combo box CategoryID (DFM,D,E)
3. In Field Order I enter Order part Number(1,1a,2,3,4,est…est)
4. Is again Combo Box order Year (02,03…)
So my Order Number look like this : NIC – DFM1\02
This is Code to make Order Number : Me.OrderNo = Me.VesselID.Column(1) & " - " & Me.CategoryID.Column(1) & Me.Order & "\" & Me.OrderYearID.Column(1)
And I put code on tree event :
- Order_AfterUpdate, Order_Change(),OrderYearID_AfterUpdate() ,
Now Form Propertis and Combo Box Propertis:
Form Record Source :
SELECT Order.* FROM [Order]
WHERE (((Order.VesselID)=[FORMS]![ORDER]![cmbVesselID]))
ORDER BY Order.CategoryID, Val([Order]);
CmbVesselID (events) :
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
CmbOrderID (event)
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
CmbInvoiceNo(event)
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
All orders is stored in Table Orders.
Table Orders Have PK on OrderID (Auto Number) No duplicate and on OrderNo.(text Field)
Now Mystery.
My DB is separate on BE and FE.BE is on server and FE is mde file on user PC.
Now what happened.
When User Select Vessel from cmbVesselID in cmbOrderID is 1,2 or 3 ordersNo from another vessel .But if I select that order what is intruder, form bring me to first order of selected Vessel .So I open my BE and I run Duplicate Order Query.And I have DUPLICATE RECORDS.!!!!! So OrderID number is duplicated .Let say duplicate orderID is :1,3 and 12 . and I have orders from 1 up to 20.
I open Table Orders ,select Field OrderID and sorte Ascending.I press ctrl+f and search property is this :
Find What : 1
Look in : OrderID
Match: Any Part of field
So first was selected number 1 on position orderID = 1 then I select find next
Now number 1 was selected between OrderID 5 and OrderID 7.orderID 6 is missing.So my Access replace record 6 with record 1.same is with another two duplicated Orders.If I look my orderID they are in this orders 1,2,3,4,5,1,7,8,9,3,11,12,13,14,15,12,17,18,19,20.So I go to Design view and surprise,
PK is on OrderID and on OrderNo. So I Try to Compact and Repair BE. After Compact and Repair PK was lost on booth field (OrderID and OrderNo) and
OrderID was in this Order : 1,1,2,3,3,4,5,7,8,9,11,12,12,13,14,15,17,18,19,20.
I have Access 2000 SP1 and SP2 .If you want I can send you Table Orders.Only I need to cut not damaged record because I have 13500 records and if I send you all is to big!!!!
Please Help !!!!!!!!!!
Thanks
Fule
Please I realy need help whit this. First I will explain my Db.
I have Form (Orders) and in Header of this form is tree combo search box. So from first comboBox (cmbVessel )I select Vessel and by selection of vessel from second comboBox (cmbOrderNo) I can select Order Number related to vessel. Treed is Invoice Numbers related to vessel.
So when I select Vessel then OrderNo in Details of form I can see order, and go to next order and est. est.…..Now just to explain how I make Order Number because I thin is here my mystery.OrderNo is created from 4 field:
1. When I select Vessel from first combo box in Hidden field of form I get Vessel Code (NIC)
2. Then in Ditel of form is combo box CategoryID (DFM,D,E)
3. In Field Order I enter Order part Number(1,1a,2,3,4,est…est)
4. Is again Combo Box order Year (02,03…)
So my Order Number look like this : NIC – DFM1\02
This is Code to make Order Number : Me.OrderNo = Me.VesselID.Column(1) & " - " & Me.CategoryID.Column(1) & Me.Order & "\" & Me.OrderYearID.Column(1)
And I put code on tree event :
- Order_AfterUpdate, Order_Change(),OrderYearID_AfterUpdate() ,
Now Form Propertis and Combo Box Propertis:
Form Record Source :
SELECT Order.* FROM [Order]
WHERE (((Order.VesselID)=[FORMS]![ORDER]![cmbVesselID]))
ORDER BY Order.CategoryID, Val([Order]);
CmbVesselID (events) :
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
CmbOrderID (event)
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
CmbInvoiceNo(event)
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
All orders is stored in Table Orders.
Table Orders Have PK on OrderID (Auto Number) No duplicate and on OrderNo.(text Field)
Now Mystery.
My DB is separate on BE and FE.BE is on server and FE is mde file on user PC.
Now what happened.
When User Select Vessel from cmbVesselID in cmbOrderID is 1,2 or 3 ordersNo from another vessel .But if I select that order what is intruder, form bring me to first order of selected Vessel .So I open my BE and I run Duplicate Order Query.And I have DUPLICATE RECORDS.!!!!! So OrderID number is duplicated .Let say duplicate orderID is :1,3 and 12 . and I have orders from 1 up to 20.
I open Table Orders ,select Field OrderID and sorte Ascending.I press ctrl+f and search property is this :
Find What : 1
Look in : OrderID
Match: Any Part of field
So first was selected number 1 on position orderID = 1 then I select find next
Now number 1 was selected between OrderID 5 and OrderID 7.orderID 6 is missing.So my Access replace record 6 with record 1.same is with another two duplicated Orders.If I look my orderID they are in this orders 1,2,3,4,5,1,7,8,9,3,11,12,13,14,15,12,17,18,19,20.So I go to Design view and surprise,
PK is on OrderID and on OrderNo. So I Try to Compact and Repair BE. After Compact and Repair PK was lost on booth field (OrderID and OrderNo) and
OrderID was in this Order : 1,1,2,3,3,4,5,7,8,9,11,12,12,13,14,15,17,18,19,20.
I have Access 2000 SP1 and SP2 .If you want I can send you Table Orders.Only I need to cut not damaged record because I have 13500 records and if I send you all is to big!!!!
Please Help !!!!!!!!!!
Thanks
Fule
