Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
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
etc...

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.

Thoughts?
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)
    Else
       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
NICE!
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)
    Else
        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
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.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
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.