Solved How to programatically update integers in a one column combobox list? (1 Viewer)

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
I have a form with an unbound combobox that has a manually list of integers that represent trip numbers - T_TN). When a user picks a number from that list, it executes "After Update" VBA that opens a report containing all information pertaining to that trip. Within that VBA, I have VBA statement that, also, gets the last trip's trip number - LT_TN.

I need a way that will enable me to compare the LT_TN with the list's last T_TN to update the list with all T_TN's between the list's last T_TN and the LT_TN, including the LT_TN.

I tried creating my own code, as well as, adapting several related examples that I found on the internet. I was unsuccessful, after spending seven 8 hour plus days, head banging and learning from the effort. So, as it has been in the past, I am, once again, asking for help from the wizards.

And 'Yes', I do like it here.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Sorry, I got lost in all your terminologies. Can you post some images instead? Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 12:22
Joined
Mar 14, 2017
Messages
8,738
also, gets the last trip's trip number - LT_TN.
What does this mean? What is the 'last trip'? the last trip someone went on?

Please speak only using database terms - exclude all business lingo.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
What does this mean? What is the 'last trip'? the last trip someone went on?

Please speak only using database terms - exclude all business lingo.
Hello Isacc, I was trying to describe a scenario that would help those who want to help understand the application. Yes, if I were a programmer, I would use non-business terms. However, any programmer who understands a problem will be able to offer a solution to what I requested help with.
 

bob fitz

AWF VIP
Local time
Today, 19:22
Joined
May 23, 2011
Messages
4,717
Hello Isacc, I was trying to describe a scenario that would help those who want to help understand the application. Yes, if I were a programmer, I would use non-business terms. However, any programmer who understands a problem will be able to offer a solution to what I requested help with.
Actually, I'm not sure that that is correct because like the other two members that have replied, I also think some images or perhaps better still, a copy of the db would be useful :)
 

Minty

AWF VIP
Local time
Today, 19:22
Joined
Jul 26, 2013
Messages
10,355
I'm afraid I agree with the others, you are describing a process, but I think we need to see two things,
a) some examples of your starting data,
b) some examples of the expected outputs.

The sample data should include enough to generate all expected outcomes.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
Hi. Sorry, I got lost in all your terminologies. Can you post some images instead? Thanks.
I don't have any VBA (images?), but consider this:
1. Given: a combobox's value list configured with a 'blank space' at the top of the list followed by numbers 1 through 10 below it.
2. And, an LT_TN equal to 14 (obtained from a DLookup statement that is included in the target VBA written under 'On Check').
3. After a user picks the down-arrow on the right-hand side of the combobox, the value list will open exposing the entire value list.
3. Next, by picking the 'blank space', I want the VBA value list to add the numbers: 11, 12, 13, 14 to the list.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
I'm afraid I agree with the others, you are describing a process, but I think we need to see two things,
a) some examples of your starting data,
b) some examples of the expected outputs.

The sample data should include enough to generate all expected outcomes.
I don't have and code written. I described the process hoping to get someone to understand the process enough to show me how to do it.
I submitted a simpler approach to theDBguy. That should help you.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:22
Joined
Oct 29, 2018
Messages
21,358
I don't have any VBA (images?), but consider this:
1. Given: a combobox's value list configured with a 'blank space' at the top of the list followed by numbers 1 through 10 below it.
2. And, an LT_TN equal to 14 (obtained from a DLookup statement that is included in the target VBA written under 'On Check').
3. After a user picks the down-arrow on the right-hand side of the combobox, the value list will open exposing the entire value list.
3. Next, by picking the 'blank space', I want the VBA value list to add the numbers: 11, 12, 13, 14 to the list.
Hi. Thanks for the additional information. Quick question, what is the Row Source Type of the Combobox? You would need a different approach for a Value List as compared to a Table/Query. From your original description, I am thinking it is a Value List. If so, will the list of numbers just continue to add up? Or, will you need to go down (reduce) the numbers too?
 

shokly

New member
Local time
Today, 12:22
Joined
Jan 13, 2021
Messages
7
Hi, I think you may add the following VBA code to your after update event of the combobox

Code:
Private Sub yourCombo_AfterUpdate()
    If youcombo.Selected(-1) Then
    
        Dim lastItem as integer
        Dim lastT as integer
        Dim i As Long
        
        lastItem= yourCombo.ListCount - 1
        lastT= LT_TN
        
        with yourCombo
            For i= lastItem to lastT
                .AddItem i
            Next i
        end with
    End If
end sub
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
Hi. Thanks for the additional information. Quick question, what is the Row Source Type of the Combobox? You would need a different approach for a Value List as compared to a Table/Query. From your original description, I am thinking it is a Value List. If so, will the list of numbers just continue to add up? Or, will you need to go down (reduce) the numbers too?
The Row Source is a value list (integers). Last year, it with a list between 1 & 99. I grew beyond 99. Those had to be manually entered. Because the list last year exceeded 250 with the additional burden of requiring the user to scroll through a long list to access a number to select to open a specific report, they want two things: (1) new numbers to be added via the DB, automatically & (2) the value list in descending order (got the later covered. Yes, the numbers will continue to accumulate and must be retained to be available for selecting the reports associated with them., which are summaries individual trips.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
Hi, I think you may add the following VBA code to your after update event of the combobox

Code:
Private Sub yourCombo_AfterUpdate()
    If youcombo.Selected(-1) Then
   
        Dim lastItem as integer
        Dim lastT as integer
        Dim i As Long
       
        lastItem= yourCombo.ListCount - 1
        lastT= LT_TN
       
        with yourCombo
            For i= lastItem to lastT
                .AddItem i
            Next i
        end with
    End If
end sub
Ok shokly, I'll give that a try, tomorrow after I return from a visit to my doctor. Thank you.
 

Minty

AWF VIP
Local time
Today, 19:22
Joined
Jul 26, 2013
Messages
10,355
I don't have and code written. I described the process hoping to get someone to understand the process enough to show me how to do it.
I submitted a simpler approach to theDBguy. That should help you.
I wasn't expecting any code, simply some data, your post #11 expanded on what your data was and where you needed it to go, so that helped considerably.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:22
Joined
Sep 12, 2006
Messages
15,614
I think the thing is, normally, you wouldn't need to change data.

Your trip details should be linked to the trip. If you want to select trip 1, and trip 2, and compare the difference, then you can use unmatched queries to identify anything that is in one trip bot not in the other, and then use that to automate the synchronisation of the details.. Maybe this is what you are talking about.

Note that Access isn't like Excel, so there isn't a concept of a "last trip" and "previous trip". You can get to it, but it's not always the best way of working. Access needs a data-centric design method - so in general you think about exploiting your data by manging certain tables, but more generally, subsets of certain tables. So what you may need is the subset of data for trip A, and the subset of data for trip B, and compare them with a single process. There will be items in A not in B, Items in both A and B, and items in B not in A, and the aim is to use these relationships to get what you need out of the database.

You don't have tables for separate trips, rather a single table (or structure of tables) with all the data, and you select the subset of those trips that currently interests you. eg all trips between 2 dates. It's quite rare to ever view all the data in a table, to be honest, and the important thing is to design the database in a way that lets you ensure the data integrity. Because you rarely see all the data, you need to be sure that what you are seeing is a correct view of the part you wanted.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
Ok shokly, I'll give that a try, tomorrow after I return from a visit to my doctor. Thank you.
Hi, I think you may add the following VBA code to your after update event of the combobox

Code:
Private Sub yourCombo_AfterUpdate()
    If youcombo.Selected(-1) Then
  
        Dim lastItem as integer
        Dim lastT as integer
        Dim i As Long
      
        lastItem= yourCombo.ListCount - 1
        lastT= LT_TN
      
        with yourCombo
            For i= lastItem to lastT
                .AddItem i
            Next i
        end with
    End If
end sub

The Row Source is a value list (integers). Last year, it with a list between 1 & 99. I grew beyond 99. Those had to be manually entered. Because the list last year exceeded 250 with the additional burden of requiring the user to scroll through a long list to access a number to select to open a specific report, they want two things: (1) new numbers to be added via the DB, automatically & (2) the value list in descending order (got the later covered. Yes, the numbers will continue to accumulate and must be retained to be available for selecting the reports associated with them., which are summaries individual trips.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
In the form's VBA under the combobox, I use this strLT_TN = DMax("[T_TN]", "qryTrip_Sheets") to get the last record's T_TN value. And, in the value list, the highest value integer, let's say, is
less than that. All I want to do is add value list items from that integer to the strLT_TN. What I have asked the forum is some code to do that. ( haven't tested shokly's code, yet. and will next.
Thank you for your input. I appreciate everybody's responses.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:22
Joined
Sep 12, 2006
Messages
15,614
to put it another way

if you have records related to trip one, and you want those same records to be included in the records related to trip 2, one way is simply to append all the trip 1 records to trip 2 with an append query. If your indexing is correct, duplicates will be rejected, and missing items will be appended. all without code. It's a matter of just manipulating the data as a set of data, and understanding how a database can help you achieve what you want, by doing database actions. It's much harder to achieve the same result with programming constructs, or to try to emulate excel.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
to put it another way

if you have records related to trip one, and you want those same records to be included in the records related to trip 2, one way is simply to append all the trip 1 records to trip 2 with an append query. If your indexing is correct, duplicates will be rejected, and missing items will be appended. all without code. It's a matter of just manipulating the data as a set of data, and understanding how a database can help you achieve what you want, by doing database actions. It's much harder to achieve the same result with programming constructs, or to try to emulate excel.
I solve my problem by understanding shokly's code and modifying it. I'll post that code, later, today.
 

Punice

Registered User.
Local time
Today, 15:22
Joined
May 10, 2010
Messages
135
Hi, I think you may add the following VBA code to your after update event of the combobox

Code:
Private Sub yourCombo_AfterUpdate()
    If youcombo.Selected(-1) Then
  
        Dim lastItem as integer
        Dim lastT as integer
        Dim i As Long
      
        lastItem= yourCombo.ListCount - 1
        lastT= LT_TN
      
        with yourCombo
            For i= lastItem to lastT
                .AddItem i
            Next i
        end with
    End If
end sub
Shokly, Your code was instrumental in helping me find the solution to what I wanted, as presented in my initial post and simplfied, down the line.
Thanks you.

Instead of using 'after update' to run the code, I found that the on-load was better. For the code to run as desired, I edited the comboBox and entered '1' as the initial value in the list. When the code runs, it fills-in the value list with all the numbers between '1' and the number of the last trip. I tried to get the value list to display in descending order so that that number would appear at the top of the list, but to no avail.
Here is the code. It's not pretty, but is works.[
ICODE]
Private Sub Form_Load()
Dim strLT_TN As Integer
Dim strFT_TN As Integer
Dim lngView As Long
Dim lastItem As Integer

T_TN = [Forms]![frmReport_Selector]![cboBoth_lbl] 'gets the trip number
strLT_TN = DMax("[T_TN]", "qryTrip_Sheets") 'gets the last trip record's T_TN value

'update the cboBoth_lbl's value list sequence to match the trip number of the last trip made.
lastItem = cboBoth_lbl.ListCount - 1 'gets last value list integer
If lastItem <> strLT_TN Then
With cboBoth_lbl
Do While lastItem <= strLT_TN
.AddItem lastItem
lastItem = lastItem + 1
Loop
End With
End If
[/ICODE]
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:22
Joined
Sep 12, 2006
Messages
15,614
Code:
Do While lastItem <= strLT_TN
.AddItem lastItem
lastItem = lastItem + 1
Loop

If you manage this loop the other way round, ie start with the highest, or highest plus 1, deduct 1 each time, and stop when the count gets to 0 or 1 (not sure of the precise logic), the items should get inserted in descending order.
 

Users who are viewing this thread

Top Bottom