VBA:Problem with DCOUNT + Problem while copying data from range to listbox

[jonathan00243]

Hi everyone,

I am having a hard time getting around some errors in my VBA code, so I was hoping to find some answers on this forum.

I have created a userform for creating new data that I can insert into an Access table called EMPLOYEE. While inserting the employee, I want to assign an Manager ID to each employee but the Manager ID must exist in the EMPLOYEE table, column EMPLOYEE_ID. Here is the code that I have used for this purpose:

  Private sub create_click ()
Dim conn as object
Dim rs as Object
Dim strconn as String
Dim qry as String
Dim msg

Set conn = createobject("ADODB.connection")
set rs = createobject("ADODB.recordset")

strconn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = C:\MyPath\MyDB.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 ID does not exist "
exit sub
else
. fields("Manager_ID").value = ManagerID
end if
end with

rs.update
with employee
.superviseurID.value = ""
rs.close
set rs = nothing
conn.close
set conn = nothing

msgbox "Operation completed"
end sub ()

When I run this code I get the error : 'Compile error Type' with Employee_ID highlighted in the Dcount line.

I have looked through the internet and I understand this might be due to the fact that the source field ManagerID in the userform is a textbox while the target field Manager_ID in the Access table is a number but I have tried every solution found on forums but nothing is working, hence my presence in this forum . Can someone help figure out what the problem is here please?


The second problem that I am encountering is when retrieving data from Access, copying it into an excel sheet and showing the result into a Listbox.

In the same userform, I have a search button that looks through the Employee table to find data. I am able to copy the data into a dynamic range in excel but when I try to show the result in the ListBox, only the first column is being shows. Here is how I created my dynamic range:

OutputSource= OFFSET(EMPLOYEE!$A$1;0;0;COUNTA(EMPLOYEE!$A:$A),19)

And here is the code that I have created:

Private sub search_click ()
Dim conn as object
Dim rs as Object
Dim strconn as String
Dim qry as String
Dim var1

Set conn = createobject("ADODB.connection")
set rs = createobject("ADODB.recordset")

strconn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = C:\MyPath\MyDB.accdb"

var1 = me.employeeid

if activ = true then
qry = "select * from employee where activ = 'Yes' and employeeid=" & var1
else 
qry = "select * from employee where  employeeid=" & var1
end if

application.Screenupdating = false
thisworkbook.worksheets("employee").range("a2:s10000").clearcontents

conn.open(strconn)
rs.open qry, conn

if rs.eof and rs.bof then
rs.close
conn.close
set rs = nothing
set conn= nothing
application.Screenupdating = true
msgbox "No result"
exit sub
end if
thisworkbook.worksheets("employee").range("a2").copyfromrecordset rs

rs.close
conn.close
set rs = nothing
set conn = nothing

application.screenupdating = true

me.listbox1.rowsources = "OutputSource"

end sub

Can someone help me figure out what I am doing wrong here?

Thanks