Display records by typing in "Like" items in combobox on Split Form (1 Viewer)

Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
Hi all,

I have developed a PO system in my database. It's all working well, but I have a "Search POs" form that i've developed where the user can select different comboboxes to search the PO items in different ways. I have used an If LEN() statement in the "Items" combobox and this works well for finding records that have a certain string in them, however, I would like all the records with this certain string listed in the datasheet portion of the split form.

For example:

If I choose to search by item, I engage the combobox and type in "Dover White". The combobox finds all the possible records with Dover White in them and I can choose which record I want to search by. This is great. But.....I would like to just search all the records by Dover White and have it display all the records on the datasheet instead of just within the confines of the combobox dropdown. This way the user can see all records and choose which PO they would like to look at by selecting the hyperlink to open the PO form to that record.

The code I used for the combobox in the KeyUp Event is:

Code:
If Len(Me.Combo98.Text) > 2 Then
    Me.Combo98.RowSource = "SELECT Catalogue.Item From Catalogue WHERE Catalogue.Item LIKE '*" & Me.Combo98.Text & "*'"
    
    Me.Combo98.Dropdown
Else
    Me.Combo98.RowSource = ""
End If

Is there a way to adjust this to be able to type in Dover White and have all the records with Dover White show up in the datasheet (Split form)?

We are switching from an internet program to this access database and of course the internet program will do this type of search so the expectation is that the new database will also do this.

Any help would be appreciated.

Tammy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,358
Hi Tammy. Not sure I follow without seeing your form. Can you post some screenshots please? Thanks.
 
Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
Sure thing....

Here's the form (I've hidden a couple of columns for privacy purposes)
Form1.jpg



I've typed in "Dover White".......The dropdown shows all the records that have "Dover White" in the description. That's great.
Form2.jpg


But......I want the records in the datasheet to actually show all the records that have "Dover White" instead of choosing one record to filter by.

You see.......I had to export csv files from the internet program so that I could get all this historical data to use for searching. In some cases the item description was typed in several different ways. We would also order different types of material, but with the same "Dover White" descprition. My users would like to be able to get the list so they can see all the associated purchase orders with Dover White in the description. From this list they can also see the prices and can choose to open the actual purchase order by clicking the hyperlink. This of course was the capability on the snazzy internet program so they would like the same on here......is it even possible?
 

isladogs

MVP / VIP
Local time
Today, 11:54
Joined
Jan 14, 2017
Messages
18,186
Unfortunately your screenshots are low resolution and almost illegible ...at least for me. Zooming just blurs them still further.
Can you provide better screenshots?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:54
Joined
May 21, 2018
Messages
8,463
You have set it up so that you are filtering the combobox to Dover White. You could also have a generic textbox that filters the actual records to Dover White.
 
Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
You have set it up so that you are filtering the combobox to Dover White. You could also have a generic textbox that filters the actual records to Dover White.
Not sure I follow. How would I set that up?
 
Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
Unfortunately your screenshots are low resolution and almost illegible ...at least for me. Zooming just blurs them still further.
Can you provide better screenshots?
The screen shots have been cropped. I work on two monitors. Not sure how I would get you better resolution.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:54
Joined
May 21, 2018
Messages
8,463
If you had an unbound textbox in your header.
Then the code might be something like this in the on change event of the text box.

Code:
If Len(Me.TxtSearch.Text) > 2 Then
  dim strFilter as string
strFilter =  "Item LIKE '*" & Me.TxtSearch.Text & "*'"
me.filter = strFilter
me.filterOn = true

I would never use a split form, because they suck and what is simple becomes hard. I would use an emulated split form.

So when you type WH it should return all records with WH then when you type WHI should return all records with WHI
 
Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
If you had an unbound textbox in your header.
Then the code might be something like this in the on change event of the text box.

Code:
If Len(Me.TxtSearch.Text) > 2 Then
  dim strFilter as string
strFilter =  "Item LIKE '*" & Me.TxtSearch.Text & "*'"
me.filter = strFilter
me.filterOn = true

I would never use a split form, because they suck and what is simple becomes hard. I would use an emulated split form.

So when you type WH it should return all records with WH then when you type WHI should return all records with WHI
Code works great, but the text disappears as you type. Might be a little confusing for a novice keyboarder. How can i keep the text from doing this? I'll try both scenarios and see which works best for this application. Appreciate the help!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:54
Joined
May 21, 2018
Messages
8,463
FYI. That was completely untested. I just typed some stuff. I have no idea how that will work with a split form. That is just a general idea of what to do.
 
Local time
Today, 05:54
Joined
Mar 18, 2020
Messages
34
FYI. That was completely untested. I just typed some stuff. I have no idea how that will work with a split form. That is just a general idea of what to do.
I did manage to catch the missing "End If".....LOL I'll keep playing with it. Thanks again.
 

isladogs

MVP / VIP
Local time
Today, 11:54
Joined
Jan 14, 2017
Messages
18,186
The screen shots have been cropped. I work on two monitors. Not sure how I would get you better resolution.
I use two monitors & crop images as necessary. I don't have that problem.
Anyway, sorry but I can't read it so can't assist.
 

Lanser

Registered User.
Local time
Today, 11:54
Joined
Apr 5, 2010
Messages
60
Hi Tammy,
If you look at the Database I uploaded to it contains a search form that filters on multiple fields and narrows as you type look for the frm_searchMulti and the code behind the text boxes. I can't claim the code but was so long ago I can't remember where I found it.

regards
John

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,981
You can do this a couple of ways. One way is to use a listbox on an unbound form. You can enlarge it to show the columns that will help the user to choose. Then you have a subform in single view that shows the selected record for editing. The RecordSource for the subform would reference the listbox.

Select ... From ... Where ThePK = Forms!mainform!lstChoose

Then in the click event of the listbox, you requery the subform.

Me.sfrmDetails.Requery

OR, instad of a listbox, you can use another subform with filters. This subform should have its "Allow" properties set to no to prevent updating.
 

Users who are viewing this thread

Top Bottom