Combo Box Search Issue (2 Viewers)

[bonzitre]

Hey everyone, so, I am having issues with a combo box search. I got it to work, was happy, saved, closed, when I opened it again, it is broken and won't work no matter what I do.

Background:
I am building a database that will create a file for upload for medical statistics. There are about 250 questions that are answered, but the answers are coded. There are Boolean, Date, Decimal, Integer, MultiSelect, Select, Text, Y/N/NA, etc questions. For select questions, I am using a dropdown thats row source is a table with the appropriate ID to go with the user interface answer (Gender/Sex for example, 1 is Male, 2 is Female, 3 is Unknown). I set them up this way because there are some select questions that have a TON of options and again, there are a TON of questions overall.

So, my first form, which is the Patient Demographics called FNewPatientEntry. The form record source is the table 1PatientDemographicsIDs which is where the coded responses will be stored. I have tables (Sex, race, patorien, patgenid, etc) which have the options for each question.

I have the Patient ID, dog, age, zip, etc which are storing on the table fine. I also have the multiselect for Race which is an unbound list box that stores the values on a hidden text field and then saves the values to the table. That is working fine also.

However.... Sex, Patgenid, and Patorien comboboxes are not working. They are comboboxes with the row source for each set to the appropriate table. Each table is named as above and has the columns ID and *tablename* as a column. So yes, Sex.Sex is the table/column for the Male, Female, Unk. The row source is pulling appropriately on the form and I can click and select and it stores it appropriately.

Now comes the issue. If I try to type in the box, nothing happens. It doesn't search, it doesn't expand, nothing. I changed auto expand options, etc etc.

So, I tried to code it on the OnChange event for the combobox.

Private Sub SexCB_Change()

    SexCB.RowSource = "SELECT ID, Sex FROM Sex WHERE sex like ""*" & SexCB.Text & "*"" Order by Sex;"
    SexCB.Dropdown

End Sub

I also did this with me., the table prefix, etc. None of it works. When I use this code, when I start to type it clears the row source completely and there are no options at all. It does this on the other comboboxes as well.
I tried to leave the row source empty also to try and code pulling it and that doesn't work either. So I am not sure what is going on. Any ideas?

 

[theDBguy]

Just to isolate the problem, try creating a new form or a new database and just add a simple combobox on it to test if it behaves properly. If it does, perhaps your form is corrupted.

 

[bonzitre]

Just to isolate the problem, try creating a new form or a new database and just add a simple combobox on it to test if it behaves properly. If it does, perhaps your form is corrupted.

Yep, that did it. Just a new form. Ughhhhh. Any ideas what would cause that? The code on the form they aren't working on is below. Everything else works fine too. :

Option Compare Database
Option Explicit

Private Sub chkHomeless_AfterUpdate()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim primaryKeyValue As Variant
' Get the value of the primary key control
    primaryKeyValue = Me.PatientIDTxt
' Construct the SQL statement
    sql = "SELECT * FROM 1PatientDemographicsID WHERE [patient_display_id] = " & primaryKeyValue
' Open the recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)
      If Not rs.EOF Then
      rs.Edit
       rs!homelessID = IIf(Me.ckHomeless.Value = True, 1, 0)
       rs.Update
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Private Sub dobtxt_AfterUpdate()
    Dim dob As Date
    Dim age As Integer
    
    ' Get the date of birth from the dobtxt field
    dob = Me.DOBtxt.Value
    ' Calculate the age
    age = DateDiff("yyyy", dob, Date)
    ' Adjust the age if the birthday hasn't occurred yet this year
    If Month(dob) > Month(Date) Or (Month(dob) = Month(Date) And Day(dob) > Day(Date)) Then
            age = age - 1
        End If
    ' Set the age in the agetxt field
    Me.Agetxt.Value = age
End Sub

Private Sub AsianLB_Click()
    Dim varItem As Variant
    Dim strSelectedItems As String
    
    ' Loop through the selected items
    For Each varItem In Me.AsianLB.ItemsSelected
        ' Concatenate the selected items into a string
        strSelectedItems = strSelectedItems & Me.AsianLB.ItemData(varItem) & ", "
    Next varItem
    
    ' Remove the trailing comma and space
    If Len(strSelectedItems) > 0 Then
        strSelectedItems = Left(strSelectedItems, Len(strSelectedItems) - 2)
    End If
    
    Me.asiantxt.Value = strSelectedItems

End Sub

Private Sub HawaiianLB_Click()
    Dim varItem As Variant
    Dim strSelectedItems As String
    
    ' Loop through the selected items
    For Each varItem In Me.HawaiianLB.ItemsSelected
        ' Concatenate the selected items into a string
        strSelectedItems = strSelectedItems & Me.HawaiianLB.ItemData(varItem) & ", "
    Next varItem
    
    ' Remove the trailing comma and space
    If Len(strSelectedItems) > 0 Then
        strSelectedItems = Left(strSelectedItems, Len(strSelectedItems) - 2)
    End If
    
    Me.hawaiiantxt.Value = strSelectedItems

End Sub

Private Sub ethnicysLB_Click()
    Dim varItem As Variant
    Dim strSelectedItems As String
    
    ' Loop through the selected items
    For Each varItem In Me.ethnicysLB.ItemsSelected
        ' Concatenate the selected items into a string
        strSelectedItems = strSelectedItems & Me.ethnicysLB.ItemData(varItem) & ", "
    Next varItem
    
    ' Remove the trailing comma and space
    If Len(strSelectedItems) > 0 Then
        strSelectedItems = Left(strSelectedItems, Len(strSelectedItems) - 2)
    End If
    
    Me.Ethnicystxt.Value = strSelectedItems

End Sub

Private Sub RaceLB_Click()

    Dim varItem As Variant
    Dim strSelectedItems As String
    
    ' Loop through the selected items
    For Each varItem In Me.RaceLB.ItemsSelected
        ' Concatenate the selected items into a string
        strSelectedItems = strSelectedItems & Me.RaceLB.ItemData(varItem) & ", "
    Next varItem
    
    ' Remove the trailing comma and space
    If Len(strSelectedItems) > 0 Then
        strSelectedItems = Left(strSelectedItems, Len(strSelectedItems) - 2)
    End If
    
    Me.Racetxt.Value = strSelectedItems
    
        'Unhide Asian Selection List Box
    If InStr(Me.Racetxt.Value, "3") > 0 Then
     Me.AsianLabel.Visible = True
        Me.AsianLB.Visible = True
    Else
        Me.AsianLabel.Visible = False
        Me.AsianLB.Visible = False
    End If
    
        'Unhide Hawaiian Selection List Box
    If InStr(Me.Racetxt.Value, "5") > 0 Then
     Me.hawaiianlabel.Visible = True
        Me.HawaiianLB.Visible = True
    Else
        Me.hawaiianlabel.Visible = False
        Me.HawaiianLB.Visible = False
    End If
    
        'Unhide Ethnicys Selection List Box
    If InStr(Me.Racetxt.Value, "7") > 0 Then
     Me.ethnicyslabel.Visible = True
        Me.ethnicysLB.Visible = True
    Else
        Me.ethnicyslabel.Visible = False
        Me.ethnicysLB.Visible = False
    End If
    

End Sub


Private Sub PatientIDTxt_AfterUpdate()

    On Error GoTo Errorhandler
    
    DoCmd.RunCommand acCmdSaveRecord
    
    Exit Sub

Errorhandler:
    MsgBox "It appears this Patient already exists in the system.  If this is a new event, please update the Patient ID to reflect it is a new entry.  You will be unable to save this form until this is resolved."
    
End Sub

Private Sub SexCB_Change()

    SexCB.RowSource = "SELECT Sex FROM Sex WHERE sex like ""*" & SexCB.Text & "*"" Order by Sex;"
    SexCB.Dropdown

End Sub

 

[theDBguy]

Causes of corruption can be a mystery sometimes, so having a solid backup routine is highly recommended.

 

[riktek]

This may or may not be what's at work but you do have a case of ambiguous naming that could lie at the root of your problem.

The "Sex" case may be unique in your dB but you should verify other instances of the same difficulty don't exist.

To the point, Access does best when objects have distinct names. A well-settled way of accomplishing this is to distinguish objects of different types with type-specific prefixes.

So, what's ambiguous about your schema is that you have both a table and a field named, "Sex". To distinguish the two, rename the table to "tblSex".

More broadly, the name stem, or entity, is "Sex", and build on that. So, define tblSex as having fields SexID, Sex. The form bound to the table should be frmSex and its TextBox controls should be txtSexID and txtSex. Forms bound to related tables may have combo boxes named cboSexID. The saved RowSource query for this combo box can be named qcboSexID and have fields SexID and Sex.

Just as importantly if not more so, besides the compiler, our own brains are thwarted by ambiguity. Any of us might be convinced we are referring to the field named "Sex" when our code is referring to the table named "Sex", and we can spend a week reading over the code without catching the logic error. Having a naming convention as I describe makes these kinds of mistakes far more obvious to a human reader, not to mention the compiler. The time this saves dwarfs any quibble one might have about the inconvenience of this trivial bit of rigor.

 

[bonzitre]

This may or may not be what's at work but you do have a case of ambiguous naming that could lie at the root of your problem.

The "Sex" case may be unique in your dB but you should verify other instances of the same difficulty don't exist.

To the point, Access does best when objects have distinct names. A well-settled way of accomplishing this is to distinguish objects of different types with type-specific prefixes.

So, what's ambiguous about your schema is that you have both a table and a field named, "Sex". To distinguish the two, rename the table to "tblSex".

More broadly, the name stem, or entity, is "Sex", and build on that. So, define tblSex as having fields SexID, Sex. The form bound to the table should be frmSex and its TextBox controls should be txtSexID and txtSex. Forms bound to related tables may have combo boxes named cboSexID. The saved RowSource query for this combo box can be named qcboSexID and have fields SexID and Sex.

Just as importantly if not more so, besides the compiler, our own brains are thwarted by ambiguity. Any of us might be convinced we are referring to the field named "Sex" when our code is referring to the table named "Sex", and we can spend a week reading over the code without catching the logic error. Having a naming convention as I describe makes these kinds of mistakes far more obvious to a human reader, not to mention the compiler. The time this saves dwarfs any quibble one might have about the inconvenience of this trivial bit of rigor.

Yeah, I considered this. What is weird is I did change my table to TSex so it is Tsex.id tsex.sex and the field on the main table is SexID. But this also happens on unique naming such as my list of asian ethnicities (again, this is a report for a government entity) so there is no Asian.Asian but if I create a CB with that table, it is still broken. Now, even if I create a new form, the CB won't work for searching it or typing. Doesn't matter if I try to use the core function or programatically with VBA code.

What sucks is this database has 126 tables total with various options for these questions. Some of these tables have 30+ options and a majority of them are going to be tied to a CB or LB. I didn't think this would be the part that killed me. I will say, at least it did it pretty soon after starting to build my first form, because if I had gotten all 246 questions loaded onto forms and it started I would have died.

 

[riktek]

Yeah, I considered this. What is weird is I did change my table to TSex so it is Tsex.id tsex.sex and the field on the main table is SexID. But this also happens on unique naming such as my list of asian ethnicities (again, this is a report for a government entity) so there is no Asian.Asian but if I create a CB with that table, it is still broken. Now, even if I create a new form, the CB won't work for searching it or typing. Doesn't matter if I try to use the core function or programatically with VBA code.

What sucks is this database has 126 tables total with various options for these questions. Some of these tables have 30+ options and a majority of them are going to be tied to a CB or LB. I didn't think this would be the part that killed me. I will say, at least it did it pretty soon after starting to build my first form, because if I had gotten all 246 questions loaded onto forms and it started I would have died.

You just pointed out something else that might be at work.

Ambiguity is one thing, and to be avoided, and consistency is another, to be sought.

Specifically, you're using inconsistent key field names, id in one case and SexID in another. Instead, name all key fields, both primary keys and foreign keys consistently, i.e., with identical names. The key field names should be SexID in all tables where the field occurs.

It also isn't clear why you have two tables for Sex data.

You should consider comprehensively renaming your objects, consistently with the Lesczynski-Reddick standard, and sooner than later. Before that, though, it sounds as if you're using lookup fields in some of your tables. Don't. Use a FK instead and join the tables on that field, taking care to enforce referential integrity. This may explain some or all of the combo box issues.

 

[LarryE]

You just pointed out something else that might be at work.

Ambiguity is one thing, and to be avoided, and consistency is another, to be sought.

Specifically, you're using inconsistent key field names, id in one case and SexID in another. Instead, name all key fields, both primary keys and foreign keys consistently, i.e., with identical names. The key field names should be SexID in all tables where the field occurs.

It also isn't clear why you have two tables for Sex data.

You should consider comprehensively renaming your objects, consistently with the Lesczynski-Reddick standard, and sooner than later. Before that, though, it sounds as if you're using lookup fields in some of your tables. Don't. Use a FK instead and join the tables on that field, taking care to enforce referential integrity. This may explain some or all of the combo box issues.

In addition to what others have said, if you have 126 tables, something is very very wrong with your initial design. I suggest you take a screenshot of your relationship window and post it. Maybe someone in here can make some sense out of it.

 

[Pat Hartman]

because if I had gotten all 246 questions loaded onto forms and it started I would have died.

I'm guessing you have made a lot of extra work for yourself by not properly normalizing the tables. This design pattern is similar to that of a standard survey. You have one table with a row for each question. Usually there will be a couple of fields in addition to the text of the question to allow you to group the questions into meaningful sets.

Here's a simple sample that might give you some ideas. Don't confuse efficiency of use with an export format. To export a flat record, you can use a CrossTab query.

I haven't used this in a while I think if you save the letter in the same folder as the database, the app will find the .doc file. If not you'll get an error which hopefully you can resolve by fixing a reference.

 

[mike60smart]

Yeah, I considered this. What is weird is I did change my table to TSex so it is Tsex.id tsex.sex and the field on the main table is SexID. But this also happens on unique naming such as my list of asian ethnicities (again, this is a report for a government entity) so there is no Asian.Asian but if I create a CB with that table, it is still broken. Now, even if I create a new form, the CB won't work for searching it or typing. Doesn't matter if I try to use the core function or programatically with VBA code.

What sucks is this database has 126 tables total with various options for these questions. Some of these tables have 30+ options and a majority of them are going to be tied to a CB or LB. I didn't think this would be the part that killed me. I will say, at least it did it pretty soon after starting to build my first form, because if I had gotten all 246 questions loaded onto forms and it started I would have died.

Hi
Can you upload a copy of your database with no confidential data?

 

[bonzitre]

Hi
Can you upload a copy of your database with no confidential data?

It has nothing confidential at this time. I am open to design ideas. I know enough to be dangerous. This is just the first time I have tried to build a database with the requirements that are needed for this upload.

So, BLUF, each table is a question. I am using tables for the lookup for the eventual field that will be on the various forms for entry. This is a 15 Page American Heart Association Stroke Care Record. It has a LOT of questions. I am using tables because each answer has an ID which is what will go on the upload file, not the answer itself, and the IDs are not always as simple as 1, 2, 3, 4.

I also created a Boolean, Date, Decimal, Integer, Y/N, Y/N/ND, etc tables. The ones labeled Questions are where those questions would be stored. The ones not labelled questions are to format the key field correctly (their boolean is 0, 1 unlike Access 0, -1).

The rest of them have the options for the questions that coorolate to the requirement. Initially I considered doing just a few tables and each question being it's own column but then I assumed I would have blank space on the questions with less options than the others.

The queries and forms that are there were just to start when I ran into the CB issue so luckily it was on the first form.

Open to any input. It is appreciated. Normally when I do anything in access I am building it based off our needs so it isn't nearly as chaotic. Eventually the main tables will become a sharepoint list while the tables used for lookup aren't.

Also, I am not using lookup fields. I am using the SQL on the form side to do the lookups. I did start making them lookups then decided against, so the select and multi-select tables are showing lookups, but that is going to change.

 

[GaP42]

I appreciate the complexity of the questionnaire/response needed for the medical setting. It is indeed quite challenging and you may not be up to rejigging (normalising) the structure as suggested above. 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.
The table structure below supports a range of question types: multiple choice-single answer, multiple choice - multi-answer, short answer-free text, long answer free text, likert scales, and ranking style questions. There are various attributes to group and sequence, conditionally skip questions. If you find that you need to rethink the tables, this could be used.

 

[bonzitre]

I appreciate the complexity of the questionnaire/response needed for the medical setting. It is indeed quite challenging and you may not be up to rejigging (normalising) the structure as suggested above. 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.
The table structure below supports a range of question types: multiple choice-single answer, multiple choice - multi-answer, short answer-free text, long answer free text, likert scales, and ranking style questions. There are various attributes to group and sequence, conditionally skip questions. If you find that you need to rethink the tables, this could be used.

So, I am going to try to do the following and see if it works:

1) I am renaming my tables to add Tbl after them
2) inside each table, the like named column will have Opt added after it to designate it is the list of options
3) I am rebuilding any table that uses a lookup to remove that and just make them an integer storage.
4) I will transfer all the tables to a new database and go from there to hopefully rid myself of any bad juju that is in that database.

hopefully that will help.

 

[bonzitre]

So, I am going to try to do the following and see if it works:

1) I am renaming my tables to add Tbl after them
2) inside each table, the like named column will have Opt added after it to designate it is the list of options
3) I am rebuilding any table that uses a lookup to remove that and just make them an integer storage.
4) I will transfer all the tables to a new database and go from there to hopefully rid myself of any bad juju that is in that database.

hopefully that will help.

So, each of these tables have the final name the answer has to be under. 1PatientDemo is the main table with patient demographics where the patient ID is assigned by the user. Each of the tables has a patient_display_ID field to be the child key. Date, Decimal, Text tables, each field is it's respective data type. Select, Boolean, and the yes/no/other options are all number fields to store the selection when it has been made. The Multiselect table are all short text which will store the string for the multi-select. For multi-select I plan to make List Boxes and a hidden text field that stores the selection from the list box and then saves that string to the table.

My tables have been updated to have a suffix tbl and the corresponding field under the table has the Opt suffix

Each table has the available options with the required ID per the entity these get uploaded to.

I will use sql lookups for the CB or LBs and not use the lookup wizard.

I will now cross my fingers and hope that as I make my form, my CB search works.......

 

[bonzitre]

So, even after all that, without doing code, the CB search function breaks....

 

[mike60smart]

So, even after all that, without doing code, the CB search function breaks....

Can you upload the database so we can see the problem

 

[Gasman]

I use prefix not suffix.

 

[bonzitre]

Here it is. Let me know if you see anything I am doing that is messed up. I have never had any issue like this before and it is driving me crazy.

 

[mike60smart]

Here it is. Let me know if you see anything I am doing that is messed up. I have never had any issue like this before and it is driving me crazy.

Why are your PK and all your related FK's set as text Data Types?

 

[mike60smart]

On your FNewPatient Form where is the Search Code located?