Results 1 to 7 of 7
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Pass recordset field into function

    I'm trying to make a pop message that lists schools with missing emails



    I have on the form

    Code:
    'check to see if two emails or if both are the same
    If isBookingBothEmail(rs!SchoolName, rs!SchoolEmail, rs!TeacherEmail, rs ) = False Then
    rs.Close
    Set rs = Nothing
    Exit Sub
    End If
    the module function is

    Code:
    'checks to see if emails are duplicates or if emails are missing
    Function isBookingBothEmail(strSchoolName As String, strEmail1 As String, strEmail2 As String, Records As dao.recordset) As Boolean
    
    
    Dim strList As String
    
    
    
    Records.MoveFirst
    Do While Records.EOF
    
    
    If IsNothing(strEmail1) Or IsNothing(strEmail2) Then
    strList = strList & strSchoolName & vbCrLf
    isBookingBothEmail = False
    End If
    Loop
    
    
    
    
    
    If isBookingBothEmail = False Then
    MsgBox "These schools have prevented emailing as they have missing emails" & vbCrLf & strList
    Exit Function
    End If
    
    
    
    
    
    
    Records.MoveFirst
    Do While Records.EOF
    
    
    If strEmail1 = strEmail2 Then
    strList = strList & strSchoolName & vbCrLf
    isBookingBothEmail = False
    Records.MoveNext
    End If
    
    
    Loop
    
    
    If isBookingBothEmail = False Then
    MsgBox "These schools have prevented emailing as don't have two seperate emails" & vbCrLf & strList
    End If
    
    
    End Function
    The problem I find is that rs!schoolname doesn't pass at all - I can't get it to show anywhere and it comes up blank!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the sql the recordset is set to?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    What is the sql the recordset is set to?
    me.recordsetclone

    let me guess, I have to pass that instead and set it?

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    maybe pass the string of the name of the field rather than the recordset field?

    IsNothing(records!(strEmail1)) Or IsNothing(records!(strEmail2))

    and instead of passing rs!schoolname pass instead "schoolname"

  5. #5
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Note I have fixed

    If strEmail1 = strEmail2 Then
    strList = strList & strSchoolName & vbCrLf
    isBookingBothEmail = False
    Records.MoveNext
    End If

    to be

    If strEmail1 = strEmail2 Then
    strList = strList & strSchoolName & vbCrLf
    isBookingBothEmail = False
    End If

    Records.MoveNext

    but yeah still same problem

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    oh and Do until records.EOF

    not do while

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    ok I think I have it working

    Code:
    If isBookingBothEmail("[SchoolName]", "[SchoolEmail]", "[TeacherEmail]", rs) = False Then
    MsgBox "negative"
    rs.Close
    Set rs = Nothing
    Exit Sub
    End If
    Code:
    'checks to see if emails are duplicates or if emails are missing
    Function isBookingBothEmail(strSchoolName As String, strEmail1 As String, strEmail2 As String, records As DAO.recordset) As Boolean
    
    
    Dim strList As String
    
    
    'First check - missing emails?
    records.MoveFirst
    Do Until records.EOF
    If IsNothing(records.Fields(strEmail1)) Or IsNothing(records.Fields(strEmail2)) Then
    strList = strList & records.Fields(strSchoolName) & vbCrLf
    isBookingBothEmail = False
    End If
    records.MoveNext
    Loop
    
    
    
    
    
    
    If isBookingBothEmail = False Then
    MsgBox "These schools have prevented emailing as they have missing emails" & vbCrLf & strList
    Exit Function
    End If
    
    
    
    
    
    
    'Second check - only one email as they are both the same
    records.MoveFirst
    Do Until records.EOF
    If records.Fields(strEmail1) = records.Fields(strEmail2) Then
    strList = strList & records.Fields(strSchoolName) & vbCrLf
    isBookingBothEmail = False
    End If
    records.MoveNext
    Loop
    
    
    If isBookingBothEmail = False Then
    MsgBox "These schools have prevented emailing as don't have two seperate emails" & vbCrLf & strList
    Exit Function
    End If
    
    
    'Third check - vaild emails
    records.MoveFirst
    Do Until records.EOF
    If IsValidEmail(records.Fields(strEmail1)) Or IsValidEmail(records.Fields(strEmail2)) = False Then
    strList = strList & records.Fields(strSchoolName) & vbCrLf
    isBookingBothEmail = False
    End If
    records.MoveNext
    Loop
    
    
    If isBookingBothEmail = False Then
    MsgBox "These schools have prevented emailing as they don't have valid emails" & vbCrLf & strList
    Exit Function
    End If
    
    
    
    
    
    
    
    
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-18-2014, 07:02 PM
  2. Replies: 3
    Last Post: 08-22-2013, 12:14 PM
  3. How to Pass ListBox to Function?
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 03-05-2013, 12:13 PM
  4. Replies: 2
    Last Post: 01-14-2012, 05:08 PM
  5. pass a variable to the MsgBox function
    By 3dmgirl in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 07:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums