I need to be able to type in text box and for the list box to show me all similar text. No VBA )-:

[Ecc_9:10]

Hi all,

I am quite new to Access and the VBA world but managed to create a lovely form using VBA in excel. My work place has disabled macros so when I took the file to work it would not work. I decided I would use Access (better for what we are doing anyway). I have recreated the form however the search option I originally used was a combo box with a command (search button). You start typing in the combo box and when the correct item is chosen in the list you press search and the form populates with specific fields for that record.

The Problem
The problem with this is when there are two similar texts in the combo list. The form populates record 1 as a default. This is very annoying. In the excel form I created using VBA you could type in text box and the list would decrease showing all the possible options that match the text in the listbox. From there you could choose the correct one.

Is there anyway I can recreate this using only the macro builder (No VBA) in Access?

 

[Pat Hartman]

Access works the same way if you are not using any code to alter the behavior.

 

[Ecc_9:10]

Access works the same way if you are not using any code to alter the behavior.

Sorry are you saying it will work or it won't work. It was working perfectly in excel but in Access I don't know how to do this without VBA code unless I use the combo box method and that method is glitchy.

 

[The_Doc_Man]

Pat's comment was that Access VBA and Excel VBA both are VBA and work the same way. The control objects (such as combo boxes or text boxes) work the same way. The difference between Access VBA and Excel VBA is whatever specific object the VBA code is working on, not VBA itself.

However, your question contains the seeds of its own doom in this limited sense.

My work place has disabled macros so when I took the file to work it would not work.

Is there anyway I can recreate this using only the macro builder (No VBA) in Access?

The Access macro builder will produce something that ALSO would be useless if macros are disabled. Your better bet would be to inquire as to whether your office rules allow you to declare a "trusted location" which would allow you to run macros if the DB file is in that location.

 

[theDBguy]

Hi. Welcome to AWF!

It would be hard to work with Access without VBA enabled, but I suppose some functionalities are possible using only macros. If you need help with your macros, consider posting a sample db.

 

[Pat Hartman]

@Lah_01 If you have no code that is altering the behavior of a combo or listbox, when you type into the box, the controls fills as you type. It doesn't automatically drop so maybe that is what you are asking how to do. You always just tab to fill with the rest of the current value.

 

[Ecc_9:10]

@Pat Hartman Yes that's what I want. A textbox that controls the filtering of a list box, but I am not able to use the code builder. Functionality of the form only works when I use the macro builder and I am not quite sure how to achieve what I want to using the macro builder.

@theDBguy I have attached a sample DB with one form of what I am trying to achieve, and another showing what I currently have. (The trying to achieve form has a text box that isn't linked to the list because I do not know how to do it)

You will notice that the 'what I currently have' form is glitchy. When you click the search button it defaults to record 2 and then starts to work when you go down the list and choose an option (sometimes!). I think this may be sure to the fact that I set the data entry field in form properties to yes. I had to do this so when you open the form you get blank boxes.

This form is not for data entry it is just to view a record based on what the user chooses or types in the text box.

I need for it to do the following:-
1) Be blank when its opened.
2) User is able to type in the search text box, which filters a list , then you can choose correct item.
3) Form only populates when an option from a list has been chosen and the search button is clicked

This I managed to do with VBA in excel . With Access however I can't used VBA.

I hope this makes sense.

 

[Gasman]

This I managed to do with VBA in excel . With Access however I can't used VBA.

I hope this makes sense.

Not to me? Why are you able to use VBA in Excel but not Access?

 

[Pat Hartman]

I don't create macros.

This is the single line of VBA that does what you want.

Private Sub combo_Search_GotFocus()
    Me.combo_Search.Dropdown
End Sub

It goes in the GotFocus event. There may or may not be a way to do this with a macro.

 

[Ecc_9:10]

Not to me? Why are you able to use VBA in Excel but not Access?

Sorry Excel using my own personal excel account. When I used the work excel nothing worked so I started using Access and only the macro builder.

 

[Gasman]

Look and see if you have trusted locations in Access for where your db is.
Otherwise see your it dept for access to vba.
You cannot be expected to write code if your hands are bound behind you.

 

[tvanstiphout]

As already mentioned above: you need to discuss with higher-ups what computing environment will be available to you. "None" is not a good answer, but if that IS the answer, then you may want to reconsider if this is the best company for you.
Of course before you talk to them, you read up on macro security and on Trusted Locations, so you can have an intelligent discussion. Did you know VBA projects can now be digitally signed? No? Don't even know what that really means? You should, before you have that discussion.

 

[Ecc_9:10]

I don't create macros.

This is the single line of VBA that does what you want.

Private Sub combo_Search_GotFocus()
    Me.combo_Search.Dropdown
End Sub

It goes in the GotFocus event. There may or may not be a way to do this with a macro.

Hi Pat,

I tried this but it's not doing what I wanted it to do.

Also update ...I now can use VBA as I have been given some the permission to do so.

This is how I wanted the combo box to work:-

For example if the list contains the following:

Avengers 1- (AV1)
Avengers 2 (AV2)
Avengers 3 (AV3)
Batman 1 (BM1)
Batman 4 ( BM4)

And I type in AV I would want the drop down to show all the Avengers option until I type the specific number, and then it would show me Avengers 2(AV2) if I typed 2 next.

I'm guessing now that I can use VBA I should use separate text box and list and then use VBA code to control the search? This is what I did in excel.

 

[Ecc_9:10]

Look and see if you have trusted locations in Access for where your db is.
Otherwise see your it dept for access to vba.
You cannot be expected to write code if your hands are bound behind you.

I have done this and have managed to get access to use VBA I have just checked and seems to be working now.

 

[GPGeorge]

Hi Pat,

I tried this but it's not doing what I wanted it to do.

Also update ...I now can use VBA as I have been given some the permission to do so.

This is how I wanted the combo box to work:-

For example if the list contains the following:

Avengers 1- (AV1)
Avengers 2 (AV2)
Avengers 3 (AV3)
Batman 1 (BM1)
Batman 4 ( BM4)

And I type in AV I would want the drop down to show all the Avengers option until I type the specific number, and then it would show me Avengers 2(AV2) if I typed 2 next.

I'm guessing now that I can use VBA I should use separate text box and list and then use VBA code to control the search? This is what I did in excel.

All of the comments about using or not using VBA or macros aside, your issue here can be handled with a bit of creative thinking.

You want to type in AV2 or BM1 and have that choice selected.

Create an additional column, after the bound column (which should be bound to the appropriate Primary Key), and before the fully written out name. In this helper column, enter the letters and numbers on which you want to match, e.g. AV1 or BM4. Do not include the parenthesis.

Set the visible width of this helper column to some narrow, but non-zero width, such as .01 -- you might need to experiment a bit to get a width that works, but is not apparent to the user.

Now, the match as you type function will operate on that helper column, not on the full text column.

The downside, of course, is that ONLY the values in the helper column can be used to search. E.g., if the user types "Ave..." nothing will match that. It may or may not be a better user experience, so do some testing with users before deploying.

 

[Gasman]

If you want to type in av2, then they need to be at the start of the text, not the end

Or search here for @MajP 's classes for combos

 

[GPGeorge]

If you want to type in av2, then they need to be at the start of the text, not the end

Or search here for @MajP 's classes for combos

That's why I suggested a very narrow "helper" column in that first position, before the full text value. Access will use it even though the user should not be aware of its existence. I also noted the main drawback to that approach. This one is .05" wide, for example.

 

[The_Doc_Man]

I've never had trouble with using a column in a combo box where the visible width was 0.00" - it just doesn't show up but you can still use it in a program by selecting the appropriate column number (remembering, of course, that Access frequently starts its numbering from zero.

 

[GPGeorge]

I've never had trouble with using a column in a combo box where the visible width was 0.00" - it just doesn't show up but you can still use it in a program by selecting the appropriate column number (remembering, of course, that Access frequently starts its numbering from zero.

A 0 width may work for VBA, but I'd have to revisit it to verify that it would be effective for this requirement.

 

[Pat Hartman]

And I type in AV I would want the drop down to show all the Avengers option until I type the specific number, and then it would show me Avengers 2(AV2) if I typed 2 next.

You have to type what the list shows. So "Aveng" that will limit the list.

If the users know the codes, then you can change what the first visible column is and let them type that, but then you might want to show the text in a separate control. Another option is to concatenate the two fields and use that as the visible field for the control. REMEMBER with a combo, the visible field is not necessarily the bound field and in most applications, you will see that the bound field is hidden and a different text field is what a combo shows.