Can someone please check my code? No Intellisense for items

microsoft.public.vb.general.discussion

6 Mar 2006 3:50 PM

Sandy

Hello -

I have the following code, so far. I am trying to merely fill cboState and
cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or MoveNext.

Set cnn = New ADODB.Connection

    With cnn
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        .CursorLocation = adUserClient
        .Open
    End With

    Set cmdStates = New ADODB.Command
    With cmdStates
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "Select distinct State from tblDeclaration"
    End With

    Set cmdDeclarationNo = New ADODB.Command
    With cmdDeclarationNo
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "Select DeclarationNo from tblDeclaration Where
State=" & cboDCStates
    End With

    Set rsStates = New ADODB.Recordset
    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic

    While Not rsStates.EOF
        cboState.AddItem rsStates!State
        rsStates.MoveNext
    Wend

   Set rsDeclarationNo = New ADODB.Recordset
rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic

While Not rsDeclarationNo.EOF
    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
    rsDeclarationNo.MoveNext
   Wend

End Sub

I have the following references set:
VB for Apps
VB runtime objects & Procedures
VB objects and procedures
OLE automation
MS ActiveX Data Objects 2.8 Library
MS ActiveX Data Objects Recordset 2.8 Library
MS Data Binding Collection

ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!

--
Sandy

6 Mar 2006 4:21 PM

Steve Barnett

Have you tried compiling the code. It often gives you clues as to what the
problem is. The code you supplied below worked fine on a form with just
combo box (once I added a function header).

Steve

Show quoteHide quote

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
> Hello -
>
> I have the following code, so far. I am trying to merely fill cboState
> and
> cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or
> MoveNext.
>
> Set cnn = New ADODB.Connection
>
>    With cnn
>        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>        .CursorLocation = adUserClient
>        .Open
>    End With
>
>    Set cmdStates = New ADODB.Command
>    With cmdStates
>        .ActiveConnection = cnn
>        .CommandType = adCmdText
>        .CommandText = "Select distinct State from tblDeclaration"
>    End With
>
>    Set cmdDeclarationNo = New ADODB.Command
>    With cmdDeclarationNo
>        .ActiveConnection = cnn
>        .CommandType = adCmdText
>        .CommandText = "Select DeclarationNo from tblDeclaration Where
> State=" & cboDCStates
>    End With
>
>    Set rsStates = New ADODB.Recordset
>    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>
>    While Not rsStates.EOF
>        cboState.AddItem rsStates!State
>        rsStates.MoveNext
>    Wend
>
>   Set rsDeclarationNo = New ADODB.Recordset
> rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic
>
> While Not rsDeclarationNo.EOF
>    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>    rsDeclarationNo.MoveNext
>   Wend
>
> End Sub
>
> I have the following references set:
> VB for Apps
> VB runtime objects & Procedures
> VB objects and procedures
> OLE automation
> MS ActiveX Data Objects 2.8 Library
> MS ActiveX Data Objects Recordset 2.8 Library
> MS Data Binding Collection
>
> ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
> --
> Sandy

6 Mar 2006 6:37 PM

Sandy

Thanks for your reply, Steve!

I finally got it to sort of run, but found out the main culprit:

Set cmdDeclarationNo = New ADODB.Command
    With cmdDeclarationNo
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "Select DeclarationNo from tblDeclaration Where
State=" & cboDCStates <<<<<<<<<<<<<<<< HERE IS THE PROBLEM
    End With

That's the way I would do it in Access, but apparently that's not how to do
it in VB6. I need to narrow the list in cboDeclarationNo according to the
State that is selected in cboStates. Would you know how to code that?

Thanks again for responding!

--
Sandy

Show quoteHide quote

"Steve Barnett" wrote:

> Have you tried compiling the code. It often gives you clues as to what the
> problem is. The code you supplied below worked fine on a form with just
> combo box (once I added a function header).
>
> Steve
>
>
> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
> > Hello -
> >
> > I have the following code, so far. I am trying to merely fill cboState
> > and
> > cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or
> > MoveNext.
> >
> > Set cnn = New ADODB.Connection
> >
> >    With cnn
> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> > Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> > Source=DAO105655"
> >        .CursorLocation = adUserClient
> >        .Open
> >    End With
> >
> >    Set cmdStates = New ADODB.Command
> >    With cmdStates
> >        .ActiveConnection = cnn
> >        .CommandType = adCmdText
> >        .CommandText = "Select distinct State from tblDeclaration"
> >    End With
> >
> >    Set cmdDeclarationNo = New ADODB.Command
> >    With cmdDeclarationNo
> >        .ActiveConnection = cnn
> >        .CommandType = adCmdText
> >        .CommandText = "Select DeclarationNo from tblDeclaration Where
> > State=" & cboDCStates
> >    End With
> >
> >    Set rsStates = New ADODB.Recordset
> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
> >
> >    While Not rsStates.EOF
> >        cboState.AddItem rsStates!State
> >        rsStates.MoveNext
> >    Wend
> >
> >   Set rsDeclarationNo = New ADODB.Recordset
> > rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic
> >
> > While Not rsDeclarationNo.EOF
> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
> >    rsDeclarationNo.MoveNext
> >   Wend
> >
> > End Sub
> >
> > I have the following references set:
> > VB for Apps
> > VB runtime objects & Procedures
> > VB objects and procedures
> > OLE automation
> > MS ActiveX Data Objects 2.8 Library
> > MS ActiveX Data Objects Recordset 2.8 Library
> > MS Data Binding Collection
> >
> > ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
> > --
> > Sandy
>
>
>

6 Mar 2006 7:05 PM

Ken Halter

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:C3945D72-5B46-4255-92B1-0FE05500CF72@microsoft.com...
> Thanks for your reply, Steve!
>
> I finally got it to sort of run, but found out the main culprit:
>
> That's the way I would do it in Access, but apparently that's not how to
> do
> it in VB6. I need to narrow the list in cboDeclarationNo according to the

Try to avoid relying on Default object properties.

> State that is selected in cboStates. Would you know how to code that?
>
> Thanks again for responding!

'===========
Option Explicit

Private Sub Command1_Click()
   Dim sCmdText As String
   Dim sSelectedState As String

   sSelectedState = cboDCStates.Text
   'or.....
   sSelectedState = cboDCStates.List(cboDCStates.ListIndex)

   sCmdText = "Select DeclarationNo from tblDeclaration"
   sCmdText = sCmdText & " Where State=" & sSelectedState
   Debug.Print sCmdText 'good to debug.print it before assigning it...

>    With cmdDeclarationNo
>        .ActiveConnection = cnn
>        .CommandType = adCmdText
>        .CommandText = sCmdText
>    End With

End Sub

Private Sub Form_Load()
   With cboDCStates
      .AddItem "AZ"
      .AddItem "CA"
      .AddItem "Confusion"
      .ListIndex = 2
   End With
End Sub
'===========

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Freeware 4 color Gradient Frame? http://www.vbsight.com/GradFrameCTL.htm

7 Mar 2006 6:39 AM

Ken Halter

"Ken Halter" <Ken_Halter@Use_Sparingly_Hotmail.com> wrote in message
news:OXh$qDVQGHA.740@TK2MSFTNGP12.phx.gbl...
>
>> State that is selected in cboStates. Would you know how to code that?

My reply didn't answer that question (oops)... I'm surely no "DB Guy". Thing
is, if it's all done in code, it's pretty easy to navigate around DBs using
record IDs. If you're using databound controls, I have no clue. Data binding
robs you of your ability to take full control of the way data is
read/displayed/stored so it's a bit on the "bad" side imo.

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm

7 Mar 2006 9:10 AM

Steve Barnett

Have you tried:

.CommandText = "Select DeclarationNo from tblDeclaration Where State='"
& cboDCStates.text & "'"

Note that the State= is followed by a single quote then the double quote.
The end of the line consists of double-quote + single quote + double quote.
For the command to work, I believe the text parameter in the Where clause
must be in single quotes.

Steve

Show quoteHide quote

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:C3945D72-5B46-4255-92B1-0FE05500CF72@microsoft.com...
> Thanks for your reply, Steve!
>
> I finally got it to sort of run, but found out the main culprit:
>
> Set cmdDeclarationNo = New ADODB.Command
>    With cmdDeclarationNo
>        .ActiveConnection = cnn
>        .CommandType = adCmdText
>        .CommandText = "Select DeclarationNo from tblDeclaration Where
> State=" & cboDCStates <<<<<<<<<<<<<<<< HERE IS THE PROBLEM
>    End With
>
> That's the way I would do it in Access, but apparently that's not how to
> do
> it in VB6. I need to narrow the list in cboDeclarationNo according to the
> State that is selected in cboStates. Would you know how to code that?
>
> Thanks again for responding!
>
>
>
>
>
> --
> Sandy
>
>
> "Steve Barnett" wrote:
>
>> Have you tried compiling the code. It often gives you clues as to what
>> the
>> problem is. The code you supplied below worked fine on a form with just
>> combo box (once I added a function header).
>>
>> Steve
>>
>>
>> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
>> news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
>> > Hello -
>> >
>> > I have the following code, so far. I am trying to merely fill
>> > cboState
>> > and
>> > cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or
>> > MoveNext.
>> >
>> > Set cnn = New ADODB.Connection
>> >
>> >    With cnn
>> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
>> > Security=SSPI;Persist Security Info=False;Initial
>> > Catalog=CallTracker;Data
>> > Source=DAO105655"
>> >        .CursorLocation = adUserClient
>> >        .Open
>> >    End With
>> >
>> >    Set cmdStates = New ADODB.Command
>> >    With cmdStates
>> >        .ActiveConnection = cnn
>> >        .CommandType = adCmdText
>> >        .CommandText = "Select distinct State from tblDeclaration"
>> >    End With
>> >
>> >    Set cmdDeclarationNo = New ADODB.Command
>> >    With cmdDeclarationNo
>> >        .ActiveConnection = cnn
>> >        .CommandType = adCmdText
>> >        .CommandText = "Select DeclarationNo from tblDeclaration Where
>> > State=" & cboDCStates
>> >    End With
>> >
>> >    Set rsStates = New ADODB.Recordset
>> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>> >
>> >    While Not rsStates.EOF
>> >        cboState.AddItem rsStates!State
>> >        rsStates.MoveNext
>> >    Wend
>> >
>> >   Set rsDeclarationNo = New ADODB.Recordset
>> > rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic,
>> > adLockOptimistic
>> >
>> > While Not rsDeclarationNo.EOF
>> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>> >    rsDeclarationNo.MoveNext
>> >   Wend
>> >
>> > End Sub
>> >
>> > I have the following references set:
>> > VB for Apps
>> > VB runtime objects & Procedures
>> > VB objects and procedures
>> > OLE automation
>> > MS ActiveX Data Objects 2.8 Library
>> > MS ActiveX Data Objects Recordset 2.8 Library
>> > MS Data Binding Collection
>> >
>> > ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
>> > --
>> > Sandy
>>
>>
>>

7 Mar 2006 7:21 PM

Sandy

Thanks for responding again, Steve.

Now, although I am getting no error messages, there is nothing being put
into cboDeclarationNo at all. What am I doing wrong?

In Access, there's an AfterUpdate event for comboboxes that takes care of
situations like this.

[Note I've changed cboDCStates to cboState]

Private Sub cboState_Change()
    Dim cnn2 As New ADODB.Connection
    With cnn2
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        '.CursorLocation = adUserClient
        .Open
    End With
    Set cmdDeclarationNo = New ADODB.Command
    With cmdDeclarationNo
        .ActiveConnection = cnn2
        .CommandType = adCmdText
        .CommandText = "Select Distinct DeclarationNo from tblDeclaration
Where State='" & cboState.Text & "'"
    End With

    Set rsDeclarationNo = New ADODB.Recordset
    rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic

While Not rsDeclarationNo.EOF
    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
    rsDeclarationNo.MoveNext
   Wend
End Sub

Private Sub Form_Load()
    Load frmBackground
    frmBackground.Show

    Dim rsStates As Recordset
    Dim rsDeclarationNo As Recordset
    Dim rsCallType As Recordset
    Dim cmdStates As Command
    Dim cmdDeclarationNo As Command
    Dim cmdCallType As Command
    Dim cnn As Connection

    Set cnn = New ADODB.Connection

    With cnn
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        '.CursorLocation = adUserClient
        .Open
    End With

    Set cmdStates = New ADODB.Command
    With cmdStates
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "Select distinct State from tblDeclaration"

    End With

    Set rsStates = New ADODB.Recordset
    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic

    While Not rsStates.EOF
        cboState.AddItem rsStates!State
        rsStates.MoveNext
    Wend

End Sub

--
Sandy

Show quoteHide quote

"Steve Barnett" wrote:

> Have you tried:
>
>     .CommandText = "Select DeclarationNo from tblDeclaration Where State='"
> & cboDCStates.text & "'"
>
> Note that the State= is followed by a single quote then the double quote.
> The end of the line consists of double-quote + single quote + double quote.
> For the command to work, I believe the text parameter in the Where clause
> must be in single quotes.
>
> Steve
>
>
>
> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> news:C3945D72-5B46-4255-92B1-0FE05500CF72@microsoft.com...
> > Thanks for your reply, Steve!
> >
> > I finally got it to sort of run, but found out the main culprit:
> >
> > Set cmdDeclarationNo = New ADODB.Command
> >    With cmdDeclarationNo
> >        .ActiveConnection = cnn
> >        .CommandType = adCmdText
> >        .CommandText = "Select DeclarationNo from tblDeclaration Where
> > State=" & cboDCStates <<<<<<<<<<<<<<<< HERE IS THE PROBLEM
> >    End With
> >
> > That's the way I would do it in Access, but apparently that's not how to
> > do
> > it in VB6. I need to narrow the list in cboDeclarationNo according to the
> > State that is selected in cboStates. Would you know how to code that?
> >
> > Thanks again for responding!
> >
> >
> >
> >
> >
> > --
> > Sandy
> >
> >
> > "Steve Barnett" wrote:
> >
> >> Have you tried compiling the code. It often gives you clues as to what
> >> the
> >> problem is. The code you supplied below worked fine on a form with just
> >> combo box (once I added a function header).
> >>
> >> Steve
> >>
> >>
> >> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> >> news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
> >> > Hello -
> >> >
> >> > I have the following code, so far. I am trying to merely fill
> >> > cboState
> >> > and
> >> > cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or
> >> > MoveNext.
> >> >
> >> > Set cnn = New ADODB.Connection
> >> >
> >> >    With cnn
> >> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> >> > Security=SSPI;Persist Security Info=False;Initial
> >> > Catalog=CallTracker;Data
> >> > Source=DAO105655"
> >> >        .CursorLocation = adUserClient
> >> >        .Open
> >> >    End With
> >> >
> >> >    Set cmdStates = New ADODB.Command
> >> >    With cmdStates
> >> >        .ActiveConnection = cnn
> >> >        .CommandType = adCmdText
> >> >        .CommandText = "Select distinct State from tblDeclaration"
> >> >    End With
> >> >
> >> >    Set cmdDeclarationNo = New ADODB.Command
> >> >    With cmdDeclarationNo
> >> >        .ActiveConnection = cnn
> >> >        .CommandType = adCmdText
> >> >        .CommandText = "Select DeclarationNo from tblDeclaration Where
> >> > State=" & cboDCStates
> >> >    End With
> >> >
> >> >    Set rsStates = New ADODB.Recordset
> >> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
> >> >
> >> >    While Not rsStates.EOF
> >> >        cboState.AddItem rsStates!State
> >> >        rsStates.MoveNext
> >> >    Wend
> >> >
> >> >   Set rsDeclarationNo = New ADODB.Recordset
> >> > rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic,
> >> > adLockOptimistic
> >> >
> >> > While Not rsDeclarationNo.EOF
> >> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
> >> >    rsDeclarationNo.MoveNext
> >> >   Wend
> >> >
> >> > End Sub
> >> >
> >> > I have the following references set:
> >> > VB for Apps
> >> > VB runtime objects & Procedures
> >> > VB objects and procedures
> >> > OLE automation
> >> > MS ActiveX Data Objects 2.8 Library
> >> > MS ActiveX Data Objects Recordset 2.8 Library
> >> > MS Data Binding Collection
> >> >
> >> > ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
> >> > --
> >> > Sandy
> >>
> >>
> >>
>
>
>

8 Mar 2006 4:11 AM

Ken Halter

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:98629946-2C08-42A7-9EC6-66AFB3E97A86@microsoft.com...
> Thanks for responding again, Steve.
>
> Now, although I am getting no error messages, there is nothing being put
> into cboDeclarationNo at all. What am I doing wrong?
>
> In Access, there's an AfterUpdate event for comboboxes that takes care of
> situations like this.
>
> [Note I've changed cboDCStates to cboState]
>
> Private Sub cboState_Change()

That should probably be Click... not Change. Change fires when you type in
the box. Click fires when you select an item from the list.

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm

8 Mar 2006 2:18 PM

Sandy

Thanks guys!! I finally got it to work. -- just one other question -- below
is my code. It seems like it's awfully lengthy and redundant. Any
suggestions?

Option Explicit
Private Sub cboDeclarationNo_Click()
    Dim cmdDeclaration As ADODB.Command
    Dim rsDeclaration As ADODB.Recordset
    Dim cnn3 As Connection
    Set cnn3 = New ADODB.Connection
    With cnn3
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        '.CursorLocation = adUserClient
        .Open
    End With
    Set cmdDeclaration = New ADODB.Command
    With cmdDeclaration
        .ActiveConnection = cnn3
        .CommandType = adCmdText
        .CommandText = "Select Declaration from tblDeclaration " _
            & "Where DeclarationNo='" & cboDeclarationNo.Text & "'"
'Where State = " & cboState
    End With

    Set rsDeclaration = New ADODB.Recordset
    rsDeclaration.Open cmdDeclaration, , adOpenStatic, adLockOptimistic
    txtDeclaration.Text = rsDeclaration!Declaration

    rsDeclaration.Close
    cnn3.Close
    Set rsDeclaration = Nothing
    Set cnn3 = Nothing

End Sub

Private Sub cboState_Click()
    cboDeclarationNo.Clear
    Dim cmdDeclarationNo As ADODB.Command
    Dim rsDeclarationNo As ADODB.Recordset
    Dim cnn2 As Connection
    Set cnn2 = New ADODB.Connection
    With cnn2
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        '.CursorLocation = adUserClient
        .Open
    End With
    Set cmdDeclarationNo = New ADODB.Command
    With cmdDeclarationNo
        .ActiveConnection = cnn2
        .CommandType = adCmdText
        .CommandText = "Select Distinct DeclarationNo from tblDeclaration " _
            & "Where State='" & cboState.Text & "'"
    End With

    Set rsDeclarationNo = New ADODB.Recordset
    rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic

While Not rsDeclarationNo.EOF
    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
    rsDeclarationNo.MoveNext
   Wend

    rsDeclarationNo.Close
    cnn2.Close
    Set rsDeclarationNo = Nothing
    Set cnn2 = Nothing
End Sub

Private Sub Form_Load()
    Load frmBackground
    frmBackground.Show
    txtName.Text = UserName
    txtDate.Text = Now()
    txtLoanType.Text = ""
    Dim rsStates As Recordset
    Dim rsDeclarationNo As Recordset
    Dim rsCallType As Recordset
    Dim cmdStates As Command
    Dim cmdDeclarationNo As Command
    Dim cmdCallType As Command
    Dim cnn As Connection

    Set cnn = New ADODB.Connection

    With cnn
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
        '.CursorLocation = adUserClient
        .Open
    End With
'FILL STATES COMBO
    Set cmdStates = New ADODB.Command
    With cmdStates
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "Select distinct State from tblDeclaration"
    End With

    Set rsStates = New ADODB.Recordset
    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic

    While Not rsStates.EOF
        cboState.AddItem rsStates!State
        rsStates.MoveNext
    Wend
'FILL CALLTYPE COMBO
Set cmdCallType = New ADODB.Command
   With cmdCallType
        .ActiveConnection = cnn
        '.CommandType = acCmdText
        .CommandText = "Select CallType from tblCallType"
    End With

    Set rsCallType = New ADODB.Recordset
    rsCallType.Open cmdCallType, , adOpenStatic, adLockOptimistic

    While Not rsCallType.EOF
    cboCallType.AddItem rsCallType!CallType
    rsCallType.MoveNext
    Wend

    rsStates.Close
    rsCallType.Close
    cnn.Close
    Set rsStates = Nothing
    Set rsCallType = Nothing
    Set cnn = Nothing

End Sub

--
Sandy

Show quoteHide quote

"Sandy" wrote:

> Thanks for responding again, Steve.
>
> Now, although I am getting no error messages, there is nothing being put
> into cboDeclarationNo at all. What am I doing wrong?
>
> In Access, there's an AfterUpdate event for comboboxes that takes care of
> situations like this.
>
> [Note I've changed cboDCStates to cboState]
>
> Private Sub cboState_Change()
>     Dim cnn2 As New ADODB.Connection
>     With cnn2
>         .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>         '.CursorLocation = adUserClient
>         .Open
>     End With
>     Set cmdDeclarationNo = New ADODB.Command
>     With cmdDeclarationNo
>         .ActiveConnection = cnn2
>         .CommandType = adCmdText
>         .CommandText = "Select Distinct DeclarationNo from tblDeclaration
> Where State='" & cboState.Text & "'"
>     End With
>
>     Set rsDeclarationNo = New ADODB.Recordset
>     rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic
>
>   While Not rsDeclarationNo.EOF
>     cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>     rsDeclarationNo.MoveNext
>    Wend
> End Sub
>
> Private Sub Form_Load()
>     Load frmBackground
>     frmBackground.Show
>
>     Dim rsStates As Recordset
>     Dim rsDeclarationNo As Recordset
>     Dim rsCallType As Recordset
>     Dim cmdStates As Command
>     Dim cmdDeclarationNo As Command
>     Dim cmdCallType As Command
>     Dim cnn As Connection
>
>     Set cnn = New ADODB.Connection
>
>     With cnn
>         .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>         '.CursorLocation = adUserClient
>         .Open
>     End With
>
>     Set cmdStates = New ADODB.Command
>     With cmdStates
>         .ActiveConnection = cnn
>         .CommandType = adCmdText
>         .CommandText = "Select distinct State from tblDeclaration"
>
>     End With
>
>     Set rsStates = New ADODB.Recordset
>     rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>
>     While Not rsStates.EOF
>         cboState.AddItem rsStates!State
>         rsStates.MoveNext
>     Wend
>
>
> End Sub
> --
> Sandy
>
>
> "Steve Barnett" wrote:
>
> > Have you tried:
> >
> >     .CommandText = "Select DeclarationNo from tblDeclaration Where State='"
> > & cboDCStates.text & "'"
> >
> > Note that the State= is followed by a single quote then the double quote.
> > The end of the line consists of double-quote + single quote + double quote.
> > For the command to work, I believe the text parameter in the Where clause
> > must be in single quotes.
> >
> > Steve
> >
> >
> >
> > "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> > news:C3945D72-5B46-4255-92B1-0FE05500CF72@microsoft.com...
> > > Thanks for your reply, Steve!
> > >
> > > I finally got it to sort of run, but found out the main culprit:
> > >
> > > Set cmdDeclarationNo = New ADODB.Command
> > >    With cmdDeclarationNo
> > >        .ActiveConnection = cnn
> > >        .CommandType = adCmdText
> > >        .CommandText = "Select DeclarationNo from tblDeclaration Where
> > > State=" & cboDCStates <<<<<<<<<<<<<<<< HERE IS THE PROBLEM
> > >    End With
> > >
> > > That's the way I would do it in Access, but apparently that's not how to
> > > do
> > > it in VB6. I need to narrow the list in cboDeclarationNo according to the
> > > State that is selected in cboStates. Would you know how to code that?
> > >
> > > Thanks again for responding!
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Sandy
> > >
> > >
> > > "Steve Barnett" wrote:
> > >
> > >> Have you tried compiling the code. It often gives you clues as to what
> > >> the
> > >> problem is. The code you supplied below worked fine on a form with just
> > >> combo box (once I added a function header).
> > >>
> > >> Steve
> > >>
> > >>
> > >> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> > >> news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
> > >> > Hello -
> > >> >
> > >> > I have the following code, so far. I am trying to merely fill
> > >> > cboState
> > >> > and
> > >> > cboDeclarationNo. I am getting no Intellisense for EOF, AddItem or
> > >> > MoveNext.
> > >> >
> > >> > Set cnn = New ADODB.Connection
> > >> >
> > >> >    With cnn
> > >> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> > >> > Security=SSPI;Persist Security Info=False;Initial
> > >> > Catalog=CallTracker;Data
> > >> > Source=DAO105655"
> > >> >        .CursorLocation = adUserClient
> > >> >        .Open
> > >> >    End With
> > >> >
> > >> >    Set cmdStates = New ADODB.Command
> > >> >    With cmdStates
> > >> >        .ActiveConnection = cnn
> > >> >        .CommandType = adCmdText
> > >> >        .CommandText = "Select distinct State from tblDeclaration"
> > >> >    End With
> > >> >
> > >> >    Set cmdDeclarationNo = New ADODB.Command
> > >> >    With cmdDeclarationNo
> > >> >        .ActiveConnection = cnn
> > >> >        .CommandType = adCmdText
> > >> >        .CommandText = "Select DeclarationNo from tblDeclaration Where
> > >> > State=" & cboDCStates
> > >> >    End With
> > >> >
> > >> >    Set rsStates = New ADODB.Recordset
> > >> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
> > >> >
> > >> >    While Not rsStates.EOF
> > >> >        cboState.AddItem rsStates!State
> > >> >        rsStates.MoveNext
> > >> >    Wend
> > >> >
> > >> >   Set rsDeclarationNo = New ADODB.Recordset
> > >> > rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic,
> > >> > adLockOptimistic
> > >> >
> > >> > While Not rsDeclarationNo.EOF
> > >> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
> > >> >    rsDeclarationNo.MoveNext
> > >> >   Wend
> > >> >
> > >> > End Sub
> > >> >
> > >> > I have the following references set:
> > >> > VB for Apps
> > >> > VB runtime objects & Procedures
> > >> > VB objects and procedures
> > >> > OLE automation
> > >> > MS ActiveX Data Objects 2.8 Library
> > >> > MS ActiveX Data Objects Recordset 2.8 Library
> > >> > MS Data Binding Collection
> > >> >
> > >> > ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
> > >> > --
> > >> > Sandy
> > >>
> > >>
> > >>
> >
> >
> >

9 Mar 2006 9:23 AM

Steve Barnett

First the discllaimer... I tossed this code together in a text editor, so
the syntax may not be entirely correct... I've also no used ADO (firmly
stuck with a DAO database). However, that said, what I would do is something
along the lines of the routines below.

If you run through the code, you should note that I have split out the
creation of a connection to a routine of it's own (it's the same code every
time) and that I have created a separate routine for opening a recordset,
as the process is roughly the same every time.

I beliee the code is easier to read and gives you two points where you need
to add error trapping code; the open of the connection and the getting of
the recordset. However, all of this is taste and fancy - this is just how I
would have gone about it. You could further change this by moving the call
to GetConnection in to the GetRecordset function, if you wish - I just like
to keep them separate.

Steve

Option Explicit

Private Sub cboDeclarationNo_Click()
    Dim rsDeclaration As ADODB.Recordset
    Dim cnn3 As Connection

    set cnn3 = GetConnection()
    Set rsDeclaration = GetRecordset(cnn3, "Select Declaration from
tblDeclaration " _
            & "Where DeclarationNo='" & cboDeclarationNo.Text & "'"
    txtDeclaration.Text = rsDeclaration!Declaration

    closerecordset rsDeclaration
    CloseConnection cnn3

End Sub

Private Sub cboState_Click()
    Dim rsDeclarationNo As ADODB.Recordset
    Dim cnn2 As Connection

    cboDeclarationNo.Clear

    set cnn2 = GetConnection()
    set rsDeclatarionNo = GetRecordset(cnn2, "Select Distinct DeclarationNo
from tblDeclaration " _
            & "Where State='" & cboState.Text & "'"

   While Not rsDeclarationNo.EOF
      cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
       rsDeclarationNo.MoveNext
    Wend

    closeRecordset rsDeclarationNo
    closeConnection cnn2
End Sub

Private Sub Form_Load()
    Dim rsStates As Recordset
    Dim rsCallType As Recordset
    Dim cnn As Connection

    Load frmBackground
    frmBackground.Show

    txtName.Text = UserName
    txtDate.Text = Now()
    txtLoanType.Text = ""

    set cnn =GetConnection()

'FILL STATES COMBO
set rsStates = GetRecordset(cnn, "Select distinct State from
tblDeclaration")

    While Not rsStates.EOF
        cboState.AddItem rsStates!State
        rsStates.MoveNext
    Wend

    CloseRecordset rsStates

'FILL CALLTYPE COMBO
set rsCallType = GetRecordset(cnn, "Select CallType from tblCallType")

    While Not rsCallType.EOF
     cboCallType.AddItem rsCallType!CallType
     rsCallType.MoveNext
    Wend

    CloseRecordset rsCallType
    CloseConnection cnn
End Sub

Public Function GetConnection(byval connectionString as string) as
ADODB.Connection
Dim oConnection as Connection

Set oConnection = new AdoDb.Connection

With oConnection
     .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
Source=DAO105655"
     '.CursorLocation = adUserClient
     .Open
End With

set GetConnection = oConnection

End Function

Public sub CloseConnection(byref oConnection as Connection)
     oConnection.close
     set oConnection = nothing
End sub

Public function GetRecordset(byval oConnection as Connection, byval
SQlstatement as string)
Dim cmdCommand as AdoDb.Command
Dim rsRecordset as AdoDb.Recordset

set cmdCommand = new AdoDb.Command
cmdCommand.ActiveConnection = cnn3
cmdCommand.CommandType = adCmdText
cmdCommand.CommandText = SQlstatement

set rsRecordset = new Adodb.recordset
rsRecordset.Open cmdCommand, , adOpenStatic, adLockOptomistic

set GetRecordset = rsRecordset
End Function

Public sub CloseRecordset(byref rsRecordset as recordset)
rsRecordset.close
set rsRecordset = nothing
end sub

Show quoteHide quote

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:AB34A774-BDA1-40B2-8A78-EBFBFBAEA7D7@microsoft.com...
> Thanks guys!! I finally got it to work. -- just one other question --
> below
> is my code. It seems like it's awfully lengthy and redundant. Any
> suggestions?
>
> Option Explicit
> Private Sub cboDeclarationNo_Click()
>    Dim cmdDeclaration As ADODB.Command
>    Dim rsDeclaration As ADODB.Recordset
>    Dim cnn3 As Connection
>    Set cnn3 = New ADODB.Connection
>    With cnn3
>        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>        '.CursorLocation = adUserClient
>        .Open
>    End With
>    Set cmdDeclaration = New ADODB.Command
>    With cmdDeclaration
>        .ActiveConnection = cnn3
>        .CommandType = adCmdText
>        .CommandText = "Select Declaration from tblDeclaration " _
>            & "Where DeclarationNo='" & cboDeclarationNo.Text & "'"
> 'Where State = " & cboState
>    End With
>
>    Set rsDeclaration = New ADODB.Recordset
>    rsDeclaration.Open cmdDeclaration, , adOpenStatic, adLockOptimistic
>    txtDeclaration.Text = rsDeclaration!Declaration
>
>    rsDeclaration.Close
>    cnn3.Close
>    Set rsDeclaration = Nothing
>    Set cnn3 = Nothing
>
> End Sub
>
> Private Sub cboState_Click()
>    cboDeclarationNo.Clear
>    Dim cmdDeclarationNo As ADODB.Command
>    Dim rsDeclarationNo As ADODB.Recordset
>    Dim cnn2 As Connection
>    Set cnn2 = New ADODB.Connection
>    With cnn2
>        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>        '.CursorLocation = adUserClient
>        .Open
>    End With
>    Set cmdDeclarationNo = New ADODB.Command
>    With cmdDeclarationNo
>        .ActiveConnection = cnn2
>        .CommandType = adCmdText
>        .CommandText = "Select Distinct DeclarationNo from tblDeclaration "
> _
>            & "Where State='" & cboState.Text & "'"
>    End With
>
>    Set rsDeclarationNo = New ADODB.Recordset
>    rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic
>
> While Not rsDeclarationNo.EOF
>    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>    rsDeclarationNo.MoveNext
>   Wend
>
>    rsDeclarationNo.Close
>    cnn2.Close
>    Set rsDeclarationNo = Nothing
>    Set cnn2 = Nothing
> End Sub
>
>
> Private Sub Form_Load()
>    Load frmBackground
>    frmBackground.Show
>    txtName.Text = UserName
>    txtDate.Text = Now()
>    txtLoanType.Text = ""
>    Dim rsStates As Recordset
>    Dim rsDeclarationNo As Recordset
>    Dim rsCallType As Recordset
>    Dim cmdStates As Command
>    Dim cmdDeclarationNo As Command
>    Dim cmdCallType As Command
>    Dim cnn As Connection
>
>    Set cnn = New ADODB.Connection
>
>    With cnn
>        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>        '.CursorLocation = adUserClient
>        .Open
>    End With
> 'FILL STATES COMBO
>    Set cmdStates = New ADODB.Command
>    With cmdStates
>        .ActiveConnection = cnn
>        .CommandType = adCmdText
>        .CommandText = "Select distinct State from tblDeclaration"
>    End With
>
>    Set rsStates = New ADODB.Recordset
>    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>
>    While Not rsStates.EOF
>        cboState.AddItem rsStates!State
>        rsStates.MoveNext
>    Wend
> 'FILL CALLTYPE COMBO
> Set cmdCallType = New ADODB.Command
>   With cmdCallType
>        .ActiveConnection = cnn
>        '.CommandType = acCmdText
>        .CommandText = "Select CallType from tblCallType"
>    End With
>
>    Set rsCallType = New ADODB.Recordset
>    rsCallType.Open cmdCallType, , adOpenStatic, adLockOptimistic
>
>    While Not rsCallType.EOF
>    cboCallType.AddItem rsCallType!CallType
>    rsCallType.MoveNext
>    Wend
>
>    rsStates.Close
>    rsCallType.Close
>    cnn.Close
>    Set rsStates = Nothing
>    Set rsCallType = Nothing
>    Set cnn = Nothing
>
> End Sub
>
> --
> Sandy
>
>
> "Sandy" wrote:
>
>> Thanks for responding again, Steve.
>>
>> Now, although I am getting no error messages, there is nothing being put
>> into cboDeclarationNo at all. What am I doing wrong?
>>
>> In Access, there's an AfterUpdate event for comboboxes that takes care of
>> situations like this.
>>
>> [Note I've changed cboDCStates to cboState]
>>
>> Private Sub cboState_Change()
>>     Dim cnn2 As New ADODB.Connection
>>     With cnn2
>>         .ConnectionString = "Provider=SQLOLEDB.1;Integrated
>> Security=SSPI;Persist Security Info=False;Initial
>> Catalog=CallTracker;Data
>> Source=DAO105655"
>>         '.CursorLocation = adUserClient
>>         .Open
>>     End With
>>     Set cmdDeclarationNo = New ADODB.Command
>>     With cmdDeclarationNo
>>         .ActiveConnection = cnn2
>>         .CommandType = adCmdText
>>         .CommandText = "Select Distinct DeclarationNo from tblDeclaration
>> Where State='" & cboState.Text & "'"
>>     End With
>>
>>     Set rsDeclarationNo = New ADODB.Recordset
>>     rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic,
>> adLockOptimistic
>>
>>   While Not rsDeclarationNo.EOF
>>     cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>>     rsDeclarationNo.MoveNext
>>    Wend
>> End Sub
>>
>> Private Sub Form_Load()
>>     Load frmBackground
>>     frmBackground.Show
>>
>>     Dim rsStates As Recordset
>>     Dim rsDeclarationNo As Recordset
>>     Dim rsCallType As Recordset
>>     Dim cmdStates As Command
>>     Dim cmdDeclarationNo As Command
>>     Dim cmdCallType As Command
>>     Dim cnn As Connection
>>
>>     Set cnn = New ADODB.Connection
>>
>>     With cnn
>>         .ConnectionString = "Provider=SQLOLEDB.1;Integrated
>> Security=SSPI;Persist Security Info=False;Initial
>> Catalog=CallTracker;Data
>> Source=DAO105655"
>>         '.CursorLocation = adUserClient
>>         .Open
>>     End With
>>
>>     Set cmdStates = New ADODB.Command
>>     With cmdStates
>>         .ActiveConnection = cnn
>>         .CommandType = adCmdText
>>         .CommandText = "Select distinct State from tblDeclaration"
>>
>>     End With
>>
>>     Set rsStates = New ADODB.Recordset
>>     rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>>
>>     While Not rsStates.EOF
>>         cboState.AddItem rsStates!State
>>         rsStates.MoveNext
>>     Wend
>>
>>
>> End Sub
>> --
>> Sandy
>>
>>
>> "Steve Barnett" wrote:
>>
>> > Have you tried:
>> >
>> >     .CommandText = "Select DeclarationNo from tblDeclaration Where
>> > State='"
>> > & cboDCStates.text & "'"
>> >
>> > Note that the State= is followed by a single quote then the double
>> > quote.
>> > The end of the line consists of double-quote + single quote + double
>> > quote.
>> > For the command to work, I believe the text parameter in the Where
>> > clause
>> > must be in single quotes.
>> >
>> > Steve
>> >
>> >
>> >
>> > "Sandy" <Sa***@discussions.microsoft.com> wrote in message
>> > news:C3945D72-5B46-4255-92B1-0FE05500CF72@microsoft.com...
>> > > Thanks for your reply, Steve!
>> > >
>> > > I finally got it to sort of run, but found out the main culprit:
>> > >
>> > > Set cmdDeclarationNo = New ADODB.Command
>> > >    With cmdDeclarationNo
>> > >        .ActiveConnection = cnn
>> > >        .CommandType = adCmdText
>> > >        .CommandText = "Select DeclarationNo from tblDeclaration Where
>> > > State=" & cboDCStates <<<<<<<<<<<<<<<< HERE IS THE PROBLEM
>> > >    End With
>> > >
>> > > That's the way I would do it in Access, but apparently that's not how
>> > > to
>> > > do
>> > > it in VB6. I need to narrow the list in cboDeclarationNo according
>> > > to the
>> > > State that is selected in cboStates. Would you know how to code
>> > > that?
>> > >
>> > > Thanks again for responding!
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > --
>> > > Sandy
>> > >
>> > >
>> > > "Steve Barnett" wrote:
>> > >
>> > >> Have you tried compiling the code. It often gives you clues as to
>> > >> what
>> > >> the
>> > >> problem is. The code you supplied below worked fine on a form with
>> > >> just
>> > >> combo box (once I added a function header).
>> > >>
>> > >> Steve
>> > >>
>> > >>
>> > >> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
>> > >> news:5468F890-DC39-4F21-A6E6-658A0115BA7A@microsoft.com...
>> > >> > Hello -
>> > >> >
>> > >> > I have the following code, so far. I am trying to merely fill
>> > >> > cboState
>> > >> > and
>> > >> > cboDeclarationNo. I am getting no Intellisense for EOF, AddItem
>> > >> > or
>> > >> > MoveNext.
>> > >> >
>> > >> > Set cnn = New ADODB.Connection
>> > >> >
>> > >> >    With cnn
>> > >> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
>> > >> > Security=SSPI;Persist Security Info=False;Initial
>> > >> > Catalog=CallTracker;Data
>> > >> > Source=DAO105655"
>> > >> >        .CursorLocation = adUserClient
>> > >> >        .Open
>> > >> >    End With
>> > >> >
>> > >> >    Set cmdStates = New ADODB.Command
>> > >> >    With cmdStates
>> > >> >        .ActiveConnection = cnn
>> > >> >        .CommandType = adCmdText
>> > >> >        .CommandText = "Select distinct State from tblDeclaration"
>> > >> >    End With
>> > >> >
>> > >> >    Set cmdDeclarationNo = New ADODB.Command
>> > >> >    With cmdDeclarationNo
>> > >> >        .ActiveConnection = cnn
>> > >> >        .CommandType = adCmdText
>> > >> >        .CommandText = "Select DeclarationNo from tblDeclaration
>> > >> > Where
>> > >> > State=" & cboDCStates
>> > >> >    End With
>> > >> >
>> > >> >    Set rsStates = New ADODB.Recordset
>> > >> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
>> > >> >
>> > >> >    While Not rsStates.EOF
>> > >> >        cboState.AddItem rsStates!State
>> > >> >        rsStates.MoveNext
>> > >> >    Wend
>> > >> >
>> > >> >   Set rsDeclarationNo = New ADODB.Recordset
>> > >> > rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic,
>> > >> > adLockOptimistic
>> > >> >
>> > >> > While Not rsDeclarationNo.EOF
>> > >> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>> > >> >    rsDeclarationNo.MoveNext
>> > >> >   Wend
>> > >> >
>> > >> > End Sub
>> > >> >
>> > >> > I have the following references set:
>> > >> > VB for Apps
>> > >> > VB runtime objects & Procedures
>> > >> > VB objects and procedures
>> > >> > OLE automation
>> > >> > MS ActiveX Data Objects 2.8 Library
>> > >> > MS ActiveX Data Objects Recordset 2.8 Library
>> > >> > MS Data Binding Collection
>> > >> >
>> > >> > ANY IDEAS AT ALL WILL BE APPRECIATED!!! I'm really stuck here!
>> > >> > --
>> > >> > Sandy
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

9 Mar 2006 3:40 PM

Sandy

Thanks, Steve! Your code SURE IS easier to read!

I really appreciate it!

--
Sandy

Show quoteHide quote

"Steve Barnett" wrote:

> First the discllaimer... I tossed this code together in a text editor, so
> the syntax may not be entirely correct... I've also no used ADO (firmly
> stuck with a DAO database). However, that said, what I would do is something
> along the lines of the routines below.
>
> If you run through the code, you should note that I have split out the
> creation of a connection to a routine of it's own (it's the same code every
> time) and that I have created a separate routine for opening a recordset,
> as the process is roughly the same every time.
>
> I beliee the code is easier to read and gives you two points where you need
> to add error trapping code; the open of the connection and the getting of
> the recordset. However, all of this is taste and fancy - this is just how I
> would have gone about it. You could further change this by moving the call
> to GetConnection in to the GetRecordset function, if you wish - I just like
> to keep them separate.
>
> Steve
>
> Option Explicit
>
> Private Sub cboDeclarationNo_Click()
>     Dim rsDeclaration As ADODB.Recordset
>     Dim cnn3 As Connection
>
>     set cnn3 = GetConnection()
>     Set rsDeclaration = GetRecordset(cnn3, "Select Declaration from
> tblDeclaration " _
>             & "Where DeclarationNo='" & cboDeclarationNo.Text & "'"
>     txtDeclaration.Text = rsDeclaration!Declaration
>
>     closerecordset rsDeclaration
>     CloseConnection cnn3
>
> End Sub
>
> Private Sub cboState_Click()
>     Dim rsDeclarationNo As ADODB.Recordset
>     Dim cnn2 As Connection
>
>     cboDeclarationNo.Clear
>
>     set cnn2 = GetConnection()
>     set rsDeclatarionNo = GetRecordset(cnn2, "Select Distinct DeclarationNo
> from tblDeclaration " _
>             & "Where State='" & cboState.Text & "'"
>
>    While Not rsDeclarationNo.EOF
>       cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
>        rsDeclarationNo.MoveNext
>     Wend
>
>     closeRecordset rsDeclarationNo
>     closeConnection cnn2
> End Sub
>
>
> Private Sub Form_Load()
>     Dim rsStates As Recordset
>     Dim rsCallType As Recordset
>     Dim cnn As Connection
>
>     Load frmBackground
>     frmBackground.Show
>
>     txtName.Text = UserName
>     txtDate.Text = Now()
>     txtLoanType.Text = ""
>
>     set cnn =GetConnection()
>
> 'FILL STATES COMBO
>   set rsStates = GetRecordset(cnn, "Select distinct State from
> tblDeclaration")
>
>     While Not rsStates.EOF
>         cboState.AddItem rsStates!State
>         rsStates.MoveNext
>     Wend
>
>     CloseRecordset rsStates
>
> 'FILL CALLTYPE COMBO
>   set rsCallType = GetRecordset(cnn, "Select CallType from tblCallType")
>
>     While Not rsCallType.EOF
>      cboCallType.AddItem rsCallType!CallType
>      rsCallType.MoveNext
>     Wend
>
>     CloseRecordset rsCallType
>     CloseConnection cnn
> End Sub
>
>
> Public Function GetConnection(byval connectionString as string) as
> ADODB.Connection
> Dim oConnection as Connection
>
> Set oConnection = new AdoDb.Connection
>
>   With oConnection
>      .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> Source=DAO105655"
>      '.CursorLocation = adUserClient
>      .Open
>   End With
>
>   set GetConnection = oConnection
>
> End Function
>
> Public sub CloseConnection(byref oConnection as Connection)
>      oConnection.close
>      set oConnection = nothing
> End sub
>
> Public function GetRecordset(byval oConnection as Connection, byval
> SQlstatement as string)
> Dim cmdCommand as AdoDb.Command
> Dim rsRecordset as AdoDb.Recordset
>
> set cmdCommand = new AdoDb.Command
> cmdCommand.ActiveConnection = cnn3
> cmdCommand.CommandType = adCmdText
> cmdCommand.CommandText = SQlstatement
>
>   set rsRecordset = new Adodb.recordset
>   rsRecordset.Open cmdCommand, , adOpenStatic, adLockOptomistic
>
> set GetRecordset = rsRecordset
> End Function
>
> Public sub CloseRecordset(byref rsRecordset as recordset)
> rsRecordset.close
> set rsRecordset = nothing
> end sub
>
>
>
> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> news:AB34A774-BDA1-40B2-8A78-EBFBFBAEA7D7@microsoft.com...
> > Thanks guys!! I finally got it to work. -- just one other question --
> > below
> > is my code. It seems like it's awfully lengthy and redundant. Any
> > suggestions?
> >
> > Option Explicit
> > Private Sub cboDeclarationNo_Click()
> >    Dim cmdDeclaration As ADODB.Command
> >    Dim rsDeclaration As ADODB.Recordset
> >    Dim cnn3 As Connection
> >    Set cnn3 = New ADODB.Connection
> >    With cnn3
> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> > Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> > Source=DAO105655"
> >        '.CursorLocation = adUserClient
> >        .Open
> >    End With
> >    Set cmdDeclaration = New ADODB.Command
> >    With cmdDeclaration
> >        .ActiveConnection = cnn3
> >        .CommandType = adCmdText
> >        .CommandText = "Select Declaration from tblDeclaration " _
> >            & "Where DeclarationNo='" & cboDeclarationNo.Text & "'"
> > 'Where State = " & cboState
> >    End With
> >
> >    Set rsDeclaration = New ADODB.Recordset
> >    rsDeclaration.Open cmdDeclaration, , adOpenStatic, adLockOptimistic
> >    txtDeclaration.Text = rsDeclaration!Declaration
> >
> >    rsDeclaration.Close
> >    cnn3.Close
> >    Set rsDeclaration = Nothing
> >    Set cnn3 = Nothing
> >
> > End Sub
> >
> > Private Sub cboState_Click()
> >    cboDeclarationNo.Clear
> >    Dim cmdDeclarationNo As ADODB.Command
> >    Dim rsDeclarationNo As ADODB.Recordset
> >    Dim cnn2 As Connection
> >    Set cnn2 = New ADODB.Connection
> >    With cnn2
> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> > Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> > Source=DAO105655"
> >        '.CursorLocation = adUserClient
> >        .Open
> >    End With
> >    Set cmdDeclarationNo = New ADODB.Command
> >    With cmdDeclarationNo
> >        .ActiveConnection = cnn2
> >        .CommandType = adCmdText
> >        .CommandText = "Select Distinct DeclarationNo from tblDeclaration "
> > _
> >            & "Where State='" & cboState.Text & "'"
> >    End With
> >
> >    Set rsDeclarationNo = New ADODB.Recordset
> >    rsDeclarationNo.Open cmdDeclarationNo, , adOpenStatic, adLockOptimistic
> >
> > While Not rsDeclarationNo.EOF
> >    cboDeclarationNo.AddItem rsDeclarationNo!DeclarationNo
> >    rsDeclarationNo.MoveNext
> >   Wend
> >
> >    rsDeclarationNo.Close
> >    cnn2.Close
> >    Set rsDeclarationNo = Nothing
> >    Set cnn2 = Nothing
> > End Sub
> >
> >
> > Private Sub Form_Load()
> >    Load frmBackground
> >    frmBackground.Show
> >    txtName.Text = UserName
> >    txtDate.Text = Now()
> >    txtLoanType.Text = ""
> >    Dim rsStates As Recordset
> >    Dim rsDeclarationNo As Recordset
> >    Dim rsCallType As Recordset
> >    Dim cmdStates As Command
> >    Dim cmdDeclarationNo As Command
> >    Dim cmdCallType As Command
> >    Dim cnn As Connection
> >
> >    Set cnn = New ADODB.Connection
> >
> >    With cnn
> >        .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> > Security=SSPI;Persist Security Info=False;Initial Catalog=CallTracker;Data
> > Source=DAO105655"
> >        '.CursorLocation = adUserClient
> >        .Open
> >    End With
> > 'FILL STATES COMBO
> >    Set cmdStates = New ADODB.Command
> >    With cmdStates
> >        .ActiveConnection = cnn
> >        .CommandType = adCmdText
> >        .CommandText = "Select distinct State from tblDeclaration"
> >    End With
> >
> >    Set rsStates = New ADODB.Recordset
> >    rsStates.Open cmdStates, , adOpenStatic, adLockOptimistic
> >
> >    While Not rsStates.EOF
> >        cboState.AddItem rsStates!State
> >        rsStates.MoveNext
> >    Wend
> > 'FILL CALLTYPE COMBO
> > Set cmdCallType = New ADODB.Command
> >   With cmdCallType
> >        .ActiveConnection = cnn
> >        '.CommandType = acCmdText
> >        .CommandText = "Select CallType from tblCallType"
> >    End With
> >
> >    Set rsCallType = New ADODB.Recordset
> >    rsCallType.Open cmdCallType, , adOpenStatic, adLockOptimistic
> >
> >    While Not rsCallType.EOF
> >    cboCallType.AddItem rsCallType!CallType
> >    rsCallType.MoveNext
> >    Wend
> >
> >    rsStates.Close
> >    rsCallType.Close
> >    cnn.Close
> >    Set rsStates = Nothing
> >    Set rsCallType = Nothing
> >    Set cnn = Nothing
> >
> > End Sub
> >
> > --
> > Sandy
> >
> >
> > "Sandy" wrote:
> >
> >> Thanks for responding again, Steve.
> >>
> >> Now, although I am getting no error messages, there is nothing being put
> >> into cboDeclarationNo at all. What am I doing wrong?
> >>
> >> In Access, there's an AfterUpdate event for comboboxes that takes care of
> >> situations like this.
> >>
> >> [Note I've changed cboDCStates to cboState]
> >>
> >> Private Sub cboState_Change()
> >>     Dim cnn2 As New ADODB.Connection
> >>     With cnn2

6 Mar 2006 10:26 PM

Michael C

The problem is you haven't overused the with operator enough

Michael