Passing a variable from form to form.

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

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.

.

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

VB Code:

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:

VB Code:

Call PopulateListView(Me, ListView1, Rs)

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

Add more subitems I guess :rolleyes:

.

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

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

Code:

---

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

---

.

Quote:

---

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

Code:

---

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.