Changing the source of Combobox

alpertunga65

Registered User.
Local time
Today, 23:13
Joined
Mar 15, 2013
Messages
21
Hello,

I have a form (named Example) to create reports by selecting fields from tables or querries

On this form, there is a option box (name is KynkSec) with two options (Table, Query) and a combobox named as KynkTurSec.

I want to change the data source of combobox either table or query. By afterupdate, that combobox is requering the listbox "ListKynkAlan" and I can see fields of selected table or query. (That is my dream):))
Unfortunately I can not do that. Combobox is showing only tables or both of tables and queries. But not only query.

Here is str source of my combobox:

SELECT MsysObjects.Name, MsysObjects.Type FROM MsysObjects WHERE (((MsysObjects.Type)=1) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys")) OR (((MsysObjects.Type)=5) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys")) ORDER BY MsysObjects.Name; That is showing both of tables and queries.

And I wrote a code for KynkSec option box;

Private Sub KynkSec_AfterUpdate()
' Populate rowsource of KynkTurSec

Dim strSQL As String

On Error GoTo HandleErr

Select Case KynkTurSec

Case 1

strSQL = "SELECT MsysObjects.Name FROM MsysObjects" _
& WHERE(((Left$([Name], 1)) <> "~") And ((MsysObjects.Type) = 1) And ((Left$([Name], 4)) <> "Msys"))
Order BY(MsysObjects.Name)

Case 2

strSQL = "SELECT MsysObjects.Name FROM MsysObjects" _
& WHERE(((Left$([Name], 1)) <> "~") And ((MsysObjects.Type) = 5) And ((Left$([Name], 4)) <> "Msys"))
Order BY(MsysObjects.Name)


Case Else
End Select

With Me!KynkTurSec
.Value = Null
.RowSource = strSQL
.Requery
.Value = .ItemData(0)
End With

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Example.KynkSec_AfterUpdate"
End Select
Resume ExitHere
Resume


End Sub

But this code is not working and giving a warning messsage "Sub or function is not defined"

So How can I change the source of combobox, either table or Query?
Thanks.
 
You can refactor that code like this, which is a fair bit simpler . . .
Code:
Private Const SQL As String = _
    "SELECT [Name] FROM MSysObjects " & _
    "WHERE Flags = 0 AND [Type] = [prm0] " & _
    "ORDER BY [Name];"

Private Sub KynkSec_AfterUpdate()
    Dim tmp As Integer
    tmp = 1

    With Me!KynkTurSec
        If .Value = 2 Then tmp = 5
        .Value = Null
        .RowSource = Replace(SQL, "[prm0]", tmp)
        .Requery
        .Value = .ItemData(0)
    End With
End Sub
And your SQL can look like this . . .
Code:
SELECT t.Name, t.Type
FROM MSysObjects AS t
WHERE t.Flags = 0 
    AND (t.Type = 1 Or t.Type = 5)
ORDER BY t.Name;
. . . and do the same job. Note that the objects you want all have Flags = 0 in MSysObjects.
Does that solve any of those problems?
 
You can refactor that code like this, which is a fair bit simpler . . .
Code:
Private Const SQL As String = _
    "SELECT [Name] FROM MSysObjects " & _
    "WHERE Flags = 0 AND [Type] = [prm0] " & _
    "ORDER BY [Name];"
 
Private Sub KynkSec_AfterUpdate()
    Dim tmp As Integer
    tmp = 1
 
    With Me!KynkTurSec
        If .Value = 2 Then tmp = 5
        .Value = Null
        .RowSource = Replace(SQL, "[prm0]", tmp)
        .Requery
        .Value = .ItemData(0)
    End With
End Sub
And your SQL can look like this . . .
Code:
SELECT t.Name, t.Type
FROM MSysObjects AS t
WHERE t.Flags = 0 
    AND (t.Type = 1 Or t.Type = 5)
ORDER BY t.Name;
. . . and do the same job. Note that the objects you want all have Flags = 0 in MSysObjects.
Does that solve any of those problems?

Hello lagbolt,

First of all, Thanks for your quick reply. I tried your codes.
But unfortunately codes didnt worked. After witting codes; when I clicked Tables or Query, it showed both tables and queries.
I used the first part of codes for afterupdate property of KynkSec,
But I could not understand where to put SQL code?? I used it as the source of KynkTurSec. After using all of them, I tried again. But clicking Table or Query didnt change the result. I did some mistakes but where?

Thanks again.
 
Codes didn't work. OK. Not sure how to troubleshoot that. You can post your DB if you want, I'll take a look.
 

Users who are viewing this thread

Back
Top Bottom