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
|
|||
|
|||
Adding data on subform with refresh button
I've created a db for my burea and everything works great except one
featu I've created a form that displays all the data for a peice of legislation (I work in government). Included on that page, is the name of the analyst who in doing the analysis. Since a peice of legislation can have more than one person working on, I wanted the users to have the ability to add an additional analyst. I created a subform with a combo box that they could use to select the name of an additional analyst. The idea is to then have them press a "Refresh" button which would requery a second subform, and not only save the additional analyst, but also have it appear on this requeried subform. Here's the problem: When ever someone tries to do this it automatically overwrites the analyst name who was orginially displayed on the subform. I know I'm doing something wrong, and I know I'm all buggered up at this point. Any advice would be greatly appreciated. Below is some of the information you might need along with a more detailed description of how this feature should work. Thank you in advance, Joann I've created a form with 3 tabs. On the "Analyst Tab" is the main form control subfAnalystAttatched. The record source for subfAnalystAttatched is the Master table which holds only 1 record for each peice of legislation that we work on with the primary key being Documents_Doc ID. Since more than one analyst can work on legislation, I've created a seperate table, DocAnalyst, that lists all the analysts for a particular peice of legislation. The primary key for this table is an autonumber called DocAnalyst. This table also contains the field DocID (equal to Documents_DocID from the Master table) and AnalystCode which lists the number associated with each analyst's last name. The control name of the first subform is subfAnalystID. This subform record source for this subform is a query that links the Master and DocAnalyst tabels by Documents_docID and DocID. This query also links the DocAnalyst and AnalystCodes tabels so that the last name will be displayed instead of just a numeric code. There are two text boxes: Documents_docID (not visible) and Analyst. The format is continuous forms. The control name of the second subform is subfAnalystTabNewAnalyst. The record source for this subform is a query linking the DocAnalyst and Master tables. This subform contains a combo box that lists all the analysts along with their numeric code (that column is not visible), a textbox for Documents_docID (not visible), and a textbox for DocAnalyst (not visible), along with a "Refresh" button. The idea is, the user could go to that tab which displays all the data for that particular peice of legislation, and if an additional analyst worked on the case, they could select their name from the combo box, click the refresh button, and have it show up in subform above (subfAnalystID). Here is the code for the Refresh button: Private Sub CmdRefresh_Click() On Error GoTo Err_CmdRefresh_Click DoCmd.GoToRecord , , acNewRec Me.Parent!subfAnalystID.Requery Exit_CmdRefresh_Click: Exit Sub Err_CmdRefresh_Click: MsgBox Err.Description Resume Exit_CmdRefresh_Click End Sub |
#2
|
|||
|
|||
Adding data on subform with refresh button
Your problem is in the RecordSource of your second subform,
subfAnalystTabNewAnalyst. Since this is tied to the DocAnalyst table, it will display the first Analyst it finds in that table for the document. When you select a different analyst in the combo box, it will simply over-write that record in DocAnalyst. An alternate (and, in my mind, simpler) way would be to make the analyst text box in your first subform (subfAnalystID) a combo box instead, with its Control Source the same as it is now but with a RowSource that again lists all possible analysts. Since you have a continuous form, adding a new analyst would require nothing more than the user starting a new record in the continuous form and selecting the analyst from the combo box; the document id would automatically be set for you. You can then totally eliminate the second subform (subfAnalystTabNewAnalyst). HTH, Carl Rapson wrote in message oups.com... I've created a db for my burea and everything works great except one featu I've created a form that displays all the data for a peice of legislation (I work in government). Included on that page, is the name of the analyst who in doing the analysis. Since a peice of legislation can have more than one person working on, I wanted the users to have the ability to add an additional analyst. I created a subform with a combo box that they could use to select the name of an additional analyst. The idea is to then have them press a "Refresh" button which would requery a second subform, and not only save the additional analyst, but also have it appear on this requeried subform. Here's the problem: When ever someone tries to do this it automatically overwrites the analyst name who was orginially displayed on the subform. I know I'm doing something wrong, and I know I'm all buggered up at this point. Any advice would be greatly appreciated. Below is some of the information you might need along with a more detailed description of how this feature should work. Thank you in advance, Joann I've created a form with 3 tabs. On the "Analyst Tab" is the main form control subfAnalystAttatched. The record source for subfAnalystAttatched is the Master table which holds only 1 record for each peice of legislation that we work on with the primary key being Documents_Doc ID. Since more than one analyst can work on legislation, I've created a seperate table, DocAnalyst, that lists all the analysts for a particular peice of legislation. The primary key for this table is an autonumber called DocAnalyst. This table also contains the field DocID (equal to Documents_DocID from the Master table) and AnalystCode which lists the number associated with each analyst's last name. The control name of the first subform is subfAnalystID. This subform record source for this subform is a query that links the Master and DocAnalyst tabels by Documents_docID and DocID. This query also links the DocAnalyst and AnalystCodes tabels so that the last name will be displayed instead of just a numeric code. There are two text boxes: Documents_docID (not visible) and Analyst. The format is continuous forms. The control name of the second subform is subfAnalystTabNewAnalyst. The record source for this subform is a query linking the DocAnalyst and Master tables. This subform contains a combo box that lists all the analysts along with their numeric code (that column is not visible), a textbox for Documents_docID (not visible), and a textbox for DocAnalyst (not visible), along with a "Refresh" button. The idea is, the user could go to that tab which displays all the data for that particular peice of legislation, and if an additional analyst worked on the case, they could select their name from the combo box, click the refresh button, and have it show up in subform above (subfAnalystID). Here is the code for the Refresh button: Private Sub CmdRefresh_Click() On Error GoTo Err_CmdRefresh_Click DoCmd.GoToRecord , , acNewRec Me.Parent!subfAnalystID.Requery Exit_CmdRefresh_Click: Exit Sub Err_CmdRefresh_Click: MsgBox Err.Description Resume Exit_CmdRefresh_Click End Sub |
Thread Tools | |
Display Modes | |
|
|