List Box (1 Viewer)

cbabi2

Member
Local time
Today, 15:38
Joined
Sep 29, 2014
Messages
34
Hi to everyone,

I have a form (frmA) with a list box (ctrList) where records are taken from a query (qryA) and all fields on the query are taken from the main table (tblA)
Please help me to do the following things as I am slowly trying to learn how work around MS Access and VB codes.

1. Want it that when I open the form the 1st record on the list is automatically selected
2. I also want the column heading of the list to be center-aligned (currently, all of the column heads are left-aligned) and if possible to make the column heads font to bold
3. also want to make the background color of row in the list different to the next row, just how it looks like in the report

Thanks in advance
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
1 is very doable something like this
Code:
Private Sub Form_Load()
  'Used itemdata(0) if no header is used
  Me.List2 = Me.List2.ItemData(1)
End Sub

2 and 3 cannot be done with a listbox, they have no real formatting. You can fake it with a subform though.
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
1. Want it that when I open the form the 1st record on the list is automatically selected
This should be automatic, if you include the pry key in the row source select statement

I also want the column heading of the list to be center-aligned (currently, all of the column heads are left-aligned) and if possible to make the column heads font to bold
I have attached how to make it bold
also want to make the background color of row in the list different to the next row, just how it looks like in the report
let me think abou this
 

Attachments

  • list column head bold.PNG
    list column head bold.PNG
    25.3 KB · Views: 336

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
This should be automatic, if you include the pry key in the row source select statement
No the first item in the list will not be selected.

If you want fancy formatting you are best off rolling your own subform to work as a listbox. There are a few moving pieces to simulate binding the control. However, it is not that difficult. In this example there is a bound listbox and a "form listbox". You can scroll through the selections and see that it acts as if bound. I did not include the select first item, but I provided the code earlier. In this case it would create a brand new record each time and would make the demo confusing.
frmAsListbox.jpg
 

Attachments

  • FrmAsListbox.accdb
    528 KB · Views: 325

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
No the first item in the list will not be selected
You have to put the order by clause. e.g
SELECT [Assets].[ID], [Assets].[Item] FROM Assets ORDER BY [ID], [Item];

here ID is pkey, and an auto number field
 

Attachments

  • listbox ordered.PNG
    listbox ordered.PNG
    10.6 KB · Views: 331

Gasman

Enthusiastic Amateur
Local time
Today, 22:38
Joined
Sep 21, 2011
Messages
14,048
You have to put the order by clause. e.g
SELECT [Assets].[ID], [Assets].[Item] FROM Assets ORDER BY [ID], [Item];

here ID is pkey, and an auto number field
Nothing is selected there? :unsure:
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
No the first item in the list will not be selected.
Hi,
I have re-attached the file you sent earlier, and sorted by pkey, pls check how it looks now.
 

Attachments

  • FrmAsListbox.accdb
    676 KB · Views: 344

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
I have re-attached the file you sent earlier, and sorted by pkey, pls check how it looks now.
Does Nothing. What is the point you are trying to make?
I already explained how to select the first item of a listbox.
Me.ListBoxName = Me.ListBoxName.ItemData(1)
or ItemDate(0) if there is no header row.

I do not think you read the question.
 
Last edited:

cbabi2

Member
Local time
Today, 15:38
Joined
Sep 29, 2014
Messages
34
This should be automatic, if you include the pry key in the row source select statement


I have attached how to make it bold

let me think abou this
I want the text of the column head to be bold and the text of the record remains normal

As for the auto select of the 1st item on the list. I checked @oleronesoftwares attachment and its working but @MajP code is what I exactly need as I dont want to show the primary key on the list.

In this form (frmA) I have a search box which is working good (thanks to you guys at this forum) it looks though the whole fields on the query (qryA) and show the relevant result on the same listbox (ctrList)...if there was no entry on the search box the listbox shows all records...... What I need this time is to put a control box so that I can print the results shown on the list box whether is was complete or came from search results

Thanks guys for the great help... I thought leaning access was enough but VB code is another thing. :)(y)🙏🙏
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
You can in query design view, uncheck the show property of the pkey field.

Check the attached images, familiarize yourself with the list box properties, so much can be done with it.
 

Attachments

  • listbox id hidden design.PNG
    listbox id hidden design.PNG
    19.6 KB · Views: 324
  • listbox id hidden.PNG
    listbox id hidden.PNG
    23.1 KB · Views: 323

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
@oleronesoftwares example still does nothing as far as I can tell. Sorting a listbox by PK does not automatically select. Maybe he can post an example where it actually does. No image or posted attachments actually shows this behavior. Maybe we are talking two different things. In my mind "select" means to select the item. Here are two listboxes one with code and the other without. The one on the right has code when the form opens it is selected.
Selected.png

This code works regardless of sorting. Also you normally hide a column by setting its width to 0 in the "column widths" property.

Selected2.png

In the attachment provided the code actually selects the first item as I have stated.

If you want a second opinion
 

Attachments

  • FrmDefaultValue.accdb
    1 MB · Views: 275

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
I want the text of the column head to be bold and the text of the record remains normal
If you want any of these formatting options as I have shown you will need to fake it with a continuous form. My example was probably more complicated then needed, because I was demonstrating how to fake "binding" the subform. I assume your "list" will be unbounded based on your description. So with a little work you should be able to get all the effects you want with a subform. However, my example also shows some tricks to make the current record appear "selected". If you want to go with the subform to simulate a listbox then there are a few steps using conditional formatting that create that effect.
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
@cbabi2 can you provide more clarification, preferably with images showing what you have now and what you intend to have.

 

cbabi2

Member
Local time
Today, 15:38
Joined
Sep 29, 2014
Messages
34
This is the sample of my database.

Its fine with me if I cant format the column head of the text box.... I just want to be able to print the search result on the listbox when I click the "Print Selected" Control Button
 

Attachments

  • SAMPLE.accdb
    3.3 MB · Views: 361

Gasman

Enthusiastic Amateur
Local time
Today, 22:38
Joined
Sep 21, 2011
Messages
14,048
Wow, I hope everyone has the same size screen as you, as I cannot see anything of vessel type? :(
That is on a 17" laptop @ 1440 * 900, the highest I can go
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:38
Joined
May 21, 2018
Messages
8,463
You should have a PK in the search results somewhere. I used the vesselname since there was none.
Code:
Private Sub frmbutPrintSelected_Click()
  DoCmd.ShowToolbar "ribbon", acToolbarYes
  DoCmd.OpenReport "rptVessel", acViewPreview, , "strVesselName = '" & Me.ctrSearchResults & "'"
  DoCmd.Maximize
End Sub

if you had a numeric PK it would be like
DoCmd.OpenReport "rptVessel", acViewPreview, , "SomePKFieldInReport = " & Me.ctrSearchResults
without single quotes.
You can bind the search form to a hidden field if that is the PK.
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:38
Joined
Sep 22, 2014
Messages
1,159
Its fine with me if I cant format the column head of the text box.... I just want to be able to print the search result on the listbox when I click the "Print Selected" Control Button
Unfortunately, i am unable to open the database, was any one able to open it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:38
Joined
Sep 21, 2011
Messages
14,048
Yes, I could open it and I only have 2007 ?
 

Users who are viewing this thread

Top Bottom