I have a user form in Excel which collates all the information I need, and then updates into a table in an access database.
I am trying to understand that when I input a job number in the form that it then runs a query to the database and finds out if that job number exists in the table already, and if so then it alerts me to say allready in database. How can I do this?
Dim ConString As String
Dim cn As ADODB.Connection
Dim sql As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
ConString = "Driver=Microsoft Access Driver (*.mdb);" & _
Set rs = New ADODB.Recordset
sql = "SELECT YourFieldName FROM tblTable WHERE YourFieldName = " & TextBoxName
rs.Open "tblEmployees", cn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
MsgBox "Record exists!"
set rs = Nothing
set cn = Nothing
You will also need to set a reference to the Mocrosoft ActiveX Data Object library.
Thanks for that, I have tried it and it works great when I input a job number that was already in the DB it tells me its there. A problem it does have is that when I input a Job No that isnt in the DB it tells me record exists. Why would this happen?
That bit now works but it seems to be falling over on the Connection String.
I keep getting this message Run-Time Error -2147217904
[Microsoft][ODBC Microsoft Access Driver] Too few parameters Expected1.
I dont understand what that means...
I tried this below and again it didnt like that either
Ok yes I was being a muppet, I had input the sql without telling what table the field was in. Fixed that now. As the text box could be is a string, at the end of the sql what do I need type to get it to recognize the string
sQRY = "SELECT tblCSATData.JobNo FROM tblCSATData WHERE tblCSATData.JobNo = " & usrFrmCustInput.txtJobNum
as there seems to be a syntax error, not sure if its me having a bad day and not seeing simple things.