Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with SQL query

    Hi

    In my previous post I asked how to get part of an e-mail address, which would have been used in the following code.
    Although I think I may have figured an alternate way, of doing what I intended without using the InStr, but I still can't get it working.

    The plan was to select an area from a dropdown list, and then filter that with again to find the specific person, then fill in the textboxes on the form with their name and e-mail address.

    In my database I have three tables:-

    One listing Administrators.
    One listing Section Managers.
    And one linking these tables together.

    I created a view in SQL Server and linked this to access.

    On my main menu however, is a textbox which gets the users windows login. I believe that the login name is the same as the e-mail address before the @ symbol.

    So I created a variable to store the login name, and added a Like condition to the recordset I created to get a persons record.

    This is my code, but it keeps coming back saying no record.

    Code:
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim strperson As String
    
    strperson = Form_frmMenu!txtUser
    
    Set db = CurrentDb()
    sSQL = " SELECT BArea, PA, PAEmail, SM, SMEmail" & _
           " FROM dbo_VwStaff_Mail" & _
           " WHERE BArea='" & Me!cmboBArea & "'" & _
           " AND PAEmail LIKE'" & strperson & "'" & _
           " OR SMEmail LIKE'" & strperson & "'"
    
    Debug.Print sSQL
    Set rst = db.OpenRecordset(sSQL)
    
    If rst![PAEmail] Like strperson Then
    Me.[txtName] = rst![PA]
    Me.[Email] = rst![PAEmail]
    Else
    End If
    
    If rst![SMEmail] Like strperson Then
    Me.[txtName] = rst![SM]
    Me.[Email] = rst![SMEmail]
    Else
    End If
    Can anyone help?

    Should I use the InStr syntax in this bit of code?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Shouldn't your SQL be:
    Code:
    sSQL = " SELECT BArea, PA, PAEmail, SM, SMEmail" & _
    " FROM dbo_VwStaff_Mail" & _
    " WHERE BArea='" & Me!cmboBArea & "'" & _
    " AND (PAEmail LIKE '" & strperson & "*'" & _
    " OR SMEmail LIKE '" & strperson & "*')"
    ??
    Last edited by pootle flump; 02-28-06 at 10:19.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks pootle, I realised after that I was missing an extra line in my SQL

    It should have been:-

    Code:
    sSQL = " SELECT BArea, PA, PAEmail, SM, SMEmail" & _
           " FROM dbo_VwStaff_Mail" & _
           " WHERE BArea='" & Me!cmboBArea & "'" & _
           " AND PAEmail LIKE'" & strperson & "*'" & _
           " OR BArea='" & Me!cmboBArea & "'" & _
           " AND SMEmail LIKE'" & strperson & "*'"
    I've managed to sort the recordset out, but now I'm having problems trying to populate the textbox controls on the form. It just isn't populating them at all.

    This is what I have so far for populating the textboxes:

    Code:
    If rst.RecordCount < 1 Then
    MsgBox "Nobody with the E-Mail Address '" & strperson & "@reaseheath.ac.uk' was found in " & Me!cmboBArea & ".", vbOKOnly
    ElseIf rst.RecordCount > 0 Then
        If rst![PAEmail] Like strperson Then
        Me.[txtName] = rst![PA]
        Me.[Email] = rst![PAEmail]
        ElseIf rst![SMEmail] Like strperson Then
        Me.[txtName] = rst![SM]
        Me.[Email] = rst![SMEmail]
        Else
        MsgBox "Nobody with the E-Mail Address '" & strperson & "@reaseheath.ac.uk' was found in " & Me!cmboBArea & ".", vbOKOnly
        End If
    End If
    When the recordcount is 0 it displays the message like it should, but when the recordcount is 1 it doesn't seem to populate the controls on the form.

    Anyone know why?

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    rst.movefirst?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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