G
Gijs Beukenoot
Hi all,
I'm probably missing something, but I'm trying to get my external data shown
in a combobox.
I have an Access frontend, SQL backend.
I had a form showing a combobox with a fairly large SQL statement so I've
removed that and I've created that query as view in SQL server. The view
works (in SQL) and is giving me the expected records back.
So, in the form_open event, I fill a recordset and then set the combobox
recordset property to this recordset. I can see (breakpointing the "Set
Me.ComboBoxName.Recordset = rsTest) that the rsTest-recordset contains data
(recordcount is the same as the recordcount on SQL) but the combobox doesn't
get any data... What am I missing?
And yes, I know I could fill the combobox by just "AddItem"-ing the
recordset, but in that case I need to make a separate routine for each
combobox/list due to the difference in fieldnames (I have more than one form
(and thus many combo's and lists) that I want to translate this way). And
while we're on that subject, is the only way to clear a combobox in Access
to remove all the items from a combobox (listbased, not querybased) bu
..RemoveItem them?
Dim rsTest as ADODB.Recordset
Private Sub Form_Load()
If GetView(rsTest, "vwSomeComboBox") Then
Set Me.ComboBoxName.Recordset = rsTest
Else
<....>
Endif
End Sub
Public Function GetViewData(ByRef p_rsRecordset as ADODB.Recordset, byVal
p_sViewName as String, Optional ByVal p_sParameter As String = "")
On Error Goto GetViewData_Error
Set p_rsRecordset = new ADODB.Recordset
With p_rsRecordset
.ActiveConnection = conDatabase
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
If p_sParameter = "" Then
.Source = "SELECT * FROM " & p_sViewName & ";"
Else
.Source = "SELECT * FROM " & p_sViewName & " WHERE " &
p_sParameter & " ;"
End If
.Open
GetViewData = True
End With
GetViewData_Exit:
On Error Goto 0
Exit Function
GetViewData_Error:
<Log Error and show user>
GetViewData = False
Resume GetViewData_Exit
End Function
I'm probably missing something, but I'm trying to get my external data shown
in a combobox.
I have an Access frontend, SQL backend.
I had a form showing a combobox with a fairly large SQL statement so I've
removed that and I've created that query as view in SQL server. The view
works (in SQL) and is giving me the expected records back.
So, in the form_open event, I fill a recordset and then set the combobox
recordset property to this recordset. I can see (breakpointing the "Set
Me.ComboBoxName.Recordset = rsTest) that the rsTest-recordset contains data
(recordcount is the same as the recordcount on SQL) but the combobox doesn't
get any data... What am I missing?
And yes, I know I could fill the combobox by just "AddItem"-ing the
recordset, but in that case I need to make a separate routine for each
combobox/list due to the difference in fieldnames (I have more than one form
(and thus many combo's and lists) that I want to translate this way). And
while we're on that subject, is the only way to clear a combobox in Access
to remove all the items from a combobox (listbased, not querybased) bu
..RemoveItem them?
Dim rsTest as ADODB.Recordset
Private Sub Form_Load()
If GetView(rsTest, "vwSomeComboBox") Then
Set Me.ComboBoxName.Recordset = rsTest
Else
<....>
Endif
End Sub
Public Function GetViewData(ByRef p_rsRecordset as ADODB.Recordset, byVal
p_sViewName as String, Optional ByVal p_sParameter As String = "")
On Error Goto GetViewData_Error
Set p_rsRecordset = new ADODB.Recordset
With p_rsRecordset
.ActiveConnection = conDatabase
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
If p_sParameter = "" Then
.Source = "SELECT * FROM " & p_sViewName & ";"
Else
.Source = "SELECT * FROM " & p_sViewName & " WHERE " &
p_sParameter & " ;"
End If
.Open
GetViewData = True
End With
GetViewData_Exit:
On Error Goto 0
Exit Function
GetViewData_Error:
<Log Error and show user>
GetViewData = False
Resume GetViewData_Exit
End Function