Free Lessons
Fast Tips
Topic Index
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Home > Forums > Access
Access Forum

Back to Access Forum

Button to Cycle Combo Upload Images   Link  
Dan Jackson 
2 months ago
Hi all,

I have a combo box listing 8 people with an associated key

1 Glen
2 Adam
3 Colin

I'd like to have a button move through the list of people, set that as the value and cycle the list.

Problem is, 50% of the time, the combo box will be set manually so i don't want it cycling based on whats in the box but rather by its own list. I'm thinking maybe a single field table which tracks the history of entries?

This is the combo and the button. Currently showing Glen

Click the button and it moves to Adam

Once it gets to Sales Manager, it then moves back up to Glen

The key point is i don't want it to cycle based on whats in the combo but rather by its own list, so if the combo is changed manually, it wont affect the cycling.

Thank You
Dan Jackson
2 months ago
Hmm Thinking

I could use a hidden combo and write that value to the visible one. That would also resolve the issue of if another person is ever added in the future.

Kevin Robertson
2 months ago
Give this a go Dan.

Private Sub btnNextName_Click()

    Dim X As Long
    X = cboNames.ListIndex
    If X = cboNames.ListCount - 1 Then
       cboNames = cboNames.Column(0, 0)
       cboNames = cboNames.Column(0, X + 1)
    End If

End Sub
Juan C Rivera
2 months ago
I also like the set date example that Richard did by using the arrow buttons up and down to move this can be used to move back and forward with the names.  Don't know if you wanted to go there but thinking it will give it a bit more functionality (leggo blocks to play with)

Date Buttons
Kevin Robertson
2 months ago
I had fun with this, so I added more buttons - Previous, Cycle (like you wanted) and Next.
Kevin Robertson
2 months ago

Juan C Rivera
2 months ago
Dan Jackson
2 months ago
Hmm. Most interesting. Ill look forward to having a play with this today, thank you!

Ill probably set this on an invisible combo, then have that write the value to the actual combo. That should get around the manual entry issue.

Just one thing i wanted to check. In your code
cboNames = cboNames.Column(0, X + 1)
You are applying the +1 to the second column in your combo. Is this by design? I would have thought the +1 would relate to the key column rather than the name column, or am i missing something? Cheers
Kevin Robertson
2 months ago
The +1 is incrementing the row of the Combo Box.
Combo.Column(column, row)
Dan Jackson
2 months ago
Just been playing with this. I've got the combo called "Combo_NextAdvisor" which is hidden and cycles as your code does.

1st it looks up the value from table to get the last used value.
2nd it writes the value from the invisible "Combo_NextAdvisor" to the visible combo "Combo_Advisor"
3rd it then cycles to the next one.
4th it writes the new value to the table

I'm assuming i use Dlookup, but two questions are:

1. How do I lookup, specifically the last record in the table?
2. How do i write the current value to a new record? I'm assuming I need to do a SQL INSERT but i haven't yet had the chance to research the SQL seminar yet.

Here is what i have so far.

'   <<<<<  1st. Read Last Used Value From NextAdvisorT (Dlookup) _
                2nd. Write Value From Combo_NextAdvisor To Combo_Advisor _
                3rd. Move the value on one (Kevins Code) _
                4th. SQL Write The Value as a New Record in NextAdvisorT >>>>>

'   <<<<< 1st >>>>>

'   <<<<< 2nd >>>>>
    Combo_Advisor = Combo_NextAdvisor

'   <<<<< 3rd >>>>>
Dim X As Long
X = Combo_NextAdvisor.ListIndex

    If X = Combo_NextAdvisor.ListCount - 1 Then
        Combo_NextAdvisor = Combo_NextAdvisor.Column(0, 0)
        Combo_NextAdvisor = Combo_NextAdvisor.Column(0, X + 1)
    End If
'   <<<<< 4th >>>>>

As always, a huge thank you for any help received
Dan Jackson
2 months ago
Almost there. I've managed to work out the 1st question but don't have a clue on the 2nd. This is what I did to lookup the last value (My first use of a variable that i'd written myself yay!)
Dim Y As Long
Y = DMax("ID", "NextAdvisorT")
Combo_NextAdvisor = DLookup("NextAdvisor", "NextAdvisorT", "ID=" & Y)

This is the table. It'll just keep a historical list going, which is fine!

All i need now is to be able to write the value back to the table at the end of the routine. How would I achieve this (SQL INSERT?)
Kevin Robertson
2 months ago
Here's how I wrote the value to the table in my sample:

If Not IsNull(cboNames) Then
    CurrentDb.Execute "INSERT INTO NameT (FirstName) " & _
                                "VALUES (""" & cboNames.Column(1) & """)"
    MsgBox cboNames.Column(1) & " added!", vbInformation, "Success"
End If

Since inserting a number you won't need the quotes.
CurrentDb.Execute "INSERT INTO YourTable (YourFieldName) " & _
                            "VALUES (" & YourComboBox & ")"
Dan Jackson
2 months ago
You absolute legend, thanks Kevin!

Kevin Robertson
2 months ago
You're welcome.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Access Forum Comments

Start a NEW Conversation
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
Subscribe to Access Forum
Get notifications when this page is updated

Check out these other pages that may be of interest to you:

2/2/2023Import Objects
2/1/2023Continuous Forms Not Working
1/31/2023Group On Top
1/30/2023Association 8
1/27/2023Math in Fields
1/26/2023Association 7
1/25/2023Change Query
1/24/2023Association 6
1/23/2023Association 5
1/20/2023Association 4

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/3/2023 10:27:31 PM.