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
|
|||
|
|||
'Current Row' or similar for query-based combo box
Apologies if this is an obvious question, but I can't find any suitable query
function or operator. I have a series of tables with RI enforced foreign keys. When keying data into a given row, I want to be able to limit the combo box values by a value I have already entered on the current (uncommitted) row. Is there an SQL function or a VBA method of qualifying the combo box query to limit the results of the query by a value on the current row? For example... select J1.Part_No from Parts J1 where J1.Part_Year = current row (INV.Part_Year) ; * where INV.Part_Year is a value on the row I am keying to table INV Any help is greatly appreciated. |
#3
|
|||
|
|||
'Current Row' or similar for query-based combo box
If you are entering directly into a table (or query) then there is no way to
accomplish what you want to do. IF you are entering data into a form, then there is a possibility of doing what you want. In a continuous form, the solution can be a bit complex. On a continuous form, there is only one control shown many times (once for each row). So if you change the row source of a combobox depending on the row you are in, other rows that need other values to display will suddenly display blank. The data is still in the table, the problem is that the combobox control does not have that particular value available to display. So, post back if you are using a form to display and modify the data. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Apologies if this is an obvious question, but I can't find any suitable query function or operator. I have a series of tables with RI enforced foreign keys. When keying data into a given row, I want to be able to limit the combo box values by a value I have already entered on the current (uncommitted) row. Is there an SQL function or a VBA method of qualifying the combo box query to limit the results of the query by a value on the current row? For example... select J1.Part_No from Parts J1 where J1.Part_Year = current row (INV.Part_Year) ; * where INV.Part_Year is a value on the row I am keying to table INV Any help is greatly appreciated. |
#4
|
|||
|
|||
'Current Row' or similar for query-based combo box
Thanks for the information John.
Based on a prior post, I have created a form to access my table. By using form.[field_name] in my combobox query I am able to get the intended result. However as you pointed out, the results are only valid for the first row that is accessed when I open the form. The form.[field_name] values do not update when I scroll to the next row in the table. You mentioned doing a postback. I am not familiar with this function. I am thinking of using the Onlostfocus event as the trigger for each form field value to be updated. Can you give me an example of how to do the postback? Thanks again for your help with this. -Dan "John Spencer" wrote: If you are entering directly into a table (or query) then there is no way to accomplish what you want to do. IF you are entering data into a form, then there is a possibility of doing what you want. In a continuous form, the solution can be a bit complex. On a continuous form, there is only one control shown many times (once for each row). So if you change the row source of a combobox depending on the row you are in, other rows that need other values to display will suddenly display blank. The data is still in the table, the problem is that the combobox control does not have that particular value available to display. So, post back if you are using a form to display and modify the data. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Apologies if this is an obvious question, but I can't find any suitable query function or operator. I have a series of tables with RI enforced foreign keys. When keying data into a given row, I want to be able to limit the combo box values by a value I have already entered on the current (uncommitted) row. Is there an SQL function or a VBA method of qualifying the combo box query to limit the results of the query by a value on the current row? For example... select J1.Part_No from Parts J1 where J1.Part_Year = current row (INV.Part_Year) ; * where INV.Part_Year is a value on the row I am keying to table INV Any help is greatly appreciated. |
#5
|
|||
|
|||
'Current Row' or similar for query-based combo box
No, POST BACK to the newsgroup and ask for more help.
You can use the form's current event to force the combobox to be requeried. Me.NameOfCombobox.Requery John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Thanks for the information John. Based on a prior post, I have created a form to access my table. By using form.[field_name] in my combobox query I am able to get the intended result. However as you pointed out, the results are only valid for the first row that is accessed when I open the form. The form.[field_name] values do not update when I scroll to the next row in the table. You mentioned doing a postback. I am not familiar with this function. I am thinking of using the Onlostfocus event as the trigger for each form field value to be updated. Can you give me an example of how to do the postback? Thanks again for your help with this. -Dan "John Spencer" wrote: If you are entering directly into a table (or query) then there is no way to accomplish what you want to do. IF you are entering data into a form, then there is a possibility of doing what you want. In a continuous form, the solution can be a bit complex. On a continuous form, there is only one control shown many times (once for each row). So if you change the row source of a combobox depending on the row you are in, other rows that need other values to display will suddenly display blank. The data is still in the table, the problem is that the combobox control does not have that particular value available to display. So, post back if you are using a form to display and modify the data. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Apologies if this is an obvious question, but I can't find any suitable query function or operator. I have a series of tables with RI enforced foreign keys. When keying data into a given row, I want to be able to limit the combo box values by a value I have already entered on the current (uncommitted) row. Is there an SQL function or a VBA method of qualifying the combo box query to limit the results of the query by a value on the current row? For example... select J1.Part_No from Parts J1 where J1.Part_Year = current row (INV.Part_Year) ; * where INV.Part_Year is a value on the row I am keying to table INV Any help is greatly appreciated. |
#6
|
|||
|
|||
'Current Row' or similar for query-based combo box
Dooh! I'm an idiot. ;-)
Thanks for the clarification. The requery solution is working very well. Thanks again for all your help. BTW - I do seem to need the requery function defined for the onlostfocus event of the driver field so I get the right query results when I am entering a new row in the table. "John Spencer" wrote: No, POST BACK to the newsgroup and ask for more help. You can use the form's current event to force the combobox to be requeried. Me.NameOfCombobox.Requery John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Thanks for the information John. Based on a prior post, I have created a form to access my table. By using form.[field_name] in my combobox query I am able to get the intended result. However as you pointed out, the results are only valid for the first row that is accessed when I open the form. The form.[field_name] values do not update when I scroll to the next row in the table. You mentioned doing a postback. I am not familiar with this function. I am thinking of using the Onlostfocus event as the trigger for each form field value to be updated. Can you give me an example of how to do the postback? Thanks again for your help with this. -Dan "John Spencer" wrote: If you are entering directly into a table (or query) then there is no way to accomplish what you want to do. IF you are entering data into a form, then there is a possibility of doing what you want. In a continuous form, the solution can be a bit complex. On a continuous form, there is only one control shown many times (once for each row). So if you change the row source of a combobox depending on the row you are in, other rows that need other values to display will suddenly display blank. The data is still in the table, the problem is that the combobox control does not have that particular value available to display. So, post back if you are using a form to display and modify the data. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Dan Smith - Sojourn wrote: Apologies if this is an obvious question, but I can't find any suitable query function or operator. I have a series of tables with RI enforced foreign keys. When keying data into a given row, I want to be able to limit the combo box values by a value I have already entered on the current (uncommitted) row. Is there an SQL function or a VBA method of qualifying the combo box query to limit the results of the query by a value on the current row? For example... select J1.Part_No from Parts J1 where J1.Part_Year = current row (INV.Part_Year) ; * where INV.Part_Year is a value on the row I am keying to table INV Any help is greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|