Hi All
I am new to Access (using 2003) so please be patient; I am having troubles with this particular problem.
At the moment, our form is set up with 4 fields (that you are interested in). Status_Date, Status_time, Cust_ID, Status.
Now when entereing this information, the table is then populated but the status_Date and Status_time are merged together into one column, so in the tbl_Main, you would see this status date as "dd/mm/yy hh:mm". If it helps, the current code for this submit is:
Set rs = db.OpenRecordset("tbl_Main")
With rs
.AddNew
.Fields("Status_Date") = Me.Status_Date & " " & Me.Status_time
..........
Now the problem is as follows: sometimes we have a problem where for a particular CustID, a duplicate date and time is posted (for example "01/01/13 09:00" is posted twice, for custID "A100")
I want to stop this from happening, so I am trying to write a line of code where im counting the amount of times this certain status date appears for the certain customer, and if this amount is >= 1, then exit the function and do not save the record.
I have tried using:
If IsNull(DLookup("Cust_ID", "tbl_Main", "Status_Date = #" & Me.Status_Date & "#")) = False Then Exit Function
This sometimes works, and sometimes doesnt - this is only for the date field being entered into the form and does not take time into consideration, so obviously Id need to somehow incorporate me.status_time into this as well.
I also tried using a SQL string:
dim strSQL1 as String
strSQL1 = "SELECT COUNT(Me.Status_Date) FROM tbl_Main" & "WHERE Cust_ID = " & Me.Cust_ID & ";"
If IsNull(strSQL1) = False Then Exit Function
But this does not work at all (I am very inexperienced with SQL so im not expecting it to work as it is).
Does anyone know how I could fix my problem?
Thanks,
EJ