Open form using list box (compound key)

[Kaur]

Hi,

I have a form that has a list box (unbound) which lists all the survey
names. the data is retrieved from Survey table that is made of compound
key SurveyID and SurveyEditionID (both number fields). The list box
displays both the fields. Could some one help me with the code where if
I double click on one of the records in the list box and a second form
(Form Survey Details) opens up showing the details of the same survey
and survey Edition selected in previous form. I am pretty new to
developing the Access forms.

Any help would be appreciated.

Thanks

Kaur

[salad]

Kaur wrote:

Hi,

I have a form that has a list box (unbound) which lists all the survey
names. the data is retrieved from Survey table that is made of compound
key SurveyID and SurveyEditionID (both number fields). The list box
displays both the fields. Could some one help me with the code where if
I double click on one of the records in the list box and a second form
(Form Survey Details) opens up showing the details of the same survey
and survey Edition selected in previous form. I am pretty new to
developing the Access forms.

Any help would be appreciated.

Thanks

Kaur

Properties to look at are ListIndex, ItemData, Column, ListCount,
Selected, and related properties in online help along with their code
examples.

Here's some example junk code that scans all of the items in a listbox
and updates a record in a table.

Dim intFor As Integer
Dim rst As Recordset
Dim lngKey As Long
Dim blnSelected As Boolean
Set rst = CurrentDb.OpenRecordset("Table", dbOpenDynaset)
For intFor = 1 To Me.ListBox.ListCount - 1
lngKey = Me.ListBox.Column(0, intFor)
blnSelected = Me.ListBox.Selected(intFor)
rst.FindFirst "ID = " & lngKey
If Not rst.NoMatch Then
rst.Edit
ElseIf blnSelected Then
rst.AddNew
rst!ID = lngKey
End If
rst!YesNoField = blnSelected
rst.Update
Next

You'll notice I have the line
For intFor = 1 To Me.ListBox.ListCount - 1
The reason I start at 1 instead of 0 is that I have ListBox headings set
to True. You need to realize that when using ListIndex and ListCount.
Normally you don't process the column headings lines, so normally you
need to ignore them.

[bruce]

If your list box is named lstSurveys and the first column is the Survey
ID and the second column is the Survey Edition ID, then in the double
click event of your listbox you'll need some code like the following:

DoCmd.OpenForm "Form Survey Details", , ,"SurveyID = " &
lstSurveys.Column(0) & " and SurveyEditionID = " & lstSurveys.Column(1)

You may want to include additional code to make sure that an item is
selected in the list or that there are actually items in the list, etc.

See the online help for 'OpenForm Method' and look specifically at the
'WhereCondition' section for more information.

Bruce

[Kaur]

Thanks ever so much. I see the mistake in my code based on the code you
wrote. I very much appreciate the help. The form opens up without a
problem.

Kaur