Combo Box Search Issue

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