Access Primary Key Violation

[bzac]

VB - ADO - Access 2000.
I am using the following to find a record. If existing, need to update and else need to add a new record.
'HTIME' is date field in the table in General Date Format.
'ADate' is a date variable which is reading from CSV file and converted to vbgeneral date format with "formatdatetime".

I have a primary with a combination of fields

The problem is when I try to find the htime with adate, it doesn't find it from the table and adding a new record, eventhogh the record is already existing and without giving duplication error.
Again if am trying to find the record with the same string it is finding the earlier added record and updating properly.

How can access store duplicate primary key? It may be unique only with date & time, but when we are viewing through the access the records shows the same values for the date & time field.

::::::::::::::::::::::::::::::::
'READING CSV FILE
Open "C:\DFS\dfsprd.IN" For Input As #1 'Open file for input
Do While Not EOF(1) 'loop until end of file

Input #1, TDate, AQty 'read data into two variables
Dim AD As Date
Dim AT As Date
Dim AH As Integer
ADate = FormatDateTime(TDate, vbGeneralDate)
AH = Hour(ADate)
AT = CDate(TimeSerial(AH, 0, 0))
AD = DateValue(ADate) + TimeValue(AT)
ADate = AD
If adoActRS4.RecordCount <> 0 Then
'move to the begining of the recordset
If Not adoActRS4.BOF Then
adoActRS4.MoveFirst
End If
'find the record with sametime else addnew
Call FindRec
Else
Act_HrAdd
End If
Loop
Close #1
'Ask if the user wants to commit all the
'changes made earlier
If MsgBox(&quot;Save Actual Demand? &quot;, vbYesNo) = vbYes Then
adoActRS4.UpdateBatch adAffectAll
Else
adoActRS4.CancelBatch adAffectAll
End If

Else
MsgBox &quot;Please! Select Customer&quot; & vbCrLf & &quot;(Click on Grid-Row to select)&quot;, vbCritical, &quot;IMPORT ACTUALS&quot;
Exit Sub
End If
'close the recordset and connection
adoActRS4.Close
End Sub

Private Sub FindRec()
On Error GoTo ErrHandler
'find the record with sametime else addnew
With adoActRS4
.Find &quot;HTIME = '&quot; & ADate & &quot;'&quot;
If Not .BOF And Not .EOF Then
adoActRS4!hactual_qty = AQty
adoActRS4!hrevision = adoActRS4!hrevision + 1
adoActRS4.Update
Else
Act_HrAdd
End If
End With
ErrHandler:
Debug.Print &quot;Error Number&quot; & Err.Number & &quot;, &quot; & Err.Description, , &quot;DFS-Demand Entry&quot;
End Sub

 

[Guest_imported]

The date includes Date and time which makes it unique. Remove the time by using Short date format in the property of Access and use Format(<Date>, &quot;short date&quot in your codes.

 

[bzac]

i can't remove the time because the records are based on hour and it is making them unique.

Here the problem is I have three fields to be added/updated for each record, ie. Planned qty., Actual Qty., Scheduled Qty. each of them are add/update by different module events. Once a record is added with any of the qty, other fields have to be updated according to the entry.

Even if the time is existing it can't find the record with that time!!! and adding another record with the same time which is a primary key and without violating any rule.

 

[JerryDennison]

I would insure that the date is formatted as short date and the time as short time. This will give you a much better chance of insure no two combinations as a primary key occur.

 

[chiph]

I always try and format date/times as: &quot;yyyymmdd hhnnss&quot;, which ensures that they'll always sort correctly. It's important to leave all the leading-zeros in there (i.e. &quot;03&quot; instead of &quot;3&quot to make the field is of uniform length.

Chip H.

 

[bzac]

Thanks everyone for the effort. I have two cases 1)Checking for the datetime field with a date variable reading from a .CSV file and if exists updating other wise add new. 2)with a date variable getting from a dtppicker selection.

The table date field is in generaldate format.
Here I was converting the date variable to generaldate format only once and that was making problem for me. When it was passing thru the loop after fourth cycle somehow it's format or something else is changing which will make it unique, but looks like generaldate format.

What I did is in the loop itself first converting the same datevariable to shortdate format and then back to general date format and now it is working fine.

Once again thank you very much for responses.