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?
Jeffrey Coachman
Set the "AutoExpand" property of the combobox to: Yes
...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
In the RowSource Property of the combobox, you should see something like this:
SELECT Country FROM YourTable ORDER By Country
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?
should not matter.
Just to be sure we are on the same page, ...does the attached sample do what you are requesting?
Database151.mdb
Just to be sure we are on the same page, ...does the attached sample do what you are requesting?
Database151.mdb
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(
Me.txtCarrier.Value = Me.cboFreightInvNo.Column(
Me.txtFreightInvDate.Value
Me.txtTotalFreightAmt.Valu
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
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(
Me.txtCarrier.Value = Me.cboFreightInvNo.Column(
Me.txtFreightInvDate.Value
Me.txtTotalFreightAmt.Valu
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
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
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
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
Lookup-Issue.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok great...
Glad I could help.
;-)
Jeff
Glad I could help.
;-)
Jeff