Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2013
    Posts
    18

    Unanswered: Is This Code Correct?

    What im Trying to do is This

    a) The form shall prompt the user to enter LanID
    b) Check the T_EMPLOYEE table to see if the LanID already exist

    table field= LanID
    form field=UserID


    Private Sub Form_Load()

    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rst3 As DAO.Recordset

    ' Set the variables to the current database in use and tables
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("NRCEmployees")
    Set rst1 = db.OpenRecordset("T_EMPLOYEE")
    Set rst2 = db.OpenRecordset("T_EMPLOYEE_INFO")

    Dim strMsg As String
    Dim strLANID As String
    Dim strSQL As String

    strMsg = "Enter Employee's LanID"
    strLANID = InputBox(Prompt:=strMsg)
    Debug.Print strLANID
    With rst1
    strSQL = "Select * From rst1 where LanID = " & Me!UserID
    Debug.Print strSQL
    DoCmd****nSQL strSQL
    If Not .NoMatch Then 'we found the record
    MsgBox "We found the record"
    !Me.UserID = LanID
    End If
    End With


    rst.Close
    rst1.Close
    rst2.Close
    End Sub




    If not How Should i write The strSQL statement

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this code correct...

    well have you tried running it
    are the results from running it what you expect
    did you have sufficient test data to prove all test cases?

    clearly you haven't tried testing it as the answer to
    is this code correct...
    is no

    take a step back and work out what you need
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    18
    I Ran it and a error message popped up and said "A RunSQL action requires an argument consisting of an SQL statement."

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so now you know your code isn't working
    looking at your SQL what do you think may be the problem
    after all you are sending the value of strSQL to debug.print

    does that SQL look right?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    18
    I Am New To All This and So i have no idea Whats wrong. This is why i am asking haha

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im am a jaded developer and Im not going to help unless you make an effort
    haha
    haha
    haha
    haha
    haha
    haha
    haha
    haha

    I suspect the joke is on you
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the error message is telling you your SQL is invalid

    so looking at your SQL what do you think may be invalid about it.

    for knwo forget the VBA that is creating the SQL
    Code:
    strSQL = "Select * From rst1 where LanID = " & Me!UserID
    and instead look at the value of strSQL
    if you cannot workout how to view the debug window then instead use
    msgbox strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2013
    Posts
    18
    rst1? am i aloud to use the variable or do i have to use the actual table name? Or i Could just be missing some of these things *&"'()

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is rst1 a table or query in your db
    what is the value of Me!UserID
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2013
    Posts
    18
    rst1 is The Table. and im sorry the UserID is supposed to be LanID. And What Do you mean by the Value?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is rst1 a table in your database?

    byt the value i mean what me!LanID is

    there is a world of difference between the VBA code you are usign to create the SQL statement and the actual SQL statement.
    using msgbox strSQL will tell you what the SQL is as opposed to what you think it is or what the VBA code suggests it is.
    what you have at presnet is valid VBA which creates a string variable strSQL. however the compiler is telling you you do not have valid SQL.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jan 2013
    Posts
    18
    Yes rst1 is a table in my database and Me!LanID means The field "LanID" in my form

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you certain?
    I'd expect rst1 to be a bad attempt at referring to a recordset
    Code:
    Dim rst1 As DAO.Recordset
    Code:
    Set rst = db.OpenRecordset("NRCEmployees")
    it also conflicts with what you said originally
    What im Trying to do is This

    a) The form shall prompt the user to enter LanID
    b) Check the T_EMPLOYEE table to see if the LanID already exists
    the easiest way of doing that is probably to use a dlookup
    Code:
    if dlookup('LanID','T_Employee','LANID = " & strLANID & '"') = NULL then
      'LanID not found
    else
      'LANID  found
    endif
    but you woudl need to e certain if LANID was numeric or string
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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