The primary keys are [Employee ID] and [ABN] together. That isn't a problem for duplicates when they're together, but every [ABN] also needs to be kept unique regardless of whether there's an [Employee ID] tied to it or not.
I can't think of a way to "check" tblEmployeeBusiness for already existing [ABN]'s before executing this query...help?
If IsNull(DLookup("ABN","tblEmployeeBusiness","[ABN] = " Me.txtABN)) Then
'there is not one
Else
'there is one
End If
Assuming that there is an ABN field on a form (Me.txtABN). If not, will need to pass it to this method somehow. I am sure there are other methods, all I can think of at the moment.
If IsNull(DLookup("ABN","tblEmployeeBusiness","[ABN] = " Me.txtABN)) Then
'there is not one
Else
'there is one
End If
Assuming that there is an ABN field on a form (Me.txtABN). If not, will need to pass it to this method somehow. I am sure there are other methods, all I can think of at the moment.
ahhh cheers!
just out of curiosity though....if i had my code as follows...
Code:
If IsNull(DLookup("ABN","tblEmployeeBusiness","[ABN] = " Me.txtABN)) Then
'there is not one
[B][COLOR=Red] goto function_end[/COLOR][/B]
Else
'there is one
End If
[B][COLOR=Red]function_end:[/COLOR][/B]
You could achieve the same without using a goto statement (since these are frowned upon). You could just use: End Function. But, yes, you should be able to.
This is a fairly common topic in database applications.
In Access (against a Jet native backend - as opposed to a server BE like SQL Server) it's common to see the methods discussed in this thread here.
i.e. a check in code and then an insert if no match is found.
In a server RDBMS the abilities of the language allow conditional statements to execute (i.e. you can include the decision making in the SQL alone).
Jet doesn't allow for this - hence the common check.
Of course - there is then a fraction of a second window for other users to be performing the same insert.
It might not sound like something worth worrying about - but it's a possibility which exists, and therefore is a concern.
To perform in the insert all in one only if there is no match - you can use something like the following as your insert statement...
Code:
strSQL = " INSERT INTO tblEmployeeBusiness ( [Employee ID], ABN, [Business Name] ) " & _
" SELECT TOP 1 Forms!frmEditEmployee!cboSelectEmployee, Forms!frmEditEmployee!txtABN, Forms!frmEditEmployee!txtBusiness " & _
" FROM tblEmployeeBusiness WHERE NOT EXISTS " & _
" (SELECT Null FROM tblEmployeeBusiness " & _
" WHERE [Employee ID] = Forms!frmEditEmployee!cboSelectEmployee " & _
" AND ABN = Forms!frmEditEmployee!txtABN " & _
" AND [Business Name] = Forms!frmEditEmployee!txtBusiness)"
However in Jet the first "FROM tblEmployeeBusiness" clause is required. This prevents the Insert working on an empty table.
(The clause isn't required in SQL Server - and hence the statement works regardless).
You can maintain a dummy table - or reference another table which you know will always have at least one record (a settings table - local if need be - can be a good choice). This will allow you to perform the insert regardless. (You need to change the SQL slightly - to a less efficient statement again!)
Of course I have to point out that if avoiding duplicates is your aim - then a unique index across the fields in question is the more proper implementation. Just perform the standard insert - and it will fail if such a row already exists.
The idea of the single SQL statement is that you simply execute it. If there is a duplicate then no row is actually inserted (the statement inserts zero rows).
If there is no duplicate then it inserts one row.
(With the limitations as mentioned).
The intent is much the same with the Unique Index suggestion.
You just execute the insert - and it will fail if there is a duplicate already.
(You could react to that failure and then perform a lookup to determine what the actual existing row's PK is and display it or whatever you choose).
