I have a Search form which has a List Box on, I am trying to workout how I can get it so I can DoubleClick on the record I want and then take only 1 of the fields and input that into a TextBox on another form...

How can I do this?

pls explain better.

Set the list box to Multi Select = None.

Then either create a public variable and pass that to the form that is being opened, OR on the on open event of your form, take the value from the list box on the first form.

Put that code on the On Dbl Click event of the list box.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

This is what I have found to use

Private Sub lstSearch_DblClick(Cancel As Integer)
    If IsNull(Me.lstSearch) Then
        MsgBox "Select from the list", vbExclamation
        Exit Sub
    End If
    Form_frmMain.Initialise Me.lstSearch
    Form_frmSearchDetails.Visible = False
End Sub

This gets an error of Overflow and the Debug highlights line
    Form_frmMain.Initialise Me.lstSearch
It seems that when hovering over the yellow highlight that it has picked up the row from the list box but wont transfer into the next form.

this is the Initialise Sub in the frmMain

Sub Initialise(intNHSNumber As Long)
'set form recordsource to selected form number
Dim sQRY As String
    sQRY = _
        "SELECT jez_SWM_InputDetails.PersonalID, jez_SWM_InputDetails.NHSNo, jez_SWM_InputDetails.Surname, " & _
        "jez_SWM_InputDetails.Forename, jez_SWM_InputDetails.Gender, jez_SWM_InputDetails.Address1, jez_SWM_InputDetails.Address2, " & _
        "jez_SWM_InputDetails.Address3, jez_SWM_InputDetails.Postcode, jez_SWM_InputDetails.Telephone, jez_SWM_InputDetails.DateOfBirth, " & _
        "jez_SWM_InputDetails.ReferralReasonDescriptio n, jez_SWM_InputDetails.SourceDescription, jez_SWM_InputDetails.DateOfReferral, " & _
        "jez_SWM_InputDetails.DateReferralRecieved, jez_SWM_InputDetails.OpenorClosed, jez_SWM_InputDetails.StartingWeight, jez_SWM_InputDetails.FinalWeight, " & _
        "jez_SWM_InputDetails.Height, jez_SWM_InputDetails.StartingBMI, jez_SWM_InputDetails.FinalBMI, jez_SWM_InputDetails.StartingBloodPressure, " & _
        "jez_SWM_InputDetails.FinalBloodPressure, jez_SWM_InputDetails.StartingExerciseLevel, jez_SWM_InputDetails.FinalExerciseLevel, jez_SWM_InputDetails.StartingDietLevel, " & _
        "jez_SWM_InputDetails.FinalDietLevel, jez_SWM_InputDetails.StartingSelfEsteemScore, jez_SWM_InputDetails.FinalSelfEsteemScore, " & _
        "jez_SWM_InputDetails.StartingWaistCircumferen ce, jez_SWM_InputDetails.FinalWaistCircumference, jez_SWM_InputDetails.Comments, jez_SWM_InputDetails.InputBy, " & _
        "jez_SWM_InputDetails.InputDate, jez_SWM_InputDetails.InputFlag, jez_SWM_InputDetails.SWMDateTimeStamp " & _
        "FROM jez_SWM_InputDetails " & _
        "WHERE jez_SWM_InputDetails.NHSNo = " & intNHSNumber
    With Me
        .RecordSource = sQRY
        .txtDummy.SetFocus
        .txtNHSNo = intNHSNumber
    End With
End Sub

Where have I gone wrong?

Based on this git here: intNHSNumber


It looks like you have set your PK variable to Integer. Set it to Long and you will not get the overflow error.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, that should have been "bit" and not "git" =))))

mmcdonal

Look it up at: http://wrox.books24x7.com