Change record source

[Tara]

I have a form that I need to use in 2 different types of circumstances. In
one, it is used to add a child to the record of an existing client. In order
to get it to link correctly, I tried to use the following code:

Private Sub CommandAddChild_Click()
On Error GoTo Err_CommandAddChild_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddChild"

stLinkCriteria = "[CaseID]=" & Me![CaseID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CommandAddChild_Click:
Exit Sub

Err_CommandAddChild_Click:
MsgBox Err.Description
Resume Exit_CommandAddChild_Click

End Sub

It doesn't work at all. I have no idea why not and I'd like to figure it
out. But, in the meantime, in order to just get it up and going, I decided
to go another route. I ended up setting the default value of CaseID on
frmAddChild to whatever the value of CaseID was on the previous form. In
this case it is: =[Forms]![frmClientCase]![CaseID] That setup works.
However it creates a problem when I try to use the form for the second
circumstance, because frmClientCase is not the form that is open when
frmAddChild is called, frmNEWClientCase is the open form. So, I guess what I
really need to do is figure out why the code I first tried to execute doesn't
work at all. If I get that figured out, I can use the same code in both of
the different circumstances.

Any help is greatly appreciated!

 

[Klatuu]

You would make your life a lot easier if you would use a form/subform. The
Client should be the record source for the form and the Child the record
source for the subform.

 

[Tara]

Thanks for the suggestion and I completely agree. But due to the visual
design of the particular forms I'm working with, I'm trying to stay away from
subforms. Do you have any suggestions for making it work the way I currently
have it?

Thanks!

You would make your life a lot easier if you would use a form/subform. The
Client should be the record source for the form and the Child the record
source for the subform.
--
Dave Hargis, Microsoft Access MVP

I have a form that I need to use in 2 different types of circumstances. In
one, it is used to add a child to the record of an existing client. In order
to get it to link correctly, I tried to use the following code:

Private Sub CommandAddChild_Click()
On Error GoTo Err_CommandAddChild_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddChild"

stLinkCriteria = "[CaseID]=" & Me![CaseID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CommandAddChild_Click:
Exit Sub

Err_CommandAddChild_Click:
MsgBox Err.Description
Resume Exit_CommandAddChild_Click

End Sub

It doesn't work at all. I have no idea why not and I'd like to figure it
out. But, in the meantime, in order to just get it up and going, I decided
to go another route. I ended up setting the default value of CaseID on
frmAddChild to whatever the value of CaseID was on the previous form. In
this case it is: =[Forms]![frmClientCase]![CaseID] That setup works.
However it creates a problem when I try to use the form for the second
circumstance, because frmClientCase is not the form that is open when
frmAddChild is called, frmNEWClientCase is the open form. So, I guess what I
really need to do is figure out why the code I first tried to execute doesn't
work at all. If I get that figured out, I can use the same code in both of
the different circumstances.

Any help is greatly appreciated!

 

[Klatuu]

stLinkCriteria = "[CaseID]=" & Me![CaseID]
You said you want to open the form to add a new child. The question is,
then, in the form your are opening, "frmAddChild", does the CaseID already
exist? If not, you will get nothing. If you need to add a new record, then
you should pass the CaseID using the OpenArgs argument of the OpenForm
method. and use the OpenArgs property of frmAddChild as the value to use when
adding the new record.
--
Dave Hargis, Microsoft Access MVP

Thanks for the suggestion and I completely agree. But due to the visual
design of the particular forms I'm working with, I'm trying to stay away from
subforms. Do you have any suggestions for making it work the way I currently
have it?

Thanks!

You would make your life a lot easier if you would use a form/subform. The
Client should be the record source for the form and the Child the record
source for the subform.
--
Dave Hargis, Microsoft Access MVP

I have a form that I need to use in 2 different types of circumstances. In
one, it is used to add a child to the record of an existing client. In order
to get it to link correctly, I tried to use the following code:

Private Sub CommandAddChild_Click()
On Error GoTo Err_CommandAddChild_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddChild"

stLinkCriteria = "[CaseID]=" & Me![CaseID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CommandAddChild_Click:
Exit Sub

Err_CommandAddChild_Click:
MsgBox Err.Description
Resume Exit_CommandAddChild_Click

End Sub

It doesn't work at all. I have no idea why not and I'd like to figure it
out. But, in the meantime, in order to just get it up and going, I decided
to go another route. I ended up setting the default value of CaseID on
frmAddChild to whatever the value of CaseID was on the previous form. In
this case it is: =[Forms]![frmClientCase]![CaseID] That setup works.
However it creates a problem when I try to use the form for the second
circumstance, because frmClientCase is not the form that is open when
frmAddChild is called, frmNEWClientCase is the open form. So, I guess what I
really need to do is figure out why the code I first tried to execute doesn't
work at all. If I get that figured out, I can use the same code in both of
the different circumstances.

Any help is greatly appreciated!