ListBox problems in MS Access form

urjudo
urjudo used Ask the Experts™
on
Hi Experts,
I have few problems about a List Box in a form.  There is a Form1 in my DB that is a search form with a list box.  Attached is my DB.  my problems that I need help are:
1). The "Clear" button for some reason, it's only clear the Text box but not the List Box which I need to clear the data too when click on it.
2). when I search "tes", it show everything that has "tes", I want to be able to click on each record in the ListBox the open the form that associate with the Class and TagNo (for example: if the record the Class is "Computer" then open "Computer Form with the same TagNo so it opens the correct record with the correct form, if the "Class" is Property then opens "Property Form" with the TagNo associate with the form.  I was able to open the form but not with the correct TagNo and correct form.
3). Is possible that when the records show on the ListBox, whatever the IPNo is assigned to a user, the IP address will be shows Red, if the IP address is unassigned then is Black in the ListBox?
4). Whenever Form1 first Opens, the ListBox should be blank. (if there is noting in the Text box, Form1 ListBox should be blank)

Many Thanks
Listbox110519.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
1)  you need to reset the RowSource for the list box.  Replace Me.List0 = "" with Me.List0.RowSource = "".

2)  Private Sub List0_AfterUpdate()
Dim strFormNa As String
'compose form name based on selection:
    strFormNa = DLookup("Class", "tblMain", "TagNo = '" & Me.List0.Column(0) & "'") & "Form"
'open form where TagNo = selected record
    DoCmd.OpenForm strFormNa, , , "TagNo = '" & Me.List0.Column(0) & "'"
End Sub

3)  I don't believe Access supports conditional formatting for individual rows in list boxes.  If you change the listbox to a subform, you should be able to apply conditional formatting to the fields for a particular row.  (I think this would be in the OnCurrent event, but without building it I'm not sure.)

4) Empty the RowSource property for the list box, save, and close the form.  When you reopen it, the list box should be empty.  When you start typing in Text3, the listbox RowSource property will be set to qrySearchInfo.

Author

Commented:
@Paul,
Got it.  #1 fixed.  Thanks!
Paul Cook-GilesSenior Application Developer

Commented:
Let me know how the other solutions work.  :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@Paul,
one more little thing
2)  Private Sub List0_AfterUpdate()
 Dim strFormNa As String
 'compose form name based on selection:
     strFormNa = DLookup("Class", "tblMain", "TagNo = '" & Me.List0.Column(0) & "'") & "Form"
 'open form where TagNo = selected record
     DoCmd.OpenForm strFormNa, , , "TagNo = '" & Me.List0.Column(0) & "'"
 End Sub

I changed ComputerForm to frmComputer and PropertyForm to frmProperty and the code is not working (couldn't fine the name of the form because the name changed).. I don't know where you set the name of the form
Senior Application Developer
Commented:
change
strFormNa = DLookup("Class", "tblMain", "TagNo = '" & Me.List0.Column(0) & "'") & "Form"
to
strFormNa = "frm" &  DLookup("Class", "tblMain", "TagNo = '" & Me.List0.Column(0) & "'")

Author

Commented:
for #3. I did the conditional format in the Continuous Form.  if can't not be do that in the ListBox then that's okay.
for #4).  I got it work too.

Thanks!

Author

Commented:
Got it, it works perfect.  Thank you!!
Distinguished Expert 2017
I found an old post on how to do this.  I'm pretty sure I have a newer one so I'll keep looking but here's what I found.
ColorListboxes.docx

Author

Commented:
@Pathartman,
Great, thank you!! I will take a look of this one and please let me know if you can find the newer one.  very appreciated!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial