|
 |
access thread: RE: Unbound ADO ComboBox for 2K/97 Users using GetRows
Message #1 by "Bob Bedell" <bobbedell15@m...> on Wed, 23 Oct 2002 15:05:32 +0000
|
|
For the record:
Option Compare Database
Option Explicit
Dim m_rstContacts As ADODB.Recordset
Private Sub Form_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rstJobTitle As ADODB.Recordset
Dim strCn As String
Dim strJobTitle As String ' Combo recordset
Dim strRSContacts As String ' Form recordset
Dim strList As String
Set cnn = CurrentProject.Connection
Set m_rstContacts = New ADODB.Recordset
strRSContacts = "SELECT * FROM m01TblContacts"
m_rstContacts.Open strRSContacts, cnn, adOpenStatic, adLockOptimistic
strJobTitle = "SELECT d57JobTitle FROM d57TblJobTitle " _
& "ORDER BY d57JobTitleID"
Set rstJobTitle = New ADODB.Recordset
rstJobTitle.Open strJobTitle, cnn, adOpenForwardOnly, adLockReadOnly
' Populate combo box
strList = BuildString(rstJobTitle)
With cboJobTitle
.RowSource = strList
.RowSourceType = "Value List"
.SetFocus
.Text = cboJobTitle.ItemData(0)
End With
rstJobTitle.Close
BindAllFields
End Sub
Private Sub BindAllFields()
With Me
!txtContactsID = m_rstContacts("m01ContactsID")
!txtFirstName = m_rstContacts("m01FirstName")
!txtLastName = m_rstContacts("m01LastName")
!cboJobTitle = m_rstContacts("m01JobTitle")
End With
End Sub
Private Function BuildString(rst As ADODB.Recordset) As String
Dim strReturn As String
Dim varItems As Variant
Dim x As Integer
Dim y As Integer
rst.MoveFirst
varItems = rst.GetRows(10)
For x = LBound(varItems, 2) To UBound(varItems, 2)
For y = LBound(varItems, 1) To UBound(varItems, 1)
strReturn = strReturn & varItems(y, x) & ";"
Next y
Next x
BuildString = strReturn
End Function
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Populate (AddItem) in ComboBox
>Date: Wed, 23 Oct 2002 13:46:58 +0000
>
>Thanks Braxis,
>
>Never overlok the obvious, huh? I just realized that .AddItem and
>.RemoveItem are BRAND NEW enhancements for combo and list boxes in
>Access 2002! I didn't think I remembered these methods from 2K either,
>but I use Visual Basic so much I get confused. Anyway, these two
>NEW methods are similar to the VB methods for these control types.
>The other two new form and control methods with Access 2002 are
>Control.Move and Form.Move that set the left, top, width, and height
>properties of these controls.
>
>So George, if you want to populate that combo, use the GetRows method
>example I sent you. DON'T REINSTALL ACCESS!!!
>
>Best,
>
>Bob
>
>
>
>
>
>
>>From: braxis@b...
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] RE: Populate (AddItem) in ComboBox
>>Date: Wed, 23 Oct 2002 10:15:19 +0100 (BST)
>>
>>George
>>
>>I've been following this thread with much confusion, having never come
>>across the 'AddItem' method for comboboxes before.
>>
>>So, I tried using IntelliSense to get the AddItem method to come up on one
>>of my combo boxes - no luck. I tried looking it up in the Access help
>>files - all I found after much struggling was code on how to add entries
>>to a menu bar!
>>
>>Finally, I wrote 'AddItem' in the VBA editor and pressed <F1> and up pops
>>a window asking me which flavour of AddItem I'm interested in. Excel has
>>an AddItem method, so does the MS Flex Grid and finally so does the MS
>>Forms COMBOBOX!!!
>>
>>So, as far as I can see, the native Access combo box does not have an
>>AddItem method, at least not in Access2K.
>>
>>It should be fairly simple to code such a function though...
>>
>>Brian
>>
>> > from: George Oro <george@c...>
>> > date: Wed, 23 Oct 2002 05:46:40
>> > to: access@p...
>> > subject: Re: [access] RE: Populate (AddItem) in ComboBox
>> >
>> > Hi Bob,
>> > Thanks for your tips but still it didn't work, same error. By the way,
>>I don't have any single table lying or linked in this app.
>> > This is my Fron-end, and I'm accessing all my tables on my Back-end.
>>Here below the current code on my Form_Open:
>> >
>> > =====================================
>> > Private Sub Form_Open(Cancel As Integer)
>> > 'Database Connection
>> > Dim cn As ADODB.Connection
>> > Dim strCn As String
>> >
>> > Set cn = New ADODB.Connection
>> > strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>>Source=C:\Database\MyVBapp\Communicator\ComData.mdb;Persist Security
>>Info=False"
>> > cn.Open strCn
>> >
>> > 'Recordset Connection
>> > Dim strRSContacts As String
>> >
>> > Set rsContacts = New ADODB.Recordset
>> > strRSContacts = "SELECT * FROM m01TblContacts"
>> > rsContacts.Open strRSContacts, cn, adOpenStatic, adLockOptimistic
>> >
>> > 'Populate Job Title ComboBox
>> > Dim rsJobTitle As ADODB.Recordset
>> > Dim strJobTitle As String
>> > strJobTitle = "SELECT d57JobTitleID, d57JobTitle FROM
>>d57TblJobTitle " _
>> > & "ORDER BY d57JobTitleID"
>> >
>> > Set rsJobTitle = New ADODB.Recordset
>> > rsJobTitle.Open strJobTitle, cn, adOpenForwardOnly, adLockReadOnly
>> >
>> > txtJobTitle.RowSourceType = "Value List"
>> >
>> > With rsJobTitle
>> > .MoveFirst
>> > Do Until .EOF
>> > txtJobTitle.AddItem !d57JobTitleID & " - " & !d57JobTitle
>> > .MoveNext
>> > Loop
>> > End With
>> >
>> > With txtJobTitle
>> > .SetFocus
>> > .Text = txtJobTitle.ItemData(0)
>> > End With
>> >
>> > end ================
>> >
>> > -----Original Message-----
>> > From: Bob Bedell [mailto:bobbedell15@m...]
>> > Sent: Tuesday, October 22, 2002 9:02 PM
>> > To: Access
>> > Subject: [access] RE: Populate (AddItem) in ComboBox
>> >
>> >
>> > George,
>> >
>> > Try the following. I added two things to John's code. First, the line
>> >
>> > cboJobTitle.RowSourceType = "Value List"
>> >
>> > The AddItem method will only work if the control's rowsource type is a
>>Value
>> > List.
>> > Second, the With block:
>> >
>> > With cboJobTitle
>> > .SetFocus
>> > .Text = cboJobTitle.ItemData(0)
>> > End With
>> >
>> > This will display the first value of the Value List in the combo boxes
>>text
>> > area. Without
>> > this code, the combo will be populated, but nothing will be displayed
>>by
>> > default.
>> >
>> > '~~~~~~~~~~Start~~~~~~~~~~
>> >
>> > Private Sub Form_Open(Cancel As Integer)
>> > Dim cnn As ADODB.Connection
>> > Dim rst As ADODB.Recordset
>> > Dim strList As String
>> > Dim strSQL As String
>> >
>> > strSQL = "SELECT d57JobTitleID, d57JobTitle FROM d57TblJobTitle " _
>> > & "ORDER BY d57JobTitleID"
>> >
>> > Set cnn = CurrentProject.Connection
>> >
>> > Set rst = New ADODB.Recordset
>> > rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
>> >
>> > cboJobTitle.RowSourceType = "Value List"
>> >
>> > With rst
>> > .MoveFirst
>> > Do Until .EOF
>> > cboJobTitle.AddItem !d57JobTitleID & " - " & !d57JobTitle
>> > .MoveNext
>> > Loop
>> > End With
>> >
>> > With cboJobTitle
>> > .SetFocus
>> > .Text = cboJobTitle.ItemData(0)
>> > End With
>> >
>> > rst.Close
>> >
>> > Set rst = Nothing
>> > Set cnn = Nothing
>> > End Sub
>> >
>> > '~~~~~~~~~~Stop~~~~~~~~~~
>> >
>> > If you feel like playing around with arrays a bit, the following code
>>uses
>> > the GetRows method of the recordset object which returns a
>>two-dimensional
>> > array.
>> > The array's values are assigned to the combo boxes rowsource property.
>> >
>> > '~~~~~~~~~~Start~~~~~~~~~~
>> >
>> > Private Sub Form_Open(Cancel As Integer)
>> > Dim cnn As ADODB.Connection
>> > Dim rst As ADODB.Recordset
>> > Dim strList As String
>> > Dim strSQL As String
>> >
>> > strSQL = "SELECT d57JobTitle FROM d57TblJobTitle " _
>> > & "ORDER BY d57JobTitle"
>> >
>> > Set cnn = CurrentProject.Connection
>> >
>> > Set rst = New ADODB.Recordset
>> > rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
>> >
>> > strList = BuildString(rst)
>> > With cboJobTitle
>> > .RowSource = strList
>> > .RowSourceType = "Value List"
>> > .SetFocus
>> > .Text = cboJobTitle.ItemData(0)
>> > End With
>> > rst.Close
>> >
>> > Set rst = Nothing
>> > Set cnn = Nothing
>> > End Sub
>> >
>> > Private Function BuildString(rst As ADODB.Recordset) As String
>> > Dim strReturn As String
>> > Dim varItems As Variant
>> > Dim x As Integer
>> > Dim y As Integer
>> >
>> > rst.MoveFirst
>> >
>> > varItems = rst.GetRows(10)
>> > For x = LBound(varItems, 2) To UBound(varItems, 2)
>> > For y = LBound(varItems, 1) To UBound(varItems, 1)
>> > strReturn = strReturn & varItems(y, x) & ";"
>> > Next y
>> > Next x
>> > BuildString = strReturn
>> > End Function
>> >
>> > '~~~~~~~~~~Stop~~~~~~~~~~
>> >
>> > Best,
>> >
>> > Bob
>> >
>> >
>> >
>> >
>> >
>> > ----- Original Message -----
>> > From: "George Oro" <george@c...>
>> > To: "Access" <access@p...>
>> > Sent: Tuesday, October 22, 2002 10:52 AM
>> > Subject: [access] RE: Populate (AddItem) in ComboBox
>> >
>> >
>> > > Thanks John, but I got an error "Method or data member not found" and
>>the
>> > AddItem is highlighted.
>> > > This is what I did, I put the below code on my Form Load:
>> > >
>> > > Assuming cn is my ADODB Connection
>> > >
>> > > 'Populate Job Title ComboBox
>> > > Dim rsJobTitle as ADODB.Recordset
>> > > Dim strJobTitle As String
>> > > strJobTitle = "SELECT * FROM d57TblJobTitle ORDER BY d57JobTitle"
>> > >
>> > > Set rsJobTitle = New ADODB.Recordset
>> > > rsJobTitle.CursorType = adOpenStatic
>> > > rsJobTitle.CursorLocation = adUseClient
>> > > rsJobTitle.LockType = adLockOptimistic
>> > > rsJobTitle.Open strJobTitle, cn
>> > >
>> > > With rsJobTitle
>> > > .MoveFirst
>> > > Do Until .EOF
>> > > Me.txtJobTitle.AddItem !d57JobTitle
>> > > .MoveNext
>> > > Loop
>> > > End With
>> > > ============================================
>> > >
>> > > Did I miss something?
>> > > TIA,
>> > > George
>> > >
>> > >
>> > >
>> > > -----Original Message-----
>> > > From: John Pace [mailto:jpace@h...]
>> > > Sent: Tuesday, October 22, 2002 6:02 PM
>> > > To: Access
>> > > Subject: [access] RE: Populate (AddItem) in ComboBox
>> > >
>> > >
>> > > George,
>> > >
>> > > Do the following (I am assuming you are using a recordset call
>> > > rstJobTitle).
>> > > If you want to add all the records in the recordset to the combo box
>>do
>> > > the following:
>> > >
>> > > With rstJobTitle
>> > >
>> > > .MoveFirst
>> > >
>> > > Do Until .EOF
>> > >
>> > > ComboBox.AddItem !d57JobTitleID & " - " & !d57JobTitle
>> > >
>> > > .MoveNext
>> > >
>> > > Loop
>> > >
>> > > End With
>> > >
>> > > Hope that helps,
>> > >
>> > > John
>> > >
>> > > -----Original Message-----
>> > > From: George Oro [mailto:george@c...]
>> > > Sent: Tuesday, October 22, 2002 7:50 AM
>> > > To: Access
>> > > Subject: [access] Populate (AddItem) in ComboBox
>> > >
>> > > Thanks Bob for your idea and tips on my below post "Binding Fields".
>> > >
>> > > By the way, how can I populate or add item on my combo box using
>>code?
>> > > On my below post, I just add one ComboBox field "Job Title" and I
>>have a
>> > > table on my Access2K Back-end called d57TblJobTitle with
>> > > the following fields d57JobTitleID & d57JobTitle.
>> > >
>> > > Any help is highly appreciated.
>> > > TIA
>> > > George ;D
>> > >
>> > > -----Original Message-----
>> > > From: Bob Bedell [mailto:bobbedell15@m...]
>> > > Sent: Monday, October 21, 2002 9:15 PM
>> > > To: Access
>> > > Subject: [access] Re: Binding Fields
>> > >
>> > >
>> > > Hi George,
>> > >
>> > > That's how its done. Why are you using WithEvents in you declaration?
>> > > You don't need it in this module. Only recommendation would be: in
>>order
>> > > to prevent your cursor from falling off the beginning or end of your
>> > > recordset and producing weird error messages, have your navigation
>> > > buttons check for the recordsets BOF and EOF properties. The
>>following
>> > > code also gives the user some halpful visual cues by enabling and
>> > > disabling the appropriate navigation buttons.
>> > >
>> > > Private Sub cmdPrev_Click()
>> > >
>> > > rsContacts.MovePrevious
>> > > txtContactsID.SetFocus
>> > > rsContacts.MovePrevious
>> > > If rsContacts.BOF Then
>> > > cmdPrev.Enabled = False
>> > > Else
>> > > cmdPrev.Enabled = True
>> > > End If
>> > > cmdNext.Enabled = True
>> > > rsContacts.MoveNext
>> > >
>> > > BindAllFields
>> > >
>> > > End Sub
>> > >
>> > > Private Sub cmdNext_Click()
>> > > rsContacts.MoveNext
>> > > txtContactsID.SetFocus
>> > > rsContacts.MoveNext
>> > > If rsContacts.EOF Then
>> > > cmdNext.Enabled = False
>> > > Else
>> > > cmdNext.Enabled = True
>> > > End If
>> > > rsContacts.MovePrevious
>> > > cmdPrev.Enabled = True
>> > >
>> > > BindAllFields
>> > > End Sub
>> > >
>> > > Best,
>> > >
>> > > Bob
>> > >
>> > > >From: "George Oro" <george@c...>
>> > > >Reply-To: "Access" <access@p...>
>> > > >To: "Access" <access@p...>
>> > > >Subject: [access] Binding Fields
>> > > >Date: Mon, 21 Oct 2002 18:16:59 +0400
>> > > >
>> > > >Hi Guys,
>> > > >
>> > > >I just started connecting my database using ADODB coding, at
>>present, I
>> > > >just have 3 textbox fields and two cmdButton for test with
>> > > >the following code:
>> > > >QUESTION:
>> > > >1) Is my connection OK? otherwise what is the best?
>> > > >2) Is there any easiest way to bind my fields accordingly?
>> > > >
>> > > >Any help and idea is highly appreciated.
>> > > >Cheers,
>> > > >George
>> > > >
>> > > >MY CODE
>> > > >----------
>> > > >Option Compare Database
>> > > >Option Explicit
>> > > >Dim WithEvents rsContacts As ADODB.Recordset
>> > > >----------
>> > > >
>> > > >Private Sub Form_Open(Cancel As Integer)
>> > > > 'Database Connection
>> > > > Dim cn As ADODB.Connection
>> > > > Dim strCn As String
>> > > >
>> > > > Set cn = New ADODB.Connection
>> > > > strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> > > >Source=C:\Database\MyVBapp\Communicator ComData.mdb;Persist Security
>> > > >Info=False"
>> > > > cn.Open strCn
>> > > >
>> > > > 'Recordset Connection
>> > > > 'Dim rsContacts As ADODB.Recordset
>> > > > Dim strRSContacts As String
>> > > >
>> > > > Set rsContacts = New ADODB.Recordset
>> > > > strRSContacts = "SELECT * FROM m01TblContacts"
>> > > > rsContacts.Open strRSContacts, cn, adOpenStatic,
>>adLockOptimistic
>> > > >
>> > > > BindAllFields
>> > > >
>> > > >End Sub
>> > > >----------------
>> > > >Private Sub BindAllFields()
>> > > > With Me
>> > > > .txtContactsID = rsContacts("m01ContactsID")
>> > > > .txtFirstName = rsContacts("m01FirstName")
>> > > > .txtLastName = rsContacts("m01LastName")
>> > > > End With
>> > > >End Sub
>> > > >---------------
>> > > >Private Sub cmdNext_Click()
>> > > >On Error GoTo Err_cmdNext_Click
>> > > >
>> > > >
>> > > > rsContacts.MoveNext
>> > > > BindAllFields
>> > > >
>> > > >Exit_cmdNext_Click:
>> > > > Exit Sub
>> > > >
>> > > >Err_cmdNext_Click:
>> > > > MsgBox Err.Description
>> > > > Resume Exit_cmdNext_Click
>> > > >
>> > > >End Sub
>> > > >----------------
>> > > >Private Sub cmdPrev_Click()
>> > > >On Error GoTo Err_cmdPrev_Click
>> > > >
>> > > >
>> > > > rsContacts.MovePrevious
>> > > > BindAllFields
>> > > >
>> > > >Exit_cmdPrev_Click:
>> > > > Exit Sub
>> > > >
>> > > >Err_cmdPrev_Click:
>> > > > MsgBox Err.Description
>> > > > Resume Exit_cmdPrev_Click
>> > > >
>> > > >End Sub
>> > > >
>> > > >
>> > >
>> > >
>> > > _________________________________________________________________
>> > > Choose an Internet access plan right for you -- try MSN!
>> > > http://resourcecenter.msn.com/access/plans/default.asp
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>> >
>>
>>
>
>
>_________________________________________________________________
>Unlimited Internet access -- and 2 months free! Try MSN.
>http://resourcecenter.msn.com/access/plans/2monthsfree.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Surf the Web without missing calls! Get MSN Broadband.
http://resourcecenter.msn.com/access/plans/freeactivation.asp
Message #2 by "Bob Bedell" <bobbedell15@m...> on Wed, 23 Oct 2002 16:22:30 +0000
|
|
My apologies to anyone who may have found trying to use the .AddItem
method of the combo box in Access 97/2K more than a little frustrating.
I sincrely hope no one lost too much sleep over that, but in the end
something valuable was learned.
Since .AddItem doesn't work in 2K/97, use the GetRows method as
indicated in my previous post. A bare bones version follows:
'~~~~~~~~~~Start~~~~~~~~~~
'The following code uses the GetRows method of the recordset
'object which returns a two-dimensional array. The array's values
'are assigned to the combo boxes rowsource property.
Private Sub Form_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strList As String
Dim strSQL As String
strSQL = "SELECT DataDesc FROM tblData " _
& "ORDER BY DataDesc"
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
strList = BuildString(rst)
With cboJobTitle
.RowSource = strList
.RowSourceType = "Value List"
.SetFocus
.Text = cboJobTitle.ItemData(0)
End With
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Private Function BuildString(rst As ADODB.Recordset) As String
Dim strReturn As String
Dim varItems As Variant
Dim x As Integer
Dim y As Integer
rst.MoveFirst
varItems = rst.GetRows(10)
For x = LBound(varItems, 2) To UBound(varItems, 2)
For y = LBound(varItems, 1) To UBound(varItems, 1)
strReturn = strReturn & varItems(y, x) & ";"
Next y
Next x
BuildString = strReturn
End Function
'~~~~~~~~~~Stop~~~~~~~~~~
Best,
Bob
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Unbound ADO ComboBox for 2K/97 Users using GetRows
>Date: Wed, 23 Oct 2002 15:05:32 +0000
>
>For the record:
>
>Option Compare Database
>Option Explicit
>Dim m_rstContacts As ADODB.Recordset
>
>Private Sub Form_Open(Cancel As Integer)
> Dim cnn As ADODB.Connection
> Dim rstJobTitle As ADODB.Recordset
> Dim strCn As String
> Dim strJobTitle As String ' Combo recordset
> Dim strRSContacts As String ' Form recordset
> Dim strList As String
>
> Set cnn = CurrentProject.Connection
> Set m_rstContacts = New ADODB.Recordset
> strRSContacts = "SELECT * FROM m01TblContacts"
> m_rstContacts.Open strRSContacts, cnn, adOpenStatic, adLockOptimistic
>
> strJobTitle = "SELECT d57JobTitle FROM d57TblJobTitle " _
> & "ORDER BY d57JobTitleID"
>
> Set rstJobTitle = New ADODB.Recordset
> rstJobTitle.Open strJobTitle, cnn, adOpenForwardOnly, adLockReadOnly
>
> ' Populate combo box
> strList = BuildString(rstJobTitle)
> With cboJobTitle
> .RowSource = strList
> .RowSourceType = "Value List"
> .SetFocus
> .Text = cboJobTitle.ItemData(0)
> End With
> rstJobTitle.Close
>
> BindAllFields
>
>End Sub
>
>Private Sub BindAllFields()
> With Me
> !txtContactsID = m_rstContacts("m01ContactsID")
> !txtFirstName = m_rstContacts("m01FirstName")
> !txtLastName = m_rstContacts("m01LastName")
> !cboJobTitle = m_rstContacts("m01JobTitle")
> End With
>End Sub
>
>Private Function BuildString(rst As ADODB.Recordset) As String
> Dim strReturn As String
> Dim varItems As Variant
> Dim x As Integer
> Dim y As Integer
>
> rst.MoveFirst
>
> varItems = rst.GetRows(10)
> For x = LBound(varItems, 2) To UBound(varItems, 2)
> For y = LBound(varItems, 1) To UBound(varItems, 1)
> strReturn = strReturn & varItems(y, x) & ";"
> Next y
> Next x
> BuildString = strReturn
>End Function
>
>
>
>
>
>
>
>>From: "Bob Bedell" <bobbedell15@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] RE: Populate (AddItem) in ComboBox
>>Date: Wed, 23 Oct 2002 13:46:58 +0000
>>
>>Thanks Braxis,
>>
>>Never overlok the obvious, huh? I just realized that .AddItem and
>>.RemoveItem are BRAND NEW enhancements for combo and list boxes in
>>Access 2002! I didn't think I remembered these methods from 2K either,
>>but I use Visual Basic so much I get confused. Anyway, these two
>>NEW methods are similar to the VB methods for these control types.
>>The other two new form and control methods with Access 2002 are
>>Control.Move and Form.Move that set the left, top, width, and height
>>properties of these controls.
>>
>>So George, if you want to populate that combo, use the GetRows method
>>example I sent you. DON'T REINSTALL ACCESS!!!
>>
>>Best,
>>
>>Bob
>>
>>
>>
>>
>>
>>
>>>From: braxis@b...
>>>Reply-To: "Access" <access@p...>
>>>To: "Access" <access@p...>
>>>Subject: [access] RE: Populate (AddItem) in ComboBox
>>>Date: Wed, 23 Oct 2002 10:15:19 +0100 (BST)
>>>
>>>George
>>>
>>>I've been following this thread with much confusion, having never come
>>>across the 'AddItem' method for comboboxes before.
>>>
>>>So, I tried using IntelliSense to get the AddItem method to come up on
>>>one of my combo boxes - no luck. I tried looking it up in the Access help
>>>files - all I found after much struggling was code on how to add entries
>>>to a menu bar!
>>>
>>>Finally, I wrote 'AddItem' in the VBA editor and pressed <F1> and up pops
>>>a window asking me which flavour of AddItem I'm interested in. Excel has
>>>an AddItem method, so does the MS Flex Grid and finally so does the MS
>>>Forms COMBOBOX!!!
>>>
>>>So, as far as I can see, the native Access combo box does not have an
>>>AddItem method, at least not in Access2K.
>>>
>>>It should be fairly simple to code such a function though...
>>>
>>>Brian
>>>
>>> > from: George Oro <george@c...>
>>> > date: Wed, 23 Oct 2002 05:46:40
>>> > to: access@p...
>>> > subject: Re: [access] RE: Populate (AddItem) in ComboBox
>>> >
>>> > Hi Bob,
>>> > Thanks for your tips but still it didn't work, same error. By the way,
>>>I don't have any single table lying or linked in this app.
>>> > This is my Fron-end, and I'm accessing all my tables on my Back-end.
>>>Here below the current code on my Form_Open:
>>> >
>>> > =====================================
>>> > Private Sub Form_Open(Cancel As Integer)
>>> > 'Database Connection
>>> > Dim cn As ADODB.Connection
>>> > Dim strCn As String
>>> >
>>> > Set cn = New ADODB.Connection
>>> > strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>>>Source=C:\Database\MyVBapp\Communicator\ComData.mdb;Persist Security
>>>Info=False"
>>> > cn.Open strCn
>>> >
>>> > 'Recordset Connection
>>> > Dim strRSContacts As String
>>> >
>>> > Set rsContacts = New ADODB.Recordset
>>> > strRSContacts = "SELECT * FROM m01TblContacts"
>>> > rsContacts.Open strRSContacts, cn, adOpenStatic, adLockOptimistic
>>> >
>>> > 'Populate Job Title ComboBox
>>> > Dim rsJobTitle As ADODB.Recordset
>>> > Dim strJobTitle As String
>>> > strJobTitle = "SELECT d57JobTitleID, d57JobTitle FROM
>>>d57TblJobTitle " _
>>> > & "ORDER BY d57JobTitleID"
>>> >
>>> > Set rsJobTitle = New ADODB.Recordset
>>> > rsJobTitle.Open strJobTitle, cn, adOpenForwardOnly, adLockReadOnly
>>> >
>>> > txtJobTitle.RowSourceType = "Value List"
>>> >
>>> > With rsJobTitle
>>> > .MoveFirst
>>> > Do Until .EOF
>>> > txtJobTitle.AddItem !d57JobTitleID & " - " & !d57JobTitle
>>> > .MoveNext
>>> > Loop
>>> > End With
>>> >
>>> > With txtJobTitle
>>> > .SetFocus
>>> > .Text = txtJobTitle.ItemData(0)
>>> > End With
>>> >
>>> > end ================
>>> >
>>> > -----Original Message-----
>>> > From: Bob Bedell [mailto:bobbedell15@m...]
>>> > Sent: Tuesday, October 22, 2002 9:02 PM
>>> > To: Access
>>> > Subject: [access] RE: Populate (AddItem) in ComboBox
>>> >
>>> >
>>> > George,
>>> >
>>> > Try the following. I added two things to John's code. First, the line
>>> >
>>> > cboJobTitle.RowSourceType = "Value List"
>>> >
>>> > The AddItem method will only work if the control's rowsource type is a
>>>Value
>>> > List.
>>> > Second, the With block:
>>> >
>>> > With cboJobTitle
>>> > .SetFocus
>>> > .Text = cboJobTitle.ItemData(0)
>>> > End With
>>> >
>>> > This will display the first value of the Value List in the combo boxes
>>>text
>>> > area. Without
>>> > this code, the combo will be populated, but nothing will be displayed
>>>by
>>> > default.
>>> >
>>> > '~~~~~~~~~~Start~~~~~~~~~~
>>> >
>>> > Private Sub Form_Open(Cancel As Integer)
>>> > Dim cnn As ADODB.Connection
>>> > Dim rst As ADODB.Recordset
>>> > Dim strList As String
>>> > Dim strSQL As String
>>> >
>>> > strSQL = "SELECT d57JobTitleID, d57JobTitle FROM d57TblJobTitle "
>>>_
>>> > & "ORDER BY d57JobTitleID"
>>> >
>>> > Set cnn = CurrentProject.Connection
>>> >
>>> > Set rst = New ADODB.Recordset
>>> > rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
>>> >
>>> > cboJobTitle.RowSourceType = "Value List"
>>> >
>>> > With rst
>>> > .MoveFirst
>>> > Do Until .EOF
>>> > cboJobTitle.AddItem !d57JobTitleID & " - " & !d57JobTitle
>>> > .MoveNext
>>> > Loop
>>> > End With
>>> >
>>> > With cboJobTitle
>>> > .SetFocus
>>> > .Text = cboJobTitle.ItemData(0)
>>> > End With
>>> >
>>> > rst.Close
>>> >
>>> > Set rst = Nothing
>>> > Set cnn = Nothing
>>> > End Sub
>>> >
>>> > '~~~~~~~~~~Stop~~~~~~~~~~
>>> >
>>> > If you feel like playing around with arrays a bit, the following code
>>>uses
>>> > the GetRows method of the recordset object which returns a
>>>two-dimensional
>>> > array.
>>> > The array's values are assigned to the combo boxes rowsource property.
>>> >
>>> > '~~~~~~~~~~Start~~~~~~~~~~
>>> >
>>> > Private Sub Form_Open(Cancel As Integer)
>>> > Dim cnn As ADODB.Connection
>>> > Dim rst As ADODB.Recordset
>>> > Dim strList As String
>>> > Dim strSQL As String
>>> >
>>> > strSQL = "SELECT d57JobTitle FROM d57TblJobTitle " _
>>> > & "ORDER BY d57JobTitle"
>>> >
>>> > Set cnn = CurrentProject.Connection
>>> >
>>> > Set rst = New ADODB.Recordset
>>> > rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
>>> >
>>> > strList = BuildString(rst)
>>> > With cboJobTitle
>>> > .RowSource = strList
>>> > .RowSourceType = "Value List"
>>> > .SetFocus
>>> > .Text = cboJobTitle.ItemData(0)
>>> > End With
>>> > rst.Close
>>> >
>>> > Set rst = Nothing
>>> > Set cnn = Nothing
>>> > End Sub
>>> >
>>> > Private Function BuildString(rst As ADODB.Recordset) As String
>>> > Dim strReturn As String
>>> > Dim varItems As Variant
>>> > Dim x As Integer
>>> > Dim y As Integer
>>> >
>>> > rst.MoveFirst
>>> >
>>> > varItems = rst.GetRows(10)
>>> > For x = LBound(varItems, 2) To UBound(varItems, 2)
>>> > For y = LBound(varItems, 1) To UBound(varItems, 1)
>>> > strReturn = strReturn & varItems(y, x) & ";"
>>> > Next y
>>> > Next x
>>> > BuildString = strReturn
>>> > End Function
>>> >
>>> > '~~~~~~~~~~Stop~~~~~~~~~~
>>> >
>>> > Best,
>>> >
>>> > Bob
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > ----- Original Message -----
>>> > From: "George Oro" <george@c...>
>>> > To: "Access" <access@p...>
>>> > Sent: Tuesday, October 22, 2002 10:52 AM
>>> > Subject: [access] RE: Populate (AddItem) in ComboBox
>>> >
>>> >
>>> > > Thanks John, but I got an error "Method or data member not found"
>>>and the
>>> > AddItem is highlighted.
>>> > > This is what I did, I put the below code on my Form Load:
>>> > >
>>> > > Assuming cn is my ADODB Connection
>>> > >
>>> > > 'Populate Job Title ComboBox
>>> > > Dim rsJobTitle as ADODB.Recordset
>>> > > Dim strJobTitle As String
>>> > > strJobTitle = "SELECT * FROM d57TblJobTitle ORDER BY
>>>d57JobTitle"
>>> > >
>>> > > Set rsJobTitle = New ADODB.Recordset
>>> > > rsJobTitle.CursorType = adOpenStatic
>>> > > rsJobTitle.CursorLocation = adUseClient
>>> > > rsJobTitle.LockType = adLockOptimistic
>>> > > rsJobTitle.Open strJobTitle, cn
>>> > >
>>> > > With rsJobTitle
>>> > > .MoveFirst
>>> > > Do Until .EOF
>>> > > Me.txtJobTitle.AddItem !d57JobTitle
>>> > > .MoveNext
>>> > > Loop
>>> > > End With
>>> > > ============================================
>>> > >
>>> > > Did I miss something?
>>> > > TIA,
>>> > > George
>>> > >
>>> > >
>>> > >
>>> > > -----Original Message-----
>>> > > From: John Pace [mailto:jpace@h...]
>>> > > Sent: Tuesday, October 22, 2002 6:02 PM
>>> > > To: Access
>>> > > Subject: [access] RE: Populate (AddItem) in ComboBox
>>> > >
>>> > >
>>> > > George,
>>> > >
>>> > > Do the following (I am assuming you are using a recordset call
>>> > > rstJobTitle).
>>> > > If you want to add all the records in the recordset to the combo box
>>>do
>>> > > the following:
>>> > >
>>> > > With rstJobTitle
>>> > >
>>> > > .MoveFirst
>>> > >
>>> > > Do Until .EOF
>>> > >
>>> > > ComboBox.AddItem !d57JobTitleID & " - " & !d57JobTitle
>>> > >
>>> > > .MoveNext
>>> > >
>>> > > Loop
>>> > >
>>> > > End With
>>> > >
>>> > > Hope that helps,
>>> > >
>>> > > John
>>> > >
>>> > > -----Original Message-----
>>> > > From: George Oro [mailto:george@c...]
>>> > > Sent: Tuesday, October 22, 2002 7:50 AM
>>> > > To: Access
>>> > > Subject: [access] Populate (AddItem) in ComboBox
>>> > >
>>> > > Thanks Bob for your idea and tips on my below post "Binding Fields".
>>> > >
>>> > > By the way, how can I populate or add item on my combo box using
>>>code?
>>> > > On my below post, I just add one ComboBox field "Job Title" and I
>>>have a
>>> > > table on my Access2K Back-end called d57TblJobTitle with
>>> > > the following fields d57JobTitleID & d57JobTitle.
>>> > >
>>> > > Any help is highly appreciated.
>>> > > TIA
>>> > > George ;D
>>> > >
>>> > > -----Original Message-----
>>> > > From: Bob Bedell [mailto:bobbedell15@m...]
>>> > > Sent: Monday, October 21, 2002 9:15 PM
>>> > > To: Access
>>> > > Subject: [access] Re: Binding Fields
>>> > >
>>> > >
>>> > > Hi George,
>>> > >
>>> > > That's how its done. Why are you using WithEvents in you
>>>declaration?
>>> > > You don't need it in this module. Only recommendation would be: in
>>>order
>>> > > to prevent your cursor from falling off the beginning or end of your
>>> > > recordset and producing weird error messages, have your navigation
>>> > > buttons check for the recordsets BOF and EOF properties. The
>>>following
>>> > > code also gives the user some halpful visual cues by enabling and
>>> > > disabling the appropriate navigation buttons.
>>> > >
>>> > > Private Sub cmdPrev_Click()
>>> > >
>>> > > rsContacts.MovePrevious
>>> > > txtContactsID.SetFocus
>>> > > rsContacts.MovePrevious
>>> > > If rsContacts.BOF Then
>>> > > cmdPrev.Enabled = False
>>> > > Else
>>> > > cmdPrev.Enabled = True
>>> > > End If
>>> > > cmdNext.Enabled = True
>>> > > rsContacts.MoveNext
>>> > >
>>> > > BindAllFields
>>> > >
>>> > > End Sub
>>> > >
>>> > > Private Sub cmdNext_Click()
>>> > > rsContacts.MoveNext
>>> > > txtContactsID.SetFocus
>>> > > rsContacts.MoveNext
>>> > > If rsContacts.EOF Then
>>> > > cmdNext.Enabled = False
>>> > > Else
>>> > > cmdNext.Enabled = True
>>> > > End If
>>> > > rsContacts.MovePrevious
>>> > > cmdPrev.Enabled = True
>>> > >
>>> > > BindAllFields
>>> > > End Sub
>>> > >
>>> > > Best,
>>> > >
>>> > > Bob
>>> > >
>>> > > >From: "George Oro" <george@c...>
>>> > > >Reply-To: "Access" <access@p...>
>>> > > >To: "Access" <access@p...>
>>> > > >Subject: [access] Binding Fields
>>> > > >Date: Mon, 21 Oct 2002 18:16:59 +0400
>>> > > >
>>> > > >Hi Guys,
>>> > > >
>>> > > >I just started connecting my database using ADODB coding, at
>>>present, I
>>> > > >just have 3 textbox fields and two cmdButton for test with
>>> > > >the following code:
>>> > > >QUESTION:
>>> > > >1) Is my connection OK? otherwise what is the best?
>>> > > >2) Is there any easiest way to bind my fields accordingly?
>>> > > >
>>> > > >Any help and idea is highly appreciated.
>>> > > >Cheers,
>>> > > >George
>>> > > >
>>> > > >MY CODE
>>> > > >----------
>>> > > >Option Compare Database
>>> > > >Option Explicit
>>> > > >Dim WithEvents rsContacts As ADODB.Recordset
>>> > > >----------
>>> > > >
>>> > > >Private Sub Form_Open(Cancel As Integer)
>>> > > > 'Database Connection
>>> > > > Dim cn As ADODB.Connection
>>> > > > Dim strCn As String
>>> > > >
>>> > > > Set cn = New ADODB.Connection
>>> > > > strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>>> > > >Source=C:\Database\MyVBapp\Communicator ComData.mdb;Persist
>>>Security
>>> > > >Info=False"
>>> > > > cn.Open strCn
>>> > > >
>>> > > > 'Recordset Connection
>>> > > > 'Dim rsContacts As ADODB.Recordset
>>> > > > Dim strRSContacts As String
>>> > > >
>>> > > > Set rsContacts = New ADODB.Recordset
>>> > > > strRSContacts = "SELECT * FROM m01TblContacts"
>>> > > > rsContacts.Open strRSContacts, cn, adOpenStatic,
>>>adLockOptimistic
>>> > > >
>>> > > > BindAllFields
>>> > > >
>>> > > >End Sub
>>> > > >----------------
>>> > > >Private Sub BindAllFields()
>>> > > > With Me
>>> > > > .txtContactsID = rsContacts("m01ContactsID")
>>> > > > .txtFirstName = rsContacts("m01FirstName")
>>> > > > .txtLastName = rsContacts("m01LastName")
>>> > > > End With
>>> > > >End Sub
>>> > > >---------------
>>> > > >Private Sub cmdNext_Click()
>>> > > >On Error GoTo Err_cmdNext_Click
>>> > > >
>>> > > >
>>> > > > rsContacts.MoveNext
>>> > > > BindAllFields
>>> > > >
>>> > > >Exit_cmdNext_Click:
>>> > > > Exit Sub
>>> > > >
>>> > > >Err_cmdNext_Click:
>>> > > > MsgBox Err.Description
>>> > > > Resume Exit_cmdNext_Click
>>> > > >
>>> > > >End Sub
>>> > > >----------------
>>> > > >Private Sub cmdPrev_Click()
>>> > > >On Error GoTo Err_cmdPrev_Click
>>> > > >
>>> > > >
>>> > > > rsContacts.MovePrevious
>>> > > > BindAllFields
>>> > > >
>>> > > >Exit_cmdPrev_Click:
>>> > > > Exit Sub
>>> > > >
>>> > > >Err_cmdPrev_Click:
>>> > > > MsgBox Err.Description
>>> > > > Resume Exit_cmdPrev_Click
>>> > > >
>>> > > >End Sub
>>> > > >
>>> > > >
>>> > >
>>> > >
>>> > > _________________________________________________________________
>>> > > Choose an Internet access plan right for you -- try MSN!
>>> > > http://resourcecenter.msn.com/access/plans/default.asp
>>> > >
>>> > >
>>> > >
>>> > >
>>> > >
>>> > >
>>> > >
>>> > >
>>> > >
>>> >
>>> >
>>> >
>>>
>>>
>>
>>
>>_________________________________________________________________
>>Unlimited Internet access -- and 2 months free! Try MSN.
>>http://resourcecenter.msn.com/access/plans/2monthsfree.asp
>>
>>
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or to
>>unsubscribe send a blank email to
>
>
>_________________________________________________________________
>Surf the Web without missing calls! Get MSN Broadband.
>http://resourcecenter.msn.com/access/plans/freeactivation.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Surf the Web without missing calls! Get MSN Broadband.
http://resourcecenter.msn.com/access/plans/freeactivation.asp
|
|
|
View Cart
