Preventing Duplicates Access 2003 vs 2007

[AlanAnderson]

Hi All,
I found the following code which was supplied by one of your kind guru’s to solve someone else’s problem.
I have two questions:

1. When I run this on Access 2007 it works well but it falls over on 2003. What can I do to make this work on both (ideally) or just on 2003 (Falls over on line rst.open source:= etc.)
2. I would like to extend this so it tests for an employee AS WELL as testing for the day of the week. Ie Only gets flagged if BOTH LastName (Number linked to Primary Key in Employee file) and DayOfTheWeek (Text) In the table “RotaWeek” match with me.LastName (ComboBox)and me.DayOfTheWeek.(ComboBox)

Thanks,

Alan




Sub StopDupe()


Dim rst As New ADODB.Recordset
Dim db As ADODB.Connection
Dim Mysql As String
Set db = CurrentProject.Connection
Mysql = "Select * from RotaWeek where RotaEmployeeID = " & Me.EmployeeID & ""
rst.Open Source:=Mysql, ActiveConnection:=db, CursorType:=adOpenStatic
If Not rst.BOF And Not rst.EOF Then
MsgBox "Employee already allocated"
Else
MsgBox "Employee still available"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

[mohanvijay]

Try this



Dim rst As ADODB.Recordset
Dim Con_Str As String
Con_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Yourdatabase path;"
rst.Open "Select Query", Con_Str, adOpenStatic, adLockReadOnly
'you can use recordcount property to get count
Dim R_Count As Long
R_Count = rst.RecordCount

[AlanAnderson]

Hi,
Thanks a million for responding. I am a total newbie and battling atm.
2 things : ??
1. I dont really understand where your lines of code fit into my sub. Please explain.
2. I tried what I think and it bombs with Runtime error 424 - Object required.

I'm enclosing my code below.
Thanks,

Alan

PS Would dLookup be a better option to get a match over THREE fields

Sub DupeFind()
Dim rs As New ADODB.Recordset
Dim Con_String As String
Con_String = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=C:\Rota\Session1;"
Set db = CurrentProject.Connection
rst.Open "Select Query", Con_String, adOpenStatic, adLockReadOnly
Dim r_Count As Long
r_Count = rst.RecordCount
' MySql = "SELECT * FROM rotaweek WHERE EmployeeID= " & Me.RotaEmployeeID & ""
' rst.Open Source:=MySql, ActiveConnection:=db, CursorType:=adOpenStatic
If Not rst.BOF And Not rst.EOF Then
MsgBox "Matching records"
Else
MsgBox "No match"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

[tysonlee]

import data from oracle to access via vba , all table structure data in access?

[AlanAnderson]

import data from oracle to access via vba , all table structure data in access?


I really do appreciate all responses but in this case I really have no idea what you are talking about.

I hope you have posted in wrong place coz otherwise I am totally lost. Please explain.

Regards

Alan

[hansup]

Consider a DCount() expression instead of opening an ADO recordset to determine whether any rows exist for your target RotaEmployeeID value.

Sub StopDupe()
Dim strCriteria As String
strCriteria = "RotaEmployeeID = " & Me.EmployeeID
If DCount("*", "RotaWeek", strCriteria) > 0 Then
MsgBox "Employee already allocated"
Else
MsgBox "Employee still available"
End If
End Sub

That should avoid the problem you're facing with Access 2003, and it will also work in 2007.

[AlanAnderson]

Hi Hansup,

Thank you so much for this,

Any reason you can think of why this shouldn't work with multiple criteria. Eg strCriteria ="RotaWEmployee = " & me.employeeid AND "RotaDay = " & me.DayOfWeek etc.

Regards and thanks,

Alan

[hansup]

Any reason you can think of why this shouldn't work with multiple criteria. Eg strCriteria ="RotaWEmployee = " & me.employeeid AND "RotaDay = " & me.DayOfWeek etc.

Yes, you can include multiple criteria in the DCount() Criteria string. It is like a query WHERE clause without the word WHERE. Look at the Application.DCount Method help topic for more details.

You need to watch the details when you build the string ... it can be challenging to visualize where quotes and spaces should be included. I made some changes to your example:

strCriteria ="RotaWEmployee = " & Me.employeeid & _
" AND RotaDay = " & Me.DayOfWeek
Debug.Print "strCriteria: " & strCriteria


Debug.Print is useful to give yourself an opportunity to examine the completed string you're using as strCriteria. So, if the DCount() expression doesn't work correctly, you can go to the Immediate window (Ctrl+g) and figure out the problem with strCriteria. You can even copy the string and paste it as the WHERE clause of a new query in SQL View for further trouble-shooting.