Populate combo box from Access database - i'm on vb6

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Populate combo box from Access database - i'm on vb6

Post by sal21 »

Based the Access dabase table TABELLA_STORIA i need to devide in a block of 50 recorset. Then select the first element of ANNO in ths case 1951 and the last record of the block 50, in this case ANNO IS 2000,... second block ANNO 2001 to the and of list second block ANNO 2024.

To the and:

1951-2000
2001-2024

fill with this value a combobox

note:
the value in table are dinamic, important is to devide in blok of 50
naturally with a SQL for vb6

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

Split from this topic since it is a new question.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

HansV wrote:
10 Jun 2024, 14:12
Split from this topic since it is a new question.
tks.

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

Air code:

Code: Select all

    Dim RS As ADODB.Recordset
    Dim MinAnno As Long
    Dim MaxAnno As Long
    Dim Anno As Long
    Set RS = New ADODB.Recordset
    RS.Open Source:="SELECT Min(ANNO), Max(ANNO) FROM TABELLA_STORIA", _
        ActiveConnection:=..., Options:=adCmdText
    MinAnno = RS(0)
    MaxAnno = RS(1)
    RS.Close
    Set RS = Nothing
    For Anno = MinAnno To MaxAnno Step 50
        If Anno + 49 > MaxAnno Then
            Me.ComboBox1.AddItem Anno & "-" & MaxAnno
        Else
            Me.ComboBox1.AddItem Anno & "-" & Anno + 49
        End If
    Next Anno
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

HansV wrote:
10 Jun 2024, 14:39
Air code:

Code: Select all

    Dim RS As ADODB.Recordset
    Dim MinAnno As Long
    Dim MaxAnno As Long
    Dim Anno As Long
    Set RS = New ADODB.Recordset
    RS.Open Source:="SELECT Min(ANNO), Max(ANNO) FROM TABELLA_STORIA", _
        ActiveConnection:=..., Options:=adCmdText
    MinAnno = RS(0)
    MaxAnno = RS(1)
    RS.Close
    Set RS = Nothing
    For Anno = MinAnno To MaxAnno Step 50
        If Anno + 49 > MaxAnno Then
            Me.ComboBox1.AddItem Anno & "-" & MaxAnno
        Else
            Me.ComboBox1.AddItem Anno & "-" & Anno + 49
        End If
    Next Anno
I'M sorry... but the first block of 50 records is 1951-2001 is correct?

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

I created a small sample database. This is the result:

S2558.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

HansV wrote:
11 Jun 2024, 09:58
I created a small sample database. This is the result:


S2558.png
sorry bro.
i have cahnged a wrong parameter in your code, and have a wrong result.
Your code work perfect.

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

HansV wrote:
11 Jun 2024, 09:58
I created a small sample database. This is the result:


S2558.png
NOW....
based the value between INIZIO and FINE how to extract the list of ANNO, from the table, with SQL ADO

i have:

Private Sub CANNI_Click()

INIZIO = Split(Me.CANNI.Text, "-")(0)
FINE = Split(Me.CANNI.Text, "-")(1)

End Sub

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

You should be able to create the SQL yourself!
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

HansV wrote:
12 Jun 2024, 10:01
You should be able to create the SQL yourself!
Correct?

SQL = "SELECT ANNO, SERVIZIO FROM TABELLA_STORIA WHERE ANNO>=" & INIZIO & " AND ANNO<=" & FINE & ""

I passed the exam? :scratch:

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

That should work. You can also use

SQL = "SELECT ANNO, SERVIZIO FROM TABELLA_STORIA WHERE ANNO BETWEEN " & INIZIO & " AND " & FINE
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Populate combo box from Access database - i'm on vb6

Post by sal21 »

BETWEEN never used!

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate combo box from Access database - i'm on vb6

Post by HansV »

Best wishes,
Hans