Passing a variable from form to form.

[seh]

I have two identical forms (design). One populates a recordset (substances) into a listview, the other populates another recordset (workstations) into a listview. But both of these forms are identical design wise. I want to have just one form to display both recordsets, by means of a flag on form show or load (i'm not sure how). In the form is a sub PopulateListview:



Public Sub PopulateListview(loadRecordset As Integer)

Dim SQL As String
Dim lsiListItem As ListItem
Dim rs As ADODB.Recordset


Set rs = New ADODB.Recordset

Select Case loadRecordset

Case 0


SQL = "SELECT SubstanceAssess.SubstanceAssessID, SubstanceAssess.CoshhRef, SubstanceAssess.SubstanceName, User.Description FROM [User] INNER JOIN SubstanceAssess ON User.UserID = SubstanceAssess.UserID WHERE (((SubstanceAssess.Archived)=-1))"


Case 1


SQL = "SELECT WorkstationAssessID, WorkstationName, Location, User.Description FROM [User] INNER JOIN WorkstationAssess ON User.UserID = WorkstationAssess.UserID WHERE expired=True order by WorkstationName"


End Select



lsvListView.ListItems.Clear

With rs
.Open SQL, conn, adOpenKeyset, adLockOptimistic
If .RecordCount = 0 Then Exit Sub

Do Until .EOF
Set lsiListItem = lsvListView.ListItems.Add(, , rs(0))
lsiListItem.SubItems(1) = rs(1)
lsiListItem.SubItems(2) = rs(2)
lsiListItem.SubItems(3) = rs(3)
.MoveNext
Loop
.Close
End With

lsvListView.SelectedItem = Nothing

Set rs = Nothing

End Sub

This sub works when I set loadRecorset in form load. However the form will be loaded from a MDI form. So I need to pass loadRecordset to the form from the MDI. How? The only parameters when loading a form is modal?

Thanks.

Daz

[honeybee]

Load the form by simply using Load Form1. And then call the PopulateListView method such as:

Form1.PopulateListview 0

or 1 whatever you like. Make sure the PopulateListview method in the form remains public, else you may not be able to call it from outside.

.

[Hack]

You can put your LoadListView procedure in a .BAS module, make it Public, and pass it the Form Name, ListView name and Recordset name when you call it. Add the parameters in your procedures declaration

Public Sub PopulateListView(MyForm As Form, MyList As ListView, MyRs As ADODB.Recordset)

In this manner, you could use it from anywhere in your project. Example:

Call PopulateListView(Me, ListView1, Rs)

[seh]

Thanks for the help guys. One more question. If a recordset has more fields than another recordet, how can the listview in the PopulateListview sub set only the required amount of listitems. For example:

Do Until .EOF
Set lsiListItem = MyList.ListItems.Add(, , rs(0))
lsiListItem.SubItems(1) = rs(1)
lsiListItem.SubItems(2) = rs(2)
lsiListItem.SubItems(3) = rs(3)
.MoveNext
Loop

This is ok if the rs has 4 fields, but if the recordset has 5 fields how does the listview know this?

Thanks again.

Daz

[honeybee]

Add more subitems I guess

.

[James Stanich]

You are creating the recordset, correct? Adjust your Sub items accordingly...

[honeybee]

Just feeling pretty bored, so here is some code for ya:

Dim I As Integer
For I = 1 To rs.Fields.Count - 1 Step 1
    ListView.SubItems(I) = rs.Fields(I)
Next

.

[seh]

Originally posted by honeybee
Just feeling pretty bored, so here is some code for ya:

Dim I As Integer
For I = 1 To rs.Fields.Count - 1 Step 1
    ListView.SubItems(I) = rs.Fields(I)
Next

.

Thanks honeybee. I came up with this bit of code very similar to yours last night (intoxicated as it were).

With rs
If .RecordCount = 0 Then Exit Sub
Do Until .EOF
Set lsiListItem = MyList.ListItems.Add(, , rs(0))
For i = 1 To rs.Fields.Count - 1 Step 1
lsiListItem.SubItems(i) = rs(i)
Next
.MoveNext
Loop
End With

I found that I had to set the listitem before I did the subitem stepthrough.

Oh well it works.

Thanks all.