User Forms & Checking Inputt

JezLisle · #1 (**permalink**) 02-06-08, 08:07

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?

MikeTheBike · #2 (**permalink**) 02-06-08, 08:50

Hi

You need something like this

Code:

Sub Test()
    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);" & _
                "DBQ=C:\YourPath\DatabaseName.mdb;"

    
    cn.Open ConString
    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!"
    End If
    
    rs.Close
    cn.Close
    set rs = Nothing
    set cn = Nothing
End Sub

You will also need to set a reference to the Mocrosoft ActiveX Data Object library.

HTH

JezLisle · #3 (**permalink**) 02-06-08, 09:15

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?

savbill · #4 (**permalink**) 02-06-08, 19:46

The code Mike posted appears to have a small error.
Make sure you have set up the 'sql' varible and it is used in the 'rs.open' method in the next line.

Code:

    sql = "SELECT YourFieldName FROM tblTable WHERE YourFieldName = " & TextBoxName
    
    rs.Open sql, cn, adOpenStatic, adLockReadOnly

MikeTheBike · #5 (**permalink**) 02-07-08, 07:54

Whoops !!

Forgot to change that bit.

Thank Bill

MTB

JezLisle · #6 (**permalink**) 02-07-08, 10:11

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

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\YourPath\DatabaseName.mdb;"

MikeTheBike · #7 (**permalink**) 02-07-08, 10:19

Hi

If your are getting this message, then it's not working, is it?

This message usually means that there is an error in the SQL query string.

I think you need to post your code so we can have look see.

BTW

Quote:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\YourPath\DatabaseName.mdb;"

This will never work unless you actualy have a directory called YourPath and a database file named DatabaseName !!

MTB

JezLisle · #8 (**permalink**) 02-07-08, 11:09

Hi,
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.

Quote:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\YourPath\DatabaseName.mdb;"

I used that file path as its easier than writing in the massive file path I have on the PC, not that daft :-)