Querying One Combo Box in Another

[AllusiveKitten]

Hi

I am hoping someone can help me, I have a form with two combo boxes in it.... The first combo box gets its values from a table via an SQL query in the combo box properties. The second combo box is supposed to get its values also via an SQL query in its properties but it is depending on the result of the first combo box...

ie. First combox has the names of all of the people who have received a document. (This combo box works perfectly well)

The second combo box is to show only the dates the person from the first combo box has received a document. These values come through correctly when you first select a name, but when you change the first combo box, the second one does not update the list... Also when you select the date in the second combo box I receive the error "The Value you have entered isn't valid for this field. You may have entered a text in a numeric field"…. This I do not understand as I have not stipulated any formatting in the combo box.

Any assistance will be greatly appreciated.

Thank you
AK

[ADezii]

Hi

I am hoping someone can help me, I have a form with two combo boxes in it.... The first combo box gets its values from a table via an SQL query in the combo box properties. The second combo box is supposed to get its values also via an SQL query in its properties but it is depending on the result of the first combo box...

ie. First combox has the names of all of the people who have received a document. (This combo box works perfectly well)

The second combo box is to show only the dates the person from the first combo box has received a document. These values come through correctly when you first select a name, but when you change the first combo box, the second one does not update the list... Also when you select the date in the second combo box I receive the error "The Value you have entered isn't valid for this field. You may have entered a text in a numeric field"…. This I do not understand as I have not stipulated any formatting in the combo box.

Any assistance will be greatly appreciated.

Thank you
AK

Refer to this Tutorial for help on the Subject:
Cascading Combo Boxes

[AllusiveKitten]

Hi,

Thank you so much for your advice it has helped me alot. I have come up with the following coding..

Private Sub Txt_ProjectOffice_AfterUpdate()
  With Me!Txt_DateSent
    If IsNull(Me!Txt_ProjectOffice) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.[Project/OfficeName], Tbl_DespactchedDocuments.Date HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_ReceivedTransmittal] '" & Txt_ProjectOffice & "')));"
 
    End If
    Call .Requery
  End With
End Sub

Which is now giving me a syntax error (missing operator) in expression
(((Tbl_Despactc hedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_Receiv edTransmittal] '" & Txt_ProjectOffi ce & "')));"

I have tried a million different ways to no avail, can you see where I am going wrong??

Thank you again for your help
AK

[ADezii]

Hi,

Thank you so much for your advice it has helped me alot. I have come up with the following coding..

Private Sub Txt_ProjectOffice_AfterUpdate()
  With Me!Txt_DateSent
    If IsNull(Me!Txt_ProjectOffice) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.[Project/OfficeName], Tbl_DespactchedDocuments.Date HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_ReceivedTransmittal] '" & Txt_ProjectOffice & "')));"
 
    End If
    Call .Requery
  End With
End Sub

Which is now giving me a syntax error (missing operator) in expression
(((Tbl_Despactc hedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_Receiv edTransmittal] '" & Txt_ProjectOffi ce & "')));"

I have tried a million different ways to no avail, can you see where I am going wrong??

Thank you again for your help
AK

Try:

Private Sub Txt_ProjectOffice_AfterUpdate()
  With Me!Txt_DateSent
    If IsNull(Me!Txt_ProjectOffice) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.[Project/OfficeName], Tbl_DespactchedDocuments.Date HAVING Tbl_DespactchedDocuments.[Project/OfficeName]='" & [Forms]![FrmEntry_ReceivedTransmittal] & "' " & "'" & Txt_ProjectOffice & "';"
 
    End If
    Call .Requery
  End With
End Sub
 

[AllusiveKitten]

Hi ADezii

I have sort of got the combo boxes working together with the following coding:

Private Sub Txt_ProjectOffice_AfterUpdate()
  With Me!Txt_DateSent
    If IsNull(Me!Txt_ProjectOffice) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.[Project/OfficeName] HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName])='" & Txt_ProjectOffice & "'));"
 
    End If
    Call .Requery
  End With
End Sub

I have the second combo box bring back the correct number of records, except the actual lines are empty. Do you have any ideas what could be going wrong?

Thank you again
AK

[ADezii]

Hi ADezii

I have sort of got the combo boxes working together with the following coding:

Private Sub Txt_ProjectOffice_AfterUpdate()
  With Me!Txt_DateSent
    If IsNull(Me!Txt_ProjectOffice) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.[Project/OfficeName] HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName])='" & Txt_ProjectOffice & "'));"
 
    End If
    Call .Requery
  End With
End Sub

I have the second combo box bring back the correct number of records, except the actual lines are empty. Do you have any ideas what could be going wrong?

Thank you again
AK

How do you know the correct number of Records are being returned by the 2nd Combo Box if the lines are empty? If you are absolutely sure, check the ColumnWidths Property. A setting of 0 in any Column will hide/make invisible the Column as in: a 4 Column Combo Box with Column Widths of 1";1.5";0";2 " will make the 3rd Column invisible. Also, check the ColumnWidths against the ColumnCount Property, in the above case, ColumnCount should = 4. Let me know how you make out.

[AllusiveKitten]

Hi again Adezii,

I can see the number of lines in the drop down box, eg if it only one value is returned then there is only one line in the drop down box and if there is 3 values returned there is 3 lines in the drop down etc.

Also there should only be one column returned as I am only calling the Date's, I have checked both the column widths and also expanded it to 4 columns and made the widths large, still this is not working. Is there a way that I can send a copy to you so that you can see rather than me trying to explain it??

And again, thank you for your help
AK

[ADezii]

Hi again Adezii,

I can see the number of lines in the drop down box, eg if it only one value is returned then there is only one line in the drop down box and if there is 3 values returned there is 3 lines in the drop down etc.

Also there should only be one column returned as I am only calling the Date's, I have checked both the column widths and also expanded it to 4 columns and made the widths large, still this is not working. Is there a way that I can send a copy to you so that you can see rather than me trying to explain it??

And again, thank you for your help
AK

Yes you can. I'll send you my E-Mail address in a Private Message. Reply and send me the Database as an Attachment. Please make sure there is no sensitive data contained within it.

[ADezii]

AllusiveKitten (love that Name!), I decided to post the possible solution here so that all Members may see it.

1. Change the Row Source for the TxtProjectOffic e Combo Box to:
   

   SELECT DISTINCT Tbl_DespactchedDocuments.[Project/OfficeName] FROM Tbl_DespactchedDocuments WHERE Len([Project/OfficeName])>"0" ORDER BY Tbl_DespactchedDocuments.[Project/OfficeName]; 

2. Change the code in the AfterUpdate() Event of the TxtProjectOffic e Combo Box to:
   

   Private Sub Txt_ProjectOffice_AfterUpdate()
   Dim MySQL As String
    
   MySQL = "SELECT Distinct [Date] FROM Tbl_DespactchedDocuments WHERE Tbl_DespactchedDocuments.[Project/OfficeName] " _
           & "='" & Me!Txt_ProjectOffice & "' Order by Tbl_DespactchedDocuments.Date;"
    
   With Me!Txt_DateSent
     If IsNull(Me!Txt_ProjectOffice) Then
       .RowSource = ""
     Else
       .RowSource = MySQL
     End If
       Call .Requery
   End With
   End Sub

3. I think this is what you are looking for, if not just let me know.

[AllusiveKitten]

AllusiveKitten (love that Name!), I decided to post the possible solution here so that all Members may see it.

1. Change the Row Source for the TxtProjectOffic e Combo Box to:
   

   SELECT DISTINCT Tbl_DespactchedDocuments.[Project/OfficeName] FROM Tbl_DespactchedDocuments WHERE Len([Project/OfficeName])>"0" ORDER BY Tbl_DespactchedDocuments.[Project/OfficeName]; 

2. Change the code in the AfterUpdate() Event of the TxtProjectOffic e Combo Box to:
   

   Private Sub Txt_ProjectOffice_AfterUpdate()
   Dim MySQL As String
    
   MySQL = "SELECT Distinct [Date] FROM Tbl_DespactchedDocuments WHERE Tbl_DespactchedDocuments.[Project/OfficeName] " _
           & "='" & Me!Txt_ProjectOffice & "' Order by Tbl_DespactchedDocuments.Date;"
    
   With Me!Txt_DateSent
     If IsNull(Me!Txt_ProjectOffice) Then
       .RowSource = ""
     Else
       .RowSource = MySQL
     End If
       Call .Requery
   End With
   End Sub

3. I think this is what you are looking for, if not just let me know.

Hi ADezii,

You are an absolute treasure, the code works for bringing back the required results, but now I have the problem that the combo box/text box does not like the entry...

"The Value you entered isn't valid for this field"
**For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.**

I am stumped!!