Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Using VBA how to find records with two column primary key

    I want to avoid the user seeing a key violation dialog box by only executing an append query if the record is not found in the table.

    How do I check to see if the record is in the table?

    This is what I have so far. The goal is to achieve something like the select statement below.

    Code:
    'SELECT Count(Attendance_log.StudentID) AS CountOfStudentID
    'INTO v_already_checked_in
    'FROM Attendance_log
    'WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));
    
      '****************
      Dim rst As DAO.Recordset
        Dim strSearchID As Number
        Dim strSearchDate As String
    
        Set rst = Me.Recordset
        strSearchID = Str(Me!StudentID)
       ' strSearchDate = (Month(Now()) & "/" & Day(Now()) & "/" & Year(Now()))))
        'rst.FindFirst "StudentID = " & strSearchID  & "AND Date_Created = " & strSearchDate
            If rst.NoMatch Then
               ' NULL
               ' MsgBox "Record not found"
            Else
            MsgBox "Record Found"
            End If
        rst.Close

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT * FROM Attendance_log WHERE ((StudentID='" & Me!StudentID & "') AND (Attendance_log.Date_Created=Date()));

    Use DAO or ADO as you desire ... And all you need to test for is if BOF is True ... If true, there are no records ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Could you post an example?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by wrwelden
    Could you post an example?
    Example of what?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    This code is wrong. Would you please provide a solution that would meet this goal?

    Code:
    Dim MySQL As String
    MySQL = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID"
    INTO v_already_checked_in
    FROM Attendance_log
    WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"
    'WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"
    CurrentDb.Execute MySQL
    these functions do not work as I would have suspected

    Code:
    WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"

  6. #6
    Join Date
    Aug 2004
    Posts
    13
    You may also want to try using a DLOOKUP function to first qualify your 2 field search followed by a standard IF statement that performs the action, or displays a more appropriate message box to the user.

    intx = DLookup("Logon", "tblUsers", "[Logon] = '" _
    & Forms!frmLogin!txtUserName & _
    "' And [Password] ='" _
    & Forms!frmLogin!txtPassword & "'")

    intx2 = DLookup("SecGroup", "tblUsers", "[Logon] = '" _
    & Forms!frmLogin!txtUserName & _
    "' And [Password] ='" _
    & Forms!frmLogin!txtPassword & "'")

    If Not IsNull(intx) Then
    strUser = intx
    strSecGroup = intx2
    Call getUser
    Call getSecGroup

    DoCmd.Close
    DoCmd.OpenForm "frmMain", acNormal

    Else
    Me.lblStatus.Caption = "Please enter UserId and Password!"
    Me.lblStatus.ForeColor = vbRed
    Me.txtUserName.SetFocus
    Me.txtUserName = ""
    Me.txtPassword = ""
    End If





    The above example is applied to a user login form and returns the user name as well as their associated security group, but could be easily adapted to suit your needs. Rather than using data stored in fields on a form you can simply define your own criteria in the DLOOKUP function and return any field from your table as the result of your lookup, and you may only need to use one DLOOKUP function.
    The IF statement could remain the same. In this example the IF statement is asking of the DLOOKUP found a record, and if it did to proceed with the login, if not display a message box stating that the user must supply a valid user name and password.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by wrwelden
    This code is wrong. Would you please provide a solution that would meet this goal?

    Code:
    Dim MySQL As String
    MySQL = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID"
    INTO v_already_checked_in
    FROM Attendance_log
    WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"
    'WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"
    CurrentDb.Execute MySQL
    these functions do not work as I would have suspected

    Code:
    WHERE (((Attendance_log.Date_Created) = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));"
    Ah yes ... That's because your date is interpreted as a string literal which requires the date to be bracketed by "#" signs ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    error "Cannot execute a Select query"

    I am getting the error "Cannot execute a Select query"

    What is another way to determine if a record has already been added to the log table that works.

    Code:
    Private Sub PrintLabelCommand11_Click()
    On Error GoTo Err_PrintLabelCommand11_Click
    
        Dim MySQL As String
       
        Dim stDocName As String
        Dim stDocName2 As String
        Dim v_date As Date
        Dim v_already_checked_in As Integer
      
        v_already_checked_in = 0
           
        stDocName = "Student_Label"
        stDocName2 = "AttendanceAppend_qry"
        ' DoCmd.OpenReport stDocName, acNormal  ' print the Name Tag
        MsgBox "Printing a name tag"
        
        v_date = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
        MsgBox "v_date " & v_date
        
    'SELECT Count(Attendance_log.StudentID) AS CountOfStudentID
    'FROM Attendance_log
    'WHERE (((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo]) AND ((Attendance_log.Date_Created)=Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));
    
            
       MySQL = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID  FROM Attendance_log WHERE ((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo] AND (Attendance_log.Date_Created)= " & "#" & v_date & "#" & ");"
       CurrentDb.Execute MySQL
       
    
      
        If CountOfStudentID = 0 Then                ' Student has not been checked-in
          MsgBox "This person has not checked-in yet today"
          DoCmd.OpenQuery stDocName2, acNormal, acEdit ' Add a row to the log table
        Else
           MsgBox "This person is already checked in"
        End If
        
      
    
    
    Exit_PrintLabelCommand11_Click:
        Exit Sub
    
    Err_PrintLabelCommand11_Click:
       
        MsgBox Err.Description
      
        Resume Exit_PrintLabelCommand11_Click
        
    End Sub

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by wrwelden
    I am getting the error "Cannot execute a Select query"

    What is another way to determine if a record has already been added to the log table that works.
    That's because the Execute method works ONLY on action queries (UPDATE, INSERT, APPEND, CREATE, DELETE) ...

    Try the same query using the recordset construction you used before ... UNLESS you want to switch to ADO ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Still having syntax problems

    Thanks for your help, I am making progress, BUT,

    Error Encountered: "Too few parameters. Expected 1."

    Error Occured at line: Set rs = db.OpenRecordset(strSql)

    What am I missing? the strSpl looks OK

    Code:
    Private Sub PrintLabelCommand11_Click()
    On Error GoTo Err_PrintLabelCommand11_Click
        Dim db As Database
        Dim rs As Recordset
        Dim strSql As String
       ' Dim MySQL As String
       
        Dim stDocName As String
        Dim stDocName2 As String
        Dim v_date As Date
        Dim v_already_checked_in As Integer
      
        v_already_checked_in = 0
           
        stDocName = "Student_Label"
        stDocName2 = "AttendanceAppend_qry"
        ' DoCmd.OpenReport stDocName, acNormal  ' print the Name Tag
        MsgBox "Printing a name tag"
        
        v_date = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
        MsgBox "v_date " & v_date
        
    'SELECT Count(Attendance_log.StudentID) AS CountOfStudentID
    'FROM Attendance_log
    'WHERE (((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo]) AND ((Attendance_log.Date_Created)=Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())));
    
      ' DAO only does DML not Selects
      ' MySQL = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID  FROM Attendance_log WHERE ((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo] AND (Attendance_log.Date_Created)= " & "#" & v_date & "#" & ");"
      ' CurrentDb.Execute MySQL
       '***************
      'Dim db As Database
      'Dim rs As Recordset
      'Dim strSql As String
    
      ' Build SQL string to ...
     ' strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID "
     ' strSql = strSql & "FROM Attendance_log  "
     ' strSql = strSql & "WHERE ((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo] "
     ' strSql = strSql & "AND (Attendance_log.Date_Created)= " & "#" & v_date & "#" & "); "
      ' strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID  FROM Attendance_log WHERE ((Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo] AND (Attendance_log.Date_Created)= " & "#" & v_date & "#" & ");"
       strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID FROM Attendance_log WHERE (Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo] AND (Attendance_log.Date_Created)= " & "#" & v_date & "#" & ";"
       
    
      Set db = CurrentDb() ' Open pointer to current database
      Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL
      Do While Not rs.EOF
       ' Debug.Print ("Found: " & rs![CountOfStudentID])
       If rs![CountOfStudentID] = 0 Then                ' Student has not been checked-in
          MsgBox "This person has not checked-in yet today"
          DoCmd.OpenQuery stDocName2, acNormal, acEdit ' Add a row to the log table
        Else
           MsgBox "This person is already checked in"
        End If
        rs.MoveNext
      Loop
      rs.Close
      db.Close
       
       '*****************
    
    
    Exit_PrintLabelCommand11_Click:
        Exit Sub
    
    Err_PrintLabelCommand11_Click:
       
        MsgBox Err.Description
      
        Resume Exit_PrintLabelCommand11_Click
        
    End Sub

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What is StudentID? Is it a string? If so, you have to use quotes or single quotes around comparison values in your where clause ... So, you'd need to change this: (Attendance_log.StudentID)=[Forms]![Student_Basic]![Lookup_Combo]

    to This:
    (Attendance_log.StudentID)='" & [Forms]![Student_Basic]![Lookup_Combo] & "' ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    It is defined as number

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Did you try running this query in the QBE? I don't see anything obvious wrong with it ... Other than is the form you pull the ID from active when the query is run?
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    An example in ADO to see if a StudentID exists in the table MyStudentTable (not sure on all the other stuff going on in the posts - just thought I'd post this example):

    Function StudentExists(StudID as integer) as boolean
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select StudentID from MyStudentTable where StudentID = " & StudID & ""
    rs.open strSQL,currentproject.connection,adopenstatic,adlo ckreadonly
    if rs.eof and rs.bof then
    StudentExists = false
    else
    StudentExists = true
    end if
    rs.close
    set rs = nothing
    End Function

    then to call this function
    if StudentExists(me!StudentID) = false then
    .....
    else
    .....
    End if
    Last edited by pkstormy; 09-15-06 at 13:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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