If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 combo box, 2 txt boxes - Populate problem
On my form, I have a combo box that looks up Region from a table. In the
Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
#2
|
|||
|
|||
Hi, S.
Yes, the code can go in the On Current event. And, yes, just use another variable name, like strCBox2SQL. And check out a third-party text written for Access developers. They should treat the sequence of events thoroughly there. HTH Sprinks "S Jackson" wrote: On my form, I have a combo box that looks up Region from a table. In the Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
#3
|
|||
|
|||
Thanks, but I am still having problems. I tried making a new simplied form
just for testing purposes. I have included the two relevant tables in the query the form is based on. Combo Box 1 (cboRegion) allows the user to select the Region which is stored in the Region field of Table 1. Then the strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user select a field office within the Region they just selected. When they do this, it stores the information in the FieldOfc field in Table One and then displays the matching information in text boxes 1 and 2. The problem I am having is that the information in the cboFieldOfc keeps disappearing when you either: 1. reopen the form; or 2. make a change to the cboRegion in any of the other records. Currently, I have the code to populate cboFieldofc in the Afterupdate Event of cboRegion. I have tried putting it elsewhere (Form-OnCurrent, Form-OnOpen, Form-Afterupdate), but nothing works. I'm lost here. This is really eating my lunch! "Sprinks" wrote in message ... Hi, S. Yes, the code can go in the On Current event. And, yes, just use another variable name, like strCBox2SQL. And check out a third-party text written for Access developers. They should treat the sequence of events thoroughly there. HTH Sprinks "S Jackson" wrote: On my form, I have a combo box that looks up Region from a table. In the Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
#4
|
|||
|
|||
The Old Disappearing Data! Is this a continuous form? If so, the combo
box cannot simultaneously hold the different populations required for each chosen Region, so if the selection is not contained within the combo box' *only* list, it disappears! Confused the hell out of me, too, when I first encountered it. The solution is to bind a textbox to your FieldOfc field, and use its OnGotFocus event to pop up a modal form that contains a single unbound combo box populated by your SQL string. Use its OnUpdate event to write the selection to your textbox on the main form, and close the modal form. Here's an example from one of my applications, where the user selects a steel size based on the steel type: ' Text box on main form Private Sub txtSteelSize_GotFocus() On Error Resume Next DoCmd.OpenForm _ FormName:="frmSelectSteelSize", _ View:=acNormal, _ WindowMode:=acDialog Me!txtResult.SetFocus End Sub ' Unbound combo box on mini-form Private Sub cboSteelSize_AfterUpdate() Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtStee lSizeID = Me!cboSteelSize DoCmd.Close End Sub HTH Sprinks "S Jackson" wrote: Thanks, but I am still having problems. I tried making a new simplied form just for testing purposes. I have included the two relevant tables in the query the form is based on. Combo Box 1 (cboRegion) allows the user to select the Region which is stored in the Region field of Table 1. Then the strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user select a field office within the Region they just selected. When they do this, it stores the information in the FieldOfc field in Table One and then displays the matching information in text boxes 1 and 2. The problem I am having is that the information in the cboFieldOfc keeps disappearing when you either: 1. reopen the form; or 2. make a change to the cboRegion in any of the other records. Currently, I have the code to populate cboFieldofc in the Afterupdate Event of cboRegion. I have tried putting it elsewhere (Form-OnCurrent, Form-OnOpen, Form-Afterupdate), but nothing works. I'm lost here. This is really eating my lunch! "Sprinks" wrote in message ... Hi, S. Yes, the code can go in the On Current event. And, yes, just use another variable name, like strCBox2SQL. And check out a third-party text written for Access developers. They should treat the sequence of events thoroughly there. HTH Sprinks "S Jackson" wrote: On my form, I have a combo box that looks up Region from a table. In the Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
#5
|
|||
|
|||
Thanks for your quick response! Glad to hear I was not alone in the
"confused all-to-hell" place I was in. I have the exact same premise working on a different form and I have studied and studied it and I can't see what is different from what I am trying to do now! I guess it will remain a mystery. (Also, fyi, this is not a continuous form, but is a single form.) Your idea to use a textbox bound to the Fieldofc field and the pop-up is great! That was what I was thinking about trying next, but I wasn't ready to tackle the coding yet. I'll give your example a whirl and see if I can get it working. Many Thanks. S. Jackson "Sprinks" wrote in message ... The Old Disappearing Data! Is this a continuous form? If so, the combo box cannot simultaneously hold the different populations required for each chosen Region, so if the selection is not contained within the combo box' *only* list, it disappears! Confused the hell out of me, too, when I first encountered it. The solution is to bind a textbox to your FieldOfc field, and use its OnGotFocus event to pop up a modal form that contains a single unbound combo box populated by your SQL string. Use its OnUpdate event to write the selection to your textbox on the main form, and close the modal form. Here's an example from one of my applications, where the user selects a steel size based on the steel type: ' Text box on main form Private Sub txtSteelSize_GotFocus() On Error Resume Next DoCmd.OpenForm _ FormName:="frmSelectSteelSize", _ View:=acNormal, _ WindowMode:=acDialog Me!txtResult.SetFocus End Sub ' Unbound combo box on mini-form Private Sub cboSteelSize_AfterUpdate() Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtStee lSizeID = Me!cboSteelSize DoCmd.Close End Sub HTH Sprinks "S Jackson" wrote: Thanks, but I am still having problems. I tried making a new simplied form just for testing purposes. I have included the two relevant tables in the query the form is based on. Combo Box 1 (cboRegion) allows the user to select the Region which is stored in the Region field of Table 1. Then the strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user select a field office within the Region they just selected. When they do this, it stores the information in the FieldOfc field in Table One and then displays the matching information in text boxes 1 and 2. The problem I am having is that the information in the cboFieldOfc keeps disappearing when you either: 1. reopen the form; or 2. make a change to the cboRegion in any of the other records. Currently, I have the code to populate cboFieldofc in the Afterupdate Event of cboRegion. I have tried putting it elsewhere (Form-OnCurrent, Form-OnOpen, Form-Afterupdate), but nothing works. I'm lost here. This is really eating my lunch! "Sprinks" wrote in message ... Hi, S. Yes, the code can go in the On Current event. And, yes, just use another variable name, like strCBox2SQL. And check out a third-party text written for Access developers. They should treat the sequence of events thoroughly there. HTH Sprinks "S Jackson" wrote: On my form, I have a combo box that looks up Region from a table. In the Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
#6
|
|||
|
|||
I thought I would give you an update since you were trying to help. I gave
up. My initial reason for trying all of this was because I think I have a design flaw in my database. My central table is tblCaseInfo. I have a 2nd table called tblSurveyors. The link table b/t them is called tblCaseSurveyors. tblCaseInfo and tblCaseSurveyors have a one-to-many relationship. tblCaseInfo also has a one-to-one relationship with tblRegion which contains the names and addresses of program managers. There can be several program managers per Region. Originally, I had my form set up with a combo box to select Region from a separate lookup table, a 2nd combo box was then populated based on the selection for Region with the corresponding program managers from tblRegion. The design flaw I saw in my database was that the information about program managers contained in tblRegion is also stored in tblSurveyors. So, when a user wants to update information related to a program manager, they have to update it in two different places. So, I thought I would try to change my form by having the 2nd combo box populate with just program managers (SvyTitle field in tblSurveyors) from tblSurveyors. The first problem was the empty 2nd combo box when you newly opened the form, or changed the Region selection in another record. The second problem was it did not update the txtboxes for name, title, phone number of the program manager. I am not sure how to fix this. Do you think I am having a problem because of the relationships already established b/t tblCaseInfo, tblCaseSurveyors and tblSurveyors? Maybe I need a link table for program managers?. I'm so confused. I can't imagine how confusing this must be to you! Therefore, I give up. Thanks anyway "S Jackson" wrote in message ... Thanks for your quick response! Glad to hear I was not alone in the "confused all-to-hell" place I was in. I have the exact same premise working on a different form and I have studied and studied it and I can't see what is different from what I am trying to do now! I guess it will remain a mystery. (Also, fyi, this is not a continuous form, but is a single form.) Your idea to use a textbox bound to the Fieldofc field and the pop-up is great! That was what I was thinking about trying next, but I wasn't ready to tackle the coding yet. I'll give your example a whirl and see if I can get it working. Many Thanks. S. Jackson "Sprinks" wrote in message ... The Old Disappearing Data! Is this a continuous form? If so, the combo box cannot simultaneously hold the different populations required for each chosen Region, so if the selection is not contained within the combo box' *only* list, it disappears! Confused the hell out of me, too, when I first encountered it. The solution is to bind a textbox to your FieldOfc field, and use its OnGotFocus event to pop up a modal form that contains a single unbound combo box populated by your SQL string. Use its OnUpdate event to write the selection to your textbox on the main form, and close the modal form. Here's an example from one of my applications, where the user selects a steel size based on the steel type: ' Text box on main form Private Sub txtSteelSize_GotFocus() On Error Resume Next DoCmd.OpenForm _ FormName:="frmSelectSteelSize", _ View:=acNormal, _ WindowMode:=acDialog Me!txtResult.SetFocus End Sub ' Unbound combo box on mini-form Private Sub cboSteelSize_AfterUpdate() Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtStee lSizeID = Me!cboSteelSize DoCmd.Close End Sub HTH Sprinks "S Jackson" wrote: Thanks, but I am still having problems. I tried making a new simplied form just for testing purposes. I have included the two relevant tables in the query the form is based on. Combo Box 1 (cboRegion) allows the user to select the Region which is stored in the Region field of Table 1. Then the strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user select a field office within the Region they just selected. When they do this, it stores the information in the FieldOfc field in Table One and then displays the matching information in text boxes 1 and 2. The problem I am having is that the information in the cboFieldOfc keeps disappearing when you either: 1. reopen the form; or 2. make a change to the cboRegion in any of the other records. Currently, I have the code to populate cboFieldofc in the Afterupdate Event of cboRegion. I have tried putting it elsewhere (Form-OnCurrent, Form-OnOpen, Form-Afterupdate), but nothing works. I'm lost here. This is really eating my lunch! "Sprinks" wrote in message ... Hi, S. Yes, the code can go in the On Current event. And, yes, just use another variable name, like strCBox2SQL. And check out a third-party text written for Access developers. They should treat the sequence of events thoroughly there. HTH Sprinks "S Jackson" wrote: On my form, I have a combo box that looks up Region from a table. In the Afterupdate event of this combo box, I have the following code: 'Code for Program Manager Information Dim strSQL as String strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _ & "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _ & "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _ & "FROM tblSurveyors " _ & "WHERE tblSurveyors.Region = " & Me.Region & " " _ & "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _ & "ORDER BY tblSurveyors.svyLastName;" Me.cmbFieldOfc.RowSource = strSQL MsgBox strSQL The above code provides the data for the 2nd combo box, cmbFieldofc which is bound to the Fieldofc field in table tblCaseInfo. The two text boxes are setup like so: =[cmbFieldofc].Column(3) =[cmbFieldofc].Column(6) The problem I am having is that when I input the information for the first time, everything works great. However, when I move to a new record and then go back, the information is not correct - the 2nd combo box reverts to its original query information, which messes up the two txt boxes. So, the question is, what event on which control do I need to add code to? Is it an event on the Form? If so, I may have a problem as I already declared "strSQL as String" for a different process on the OnCurrent and AfterUpdate events on the form control. So, if I have to add the above code for the two combo boxes, do I just give it a different name (strSQL2)? Any help is greatly appreciated. I always get lost with regard to which event to assign code! TIA |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
combo boxes [code improvement] | Sam Kuo | Using Forms | 2 | November 29th, 2004 01:35 AM |
combo boxes on form with subform | Tcs | Using Forms | 0 | November 23rd, 2004 05:01 PM |
Synchronizing Multiple Combo boxes to view matching data on a Form | Mark Senibaldi | Using Forms | 4 | June 16th, 2004 08:48 PM |
Manipulate records of 2 tables | Praveen Manne | Using Forms | 11 | June 14th, 2004 02:41 PM |
Multiple combo boxes | Lee | Worksheet Functions | 2 | May 6th, 2004 02:49 PM |