Listbox basics 2

[Tronsliver]

I'm trying to insert a listbox on a form and then add items to the list box from a search of a recordset. I don't understand the basics of using a listbox verywell. I do have a basic understanding of VB. Can someone point me to a tutorial that gives a good explantion of the particulars on manipulating a listbox via VB?

Thanks

 

[TLady]

Do you want to populate ListBox from Table or Query ?

If so - you'll need to create Recordset.

TIA

P.S. Tell more about the project

 

[Tronsliver]

The recordset is already created. I want to search the record set for certain crteria and then insert the resulting information into the list box. This would be a person's name, SS#, address etc..

 

[idbr]

Tronsliver,

Is there a particular reason you are using a listbox? You may be better off using a subform by the sound of things.

Create a recordset which is based on your criteria, rather than searching the recordset for them and then use this as the control source for the subform.

Iain

 

[Tronsliver]

Thanks Iain,

I want to be able to click on the resulting data in the list box and then have another search conducted for additional information.

 

[TLady]

See how I did it

I have Search_Result text box where I am typing KeyWord.
Then Search command button run this code:

Private Sub cmd_Search_Click()

Dim mSQL As String, ListSQL As String, StrRS As String, txtStr As String
Dim SItem As Object

Set dbR = CurrentDb()
DoCmd.SetWarnings False

+++Here I am cleaning the Table wich contain Result+++
dbR.Execute "Delete from Search_Result"
+++++++++++++++++++++++++++++++++++++++++
+++Below I am Inserting into table whatever I need (looks ke you need the same data)++++++++++++++++
mSQL = "INSERT INTO Search_Result (USERID, LNAME,FNAME) " _
& "SELECT USERID, LNAME,FNAME" _
& "FROM SomeTable WHERE SomeTable.FNAME = ([Forms]![frm_Find_User]![txt_Search]) or SomeTable.LNAME = ([Forms]![frm_Find_User]![txt_Search]) or SomeTable.TERRITORYID = ([Forms]![frm_Find_User]![txt_Search]) or SomeTable.USERID = ([Forms]![frm_Find_User]![txt_Search]);"
DoCmd.RunSQL mSQL
'------------------------------------------
Set rsResp = dbR.OpenRecordset("Search_Result", dbOpenTable)
On Error Resume Next
txtStr = rsResp!LNAME
If txtStr = "" Then
MsgBox "Search Produced No Results! Please, try again!"
End If
'------------------------------------------

List_Result.RowSource = "Select USERID, LNAME,FNAME"
List_Result.SetFocus

End Sub

Hope it'll help

 

[jebry]

Hi!

You can find a lot of the basics in the Access help files but here is a quick review:

The main properties are:
ControlSource which tells what field in the recordsource of the form the list box is bound to.

RowSourceType which tells Access how to interpret the RowSource. Your choices are Table/Query, Value List and Field List

RowSource which tells Access what to populate the list box with. If the type is Table/Query then the Rowsource must be the name of a table or query or an SQL statement. If the type is Field List then the Rowsource must be the name of a table or query. If the type is Value List then the Rowsource must list the values separated by a semicolon: firstvalue;secondvalue;etc.

You also have a column count property which tells Access how many field (columns) there are in the list box. This will be the number of fields in the table or query. If the type is Value list then access will interpret the Rowsource like this: firstrowfirstcolumn;firstrowsecondcolumn;secondrowfirstcolumn;etc

If you have more than one column in a list box you can choose which column is bound if you have put anything in the controlsource. Access will automatically assume that the first column is bound. You can use the column width property to tell Access how wide each column will display. If you set the width of any column to 0 then it will not display. This is often used to display one column and store a different undisplayed column.

If you have specific needs, please ask.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Tronsliver]

TLady thanks this is what I put together thus far: The ab & ac variables are global. The code below is in a standard module that can be accessed from 6 different form modules. This is why I'm using global variables. The code that calls the procedure "PopulateExcessListBox" originates from a form module. Here is the call

PopulateExcessListBox (643)

643 identifies a specific organization in the company. The procedure itself is in a standard module. I did this so I wouldn't have to repeat the procedure in 6 different form modules.

Here is the code for PopulateExcessListBox:

Public Sub PopulateExcessListBox(varx As Integer)

Dim rst As Recordset
Dim p As Integer
Dim j As String

Set rst = CurrentDb().OpenRecordset("qryExcess", dbOpenDynaset)

With rst

.MoveFirst
Do Until .EOF
If (!orgstrPrNbr = varx) Then
j = !sidstrPOSN_NBR_Excess_IND

Select Case j
Case "9993"
ab = !sidstrName_IND
ac = !sidstrSS#
Case "9994"
"do something else here"
Case Else
p = 0
End Select

End If

.MoveNext

Loop
rst.Close
Set rst = Nothing
End With
End Sub

Once control is given back to the form module I want to be able to insert the results of global ab & ac into a listbox.

Something like this:

Me!ExcessListBox = ab
Me!ExcessListBox = ac

I know this isn't the code that should be used for the insertion but I'm sure you get the picture. I just need to know how a list box works so I can build the corret routine.

Thanks TLady.

 

[TLady]

This will do

ExcessListBox.RowSource = ab (or "ab&quot

Just keep ExcessListBox unbounded (property set)

Cheers
TIA

 

[jebry]

Hi again!

I have noticed that I have answered two separate threads. If you are having trouble using the RowSource property and want me to look at some specific code, or even at a database (zipped), please feel free to email me and I will help if I can.



Jeff Bridgham
bridgham@purdue.edu

 

[Poteen]

Tronsliver:
Two very good articles on combos & listboxes can be found on the microsoft site.

Programming Combo Box and List Box Controls in Microsoft Access, Part 1 AND Part 2

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp

Cheers,
John