Taking from ListBox into another Form

[TedMosby]

I have a ListBox which I use as a Search Form. When I double click on the list box (has 3 Columns and Col is bound) I want it it to then take the details in the ListBox and show them in the relevant form I have told it to go.

My problem is that it will only transfer the first column and nothing else folows, how can I get it to transfer from the ListBox to my Form?

This is my code

Private Sub lstSearch_DblClick(Cancel As Integer)
Dim sQRY As String
If IsNull(Me.lstSearch) Then
MsgBox "Select from the list", vbExclamation
Exit Sub
End If
Form_frmVisits.InputRecord Me.lstSearch
Form_frmSearchInputRecords.Visible = False
End Sub



ub InputRecord(intNHSNo As String)
'set form recordsource to selected form number
Dim sQRY As String
Dim varNewID As Integer
'**************************************
sQRY = _
"SELECT jez_SWM_Visits.VisitID, jez_SWM_Visits.NHSNo, jez_SWM_Visits.Surname, jez_SWM_Visits.Forename, jez_SWM_Visits.Gender, " & _
"jez_SWM_Visits.Address1, jez_SWM_Visits.Address2, jez_SWM_Visits.Address3, jez_SWM_Visits.Postcode, jez_SWM_Visits.Telephone, " & _
"jez_SWM_Visits.DateOfBirth, jez_SWM_Visits.ReferralReasonDescription, jez_SWM_Visits.SourceDescription, jez_SWM_Visits.DateOfReferral, " & _
"jez_SWM_Visits.VisitDate, jez_SWM_Visits.OpenorClosed, jez_SWM_Visits.Weight, jez_SWM_Visits.Height, jez_SWM_Visits.BMI, " & _
"jez_SWM_Visits.BloodPressure, jez_SWM_Visits.ExerciseLevel, jez_SWM_Visits.DietLevel, jez_SWM_Visits.SelfEsteem, " & _
"jez_SWM_Visits.WaistSize, jez_SWM_Visits.Comments, jez_SWM_Visits.SessionType, jez_SWM_Visits.NHSStaffName, " & _
"jez_SWM_Visits.Arrived, jez_SWM_Visits.ActiveRecord, jez_SWM_Visits.InputBy, jez_SWM_Visits.InputDate, jez_SWM_Visits.InputFlag " & _
"FROM jez_SWM_Visits " & _
"WHERE jez_SWM_Visits.NHSNo = '" & intNHSNo & "' "
Call UnLockAll
With Me
.RecordSource = sQRY
.txtNHSNo = intNHSNo
'.txtForename.SetFocus
End With
End Sub

[CreganTur]

One suggestion is that you can make the very first column of the listbox show as your primary key field. Then you can use the DoCmd.OpenForm method's WHERE Condition to open the form with the specified primary key value from the first column of your listbox. That should open your second form showing the data for said PK.

HTH:thumb

[TedMosby]

What about if the form is already open?

[CreganTur]

What about if the form is already open?

Then you'll need to look into the Column property. When you refer to the value of a listbox, by default it only pulls the value of the first column. You'll have to explicitly pull the values of the other columns you want.

I don't use listboxes very often in my applications, so I can't offer any concrete coding examples right now, but I'm sure someone else may be able to provide one if you need it.