Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2019, 03:06 AM   #16
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,760
Thanks: 28
Thanked 527 Times in 500 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Select as you type ListBox

Quote:
MajP
For info, clicking the labels to sort the list throws an error - it can't find the sortlist function
I did not demo that in this form. The other FAYT listbox that demos the feature is in that db. The FAYT class has a sort feature.

MajP is offline   Reply With Quote
Old 09-11-2019, 12:36 PM   #17
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 94
Thanks: 10
Thanked 5 Times in 5 Posts
nhorton79 is on a distinguished road
Re: Select as you type ListBox

Hi MajP,

I'm trying your examples and they seem to be what I am looking for (when I look at your supplied example).

However, when I try to implement in my database they don't work as expected.

When I attempt to use your class version I get Runtime Error 3061 Too few parameters. Expected 1.

The initialise line I've used is:

Code:
SAYT_ListBox.Initialize Me.JobList, Me.txtSearch, "[Client Name]"
And my recordsource is:
Code:
SELECT IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]) AS [Client Name], tblStatus.StatusType AS Status, tblJob.JobID AS [Job ID], tblJob.JobTitle AS [Job Title], tblJob.JobStatus_FK, Nz([JobDateExpected],DateValue(Now())) AS [Due Date]
FROM tblAccType INNER JOIN (tblContact RIGHT JOIN ((tblStatusCategory INNER JOIN tblStatus ON tblStatusCategory.StatusCategoryID = tblStatus.StatusCategory_FK) INNER JOIN (tblClient INNER JOIN tblJob ON tblClient.ClientID = tblJob.JobClient_FK) ON tblStatus.StatusID = tblJob.JobStatus_FK) ON tblContact.ContactID = tblJob.JobContact_FK) ON tblAccType.AccTypeID = tblClient.ClientAccType_FK
WHERE (((tblStatusCategory.StatusCategoryID)=[JobFrame]))
ORDER BY IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]), tblJob.JobID;
When I try to use your non class version, it always selects the item before the expected item. Think this is due to having Column Heads set to Yes, and when I set it to No, it works as expected. But can't find where I would alter your code to make it step along an extra line to account for the Column Heads.

Any thoughts?
nhorton79 is offline   Reply With Quote
Old 09-11-2019, 03:19 PM   #18
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,760
Thanks: 28
Thanked 527 Times in 500 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Select as you type ListBox

Where the code says
Code:
  lstSearch.Selected(rsSearch.absolutePostion) = True
You can do something like this

Code:
       selectedItem = rsSearch.AbsolutePosition
       If Me.lstSearch.ColumnHeads = True Then selectedItem = selectedItem + 1
       lstSearch.Selected(selectedItem) = True
or simply hard code
Code:
  lstSearch.Selected(rsSearch.absolutePostion + 1) = True
I would try to use the class because it has more features. I did not update the no class anymore. I would save the query as a Named query. Then use that query as the rowsource. That may help avoid the error.

I purposely made this more complicated so that I could search on any column not only the bound column. I would say for me 90% of the time you are searching on a visible column and the bound column is a hidden PK. You can adapt the general concepts from Arnelgp simple solution for the bound visible column and the keyup from the class. Then you can do away with the recordsets and absolute position and go directly to the value property. That is a limited solution but much easier. I normally build class modules that are universal, so I can build it once and use it everywhere.

If you post a strip down version, I will take a look. I am interested why the class fails, but believe I know where.


MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Dynamic row source for listbox from multi-select listbox sk84gtspd Forms 5 06-10-2015 02:57 PM
Find as you type in a listbox Alisa Forms 31 11-17-2012 12:48 AM
Type ahead with Listbox AEC General 3 05-24-2012 01:48 PM
select values in multi select listbox nitsua Forms 1 08-20-2005 01:40 PM
Select all Listbox AND Update listbox SeBasTiaan Forms 13 06-24-2005 02:03 AM




All times are GMT -8. The time now is 07:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World