Adding data on subform with refresh button

[joann007]

I've created a db for my burea and everything works great except one
feature:
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

 

[Carl Rapson]

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