Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Using Initialise Sub Routine

    I am trying to take data from a self built Search form and place into my main form where I can input other data and then create a main record.

    My Search bring data from another table, by double clicking I want to then be able to take the 4 fields and input onto my main from. the various codes below are how I do this but on the Code cmdShowSearch_click() i keep getting a runtime error - Overflow and the debug always shows my intNumber as Zero and the actual number in the list box as the value I have selected and therefore wont drop into my main form, how can I get around this?
    Search Form
    Code:
    Private Sub cmdShowSearch_Click()
    Dim intNHSNumber As Long
        If IsNull(Me.lstSearch) Then
            MsgBox "Select a Patient from the list", vbExclamation
            Exit Sub
        End If
        MsgBox intNHSNumber
        'Form_frmMain.Initialise Me.lstSearch
        intNHSNumber = Me.lstSearch
        Form_frmMain.Initialise intNHSNumber
        DoCmd.Close acForm, "frmSearchDetails", acSaveNo
    End Sub
    Main Form
    Code:
    Sub Initialise(intNHSNumber As Long)
    Dim sQRY As String
        sQRY = _
            "SELECT * FROM jez_SWM_PersonalDetails " & _
            "WHERE jez_SWM_PersonalDetails.PersonalID.NHSNo= " & intNHSNumber
        
        With Me
            .RecordSource = sQRY
            .txtDummy.SetFocus
            .txtNHSNo = intNHSNumber
                    
            If Me.chkInputFlag = False Then
                Assessments intNHSNumber
            End If
            
            Me.cmdSave.Enabled = True
            Me.cmdAttendance.Enabled = True
            Me.cmdSave.Enabled = True
            Me.txtForename.Enabled = True
            Me.txtSurname.Enabled = True
            Me.txtAddress1.Enabled = True
            Me.txtAddress2.Enabled = True
            Me.txtAddress3.Enabled = True
            Me.txtPostcode.Enabled = True
            Me.txtTelephone.Enabled = True
            Me.cboGender.Enabled = True
            Me.txtDOB.Enabled = True
            Me.cboReferralRsn.Enabled = True
            Me.cboReferralSource.Enabled = True
            Me.txtReferralDate.Enabled = True
            Me.txtRecievedDate.Enabled = True
        End With
    End Sub
    
    Sub Assessments(intNHSNumber As Long)
    Dim sQRY As String
        Me.txtDummy.SetFocus
        sQRY = _
            "INSERT INTO jez_SWM_ReferralDetails ([PersonalID], [NHSNo], [StartingWeight], [FinalWeight], [Height], [StartingBMI], [FinalBMI], " & _
            "[StartingBloodPressure], [FinalBloodPressure], [StartingExerciseLevel], [FinalExerciseLevel], [StartingDietLevel], [FinalDietLevel] " & _
            "[StartingSelfEsteemScore], [FinalSelfEsteemScore], [StartingWaistCircumference], [FinalWaistCircumference], [Comments], [SWMDateTimeStamp]) " & _
            "FROM jez_SWM_PersonalDetails INNER JOIN jez_SWM_ReferralDetails ON jez_SWM_PersonalDetails.PersonalID = jez_SWM_ReferralDetails.PersonalID " & _
            "WHERE jez_SWM_PersonalDetails.NHSNumber = " & intNHSNumber
        DoCmd.RunSQL sQRY
        Me.chkInputFlag = True
        Me.txtInputDate = VBA.Now
        Me.txtInputUser = fOSUserName()
        Form_fsubReferrals.Requery
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My first observation is you have
    Code:
    MsgBox intNHSNumber
    BEFORE you assign a value to the variable - so it will ALWAYS show 0.

    Code:
    Private Sub cmdShowSearch_Click()
    Dim intNHSNumber As Long
        If IsNull(Me.lstSearch) Then
            MsgBox "Select a Patient from the list", vbExclamation
            Exit Sub
        End If
        'Useless.....
        'MsgBox intNHSNumber
        'Form_frmMain.Initialise Me.lstSearch
        intNHSNumber = Me.lstSearch
        MsgBox intNHSNumber
        Form_frmMain.Initialise intNHSNumber
        DoCmd.Close acForm, "frmSearchDetails", acSaveNo
    End Sub
    Last edited by pootle flump; 11-06-08 at 13:32.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what is .rowsource for lstSearch ?

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I was under the idea that I needed to show the rowsource as what option I have chosen in the ListSearch.

    If this is wrong way of doing it, how should I do this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •