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

    Unanswered: OpenRecordset or query definition problem

    Help. What am I doing wrong?
    The Goal is:
    I have a form with a combo box.
    I select a student_Id and display a name. Then I click a button.
    What should happen is a report fires to print a name tag. Then ,If a row is not found in table Attendance_log with the same student_id and date (Primary_Key) , a row is inserted into table Attendance_log.

    I do not want the user to have to deal with a key_violation dialog box.

    Here is my code:

    Code:
    Private Sub PrintLabelCommand11_Click()
    On Error GoTo Err_PrintLabelCommand11_Click
    x = x
        Dim db As Database
        Dim daors As DAO.Recordset
        Dim strSql As String
        Dim strSearchName As String
        
        '-- Set db = DBEngine(0)(0)
       Set db = CurrentDb
            
        Dim stDocName As String
        Dim stDocName2 As String
        Dim v_date As String
        Dim v_seq  As Integer
        Dim v_already_checked_in As Integer
        
         Me!Lookup_Combo.SetFocus
        ' -- Me!FirstName.SetFocus
          
        strSearchName = Str(Me!Lookup_Combo) & Str(Month(Now())) & "/" & Str(Day(Now())) & "/" & Str(Year(Now()))
          
        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_seq = Me![Lookup_Combo]
        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())));
    
       ' -- Attendance_log.StudentID    is defined as Number, Long Integer
       ' -- Attendance_log.Date_Created is defined as Date/Time Format mask is 99/99/0000;0;_
       
      '-- Build SQL string to ...
         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 & "#" & ";" ' -- Too few parameters. Expected 1. on OpenRecordset
     ' --  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)= " & "#" & " (Month(Now()) " & " / " & " Day(Now())" & " / " & " Year(Now())   );"  ' syntax error
     ' --  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)= " & "Month(Now()) " & " / " & " Day(Now())" & " / " & " Year(Now()) " & ";"    ' Type mismatch
     ' --  strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID FROM Attendance_log WHERE (Attendance_log.StudentID)= Str(Me![Lookup_Combo]) AND (Attendance_log.Date_Created)= Str(Month(Now())) & " / " & Str(Day(Now())) & " / " & Str(Year(Now()));"  ' -- Type mismatch
     ' --  strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID FROM Attendance_log WHERE (Attendance_log.StudentID)= Me![Lookup_Combo] AND (Attendance_log.Date_Created)= Month(Now()) & " / " & Day(Now()) & " / " & Year(Now());"  ' -- Type mismatch
                                                                                                                                        
     ' -- Open Record set
     ' -- Set daors = db.OpenRecordset(query2)
     ' -- Set rs = Me.Recordset
     ' -- Set daors = db.OpenRecordset(strSql, dbOpenSnapshot) ' Create recordset based on SQL -- Too few parameters. Expected 2.
         Set daors = db.OpenRecordset(strSql, dbOpenDynaset) ' -- Too few parameters. Expected 1.
    
      
        
      FindFirst "PrimaryKey = " & strSearchName
            If rs.NoMatch Then
                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.Close
        
       '*****************
    
    
    Exit_PrintLabelCommand11_Click:
        Exit Sub
    
    Err_PrintLabelCommand11_Click:
       
       If Err.Description = "Type mismatch" Then
          MsgBox Err.Description
       Else
          MsgBox "Else " & Err.Description
       End If
      
        Resume Exit_PrintLabelCommand11_Click
        
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Concatentate the vlaue of the combo rather than a reference to its name and loaction into the SQL string and Bob is your mother's brother
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Doh!

    v_seq = [Forms]![Student_Basic]![Lookup_Combo]

    strSql = "SELECT Count(Attendance_log.StudentID) AS CountOfStudentID FROM Attendance_log WHERE (Attendance_log.StudentID)= " & v_seq & " AND Attendance_log.Date_Created= " & v_date & ";"


    Thanks That helped.
    Last edited by wrwelden; 09-20-06 at 11:18.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by wrwelden
    Could you provide an example?
    Yes
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    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 & "#" & ";" '
    should be
    Code:
    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 & "#" & ";"
    The two apostrophes in red must be removed if StudentID is numeric.

    Also - why count the number of instances? I don't think you actually use the count yes? If not then don't count - this is an unnecessary overhead.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    That leads to my next question. How do I code the FindFirst or whatever method to determine if this query returns any rows or a count of 1?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - this would be my technique:

    Code:
    'Get the first record you find matching the criteria
    strSql = "SELECT TOP 1 StudentID FROM Attendance_log WHERE (Attendance_log.StudentID)='" & [Forms]![Student_Basic]![Lookup_Combo] & "' AND Attendance_log.Date_Created= " & "#" & v_date & "#" & ";" 
     
    'If the EOF (end of file) = True then there are no records.
    If rs.EOF Then
          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.Close
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Thank you very much for your help.

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

    Still having a problem, I am still unable to validate that a record has already been

    Found it. I needed to delimit the date with # but not the sequence
    Last edited by wrwelden; 09-20-06 at 14:16.

Posting Permissions

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