Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Data (VB6)

    Hello, its been a while !

    I've created a data form (frmClaimData) to pull data from a ODBC database.
    The form is programmed to show from a menu on a main form(frm1280Image).
    The form loads using the following code (wizard)

    Private Sub Form_Load()
    Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;dsn=MM;uid=;pwd=;"

    Set adoPrimaryRS = New Recordset
    adoPrimaryRS.Open "select EST_NO,SUP_NO,REG,OWN_NME,OWN_ADD_1,OWN_ADD_2,OWN_ ADD_3,OWN_ADD_4,OWN_PCD,OWN_TEL_H,OWN_TEL_W,
    INS_NME,CLM_NO,POL_NO,INS_TEL,VEH_MAK,VEH_MOD,EST_ NME,BKI_DTE,BKO_DTE,JOB_CAT,ENQ_SRC,CAN_DES from EST", db, adOpenStatic, adLockOptimistic

    Dim oText As TextBox
    'Bind the text boxes to the data provider
    For Each oText In Me.txtFields
    Set oText.DataSource = adoPrimaryRS
    Next

    mbDataChanged = False
    End Sub

    1 /I would like it to load data which is only relevant to a text box (txtEst) on the main form.
    Currently the form loads with all the data from table EST.
    2 / Is this the quickest method?

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Hi Dave,

    Long time no see!

    You can create a public variable strEst in a standard module, and set this variable to the text of txtEst on the main form frm1280Image before opening frmClaimData:

    strEst = Me.txtEst

    You can then use strEst to add a WHERE clause to the SQL statement. In the code below I have assumed that
    a) You want to restrict the value of the field EST_NO.
    EST_NO is a text field.

    adoPrimaryRS.Open "select EST_NO, ... ,CAN_DES from EST WHERE EST_NO=" & Chr(34) & strEst & Chr(34), db, adOpenStatic, adLockOptimistic

    If you want to restrict another field, change the part WHERE EST_NO= accordingly.
    If EST_NO is a number field, you can omit the two instances of & Chr(34)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Hi Hans, yes I've been quite busy, hope you are well?

    I've had to set the variable differently because other forms may set the strEst so I did the following:

    Private Sub procDocPrint_Click(Index As Integer)
    If Me.txtEst = "" Then
    MsgBox "No Estimate No Enterred, Please Try Again", vbInformation, "Error"
    Else
    frmClaimData.Show
    frmClaimData.txtEstTemp = Me.txtEst
    End If
    End Sub

    and then

    Private Sub Form_Load()
    Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;dsn=MM;uid=;pwd=;"

    Set adoPrimaryRS = New Recordset
    adoPrimaryRS.Open "select EST_NO,SUP_NO,REG,OWN_NME,OWN_ADD_1,OWN_ADD_2,OWN_ ADD_3,OWN_ADD_4,OWN_PCD,OWN_TEL_H,OWN_TEL_W,INS_NM E,CLM_NO,
    POL_NO,INS_TEL,VEH_MAK,VEH_MOD,EST_NME,BKI_DTE,BKO _DTE,JOB_CAT,ENQ_SRC,CAN_DES from EST WHERE EST_NO=" & Me.txtEstTemp, db, adOpenStatic, adLockOptimistic
    Dim oText As TextBox
    'Bind the text boxes to the data provider
    For Each oText In Me.txtFields
    Set oText.DataSource = adoPrimaryRS
    Next

    mbDataChanged = False
    End Sub

    txtEst is a number field
    txtEstTemp is a number field

    I'm getting errors.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    The problem is that frmClaimData.Show calls the Form_Load event procedure, but at that point you haven't set txtEstTemp yet, so the SQL is not valid.
    I'd use the public variable - you can set it from different forms because it's public.

    I'm fine, thank you

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Hans

    Its been such a long time, I can't remeber how to create the Public Function in modUtilities ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Hi Dave,

    You don't need to create a public function, just a public variable:

    Public strEst As String

    Since the variable is declared in a standard module modUtilities and since the keyword Public is used, the variable can be used throughout your project. Once you assign it a value, it will keep that value until the user quits the application or an unhandled error occurs.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Hans

    I'm still having errors:
    The variable is not passed over to frmClaimData.
    If I change strEstSW in the SQL to example 79854, then the data loads. ??

    In modUtils I have put:
    Public strEstSW as String

    From frm1280Image:
    Private Sub procDocPrint_Click(Index As Integer)
    Dim strEstSW As String
    strEstSW = Me.txtEst.Text
    MsgBox strEstSW '(Making sure the variable is there)

    If Me.txtEst = "" Then
    MsgBox "No Estimate No Enterred, Please Try Again", vbInformation, "Error"
    Else
    frmClaimData.Show

    End If
    End Sub

    And then the load event for frmClaimData

    Private Sub Form_Load()
    msgbox strEstSW '(Comes up blank)
    Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;dsn=MM;uid=;pwd=;"

    Set adoPrimaryRS = New Recordset
    adoPrimaryRS.Open "select EST_NO,SUP_NO,REG,OWN_NME,OWN_ADD_1,OWN_ADD_2,OWN_ ADD_3,OWN_ADD_4,OWN_PCD,OWN_TEL_H,OWN_TEL_W,INS_NM E,
    CLM_NO,POL_NO,INS_TEL,VEH_MAK,VEH_MOD,EST_NME,BKI_ DTE,BKO_DTE,JOB_CAT,ENQ_SRC,CAN_DES
    from EST WHERE EST_NO=" & strEstSW, db, adOpenStatic, adLockOptimistic


    Dim oText As TextBox
    'Bind the text boxes to the data provider
    For Each oText In Me.txtFields
    Set oText.DataSource = adoPrimaryRS
    Next

    mbDataChanged = False
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Hi Dave,

    The line

    Dim strEstSW As String

    in procDocPrint_Click makes strEstSW a local variable within that procedure. Setting strEstSW in procDocPrint_Click does not affect the public variable. So you must remove the above line from procDocPrint_Click.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Hans, I did that a few times so something must be a miss somewhere.


    strEstSW = Me.txtEst.Text
    MsgBox strEstSW '(Making sure the variable is there)

    or

    strEstSW = Me.txtEst
    MsgBox strEstSW '(Making sure the variable is there)

    Does not hold the variable !
    I put the msgbox in all procedures to ensure I have the variable, but always returns nothing.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Do you mean that even when you run

    strEstSW = Me.txtEst
    MsgBox strEstSW '(Making sure the variable is there)

    the Msgbox shows a blank? That would mean that the control txtEst is not filled...

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Sorry Hans

    If I enter 79068 in frm1280Image-txtEst

    strEstSW = Me.txtEst
    MsgBox strEstSW '(Returns 79068 in the msgBox, so we have the variable)


    (load frmClaimData)::-

    Private Sub Form_Load()
    msgbox strEstSW '(Comes up blank even stepping through? so the variable is not held in memory)
    Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;dsn=MM;uid=;pwd=;"

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Are you absolutely sure that you removed the line

    Dim strEstSW As String

    from procDocPrint_Click?

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Dead certain:

    Private Sub procDocPrint_Click(Index As Integer)

    strEstSW = Me.txtEst
    If Me.txtEst = "" Then
    MsgBox "No Estimate No Enterred, Please Try Again", vbInformation, "Error"
    Else
    frmClaimData.Show
    End If
    End Sub

    When the code runs, strEstSW is highlited in blue and the error "Variable Not Defined" occurs ?
    It seems so simple, I can't see why it doesn't work ?
    I even duplicated the code on a separate command button in case there was a conflict of code somewhere!

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Data (VB6)

    Ahhhhhhhhhhh

    Fixed it !!

    I took the public variable statement out of modUtils and created a separate module (modTempSW), it works fine now !!
    There must be something in the Utilities module which conflicts..

    Thanks for the help Hans, sorry it took a while to fix.

    Take care

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Data (VB6)

    Whew! Glad you were able to fix it.

Posting Permissions

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