Open based on two values

[Guest]

The command button code below is pretty simple it just opens a form based on
a value called "ICNNo" which is on both forms.

This database is uses for paying claims to doctors.
This main form is for each claim and has a submenu showing mulitple doctors
associated with that claim.

I'm trying to make the form open to a specific doctor number.
WHen I click the code below - How can I make it say Please enter a provider
number! Then open the form based on both the matching ICCNo and the entered
Provider Number???


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProviderDetailEDIT"

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

Exit_cmdProvEdit_Click:
Exit Sub

 

[Marshall Barton]

The command button code below is pretty simple it just opens a form based on
a value called "ICNNo" which is on both forms.

This database is uses for paying claims to doctors.
This main form is for each claim and has a submenu showing mulitple doctors
associated with that claim.

I'm trying to make the form open to a specific doctor number.
WHen I click the code below - How can I make it say Please enter a provider
number! Then open the form based on both the matching ICCNo and the entered
Provider Number???


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProviderDetailEDIT"

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

Why would you want to prompt for a value when you already
have a form where it could be entered?

I suggest that you add another text box (named txtDoctorNo)
to the form with the button for users to enter the doctot
number. Then the code could be changed to:

stLinkCriteria = "ICNNo='" & Me!ICNNo & _
"' And DoctorNo=" & Me.txtDoctorNo

 

[Guest]

It's (doctors) already entered in the subform.
There can be multiple doctors for each claim! That is why I have a subform
listing the doctors.

Somehow - I need to be able to pick from the list of doctors (in the
subform) and open the doctor form associated with that claim..

suggestions?

The command button code below is pretty simple it just opens a form based on
a value called "ICNNo" which is on both forms.

This database is uses for paying claims to doctors.
This main form is for each claim and has a submenu showing mulitple doctors
associated with that claim.

I'm trying to make the form open to a specific doctor number.
WHen I click the code below - How can I make it say Please enter a provider
number! Then open the form based on both the matching ICCNo and the entered
Provider Number???


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProviderDetailEDIT"

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

Why would you want to prompt for a value when you already
have a form where it could be entered?

I suggest that you add another text box (named txtDoctorNo)
to the form with the button for users to enter the doctot
number. Then the code could be changed to:

stLinkCriteria = "ICNNo='" & Me!ICNNo & _
"' And DoctorNo=" & Me.txtDoctorNo

 

[Guest]

' In a module
Public Function IsOpen(strForm As String) As Boolean
IsOpen = (SysCmd(acSysCmdGetObjectState, acForm, strForm) > 0)
End Function

' In frm_search
Private frm_detail As Form_frm_detail

If IsOpen("frm_detail") And Not frm_detail Is Nothing Then
frm_detail.CallingName = strTargetKeyString
Else
DoCmd.OpenForm "frm_detail", _
DataMode:=acFormEdit, _
WindowMode:=acWindowNormal, _
OpenArgs:=Trim(CStr(lngRecordID)) & "^" & strTargetKeyString &
"^" & Me.Name
Set frm_detail = Application.Forms("frm_detail")
End If

' If the calling form is a subform then the above open must use the parent
form name
' Use Parent.Name rather than Me.Name

' ****In frm_detail*****

Private ParentForm As Form
Private ParentFormName As Variant
Private lngRecordID As Long

Property Set ParentObject(fValue As Form)
Set ParentForm = fValue
ParentFormName = ParentForm.Name
End Property

Property Let CallingName(vValue As Variant)
' put code in here to initialize your form
' run a parameterized query to retrieve the desired records
' set default values for controls
End Property

Private Sub Form_Load()
Dim lngOne As Long
Dim vTargetKey As Variant
Dim vCallingForm As Variant
Dim vInstanceID As Variant

If Not IsNull(Me.OpenArgs) Then

lngOne = 1
vInstanceID = Trim(Nz(Left(Me.OpenArgs, InStr(lngOne, Me.OpenArgs,
"^") - 1), ""))
lngOne = InStr(lngOne, Me.OpenArgs, "^") + 1
vTargetKey = Trim(Nz(Mid(Me.OpenArgs, lngOne, InStr(lngOne,
Me.OpenArgs, "^") - lngOne),

""))
lngOne = InStr(lngOne, Me.OpenArgs, "^")
vCallingForm = Trim(Nz(Right(Me.OpenArgs, Len(Me.OpenArgs) -
lngOne), ""))
Set ParentObject = Application.Forms(vCallingForm)
' If the calling form is a subform use the following
' Set ParentObject = Application.Forms(vCallingForm).subformname.Form
lngRecordID = CLng(Nz(vInstanceID, "0"))
CallingName = vTargetKey ' A method to initialize the form

End If
End Sub

' Now each form can call methods in the other

 

[Marshall Barton]

If you want to open the provider detail form to the doctor
in the current doctor record in the subform:

Assuming the DoctorNo field in the doctors table is a number
type field:

stLinkCriteria = "ICNNo='" & Me!ICNNo & _
"' And DoctorNo=" & Me.subformcontrol.Form.DoctorNo
--
Marsh
MVP [MS Access]

It's (doctors) already entered in the subform.
There can be multiple doctors for each claim! That is why I have a subform
listing the doctors.

Somehow - I need to be able to pick from the list of doctors (in the
subform) and open the doctor form associated with that claim..

The command button code below is pretty simple it just opens a form based on
a value called "ICNNo" which is on both forms.

This database is uses for paying claims to doctors.
This main form is for each claim and has a submenu showing mulitple doctors
associated with that claim.

I'm trying to make the form open to a specific doctor number.
WHen I click the code below - How can I make it say Please enter a provider
number! Then open the form based on both the matching ICCNo and the entered
Provider Number???


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProviderDetailEDIT"

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

Why would you want to prompt for a value when you already
have a form where it could be entered?

I suggest that you add another text box (named txtDoctorNo)
to the form with the button for users to enter the doctot
number. Then the code could be changed to:

stLinkCriteria = "ICNNo='" & Me!ICNNo & _
"' And DoctorNo=" & Me.txtDoctorNo