Display records by typing in "Like" items in combobox on Split Form

[Tammysdatabase]

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:

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]

Hi Tammy. Not sure I follow without seeing your form. Can you post some screenshots please? Thanks.

 

[Tammysdatabase]

Sure thing....

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

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

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]

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 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.

 

[Tammysdatabase]

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?

 

[Tammysdatabase]

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]

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.

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.

Emulating the split form

Like many users of this forum, I abandoned split forms a long time ago as I found them difficult to adapt if you want to modify them in any way In a recent thread another user wanted to keep the idea but to emulate it using a single form with a continuous subform. See this thread...

www.access-programmers.co.uk

So when you type WH it should return all records with WH then when you type WHI should return all records with WHI

 

[Tammysdatabase]

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.

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.

Emulating the split form

Like many users of this forum, I abandoned split forms a long time ago as I found them difficult to adapt if you want to modify them in any way In a recent thread another user wanted to keep the idea but to emulate it using a single form with a continuous subform. See this thread...

www.access-programmers.co.uk

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]

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.

 

[Tammysdatabase]

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]

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]

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

Solved - ComboBox list items not selectable

Hi I have an issue two almost identical forms, with an unbound combobox being used to filter a form. The combobox recordsource is a query that pulls document types from the main table I have googled and tried all the suggestions the form is not locked, edits are allowed etc I have tried...

www.access-programmers.co.uk

 

[Pat Hartman]

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.