Re-open form showing last record (when using a form with a subform)

S

sergiosr

Hi,

I have a form(Person) with a subform(Questionnaire). I created an
unbound form to add/edit questionnaires for a person. When i edit a
questionnaire for a person I want my original form to open at the same
record where I was before opening the unbound form. When I add a
questionnaire I want my original form to open at the new record.

I have the following code in the unbound form(after a new or edited
questionnaire is submitted):

DoCmd.OpenForm "Person", , , , , , [PersonID] & "|" & [QuestionnaireID]

I have the following code in the Person form:

Private Sub Form_Load()

Dim split_array
split_array = Split(Me.OpenArgs, "|")

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblQuestionnaire", dbOpenDynaset)

rs.FindFirst "[PersonID] = '" & split_array(0) & "' And
[QuestionnaireID] = '" & split_array(1) & "'"

End Sub

My problem is that this doesn't take me back to the record I was before
adding/editing a questionnaire.
My guess is because PersonID belongs to the main form(Person) and
QuestionnaireID belongs to the subform(Questionnaire)

What can I do?

Thanks
 
S

Steve Schapel

Sergiosr,

Assuming you have PersonID ansd QuestionnaireID controls on the unbound
form, and that these hold the values you need, you could do it like this...
DoCmd.OpenForm "Person", , ,"[PersonID]=" & Me.PersonID &
"[QuestionnaireID]=" & Me.QuestionnaireID

However, why are you closing the Person form at all? Wouldn't it be
easier to leave the Person form open while you are entering the
questionnaire data?
 
S

sergiosr

I cannot use this: DoCmd.OpenForm "Person", , ,"[PersonID]=" &
Me.PersonID & "[QuestionnaireID]=" & Me.QuestionnaireID

because QuestionnaireID belongs to the subform(Questionnaire) . That
is my problem.

I am closing the Person form because the way I built my database I will
have to change a lot of things in order to leave it open. I went
through many problems to get to this point and I don't want to go back
again.

Thanks,

Sergios
 
S

sergiosr

I tried the simple way but I cannot make it work. I am sure there are
some syntax errors in your code but I cannot find all of them.

Thanks,

Sergios
 
S

Steve Schapel

Sergios,

I don't think there are any syntax errors in the code. Can you give
some more information about what is happening.
 
S

sergiosr

Between "DoCmd.OpenForm "Person", , ,"[PersonID]=" & Me.PersonID" and
"Forms!Person!Questionnaire.Form.RecordSource = "SELECT * FROM
tblQuestionnaire WHERE [QuestionnaireID]=" & Me.QuestionnaireID "
shouldn't there be something like a "&" or a "," ?
 
S

Steve Schapel

Sergios,

No, these are two separate lines of code.

Of course, you may need to replace some of the details in the code to
match your specific situation. For example, I have assumed that the
subform control on the Person form is named Questionnaire, and I have
assumed that you have controls on the unbound form which are named
PersonID and QuestionnaireID, so if these controls have different names,
you will need to change the code accordingly.

Anyway, whct happens when you try to run the code?... Error message?
 
S

sergiosr

I didnt realize that those were two separate lines of code. I replaced
the details in the code to match my specific situation.

My code is:

DoCmd.OpenForm "frmPerson", , , "[PersonD]='" & Me.txtPersonID & "'"
Forms!frmPerson!fsubQuestionnaire.Form.RecordSource = "SELECT * FROM
tblQuestionnaire WHERE [QuestionnaireID]=" & Me.txtQuestionnaireID

1) This still takes me back to the first questionnaire of a person even
if I am editing the second questionnaire
2) This creates a filter and I cannot move to another record(using
"next" and "previous" command buttons)

Thanks again,

Sergios
 
S

Steve Schapel

Sergios,

I presumed that you have code to write the data from your unbound form
to the tblQuestionnaire table, and that this happens before the code we
have been discussing. Is this right? And I presume you have a number
entered into the txtQuestionnaireID textbox on the unbound form which is
included in the data written from the unbound form to the table. Yes?
If so, I can't quite see how you won't see the correct Questionnaire
record on your subform.

As for the "filter" problem, well you would need a command button on the
subform to reset the record source if you want to see all questionnaire
records, for example...
Me.RecordSource = "SELECT * FROM tblQuestionnaire"
 
S

sergiosr

Both things that you presumed are correct.

As for the filter problem, even if I include only the first line of
code (DoCmd.OpenForm "frmPerson", , , "[PersonD]='" & Me.txtPersonID &
"'") it still creates a filter. I am not changing the recordsource so I
don't understand why this happens.

Anyway, since this seems not to be working for me perhaps I should try
to use the Bookmark property of the subform, or the DoCmd.FindRecord in
my code?
Which one should be easier? I am not familiar with the Bookmark
property.

Thanks,

Sergios
 
S

Steve Schapel

Sergios,

Can you post back please with the entire subprocedure, so I can get a
bit of an idea what you are doing here. All methods rely on identifying
the subform record according to the txtQuestionnaireID value on the
unbound form, and if this is not working in one case, it probably won't
work in another.
 
S

sergiosr

Wouldn't it be easier if I use the Bookmark? That's what I am trying to
do now but I cannot find a way to save the bookmark after the form
closes and then retrieve the bookmark when the form re-opens.

Thanks,

Sergios
 
S

Steve Schapel

Sergios,

The bookmark would need to use the value of the txtQuestionnaireID
control on the unbound form.
 
S

sergiosr

What would be the code for the unbound form to save the bookmark and
for the main form to retrieve the bookmark?

Thanks again,

Sergios
 
S

Steve Schapel

Sergios,

As mentioned before, I will need to see more details of your entire
subprocedure, and hopefully track down why the QuestionnaireID is not
being recognised.
 
S

sergiosr

Well...you asked for it :)

Snapshot = Questionnaire
Individual = Person
txtQLSRID = QuestionnaireID
txtIndividualID = PersonID

Those above are the actual names that I used for my database.
Below is my original tested code that takes you back to the person and
the first questionnaire.

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdMainMenu_Click

Dim split_array
split_array = Split(Me.OpenArgs, "|")

Dim AddSnapshot As Recordset

Set AddSnapshot = CurrentDb.OpenRecordset("tblQLSR", dbOpenDynaset)

If split_array(1) = "New" Then
AddSnapshot.AddNew
Else
AddSnapshot.FindFirst "[IndividualID] = '" & split_array(0) & "'
And [SnapshotDate] = #" & split_array(1) & "#"
AddSnapshot.Edit
End If

If split_array(1) = "New" Then
If IsNull(Me.txtIndividualID.Value) Or
IsNull(Me.txtCareCoordinatorFirstName.Value) Or
IsNull(Me.txtCareCoordinatorLastName.Value) Or
IsNull(Me.txtSnapshotDate.Value) Or IsNull(Me.txtTitle.Value) Or
IsNull(Me.txtNetwork.Value) Or IsNull(Me.cboEnvironment.Value) Or
IsNull(Me.Location.Value) Or IsNull(Me.txtQLSRID.Value) Then
MsgBox "All fields are required"
ElseIf MsgBox("Add snapshot?", vbYesNo, "Confirm addition") = vbYes
Then
AddSnapshot!IndividualID = Me.txtIndividualID.Value
AddSnapshot!SnapshotDate = Me.txtSnapshotDate.Value
AddSnapshot!SnapshotID = Me.txtQLSRID.Value
AddSnapshot!EvaluatorFName =
Me.txtCareCoordinatorFirstName.Value
AddSnapshot!EvaluatorLName =
Me.txtCareCoordinatorLastName.Value
AddSnapshot!Title = Me.txtTitle.Value
AddSnapshot!PlanOfCareDate = Me.PlanOfCareDate.Value
AddSnapshot!Network = Me.txtNetwork.Value
AddSnapshot!Environment = Me.cboEnvironment.Value
AddSnapshot!Location = Me.Location.Value
AddSnapshot!Score = Me.txtScore.Value
AddSnapshot!Individuality1 = Me.chkIndiv1.Value
AddSnapshot!Individuality2 = Me.chkIndiv2.Value
AddSnapshot!Individuality3 = Me.chkIndiv3.Value
AddSnapshot!Individuality4 = Me.chkIndiv4.Value
AddSnapshot!Individuality5 = Me.chkIndiv5.Value
AddSnapshot!Integration1 = Me.chkInteg1.Value
AddSnapshot!Integration2 = Me.chkInteg2.Value
AddSnapshot!Integration3 = Me.chkInteg3.Value
AddSnapshot!Integration4 = Me.chkInteg4.Value
AddSnapshot!Integration5 = Me.chkInteg5.Value
AddSnapshot!Relationships1 = Me.chkRel1.Value
AddSnapshot!Relationships2 = Me.chkRel2.Value
AddSnapshot!Relationships3 = Me.chkRel3.Value
AddSnapshot!Relationships4 = Me.chkRel4.Value
AddSnapshot!Relationships5 = Me.chkRel5.Value
AddSnapshot!Security1 = Me.chkSec1.Value
AddSnapshot!Security2 = Me.chkSec2.Value
AddSnapshot!Security3 = Me.chkSec3.Value
AddSnapshot!Security4 = Me.chkSec4.Value
AddSnapshot!Security5 = Me.chkSec5.Value
AddSnapshot!Choice1 = Me.chkChoice1.Value
AddSnapshot!Choice2 = Me.chkChoice2.Value
AddSnapshot!Choice3 = Me.chkChoice3.Value
AddSnapshot!Choice4 = Me.chkChoice4.Value
AddSnapshot!Choice5 = Me.chkChoice5.Value
AddSnapshot!Opportunity1 = Me.chkOpp1.Value
AddSnapshot!Opportunity2 = Me.chkOpp2.Value
AddSnapshot!Opportunity3 = Me.chkOpp3.Value
AddSnapshot!Opportunity4 = Me.chkOpp4.Value
AddSnapshot!Opportunity5 = Me.chkOpp5.Value
AddSnapshot!Dignity1 = Me.chkDig1.Value
AddSnapshot!Dignity2 = Me.chkDig2.Value
AddSnapshot!Dignity3 = Me.chkDig3.Value
AddSnapshot!Dignity4 = Me.chkDig4.Value
AddSnapshot!Dignity5 = Me.chkDig5.Value
AddSnapshot!SelfAdvocacy1 = Me.chkSelf1.Value
AddSnapshot!SelfAdvocacy2 = Me.chkSelf2.Value
AddSnapshot!SelfAdvocacy3 = Me.chkSelf3.Value
AddSnapshot!SelfAdvocacy4 = Me.chkSelf4.Value
AddSnapshot!SelfAdvocacy5 = Me.chkSelf5.Value
AddSnapshot!Independence1 = Me.chkIndep1.Value
AddSnapshot!Independence2 = Me.chkIndep2.Value
AddSnapshot!Independence3 = Me.chkIndep3.Value
AddSnapshot!Independence4 = Me.chkIndep4.Value
AddSnapshot!Independence5 = Me.chkIndep5.Value
AddSnapshot!Independence6 = Me.chkIndep6.Value
AddSnapshot!Independence7 = Me.chkIndep7.Value
AddSnapshot!Independence8 = Me.chkIndep8.Value
AddSnapshot!Independence9 = Me.chkIndep9.Value
AddSnapshot!Independence10 = Me.chkIndep10.Value
AddSnapshot!Joy1 = Me.chkJoy1.Value
AddSnapshot!Joy2 = Me.chkJoy2.Value
AddSnapshot!Joy3 = Me.chkJoy3.Value
AddSnapshot!Joy4 = Me.chkJoy4.Value
AddSnapshot!Joy5 = Me.chkJoy5.Value
AddSnapshot!Staff1 = Me.chkStaff1.Value
AddSnapshot!Staff2 = Me.chkStaff2.Value
AddSnapshot!Staff3 = Me.chkStaff3.Value
AddSnapshot!Staff4 = Me.chkStaff4.Value
AddSnapshot!Staff5 = Me.chkStaff5.Value
AddSnapshot!Staff6 = Me.chkStaff6.Value
AddSnapshot.Update


If MsgBox("Snapshot added. Choose YES to add another snapshot or NO
to return to View Data", vbYesNo, "Select Yes/No") = vbYes Then
Me.txtQLSRID.Value = ""
Me.txtSnapshotDate.Value = ""
Me.txtCareCoordinatorFirstName.Value = ""
Me.txtCareCoordinatorLastName.Value = ""
Me.txtTitle.Value = ""
Me.PlanOfCareDate.Value = ""
Me.txtNetwork.Value = ""
Me.cboEnvironment.Value = ""
Me.Location.Value = ""
Me.chkIndiv1.Value = ""
Me.chkIndiv2.Value = ""
Me.chkIndiv3.Value = ""
Me.chkIndiv4.Value = ""
Me.chkIndiv5.Value = ""
Me.chkInteg1.Value = ""
Me.chkInteg2.Value = ""
Me.chkInteg3.Value = ""
Me.chkInteg4.Value = ""
Me.chkInteg5.Value = ""
Me.chkRel1.Value = ""
Me.chkRel2.Value = ""
Me.chkRel3.Value = ""
Me.chkRel4.Value = ""
Me.chkRel5.Value = ""
Me.chkSec1.Value = ""
Me.chkSec2.Value = ""
Me.chkSec3.Value = ""
Me.chkSec4.Value = ""
Me.chkSec5.Value = ""
Me.chkChoice1.Value = ""
Me.chkChoice2.Value = ""
Me.chkChoice3.Value = ""
Me.chkChoice4.Value = ""
Me.chkChoice5.Value = ""
Me.chkOpp1.Value = ""
Me.chkOpp2.Value = ""
Me.chkOpp3.Value = ""
Me.chkOpp4.Value = ""
Me.chkOpp5.Value = ""
Me.chkDig1.Value = ""
Me.chkDig2.Value = ""
Me.chkDig3.Value = ""
Me.chkDig4.Value = ""
Me.chkDig5.Value = ""
Me.chkSelf1.Value = ""
Me.chkSelf2.Value = ""
Me.chkSelf3.Value = ""
Me.chkSelf4.Value = ""
Me.chkSelf5.Value = ""
Me.chkIndep1.Value = ""
Me.chkIndep2.Value = ""
Me.chkIndep3.Value = ""
Me.chkIndep4.Value = ""
Me.chkIndep5.Value = ""
Me.chkIndep6.Value = ""
Me.chkIndep7.Value = ""
Me.chkIndep8.Value = ""
Me.chkIndep9.Value = ""
Me.chkIndep10.Value = ""
Me.chkJoy1.Value = ""
Me.chkJoy2.Value = ""
Me.chkJoy3.Value = ""
Me.chkJoy4.Value = ""
Me.chkJoy5.Value = ""
Me.chkStaff1.Value = ""
Me.chkStaff2.Value = ""
Me.chkStaff3.Value = ""
Me.chkStaff4.Value = ""
Me.chkStaff5.Value = ""
Me.chkStaff6.Value = ""

Else
AddSnapshot.Close
DoCmd.OpenForm "frmIndividual", , , , , , [txtIndividualID]
DoCmd.Close acForm, Me.Name

End If
End If
ElseIf MsgBox("Submit changes and exit?", vbYesNo, "Edit Snapshot") =
vbYes Then
If IsNull(Me.txtIndividualID.Value) Or
IsNull(Me.txtCareCoordinatorFirstName.Value) Or
IsNull(Me.txtCareCoordinatorLastName.Value) Or
IsNull(Me.txtSnapshotDate.Value) Or IsNull(Me.txtTitle.Value) Or
IsNull(Me.txtNetwork.Value) Or IsNull(Me.cboEnvironment.Value) Or
IsNull(Me.Location.Value) Then
MsgBox "All fields are required"
Else
AddSnapshot!IndividualID = Me.txtIndividualID.Value
AddSnapshot!SnapshotDate = Me.txtSnapshotDate.Value
AddSnapshot!SnapshotID = Me.txtQLSRID.Value
AddSnapshot!EvaluatorFName =
Me.txtCareCoordinatorFirstName.Value
AddSnapshot!EvaluatorLName =
Me.txtCareCoordinatorLastName.Value
AddSnapshot!Title = Me.txtTitle.Value
AddSnapshot!PlanOfCareDate = Me.PlanOfCareDate.Value
AddSnapshot!Network = Me.txtNetwork.Value
AddSnapshot!Environment = Me.cboEnvironment.Value
AddSnapshot!Location = Me.Location.Value
AddSnapshot!Score = Me.txtScore.Value
AddSnapshot!Individuality1 = Me.chkIndiv1.Value
AddSnapshot!Individuality2 = Me.chkIndiv2.Value
AddSnapshot!Individuality3 = Me.chkIndiv3.Value
AddSnapshot!Individuality4 = Me.chkIndiv4.Value
AddSnapshot!Individuality5 = Me.chkIndiv5.Value
AddSnapshot!Integration1 = Me.chkInteg1.Value
AddSnapshot!Integration2 = Me.chkInteg2.Value
AddSnapshot!Integration3 = Me.chkInteg3.Value
AddSnapshot!Integration4 = Me.chkInteg4.Value
AddSnapshot!Integration5 = Me.chkInteg5.Value
AddSnapshot!Relationships1 = Me.chkRel1.Value
AddSnapshot!Relationships2 = Me.chkRel2.Value
AddSnapshot!Relationships3 = Me.chkRel3.Value
AddSnapshot!Relationships4 = Me.chkRel4.Value
AddSnapshot!Relationships5 = Me.chkRel5.Value
AddSnapshot!Security1 = Me.chkSec1.Value
AddSnapshot!Security2 = Me.chkSec2.Value
AddSnapshot!Security3 = Me.chkSec3.Value
AddSnapshot!Security4 = Me.chkSec4.Value
AddSnapshot!Security5 = Me.chkSec5.Value
AddSnapshot!Choice1 = Me.chkChoice1.Value
AddSnapshot!Choice2 = Me.chkChoice2.Value
AddSnapshot!Choice3 = Me.chkChoice3.Value
AddSnapshot!Choice4 = Me.chkChoice4.Value
AddSnapshot!Choice5 = Me.chkChoice5.Value
AddSnapshot!Opportunity1 = Me.chkOpp1.Value
AddSnapshot!Opportunity2 = Me.chkOpp2.Value
AddSnapshot!Opportunity3 = Me.chkOpp3.Value
AddSnapshot!Opportunity4 = Me.chkOpp4.Value
AddSnapshot!Opportunity5 = Me.chkOpp5.Value
AddSnapshot!Dignity1 = Me.chkDig1.Value
AddSnapshot!Dignity2 = Me.chkDig2.Value
AddSnapshot!Dignity3 = Me.chkDig3.Value
AddSnapshot!Dignity4 = Me.chkDig4.Value
AddSnapshot!Dignity5 = Me.chkDig5.Value
AddSnapshot!SelfAdvocacy1 = Me.chkSelf1.Value
AddSnapshot!SelfAdvocacy2 = Me.chkSelf2.Value
AddSnapshot!SelfAdvocacy3 = Me.chkSelf3.Value
AddSnapshot!SelfAdvocacy4 = Me.chkSelf4.Value
AddSnapshot!SelfAdvocacy5 = Me.chkSelf5.Value
AddSnapshot!Independence1 = Me.chkIndep1.Value
AddSnapshot!Independence2 = Me.chkIndep2.Value
AddSnapshot!Independence3 = Me.chkIndep3.Value
AddSnapshot!Independence4 = Me.chkIndep4.Value
AddSnapshot!Independence5 = Me.chkIndep5.Value
AddSnapshot!Independence6 = Me.chkIndep6.Value
AddSnapshot!Independence7 = Me.chkIndep7.Value
AddSnapshot!Independence8 = Me.chkIndep8.Value
AddSnapshot!Independence9 = Me.chkIndep9.Value
AddSnapshot!Independence10 = Me.chkIndep10.Value
AddSnapshot!Joy1 = Me.chkJoy1.Value
AddSnapshot!Joy2 = Me.chkJoy2.Value
AddSnapshot!Joy3 = Me.chkJoy3.Value
AddSnapshot!Joy4 = Me.chkJoy4.Value
AddSnapshot!Joy5 = Me.chkJoy5.Value
AddSnapshot!Staff1 = Me.chkStaff1.Value
AddSnapshot!Staff2 = Me.chkStaff2.Value
AddSnapshot!Staff3 = Me.chkStaff3.Value
AddSnapshot!Staff4 = Me.chkStaff4.Value
AddSnapshot!Staff5 = Me.chkStaff5.Value
AddSnapshot!Staff6 = Me.chkStaff6.Value
AddSnapshot.Update
AddSnapshot.Close
DoCmd.OpenForm "frmIndividual", , , , , , [txtIndividualID]
DoCmd.Close acForm, Me.Name
End If
End If


Thanks,

Sergios
 
S

Steve Schapel

Sergios,

Where is the code that we have been discussing? What is the purpose of
putting the txtIndividualID into the OpenArgs argument of the OpenForm
method?

Anyway, try like this...

DoCmd.OpenForm "frmIndividual", , , "[IndividualID]='" &
Me.txtIndividualID & "'"
DoCmd.SelectObject acForm, "frmIndividual"
Forms!frmIndividual!Questionnaire.SetFocus
Forms!frmIndividual!Questionnaire.Form!SnapshotID.SetFocus
DoCmd.FindRecord Me.txtQLSRID
DoCmd.Close acForm, Me.Name

--
Steve Schapel, Microsoft Access MVP

Well...you asked for it :)

Snapshot = Questionnaire
Individual = Person
txtQLSRID = QuestionnaireID
txtIndividualID = PersonID

Those above are the actual names that I used for my database.
Below is my original tested code that takes you back to the person and
the first questionnaire.

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdMainMenu_Click

Dim split_array
split_array = Split(Me.OpenArgs, "|")

Dim AddSnapshot As Recordset

Set AddSnapshot = CurrentDb.OpenRecordset("tblQLSR", dbOpenDynaset)

If split_array(1) = "New" Then
AddSnapshot.AddNew
Else
AddSnapshot.FindFirst "[IndividualID] = '" & split_array(0) & "'
And [SnapshotDate] = #" & split_array(1) & "#"
AddSnapshot.Edit
End If

If split_array(1) = "New" Then
If IsNull(Me.txtIndividualID.Value) Or
IsNull(Me.txtCareCoordinatorFirstName.Value) Or
IsNull(Me.txtCareCoordinatorLastName.Value) Or
IsNull(Me.txtSnapshotDate.Value) Or IsNull(Me.txtTitle.Value) Or
IsNull(Me.txtNetwork.Value) Or IsNull(Me.cboEnvironment.Value) Or
IsNull(Me.Location.Value) Or IsNull(Me.txtQLSRID.Value) Then
MsgBox "All fields are required"
ElseIf MsgBox("Add snapshot?", vbYesNo, "Confirm addition") = vbYes
Then
AddSnapshot!IndividualID = Me.txtIndividualID.Value
AddSnapshot!SnapshotDate = Me.txtSnapshotDate.Value
AddSnapshot!SnapshotID = Me.txtQLSRID.Value
AddSnapshot!EvaluatorFName =
Me.txtCareCoordinatorFirstName.Value
AddSnapshot!EvaluatorLName =
Me.txtCareCoordinatorLastName.Value
AddSnapshot!Title = Me.txtTitle.Value
AddSnapshot!PlanOfCareDate = Me.PlanOfCareDate.Value
AddSnapshot!Network = Me.txtNetwork.Value
AddSnapshot!Environment = Me.cboEnvironment.Value
AddSnapshot!Location = Me.Location.Value
AddSnapshot!Score = Me.txtScore.Value
AddSnapshot!Individuality1 = Me.chkIndiv1.Value
AddSnapshot!Individuality2 = Me.chkIndiv2.Value
AddSnapshot!Individuality3 = Me.chkIndiv3.Value
AddSnapshot!Individuality4 = Me.chkIndiv4.Value
AddSnapshot!Individuality5 = Me.chkIndiv5.Value
AddSnapshot!Integration1 = Me.chkInteg1.Value
AddSnapshot!Integration2 = Me.chkInteg2.Value
AddSnapshot!Integration3 = Me.chkInteg3.Value
AddSnapshot!Integration4 = Me.chkInteg4.Value
AddSnapshot!Integration5 = Me.chkInteg5.Value
AddSnapshot!Relationships1 = Me.chkRel1.Value
AddSnapshot!Relationships2 = Me.chkRel2.Value
AddSnapshot!Relationships3 = Me.chkRel3.Value
AddSnapshot!Relationships4 = Me.chkRel4.Value
AddSnapshot!Relationships5 = Me.chkRel5.Value
AddSnapshot!Security1 = Me.chkSec1.Value
AddSnapshot!Security2 = Me.chkSec2.Value
AddSnapshot!Security3 = Me.chkSec3.Value
AddSnapshot!Security4 = Me.chkSec4.Value
AddSnapshot!Security5 = Me.chkSec5.Value
AddSnapshot!Choice1 = Me.chkChoice1.Value
AddSnapshot!Choice2 = Me.chkChoice2.Value
AddSnapshot!Choice3 = Me.chkChoice3.Value
AddSnapshot!Choice4 = Me.chkChoice4.Value
AddSnapshot!Choice5 = Me.chkChoice5.Value
AddSnapshot!Opportunity1 = Me.chkOpp1.Value
AddSnapshot!Opportunity2 = Me.chkOpp2.Value
AddSnapshot!Opportunity3 = Me.chkOpp3.Value
AddSnapshot!Opportunity4 = Me.chkOpp4.Value
AddSnapshot!Opportunity5 = Me.chkOpp5.Value
AddSnapshot!Dignity1 = Me.chkDig1.Value
AddSnapshot!Dignity2 = Me.chkDig2.Value
AddSnapshot!Dignity3 = Me.chkDig3.Value
AddSnapshot!Dignity4 = Me.chkDig4.Value
AddSnapshot!Dignity5 = Me.chkDig5.Value
AddSnapshot!SelfAdvocacy1 = Me.chkSelf1.Value
AddSnapshot!SelfAdvocacy2 = Me.chkSelf2.Value
AddSnapshot!SelfAdvocacy3 = Me.chkSelf3.Value
AddSnapshot!SelfAdvocacy4 = Me.chkSelf4.Value
AddSnapshot!SelfAdvocacy5 = Me.chkSelf5.Value
AddSnapshot!Independence1 = Me.chkIndep1.Value
AddSnapshot!Independence2 = Me.chkIndep2.Value
AddSnapshot!Independence3 = Me.chkIndep3.Value
AddSnapshot!Independence4 = Me.chkIndep4.Value
AddSnapshot!Independence5 = Me.chkIndep5.Value
AddSnapshot!Independence6 = Me.chkIndep6.Value
AddSnapshot!Independence7 = Me.chkIndep7.Value
AddSnapshot!Independence8 = Me.chkIndep8.Value
AddSnapshot!Independence9 = Me.chkIndep9.Value
AddSnapshot!Independence10 = Me.chkIndep10.Value
AddSnapshot!Joy1 = Me.chkJoy1.Value
AddSnapshot!Joy2 = Me.chkJoy2.Value
AddSnapshot!Joy3 = Me.chkJoy3.Value
AddSnapshot!Joy4 = Me.chkJoy4.Value
AddSnapshot!Joy5 = Me.chkJoy5.Value
AddSnapshot!Staff1 = Me.chkStaff1.Value
AddSnapshot!Staff2 = Me.chkStaff2.Value
AddSnapshot!Staff3 = Me.chkStaff3.Value
AddSnapshot!Staff4 = Me.chkStaff4.Value
AddSnapshot!Staff5 = Me.chkStaff5.Value
AddSnapshot!Staff6 = Me.chkStaff6.Value
AddSnapshot.Update


If MsgBox("Snapshot added. Choose YES to add another snapshot or NO
to return to View Data", vbYesNo, "Select Yes/No") = vbYes Then
Me.txtQLSRID.Value = ""
Me.txtSnapshotDate.Value = ""
Me.txtCareCoordinatorFirstName.Value = ""
Me.txtCareCoordinatorLastName.Value = ""
Me.txtTitle.Value = ""
Me.PlanOfCareDate.Value = ""
Me.txtNetwork.Value = ""
Me.cboEnvironment.Value = ""
Me.Location.Value = ""
Me.chkIndiv1.Value = ""
Me.chkIndiv2.Value = ""
Me.chkIndiv3.Value = ""
Me.chkIndiv4.Value = ""
Me.chkIndiv5.Value = ""
Me.chkInteg1.Value = ""
Me.chkInteg2.Value = ""
Me.chkInteg3.Value = ""
Me.chkInteg4.Value = ""
Me.chkInteg5.Value = ""
Me.chkRel1.Value = ""
Me.chkRel2.Value = ""
Me.chkRel3.Value = ""
Me.chkRel4.Value = ""
Me.chkRel5.Value = ""
Me.chkSec1.Value = ""
Me.chkSec2.Value = ""
Me.chkSec3.Value = ""
Me.chkSec4.Value = ""
Me.chkSec5.Value = ""
Me.chkChoice1.Value = ""
Me.chkChoice2.Value = ""
Me.chkChoice3.Value = ""
Me.chkChoice4.Value = ""
Me.chkChoice5.Value = ""
Me.chkOpp1.Value = ""
Me.chkOpp2.Value = ""
Me.chkOpp3.Value = ""
Me.chkOpp4.Value = ""
Me.chkOpp5.Value = ""
Me.chkDig1.Value = ""
Me.chkDig2.Value = ""
Me.chkDig3.Value = ""
Me.chkDig4.Value = ""
Me.chkDig5.Value = ""
Me.chkSelf1.Value = ""
Me.chkSelf2.Value = ""
Me.chkSelf3.Value = ""
Me.chkSelf4.Value = ""
Me.chkSelf5.Value = ""
Me.chkIndep1.Value = ""
Me.chkIndep2.Value = ""
Me.chkIndep3.Value = ""
Me.chkIndep4.Value = ""
Me.chkIndep5.Value = ""
Me.chkIndep6.Value = ""
Me.chkIndep7.Value = ""
Me.chkIndep8.Value = ""
Me.chkIndep9.Value = ""
Me.chkIndep10.Value = ""
Me.chkJoy1.Value = ""
Me.chkJoy2.Value = ""
Me.chkJoy3.Value = ""
Me.chkJoy4.Value = ""
Me.chkJoy5.Value = ""
Me.chkStaff1.Value = ""
Me.chkStaff2.Value = ""
Me.chkStaff3.Value = ""
Me.chkStaff4.Value = ""
Me.chkStaff5.Value = ""
Me.chkStaff6.Value = ""

Else
AddSnapshot.Close
DoCmd.OpenForm "frmIndividual", , , , , , [txtIndividualID]
DoCmd.Close acForm, Me.Name

End If
End If
ElseIf MsgBox("Submit changes and exit?", vbYesNo, "Edit Snapshot") =
vbYes Then
If IsNull(Me.txtIndividualID.Value) Or
IsNull(Me.txtCareCoordinatorFirstName.Value) Or
IsNull(Me.txtCareCoordinatorLastName.Value) Or
IsNull(Me.txtSnapshotDate.Value) Or IsNull(Me.txtTitle.Value) Or
IsNull(Me.txtNetwork.Value) Or IsNull(Me.cboEnvironment.Value) Or
IsNull(Me.Location.Value) Then
MsgBox "All fields are required"
Else
AddSnapshot!IndividualID = Me.txtIndividualID.Value
AddSnapshot!SnapshotDate = Me.txtSnapshotDate.Value
AddSnapshot!SnapshotID = Me.txtQLSRID.Value
AddSnapshot!EvaluatorFName =
Me.txtCareCoordinatorFirstName.Value
AddSnapshot!EvaluatorLName =
Me.txtCareCoordinatorLastName.Value
AddSnapshot!Title = Me.txtTitle.Value
AddSnapshot!PlanOfCareDate = Me.PlanOfCareDate.Value
AddSnapshot!Network = Me.txtNetwork.Value
AddSnapshot!Environment = Me.cboEnvironment.Value
AddSnapshot!Location = Me.Location.Value
AddSnapshot!Score = Me.txtScore.Value
AddSnapshot!Individuality1 = Me.chkIndiv1.Value
AddSnapshot!Individuality2 = Me.chkIndiv2.Value
AddSnapshot!Individuality3 = Me.chkIndiv3.Value
AddSnapshot!Individuality4 = Me.chkIndiv4.Value
AddSnapshot!Individuality5 = Me.chkIndiv5.Value
AddSnapshot!Integration1 = Me.chkInteg1.Value
AddSnapshot!Integration2 = Me.chkInteg2.Value
AddSnapshot!Integration3 = Me.chkInteg3.Value
AddSnapshot!Integration4 = Me.chkInteg4.Value
AddSnapshot!Integration5 = Me.chkInteg5.Value
AddSnapshot!Relationships1 = Me.chkRel1.Value
AddSnapshot!Relationships2 = Me.chkRel2.Value
AddSnapshot!Relationships3 = Me.chkRel3.Value
AddSnapshot!Relationships4 = Me.chkRel4.Value
AddSnapshot!Relationships5 = Me.chkRel5.Value
AddSnapshot!Security1 = Me.chkSec1.Value
AddSnapshot!Security2 = Me.chkSec2.Value
AddSnapshot!Security3 = Me.chkSec3.Value
AddSnapshot!Security4 = Me.chkSec4.Value
AddSnapshot!Security5 = Me.chkSec5.Value
AddSnapshot!Choice1 = Me.chkChoice1.Value
AddSnapshot!Choice2 = Me.chkChoice2.Value
AddSnapshot!Choice3 = Me.chkChoice3.Value
AddSnapshot!Choice4 = Me.chkChoice4.Value
AddSnapshot!Choice5 = Me.chkChoice5.Value
AddSnapshot!Opportunity1 = Me.chkOpp1.Value
AddSnapshot!Opportunity2 = Me.chkOpp2.Value
AddSnapshot!Opportunity3 = Me.chkOpp3.Value
AddSnapshot!Opportunity4 = Me.chkOpp4.Value
AddSnapshot!Opportunity5 = Me.chkOpp5.Value
AddSnapshot!Dignity1 = Me.chkDig1.Value
AddSnapshot!Dignity2 = Me.chkDig2.Value
AddSnapshot!Dignity3 = Me.chkDig3.Value
AddSnapshot!Dignity4 = Me.chkDig4.Value
AddSnapshot!Dignity5 = Me.chkDig5.Value
AddSnapshot!SelfAdvocacy1 = Me.chkSelf1.Value
AddSnapshot!SelfAdvocacy2 = Me.chkSelf2.Value
AddSnapshot!SelfAdvocacy3 = Me.chkSelf3.Value
AddSnapshot!SelfAdvocacy4 = Me.chkSelf4.Value
AddSnapshot!SelfAdvocacy5 = Me.chkSelf5.Value
AddSnapshot!Independence1 = Me.chkIndep1.Value
AddSnapshot!Independence2 = Me.chkIndep2.Value
AddSnapshot!Independence3 = Me.chkIndep3.Value
AddSnapshot!Independence4 = Me.chkIndep4.Value
AddSnapshot!Independence5 = Me.chkIndep5.Value
AddSnapshot!Independence6 = Me.chkIndep6.Value
AddSnapshot!Independence7 = Me.chkIndep7.Value
AddSnapshot!Independence8 = Me.chkIndep8.Value
AddSnapshot!Independence9 = Me.chkIndep9.Value
AddSnapshot!Independence10 = Me.chkIndep10.Value
AddSnapshot!Joy1 = Me.chkJoy1.Value
AddSnapshot!Joy2 = Me.chkJoy2.Value
AddSnapshot!Joy3 = Me.chkJoy3.Value
AddSnapshot!Joy4 = Me.chkJoy4.Value
AddSnapshot!Joy5 = Me.chkJoy5.Value
AddSnapshot!Staff1 = Me.chkStaff1.Value
AddSnapshot!Staff2 = Me.chkStaff2.Value
AddSnapshot!Staff3 = Me.chkStaff3.Value
AddSnapshot!Staff4 = Me.chkStaff4.Value
AddSnapshot!Staff5 = Me.chkStaff5.Value
AddSnapshot!Staff6 = Me.chkStaff6.Value
AddSnapshot.Update
AddSnapshot.Close
DoCmd.OpenForm "frmIndividual", , , , , , [txtIndividualID]
DoCmd.Close acForm, Me.Name
End If
End If


Thanks,

Sergios
Click to expand...
 
S

sergiosr

Hi Steve,

The code i posted here was the code that I used before trying to do any
changes. txtIndividualID was in the OpenArgs because this way I could
return to the same person after editing him for example. I was using
the following code when I was opening the person form:

Me.Recordset.FindFirst "[IndividualID] = '" & Me.OpenArgs & "'"

Anyway it works now but it's not convenient that a filter is created
and I cannot move to another person. I know that you told me I can add
a command button to reset the record source but I think that is not
very user friendly.

Thanks for your help,

Sergios
 
S

Steve Schapel

Sergios,

Ah! I misunderstood you. I thought you were referring to the
Questionnaire subform when you were complaining about the "filter", not
the main Person form.

Well, you could try an adaptation of the FindRecord code I suggested
before, if access to all Person records is what you want. Something
like this...

DoCmd.OpenForm "frmIndividual"
DoCmd.SelectObject acForm, "frmIndividual"
Forms!frmIndividual!IndividualID.SetFocus
DoCmd.FindRecord Me.txtIndividualID
Forms!frmIndividual!Questionnaire.SetFocus
Forms!frmIndividual!Questionnaire.Form!SnapshotID.SetFocus
DoCmd.FindRecord Me.txtQLSRID
DoCmd.Close acForm, Me.Name
 
S

sergiosr

I think you misunderstood me again:)

I want to re-open the person form showing the questionnaire that was
edited. I don't want a filter that prevents me from seeing all the
other persons.

Your last code takes me back to where I was. It takes me back to the
specific person but not to the specific questionnaire.

Thanks,

Sergios
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Editing records from two different tables on a single form 1
How to refresh a single record in a datasheet subform? 1
Open form for record selected in subform 2
Open form to a specific record? 5
Opening a subform in Add Mode 4
Autofill subform when move to new record on Main form 30
intermittent problem bookmarking record to subform of itself 0
excel form populating in access 02-03 but not 07 0

Top