CODEWITHSUNDEEP
excelvbams-accessadodbdcount
Bruno Bukavu Thai
Bruno Bukavu Thai

Reputation: 141

How to check if data exist in Access table

I have created a userform that insert data into an Access table. while inserting data, I want to make sure that the ID inserted must exist in the Access table. I have used the DCOUNT function to do this but this is rendering a 'Type Mismatch' error. I have tried every solution found on the internet but nothing is working here. Please help!

I have modified the DCOUNT expression to put the form variable name into '', [], creating an external variable that refers to the DCOUNT function but nothing is working 

Set conn = createobject("ADODB.connection")
set rs = createobject("ADODB.recordset")
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = C:\MyPathMyDB.accdb"
qry = "select * from employee"

with rs
.adnew
if isnumeric(ManagerID) = false then
msgbox "Invalid Manager ID"
exit sub
elseif application.worksheetfunction.dcount("Employee_ID","Employee","activ='Yes'  and Employee_ID='"  & [EmployeeForm.ManagerID] & "'") = 0 then
msgbox "Manager does not exist"
exit sub
else 
. fields("Manager_ID").value = ManagerID
end if
end with

I expect the function to determine if the Employeeform.ManagerID exist in Employee_ID. If yes, then proceed, else display error message

Upvotes: 0

Views: 1709

Answers (2)

Gustav
Gustav

Reputation: 55806

Since you check for the ManagerID to be numeric, I guess its value isn't text, and active is probably a boolean, and as you can access ManagerID on its own, use it as is, and the criteria could read:

"activ=True and Employee_ID=" & ManagerID & ""

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166146

Dcount (the one you're trying to use) is an Access function: the Excel one is for querying a worksheet range. you will need to query your access database to see if there's a record:

For example:

sql =  "select * from employee where activ='Yes' and Employee_ID='" & _
        [EmployeeForm.ManagerID] & "'"

rs.open sql, conn

If not rs.eof then
    'got a match: add the new record and update to database
else
    msgbox "Manager not found!"
end if

Upvotes: 0

Related Questions