By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Querying One Combo Box in Another

AllusiveKitten
P: 43
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
Oct 10 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,607
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
Oct 11 '07 #2

AllusiveKitten
P: 43
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_ProjectOffice_AfterUpdate()
  2.   With Me!Txt_DateSent
  3.     If IsNull(Me!Txt_ProjectOffice) Then
  4.       .RowSource = ""
  5.     Else
  6.       .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 & "')));"
  7.  
  8.     End If
  9.     Call .Requery
  10.   End With
  11. End Sub
Which is now giving me a syntax error (missing operator) in expression
(((Tbl_DespactchedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_ReceivedTransmittal] '" & Txt_ProjectOffice & "')));"

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
Oct 11 '07 #3

ADezii
Expert 5K+
P: 8,607
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_ProjectOffice_AfterUpdate()
  2.   With Me!Txt_DateSent
  3.     If IsNull(Me!Txt_ProjectOffice) Then
  4.       .RowSource = ""
  5.     Else
  6.       .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 & "')));"
  7.  
  8.     End If
  9.     Call .Requery
  10.   End With
  11. End Sub
Which is now giving me a syntax error (missing operator) in expression
(((Tbl_DespactchedDocuments.[Project/OfficeName]=[Forms]![FrmEntry_ReceivedTransmittal] '" & Txt_ProjectOffice & "')));"

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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_ProjectOffice_AfterUpdate()
  2.   With Me!Txt_DateSent
  3.     If IsNull(Me!Txt_ProjectOffice) Then
  4.       .RowSource = ""
  5.     Else
  6.       .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 & "';"
  7.  
  8.     End If
  9.     Call .Requery
  10.   End With
  11. End Sub
  12.  
Oct 11 '07 #4

AllusiveKitten
P: 43
Hi ADezii

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_ProjectOffice_AfterUpdate()
  2.   With Me!Txt_DateSent
  3.     If IsNull(Me!Txt_ProjectOffice) Then
  4.       .RowSource = ""
  5.     Else
  6.       .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.[Project/OfficeName] HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName])='" & Txt_ProjectOffice & "'));"
  7.  
  8.     End If
  9.     Call .Requery
  10.   End With
  11. 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
Oct 12 '07 #5

ADezii
Expert 5K+
P: 8,607
Hi ADezii

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_ProjectOffice_AfterUpdate()
  2.   With Me!Txt_DateSent
  3.     If IsNull(Me!Txt_ProjectOffice) Then
  4.       .RowSource = ""
  5.     Else
  6.       .RowSource = "SELECT Txt_DateSent FROM Tbl_DespactchedDocuments GROUP BY Tbl_DespactchedDocuments.Date, Tbl_DespactchedDocuments.[Project/OfficeName] HAVING (((Tbl_DespactchedDocuments.[Project/OfficeName])='" & Txt_ProjectOffice & "'));"
  7.  
  8.     End If
  9.     Call .Requery
  10.   End With
  11. 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.
Oct 12 '07 #6

AllusiveKitten
P: 43
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
Oct 14 '07 #7

ADezii
Expert 5K+
P: 8,607
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.
Oct 14 '07 #8

ADezii
Expert 5K+
P: 8,607
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 TxtProjectOffice Combo Box to:
    Expand|Select|Wrap|Line Numbers
    1. 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 TxtProjectOffice Combo Box to:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Txt_ProjectOffice_AfterUpdate()
    2. Dim MySQL As String
    3.  
    4. MySQL = "SELECT Distinct [Date] FROM Tbl_DespactchedDocuments WHERE Tbl_DespactchedDocuments.[Project/OfficeName] " _
    5.         & "='" & Me!Txt_ProjectOffice & "' Order by Tbl_DespactchedDocuments.Date;"
    6.  
    7. With Me!Txt_DateSent
    8.   If IsNull(Me!Txt_ProjectOffice) Then
    9.     .RowSource = ""
    10.   Else
    11.     .RowSource = MySQL
    12.   End If
    13.     Call .Requery
    14. End With
    15. End Sub
  3. I think this is what you are looking for, if not just let me know.
Oct 15 '07 #9

AllusiveKitten
P: 43
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 TxtProjectOffice Combo Box to:
    Expand|Select|Wrap|Line Numbers
    1. 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 TxtProjectOffice Combo Box to:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Txt_ProjectOffice_AfterUpdate()
    2. Dim MySQL As String
    3.  
    4. MySQL = "SELECT Distinct [Date] FROM Tbl_DespactchedDocuments WHERE Tbl_DespactchedDocuments.[Project/OfficeName] " _
    5.         & "='" & Me!Txt_ProjectOffice & "' Order by Tbl_DespactchedDocuments.Date;"
    6.  
    7. With Me!Txt_DateSent
    8.   If IsNull(Me!Txt_ProjectOffice) Then
    9.     .RowSource = ""
    10.   Else
    11.     .RowSource = MySQL
    12.   End If
    13.     Call .Requery
    14. End With
    15. 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!!
Oct 16 '07 #10

ADezii
Expert 5K+
P: 8,607
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!!
There seems to be a problem in that the [Txt_DateSent] Combo Box cannot digest the Long Date Format. I'll give you a temporary cure for now and I'll look into it tomorrow.
  1. Set the Format of the [Txt_DateSent] Field on your Form to Medium Date
  2. Set the Format of the [Date] Field in Tbl_DespatchedDocuments to Medium Date.
  3. You should be OK for now.
Oct 16 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.