Combo Box Search Issue (1 Viewer)

Why are your PK and all your related FK's set as text Data Types?
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?
 
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 ?
 
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.
 
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 ?
 

Attachments

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.
 
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?
 
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.
 
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.
 
Pat's arrangement supports a simple multiple choice survey style arrangement that will not meet your needs directly, however it is the basis for a more sophisticated design.
If you notice, each question can have a different list of responses and the answer allows for both a selection and a text answer.

I can't post my medical example which is a questionnaire used to help the visiting nurses decide on what types of home health aid a new client will need. In the medical example, there are three variations of the questionnaire and of course because the schema is properly normalized, an "infinite" number are possible. The medical version also has two levels of grouping to allow for logical breaks and coherent reporting.

@bonzitre I know that you really don't want to hear this. You've done a ton of work but your new schema is way off the mark and I suggest that you go back to square one. Throw it all out. Every bit of it. Chalk it up to learning how to properly normalize a schema. Your design will be extremely difficult to work with and will require constant maintenance as the survey changes over time. You are making a maintenance nightmare for yourself.

Do yourself a favor and try to create a survey with your questions but only go with a dozen or so so you see how it comes together. You will probably want a slightly more sophisticated schema than my teaching example but once you understand the teaching example, you will better understand how to proceed with this project.

Each column in tables 2-11 should be a row in a single table.

Regarding your naming of columns. Do not use all lower case or all upper case for that matter. Use CamelCase so you can distinguish individual words. Of course I'm looking at column names that shouldn't exist but I see that you are also using all lower case in the other tables.

Looks like you have a slew of tables with value lists. I'm attaching a mini-app I use in all my applications. It provides a simple way to manage all your simple lookup lists in ONE table and one set of forms. When I use this in my apps, I can also allow an admin in the user department to manage the lists so they can add new items or change the names of existing ones. Deletes are not allowed. If you don't want to use an item going forward, you mark it as inactive. That doesn't break old surveys but with a couple of lines of code, prevents the user from using the inactive value for new surveys.


I don't use the table-maintenance mini-app in the survey because the survey sample used an embedded method and I didn't want to rebuild the sample to incorporate the table-maintenance mini-app but once you understand the concept, you can decide which method would be best. If the "lists" are reused in multiple places then it will be less work for you to use the table-maintenance concept. If all the combos are unique then it doesn't matter so you can use either.
 
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.
 
@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. 😢
Code:
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.
 
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


Response.png
 

Attachments

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



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.

AutoExpand.PNG



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 2600 row table.
 

Attachments

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



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.

AutoExpand.PNG



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 2600 row table.
 

Attachments

Users who are viewing this thread

Back
Top Bottom