How to check if data exist in Access table

Question

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

`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: `select count(Employee_ID) from employee where activ='Yes' and Employee_ID='" & [EmployeeForm.ManagerID] & "'"` Unless you're actually working in access? You don't really say. — Tim Williams, May 26 '19 at 21:28
I am actually coding in Excel and my data is just stored in Access. You mean I should get rid off the Dcount function? `countNr = select count(employee_ID) from employee where activ = 'Yes' and Employee_ID ='" & [EmployeeForm.ManagerID] & "'"` and then check the condition with this new variable: `if countNr = 0 then msgbox "Manager does not exist`. — Bruno Bukavu Thai, May 27 '19 at 05:04
I have found a solution :D. I just wrapped the managerID in val and that solved it for me. Thanks everyone for the replies — Bruno Bukavu Thai, May 27 '19 at 20:30

score 1 · Answer 1 · answered May 27 '19 at 07:00

1

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 & ""

answered May 27 '19 at 07:00

Gustav

53,498
7
29
55

score 0 · Answer 2 · answered May 27 '19 at 05:45

0

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

answered May 27 '19 at 05:45

Tim Williams

154,628
8
97
125

I have tried this method but the code crashes when I want to open the record set with the following error message: Run-time error Syntax error (missing operator) in query expression `'activ = 'Yes' and Employee_ID='`. Now, I have had this error before while trying to solve my issue and I thought it was because the input field ManagerID was empty. So i added an If condition before opening the recordset: `if EmployeeForm.ManagerID = "" then EmployeeForm.ManagerID = 1 end if `. This is not helping either as the same error message still occurs :'( – Bruno Bukavu Thai May 27 '19 at 14:55

How to check if data exist in Access table

2 Answers2