Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    49

    Unanswered: Need help with recordset within a split database

    I am attempting to split my database into a front/back end application. There is now a demand on the system to allow multiple users and I feel that this setup is the best. However, I am having a hard time with some of my code using linked tables. Here is the first instance I am having trouble with. Once I can solve this, the rest will be modeled like this (with the addition of a few .AddNew's and .Edit's).

    Dim rst As Recordset
    Dim strsql As String
    Dim Answer As String
    strsql = "Select * from tblSecurity where EmployeeID = "
    strsql = strsql & "'" & TxtEmployeeID & "'"
    If (IsNull(TxtEmployeeID) Or TxtEmployeeID = "") Then
    msgbox "You must enter in your Employee ID number"
    TxtEmployeeID.SetFocus
    ElseIf (IsNull(TxtPassword) Or TxtPassword = "") Then
    msgbox "you must enter your password"
    TxtPassword.SetFocus
    Else
    Set rst = CurrentDb.OpenRecordset("tblSecurity")
    rst.Index = "primarykey"
    rst.Seek "=", TxtEmployeeID
    If rst.NoMatch Then
    msgbox "Your ID is not found in the system. Please contact the Shift Supervisor for access to the system or use 'GST1111A' and 'Password' as your login."
    TxtEmployeeID = Null
    TxtPassword = Null
    TxtEmployeeID.SetFocus
    Exit Sub
    Else
    Set rst = CurrentDb.OpenRecordset(strsql, dbopensnapshot)
    If rst(3) <> TxtPassword Then
    msgbox "The password you entered is not correct. Please try again."
    TxtPassword.SetFocus
    Exit Sub
    Else
    SecLevel = rst(4)
    UserName = rst(0)
    DoCmd.Close
    DoCmd.OpenForm "switchboard"
    End If
    If rst(7) = True Then
    DoCmd.OpenForm "frmchangepassword", , , , , acDialog
    End If
    End If
    End If

    As you can see, I only have 2 text controls and a command button on this form. This code is behind the click event of the button. When I run this code, I get the error 'Type mismatch' at the line - Set rst = CurrentDb.OpenRecordset("tblSecurity"). I originally had Microsoft DAO 3.6 Object Library and Microsoft ActiveX Data Objects Recordset 2.5 Library as references, but attempted to change only to the ADO version.

    Does any one have any suggestions, or other ways to accomplish this? I use this style recordset when adding records from a form, or when editing records. I do not have any bound forms in my database, since I feel it is harder to "Idiot proof" my user's input. Any help with this will be greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I thing that the problem is in the Library, you are use object for Microsoft DAO 3.6 Object Library and you have ADO version Library.
    See ADO reference for the object : Recordset and CurrentDb.
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2002
    Posts
    49
    Norberto,

    I have made the switches from DAO to ADO regarding the references, but what I need help with is the proper set statement. All my recordsets use currentdb.openrecordset. Id this my problem? If it is, how do I use dbEngine(0)? To reiterate, I am attempting to connect to a remote database to retrieve my data.

    Thanks

Posting Permissions

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