Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: User Forms & Checking Inputt

    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?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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?

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Whoops !!

    Forgot to change that bit.

    Thank Bill

    MTB

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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;"

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
    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

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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.

    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 :-)

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Ignore this above...

    Was me being stupid yesterday. All sorted now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •