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

    Unanswered: Using Initialise

    I have a form that opens using Initialise shown with code below...

    Code:
    Sub Initialise(intNHSNo As Long)
    'set form recordsource to selected form number
    Dim sQRY As String
        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
        With Me
            .RecordSource = sQRY
            .txtNHSNo = intNHSNo
            .txtDummy.SetFocus
        End With
    End Sub
    I also have another form which is a Search Form. When I have found the person I want to Search I have either elect the line and Single Click cmdButton or Double Click on the line, as shown in the code below


    Code:
    Private Sub cmdShowSearch_Click()
    Dim sQRY As String
        If IsNull(Me.lstSearch) Then
            MsgBox "Select from the list", vbExclamation
            Exit Sub
        End If
        sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate], [RequestType], [NHSNo]) " & _
                                 "VALUES ('" & fOSUserName & "', '" & VBA.Now & "', 'SearchInputRecord', '" & Me.lstSearch & "') "
        DoCmd.RunSQL sQRY
        Form_frmVisits.Initialise Me.lstSearch
        Form_frmSearchDetails.Visible = False
    End Sub
    
    Private Sub lstSearch_DblClick(Cancel As Integer)
        Call cmdShowSearch_Click
    End Sub
    My problem is that when I select the record I want I get the error message 'Overflow' and cant get the main form to be populated by the search.

    How can I do this?

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Where do you get the message?;

    Put a breakpoint on this line:

    Form_frmVisits.Initialise Me.lstSearch
    And debug.print me.lstSearch in the immediate window

    Is this value within the bounds of a long datatype?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have don this.
    the Debug in Immediate Window showed the NHSNo I had chosen and the error is Run-Time Error '6' Overflow.
    Both fields in the Tables are varchar(20)

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Initialise(intNHSNo As Long)

    <This sub is expecting a Long datatype not a varchar!

    Form_frmVisits.Initialise Me.lstSearch

    to

    Form_frmVisits.Initialise val(Me.lstSearch)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your error message should include a debug button (since there is no error handling) - click on this and the offending line is highlighted. Knowing the line that causes the error is 90% of problem resolution.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I think I have found it. just going over it again. I thought I'd run the same query in the SQL server and copy the number from the immediate window.

    The problem I have there is that the number from the line
    Form_frmVisits.Initialise Me.lstSearch
    and cant convert data type varchar to numeric.

    How can I make sure the number I take from the lstSearch stays as varchar?

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Ok, Ichanged the
    Initialise(intNHSNo As Long)
    to Initialise(intNHSNo As String) and retried it. on the origianal line of
    Form_frmVisits.Initialise Me.lstSearch
    and
    Form_frmVisits.Initialise val(Me.lstSearch)
    on both occassions I get the same error
    Run-time Error 2001
    you canceled the previous operation

    What does this mean?

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    You have 'told' The subroutine to expect a variable of a particular type;

    Originally, you specified Ok,
    Initialise(intNHSNo As Long)

    But you were not providing a variable of Long datatype - you were providing a String (Varchar in the table).

    If you change
    Initialise(intNHSNo As String) it will expect a string so DONT change the line that says val(me.LstString)

    However - make sure you have " either side of your value in the WHERE clause of your SQL

    "WHERE jez_SWM_Visits.NHSNo = '" & intNHSNo & "'" etc.

    <Note the additional single quotes.

    Hope this works for you and you understand WHY it wasnt working in the first place.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks,

    So should I put the Initialise(intNHSNo As String) back to Initialise(intNHSNo As Long) or keep it as string?

    I debug.print the sQRY in Initialise and compared the SQL produced in SQL Server and it worked well with putting the quotation marks on the intNHSNo. but
    also the debug on the line Form_frmVisits.Initialise val(Me.lstSearch)
    and that gives me the same NHSNo that is in the Initialise, but then gets a run-time error 2105 - You can't go to the specified record.

    I understand what your thread 8 is saying but dont still understand why I cant get it to work

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    String and Long are two different DataTypes.

    If your subroutine is defined to expect a variable of a particular type, it must be provided with that same datatype.

    If you specify Initialise(intNHSNo As Long) you must provide it with a variable which is a Long datatype. Because your underlying data is Varchar ('String' in this context) you will have to change it a numerical value for the string :- val(me.lstSearch).

    If you specify Initialise(intNHSNo As String) you must provide it with a variable which is a String datatype. In this case you will also have to put quotes around the variable when you build your SQL statement as previously explained.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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