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
|
|||
|
|||
update queries and forms
Hi
I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#3
|
|||
|
|||
Thanks very much for your quick response.
At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#4
|
|||
|
|||
It appears, then, that what you want to do is simpler than I'd been
thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#5
|
|||
|
|||
" What you want to do is to create an update query that will read the values
to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#6
|
|||
|
|||
Post the SQL statement of the select query that you've created. We need to
modify it into an update query so that we can run it from your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... " What you want to do is to create an update query that will read the values to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#7
|
|||
|
|||
Hi
Is this what you wanted? This is the SQL view of my select query for one account showing all the locations in CA for that account whose deductible I'd like to update. SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY, dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM, dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME, dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID) INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US REGIONS].STATECODE) WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND ((dbo_loc_QTE05_01.COUNTRY)="US") AND ((dbo_loc_QTE05_01.STATECODE)="CA")); Thanks "Ken Snell [MVP]" wrote: Post the SQL statement of the select query that you've created. We need to modify it into an update query so that we can run it from your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... " What you want to do is to create an update query that will read the values to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#8
|
|||
|
|||
OK - now we need you to identify the following fields from this query:
-- the field (and table that it is in) that holds the deductible value that is to be updated; -- the primary key field of the table that holds the deductible field; -- which fields correspond to the values that you're selecting on your form so that you are "identifying" the appropriate person's records that are to be updated. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Hi Is this what you wanted? This is the SQL view of my select query for one account showing all the locations in CA for that account whose deductible I'd like to update. SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY, dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM, dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME, dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID) INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US REGIONS].STATECODE) WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND ((dbo_loc_QTE05_01.COUNTRY)="US") AND ((dbo_loc_QTE05_01.STATECODE)="CA")); Thanks "Ken Snell [MVP]" wrote: Post the SQL statement of the select query that you've created. We need to modify it into an update query so that we can run it from your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... " What you want to do is to create an update query that will read the values to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#9
|
|||
|
|||
Hi
The field to be updated is called SITEDEDAMT The table it's in is called dbo_eqdet_QTE_05_01 The primary key of the table with the deductible is EQDETID The values that corrospond to what I'm selecting on my form will be a primary key identifier - called ACCGRPID Thanks "Ken Snell [MVP]" wrote: OK - now we need you to identify the following fields from this query: -- the field (and table that it is in) that holds the deductible value that is to be updated; -- the primary key field of the table that holds the deductible field; -- which fields correspond to the values that you're selecting on your form so that you are "identifying" the appropriate person's records that are to be updated. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Hi Is this what you wanted? This is the SQL view of my select query for one account showing all the locations in CA for that account whose deductible I'd like to update. SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY, dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM, dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME, dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID) INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US REGIONS].STATECODE) WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND ((dbo_loc_QTE05_01.COUNTRY)="US") AND ((dbo_loc_QTE05_01.STATECODE)="CA")); Thanks "Ken Snell [MVP]" wrote: Post the SQL statement of the select query that you've created. We need to modify it into an update query so that we can run it from your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... " What you want to do is to create an update query that will read the values to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
#10
|
|||
|
|||
ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID? If not, what is the relationship between ACCGRPID and EQDETID? What we're trying to do now is to identify the children record(s) that need to be updated can be identified from what you select on your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Hi The field to be updated is called SITEDEDAMT The table it's in is called dbo_eqdet_QTE_05_01 The primary key of the table with the deductible is EQDETID The values that corrospond to what I'm selecting on my form will be a primary key identifier - called ACCGRPID Thanks "Ken Snell [MVP]" wrote: OK - now we need you to identify the following fields from this query: -- the field (and table that it is in) that holds the deductible value that is to be updated; -- the primary key field of the table that holds the deductible field; -- which fields correspond to the values that you're selecting on your form so that you are "identifying" the appropriate person's records that are to be updated. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Hi Is this what you wanted? This is the SQL view of my select query for one account showing all the locations in CA for that account whose deductible I'd like to update. SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY, dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM, dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME, dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID) INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US REGIONS].STATECODE) WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND ((dbo_loc_QTE05_01.COUNTRY)="US") AND ((dbo_loc_QTE05_01.STATECODE)="CA")); Thanks "Ken Snell [MVP]" wrote: Post the SQL statement of the select query that you've created. We need to modify it into an update query so that we can run it from your form. -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... " What you want to do is to create an update query that will read the values to be used in the query from your form. " yes - this is exactly what I want to do. I can set up a select query easily to select the locations that I want to update - I take it this query is kept in the original query format and isn't changed to a form view or anything? The name of the form that I'll be using is called "FormUpdateQuoteValues" The names of the textboxes are "FieldUpdateTo" which is where I enter the value that I want the values to be updated to and "FieldWhere" which is where I enter the criteria eg CA. Thanks again "Ken Snell [MVP]" wrote: It appears, then, that what you want to do is simpler than I'd been thinking. What you want to do is to create an update query that will read the values to be used in the query from your form. Note: Remember that, once run, an update query cannot be undone. So you'll need to be sure that the user means to do the update before you run it, or else your data will be changed. First, let's have you create a select query (a normal query) that will select the records that you want to update. We will turn this query into an update query after you post it -- it's easier to do it this way. Also, what is the name of the form that you'll be using to enter the values that will be used by the query? And what are the names of the textboxes into which you'll type those values? -- Ken Snell MS ACCESS MVP "Mary Pode" wrote in message ... Thanks very much for your quick response. At the moment what I have set up is a form that just shows the main details of the account - account number, name of insured etc. This comes from a query picking out certain information from an underlying "Accounts" table. I don't really need to be able to see all the other information such as the locations that are part of the account because there could be tens of thousands of them! I take it this is what you were thinking would be on the subform? So what I would like are two boxes somewhere on the form - one that I would fill out with the deductible amount to be updated eg 0.05 and the other with the criteria eg CA for California locations only. And then a button that you press to essentially do the same as the run button in a regular update query. I hope this is what you wanted to know - thanks again for helping me with this! "Ken Snell [MVP]" wrote: What you seek to do can be done within a form, using unbound controls on the form and using programming behind the form that, when you click a button, will update the data as you wish. This is not a simple thing to set up, and talking you through it will take a bit of iterations, so get ready for a few exchanges here! ;-) First, let's start with what you've already set up. I assume that this is a form that is bound to a query or table that is returning the values that you're displaying. Are you using subforms for the children data? Or are you using separate forms that you open from this one main form? It will be easier for your use if you use subforms. -- Ken Snell MS ACCESS MVP "Mary Pode" Mary wrote in message ... Hi I am trying to build a "front end" to an application and am having some difficulty. Here's my problem: I would basically like to be able to do via a form what an update query does: I work in insurance and I have a table that has all the information about the insured accounts in it and each account has it's own unique identifier. I have a second table with all the policy information in it. A third table has all the information about the locations insured for each account, and each location also has a unique identifier. I would like to have a form that displays all the basic information about the insured account with some additional boxes that I can fill out to update information in the underlying location table. eg, say I have an account that has locations all over the US. I would like to be able to find that account and then fill out additional boxes that would update the deductible values at all CA locations to 5%. So in an update query the criteria would be CA and the update to field would be 0.05. I have tried so many things and just can't get this seemingly basic idea to work as part of a form. Any help would be greatly appreciated. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating Forms from Update Queries | Steve | Using Forms | 7 | December 8th, 2004 08:43 PM |
Access 2003 forms - couple of queries ... | Brian Pearson | Using Forms | 5 | December 6th, 2004 10:14 AM |
Tidying up orphan forms and queries | Richard | General Discussion | 3 | October 21st, 2004 11:58 PM |
saved queries based on other saved queries vs. one big query in a form's module using DAO | Richard Hollenbeck | Running & Setting Up Queries | 5 | September 23rd, 2004 08:38 PM |
Using Forms for Parameter Queries | Jon | Using Forms | 0 | August 6th, 2004 03:58 AM |