Combo Box Search Issue

[mike60smart]

Because I was trying to use the unique ID that has to be given to each patient that is alphanumeric up to 100 characters. Letter prefix followed by their internal patient ID.

What do you mean by search code?

Hi
Normally every table would have an Autonumber Primary Key.

If you need to enter a Unique Patient Number, then this would be a field in your Patient Table.

As for the search Code, which Combobox Control are you trying to search on ?

 

[bonzitre]

Hi
Normally every table would have an Autonumber Primary Key.

If you need to enter a Unique Patient Number, then this would be a field in your Patient Table.

As for the search Code, which Combobox Control are you trying to search on ?

I am trying to make it searchable for all of them so they don't have to click the box for every option. This way they can tab, enter a few letters, move on. The one I have been working on first is the CBSex which gets it's values from SexTbl. But ANY of them. What is weird is everything was fine, it was working initially, then stopped. The only code I have done in this new DB is the code for that CB.

 

[mike60smart]

I am trying to make it searchable for all of them so they don't have to click the box for every option. This way they can tab, enter a few letters, move on. The one I have been working on first is the CBSex which gets it's values from SexTbl. But ANY of them. What is weird is everything was fine, it was working initially, then stopped. The only code I have done in this new DB is the code for that CB.

I replaced your Sex Combobox with a standard Combobox

When you Tab into the Sex Control and type the first Letter of the required Sex it autopopulates.

All very normal. Why go down the route of lots of VB ?

 

[bonzitre]

I replaced your Sex Combobox with a standard Combobox

When you Tab into the Sex Control and type the first Letter of the required Sex it autopopulates.

All very normal. Why go down the route of lots of VB ?

Because it isn't working even without the code for me. The other CB on that page have no code behind them and they were not functioning correctly either.

 

[bonzitre]

I replaced your Sex Combobox with a standard Combobox

When you Tab into the Sex Control and type the first Letter of the required Sex it autopopulates.

All very normal. Why go down the route of lots of VB ?

So, when I open the file you added, the search isn't working for me. Does it still work for you if you reopen it?

 

[mike60smart]

So, when I open the file you added, the search isn't working for me. Does it still work for you if you reopen it?

It works just fine for me.

In the attached I redid all of the Combobox's and everything works as it should

 

[bonzitre]

It works just fine for me.

In the attached I redid all of the Combobox's and everything works as it should

Ok. Well that means there is a client side issue with my computer then. I am having some other people try to open it and we will see if it is our work computers/MS version, or if it is just me.

 

[bonzitre]

It works just fine for me.

In the attached I redid all of the Combobox's and everything works as it should

Could you try downloading the file you uploaded and test again? I tested on my personal computer at home and the search isn't working on this computer either.

 

[mike60smart]

Could you try downloading the file you uploaded and test again? I tested on my personal computer at home and the search isn't working on this computer either.

Hi
Tested the file after downloading and it does not work.

You need to recreate the Form as it must be corrupted.

 

[MajP]

@bonzitre
I do not want to be late to the game here, but do you really not want to fix this? Please consider the suggestions. @Pat Hartman and @GaP42 provided some useable approaches.

I would cry if I had to develop a database this way or I was the person inheriting this to maintain it. Seriously, I am sad for you for the immense waste of time and effort you have put in so far. Everything you do for now on is going to be way harder than necessary. Correct me if I am wrong I think there is close to a hundred tables of nothing but responses. I ran this query and got 123 tables.

SELECT Count(msysobjects.[name]) AS CountOfname, msysobjects.Type
FROM msysobjects
GROUP BY Right([name],3), msysobjects.Type
HAVING (((Right([name],3))="tbl") AND ((msysobjects.Type)=1));

You have a lot of good help here, but to me it looks like we are just helping you slice your wrists. Yes, you can make this work with brute force and effort, but you have to maintain it too. Future updates, changes, additions will be painful. Add a question add a table?

IMO If you want help, I think we should be helping you restructure it. It would not take that long. Even if it took a few days it will save months of work in the future. With these survey type databases often the best solution are more de-normalized than normalized. I would argue this is more normalized. An example is an entity attribute value model which is more denormalized, but it take many tables and make into one. There are trade offs but they can vastly reduce workload and maintenance.


My two cents.

 

[MajP]

If interested to get you started on the correct path to enlightenment. I ran some code to put all responses tables (>100) into a single table.
There are 2685 responses. I kept the old table name to help Identify what the Response Category is.
Even being non familiar with the subject matter, I can figure out in many cases what a descriptive category name is. Some I cannot.
However notice that you unnecessarily repeat response sets. So this list can get even smaller. You have a separate table for ambulance assistance for admittance and prior with all the same responses. Even in your current design that is not necessary. Either way you can clean this list up further by removing duplicate response sets. Only Need a single "Ambulate Assistance" category and you can respond to an admit, prior, post question.

Now you can categorize the response sets
Assign a response value if needed
Assign a sort order that you want the response to appear
Assign a type (Text, Boolean, Numeric)

A lot of the patient demographic information probably remains in individual tables. Especially those fields you query a lot, but a lot is just questions to surveys and can remain related to this single response choice as shown by @Pat Hartman and @GaP42

 

[MajP]

You need to recreate the Form as it must be corrupted.

I tried to recreate and even make a new form and still no luck. I think the autoexpand issue seems to be at the application level. I could not fix it whatever I tried. I do not know if this is one of the database settings.

So for everyone, this is the current form and if I type F there is no autoexpand

I had to import the tables and forms into a new db. Now in my new db I already got rid of 100+ tables and used my new table. I added a dropdown too so that it automatically drop down, but none of that was the solution. It still did not autoexpand until importing into new db.

I am still not sure on tables 1 to 10 approach. You are now handling 100s of columns in 10 tables that could be done in a handful of columns in one maybe two tables. This is way more manageable but still unmanageable in my opinion.
Again I would sacrifice over normalization for manageability.

Also my form now draws choices from the single 2000 row table.

 

[MajP]

I realize the provider table got imported into the response list. That should remain its own table IMO so I recreated it.
With that said there may be times where you really want to search a very long combo like the Provider combo. Then you may want a find/filter as you type capability. See the demo. Using the class module it only takes one line of code. In the list of 100s of providers I think the doctor's first name or last name is "Martin". This is very different from the default functionality.