Avatar of actsoft
actsoft
Flag for United States of America asked on

access 2010 lookup or key data

I have a lookup field that works fine if i scroll through the data until I find the desired data. Is there a way to change it so I can start typing the desired data and it moves to that part of the list?
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

Set the "AutoExpand" property of the combobox to: Yes
Jeffrey Coachman

...and for easier searching, make sure the values in the list are sorted.

In the RowSource Property of the combobox, you should see something like this:
SELECT Country FROM YourTable ORDER By Country
actsoft

ASKER
the auto expand property is set to yes and it is sorted by invoice number. It is a query created from a linked table, does that make a difference?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jeffrey Coachman

should not matter.


Just to be sure we are on the same page, ...does the attached sample do what you are requesting?
Database151.mdb
actsoft

ASKER
yes the example works as requested. i have another field along side this one that works fine and i can't find any difference other than this one is comprised of text that is both alpha and numeric where the other does not include any alpha characters.

Both fileds have events included:
Option Compare Database

Private Sub cboCustInvNo_Change()
Me.txtCustNo.Value = Me.cboCustInvNo.Column(1)
Me.txtCustName.Value = Me.cboCustInvNo.Column(2)
Me.txtCustInvDate.Value = Me.cboCustInvNo.Column(3)
Me.txtCustInvTotal.Value = Me.cboCustInvNo.Column(6)
Me.txtDestination.Value = Me.cboCustInvNo.Column(7)
Me.txtShipDate.Value = Me.cboCustInvNo.Column(8)

End Sub

Private Sub cboFreightInvNo_Change()
Me.txtVendNo.Value = Me.cboFreightInvNo.Column(1)
Me.txtCarrier.Value = Me.cboFreightInvNo.Column(2)
Me.txtFreightInvDate.Value = Me.cboFreightInvNo.Column(3)
Me.txtTotalFreightAmt.Value = Me.cboFreightInvNo.Column(4)

End Sub
It's the first that works correctly.p is the first field in each sheet.
lookup-works.xlsx
lookup-does-not-work.xlsx
Jeffrey Coachman

Why is your code on the change event?
Things like this are typically on the "AfterUpdate" event...
...this may be part of the problem...

I think we are at the point where a sample file will make this all much clearer.
Please follow these steps:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
actsoft

ASKER
In the form "frmFreightTracker"; field "Carrier Invoice No" I would like to key the invoice number: N500395 and have it move to that number in the list.
Lookup-Issue.zip
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

ok great...
Glad I could help.

;-)

Jeff