Check table before entry (possible double entry) 1

[fadMiS]

I have two table in my access application, Employee and Salary table.

Employee Salary
--------- -------
EmployeeID EmployeeID
LastName PayDate
FirstName Salary
Location Allowance
JobTitle ---
--

I have form where I calculate salary and I store information in Salary table, but before I store information I want to check in Salary table whether salary is already entered looking up Salary table by EmployeeID and PayDate. I would appreciate any good idea on that.

 

[Tranman]

You could include the PayDate in the primary key of the Salary table...

 

[fadMiS]

Hi Tranman

It doesn't seem that the problem is solved. Salary table is in many side related with Employee table. There are 100 of Employees that take salary at the same date so PayDate can't be primary key. I tried with Dlookup and using FindFirst functions but I failed. In order to solve this problem we have to search Salary table in two fileds at the same time Salary.EmployeeID (Number Integer) and Salary.PayDate (Date).

 

[Tranman]

Perhaps I should have been more clear...

What I was talking about (I assumed that EmployeeId was the primary key of the Salary table), was adding PayDate to the primary key. That would mean that the primary key of the Salary table would now consist of two fields: EmployeeId AND PayDate.

Then any time you tried to add a row to the table that contained a combination of EmployeeId AND PayDate that was not unique, you'd get a duplicate key violation. You could capture the error and just display a message and exit the sub when that happened.

Of course, if you don't want to do that, you could also query up a recordset where the EmployeeId and PayDate were equal to those of the entry you are attempting to create. If you find that they already exist (rs.EOF is not True) then you could just not add them. Let me know if you would like a sample of code that does that.

I still think you would be safer to have the primary key of the table consist of both fields if you want to be assured that nobody ever adds a duplicate PayDate for a single employee.

Tranman

 

[fadMiS]

I think we are much closer now. Sorry for not being so clear previously. As I have given further explanation Salary table belongs to Many side, and that means that EmployeeID in Salary table is Foreign Key (which is redundant), normally because each Employee takes salary each month. Let me give some more explanations. From my form where I calculate Salary I find Employee (cboFindEmployee combo box) and I have text box for PayDate called txtPayDate, I enter all other data (location, taxes, savings, pension etc), and I process Salary storing information in Salary table, but if user makes mistake and tries to do it again (process salary for same employee and same date) I want to stop them checking in Salary table by EmployeeID and PayDate and giving to users warning that salary is alredy entered for that employee. I would appreciate any code that does it.

 

[Tranman]

fadMiS,
I still maintain that the primary key of your Salary table should be both EmployeeId and PayDate. This will in no way prevent you from having multiple entries for the same employee in the Salary table. It will only prevent multiple entries for the same employee on the same date (what you said you want to do). This will also not prevent EmployeeId from being a foreign key to the Employee table on the many side of a one to many relationship.

Should you still want to do this (it's the right thing to do), the error you need to trap for a duplicate primary key is: -2147217900.

That being said, here is a snippet that will query the Salary table and check to see if an entry already exists for a given employee on a given date. It assumes the PayDate is a Date/Time field. You will also need a reference to the Micorsoft ActiveX Data Objects n.n Library in your project.

Public Sub PostPay()
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim strEmpNo As String
Dim strPDate as String
Dim strSQL as String

con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" &
CurrentDb.Name & ";"
strEmpNo = <value from textbox on form>
strPDate = <value from textbox on form>
strSQL = &quot;Select * from Salary where EmployeeId = '&quot; &
strEmpNo & &quot;' and PayDate = #&quot; & strPDate & &quot;#;&quot;
rs1.Open strSQL, con, adOpenDynamic, adLockOptimistic, adCmdText

Select Case rs.EOF
Case True
<Logic to add row to table>
<This would be like: con.Execute &quot;Insert into Salary..&quot;>
Case False
MsgBox &quot;Duplicate entry......whatever
End Select

rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub

The lines that are wrapped are indented 4 spaces for readability.

Good Luck,
Tranman

 

[fadMiS]

Great, thanks Tranman for your advices, I think I got your point. Now I agree with you having EmployeeID and PayDate as Primary Key. I would appreciate your code that prevents user to store double entry having error message because of duplicate key violation.

 

[Tranman]

fadMiS,
It would be something like this:

Public Sub PostPay()
On Error GoTo ErrProc

<Logic you use to insert record in table>

<Other logic to close open objects; etc>
Exit Sub

ErrProc:
Select Case Err.Number
Case &quot;-2147217900&quot;
MsgBox &quot;Dupe Paydate for Employee: &quot; & <empnbr>...
Case Else
MsgBox &quot;Error number: &quot; & err.Number & &quot; &quot; &
err.Description,vbOkOnly,&quot;ERROR&quot;
End Select
Resume Next

End Sub

The essence of this is, if you have an error, you branch to ErrProc and display a message box then resume execution at the line immediately after the line that caused the error(Resume Next), and finish whatever processing you need to do.

If everything goes OK and you don't have any errors, you make it down to the Exit Sub line, and exit, bypassing the error logic.

This is a much cleaner way to accomplish your task. It will capture any errors you have-not just duplicate primary keys, and display an appropriate message. Eventually, no matter how many errors you have, it will work its way down to the Exit Sub line, and exit.

You can probably see how having logic like this in other subroutines would not be such a bad idea...

Tranman

 

[fadMiS]

Tranman

Thanks again for sending this code. I think this problem is solved.