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

    Unanswered: Problem with listbox

    Hi

    I have created a listbox on a form, and am having trouble populateing it. Unfortunately some of the records that I'm placing in the listbox don't contain anything, so are null values.

    What I would like to do is create an If statement to say that If there are no names in the recordset then display a MsgBox to tell the user that there is nothing to select, else display only the names that are not null.

    I can't figure this out, I have tried using EOF in the If statement and, but I keep getting the 'Run-time error 94: Invalid use of Null' error.

    Here is my code:

    Code:
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim EmpID As Integer
    
    Me.List35.RowSource = ""
    EmpID = [Forms]![frmWork_Placement]![Emp_ID]
    
    Set db = CurrentDb()
    sSQL = " SELECT Emp_ID, Name1, Name2, Name3, Name4, Name5" & _
           " FROM dbo_tblEmpContacts WHERE Emp_ID = " & EmpID & ""
    
    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
    
        If rst![Name1] = "" Then
        Me.List35.Value = ""
        Else
        Do While Not rst.EOF
            Me.List35.AddItem rst![Name1]
            Me.List35.AddItem rst![Name2]
            Me.List35.AddItem rst![Name3]
            Me.List35.AddItem rst![Name4]
            Me.List35.AddItem rst![Name5]
        rst.MoveNext
        Loop
        End If
    
    rst.Close
    Set rst = Nothing
    Thanks

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

    How about:
    Code:
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim EmpID As Integer
    
    Me.List35.RowSource = ""
    EmpID = [Forms]![frmWork_Placement]![Emp_ID]
    
    Set db = CurrentDb()
    sSQL = " SELECT Emp_ID, Name1, Name2, Name3, Name4, Name5" & _
    	   " FROM dbo_tblEmpContacts WHERE Emp_ID = " & EmpID & ""
    
    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
    
    If rst.EOF Then 
    MsgBox "No Names"
    Else
    
    '	If rst![Name1] = "" Then
    '	Me.List35.Value = ""
    '	Else
    	Do While Not rst.EOF
    		If Not IsNull(rst![Name1]) THEN Me.List35.AddItem rst![Name1]
    		If Not IsNull(rst![Name2]) THEN Me.List35.AddItem rst![Name2]
    'And so on for all names
    		Me.List35.AddItem rst![Name3]
    		Me.List35.AddItem rst![Name4]
    		Me.List35.AddItem rst![Name5]
    	rst.MoveNext
    	Loop
    	End If
    
    rst.Close
    Set rst = Nothing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    That's what I wanted, thanks

    And to think I was about to do it with about 10+ If statements

    Or failing that join it all into one string.

    Thanks again.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm sure the regulars won't be surprised to see me asking this but...

    That looks like a pretty un-normalized design. What is the point of this table and why are the name columns not in their own table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    This table acts as a list of contacts to a company, of which you can have up to 5 contacts for each company. It links via the ID to the company's details, which are stored in another table.

    The only things in this table are the names of the 5 contacts, their positions and the company name.

    I can see where you are coming from though, I could have a 5 different tables just listing this information linked the the ID of the company. Then I could include stuff like their personal number, e-mail address and fax etc.

    But atm there should be no problems, but If I need to change this in the future then I will obviously have to do this.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    I can see where you are coming from though, I could have a 5 different tables just listing this information linked the the ID of the company. Then I could include stuff like their personal number, e-mail address and fax etc.
    I doubt that is what Teddy had in mind there.... I feel a link coming on....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I doubt that is what Teddy had in mind there.... I feel a link coming on....
    OH NO!!!! NOT THE NORMALIZATION LINK ... NOT AGAIN!!!! I Can't take it anymore ... No, Ted. Don't do it ... AAAAAAAAAAAhhhhhhhhhhhhhhhhhhh!
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not, Rudy may need to up his site hit rate, perhaps we should see if we can get a commission...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    why not, Rudy may need to up his site hit rate, perhaps we should see if we can get a commission...
    He'll be posting any second - I don't know how he does it but he always knows if he has been referred to in a thread.

    He's probably been wondering why his server has been on the floor these last few weeks - wonder if he'll figure out it was Teddy et al referring everyone to his site?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    Or failing that join it all into one string.
    Maybe not a bad idea.

    Check this post out. A null name would be represented by ";;" so:
    Code:
    MyString = Replace(MyString, ";;", ";")
    An idea perhaps.
    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
  •