Using VBA to populate a combo box and a form

[Corey-g via AccessMonster.com]

Hi All,

I am using Access 2K3 and DSNless connections to Oracle XE (10g R2).

I am trying to do some small 'tests' to fully understand the functionality...

I have a small table of categories with 3 fields: ID, Type, and Description
(which has 5 records)

I have created a small form with a combo box and 2 text boxes. What I would
like to do is have the combo box show one of the fields from the table, and
then as I chose a different value in the combo box, have the 2 text boxes
show the other values. (So if the cbo showed the ID, the textboxes would show
the name and description).

I can set the recordsource property (of the form) to a ADO recordset, and
this works - sort of. The text boxes show the right data, but the cbo shows
strange characters (almost like the font is wingding). I assume this is
because I'm setting the value(s) wrong, which is why I'm posting...

If anyone can help, it would be appreciated.

TIA

Corey

 

[Corey-g via AccessMonster.com]

So, from all of the reading I have done, I am thinking that this isn't
possible the way I have it set up. It seems to me that I am going to have to
set up a seperate recordset for the combo box, and then use an event
procedure to change the data displayed in the text boxes based on the combo
box selection. Is that correct? Am I going the right way?

Form Load event:
connect to db, and get recordset of ID's, then get second recordset of all
data. Then make first recordset the "Row Source" of the cbo.

Then have an "On Change" event for the cbo that will find the selected value
in the second recordset, and set the textbox values.

TIA,

Corey

 

[Corey-g via AccessMonster.com]

Okay, so I have worked through a bunch of different attempts, and I think I
am making progress, but I still don't seem to have it right.

I have tried to set the combo box to value list, and use the additem method
to build the list (from a recordset). This works in that when I click the
combo box, I get a list of the ID's (although I thought it might default to
the first value, rather than be blank). Also, this is in code as if I try to
set the Row Source Type of the cbo to value list in the properties window, I
get an error when trying to set it to the recordset.

I then use the on_change event to create another recordset based on the value
in the combo box, and have the textboxes set to the field values from the new
recordset.

This also seems to work, but the value in the combo box go back to blank
after the code runs.

Here is the code currently:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection ' Create a new ADO Connection object
With cn ' Set all connection information
.Provider = "OraOLEDB.Oracle"
.Properties("Data Source").Value = "XE"
.CursorLocation = adUseClient
.Properties("User ID").Value = UID
.Properties("Password").Value = PWD
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT DISTINCT AUDIT_CATEGORY_ID FROM AUDIT_CATEGORY"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient

.Open
End With
'MsgBox "Finished"
Set Me.Combo0.Recordset = rs
Me.Combo0.RowSourceType = "Value List"
Do Until rs.EOF
Me.Combo0.AddItem rs.Fields("Audit_Category_ID")
rs.MoveNext
Loop

Set rs = Nothing
Set cn = Nothing

End Sub

Private Sub Combo0_Change()
Dim cn As ADODB.Connection
Dim rsDetails As ADODB.Recordset

Set cn = New ADODB.Connection ' Create a new ADO Connection object
With cn ' Set all connection information
.Provider = "OraOLEDB.Oracle"
.Properties("Data Source").Value = "XE"
.CursorLocation = adUseClient
.Properties("User ID").Value = UID
.Properties("Password").Value = PWD
.Open
End With

Set rsDetails = New ADODB.Recordset
With rsDetails
Set .ActiveConnection = cn
.Source = "SELECT * FROM AUDIT_CATEGORY WHERE AUDIT_CATEGORY_ID = " &
Me.Combo0.Value
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient

.Open
End With

Set Forms("Form1").Recordset = rsDetails
Me.Text3.ControlSource = "AUDIT_CATEGORY_DESCR"
Me.Text5.ControlSource = "AUDIT_CATEGORY_TYPE"
Me.Refresh

'Set cn = Nothing
Set rsDetails = Nothing
End Sub

Anyone know where I'm going wrong?

TIA,

Corey

 

[Corey-g via AccessMonster.com]

Okay, I have also tried to remove the where clause from the second recordset,
and I am able to use the record selectors (which didn't work - obvoiusly when
the query was only returning one record), but I still lose the value in the
combo box...

I then changed the code to try and work all in one function (the "Open" Event)
, but I am back to seeing the strange characters in the combo box (I removed
the code to set the type to value list - as I also removed the second
recordset). I had thought that maybe the issue was with how I was trying to
use the 2 recordsets, but I guess I was wrong...

Most of the posts I have read have been from 2004, so I was hoping that there
were changes for Access 2K3 that worked differently...

Anyone?

Corey

 

[Tim Ferguson]

I have a small table of categories with 3 fields: ID, Type, and
Description (which has 5 records)

I have created a small form with a combo box and 2 text boxes. What I
would like to do is have the combo box show one of the fields from the
table, and then as I chose a different value in the combo box, have
the 2 text boxes show the other values. (So if the cbo showed the ID,
the textboxes would show the name and description).

It sounds as though this is an unbound form, so the following would seem
to work:

private sub myCombo_AfterUpdate

' this is a class I wrote: the meaning is obvious...
dim cmd as New SQLCommand
cmd.Add "SELECT Type, Description"
cmd.Add "FROM SmallTable"
cmd.Add "WHERE ID = " & myCombo.Value

' you can probably do this in ADO if that is what rocks
' your boat...
dim rs as DAO.Recordset
Set rs = CurrentDB().OpenRecordset( _
cmd.SQL, dbOpenSnapshot, dbForwardOnly)

' see if anything came back...
If rs.BOF Then
' no: clear the text boxes
txtType.Value = Null
txtDescription.Value = Null

Else
' okay, copy the values into the textboxes
txtType.Value = rs!Type
txtDescription.Value = rs!Description

End if

rs.Close

End Sub



If the form is bound to a recordset, then you can use the
DoCmd.GoToRecord method to move the pointer.

Hope that helps


Tim F