Open based on two values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Dan @BCBS said:
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
Click to expand...


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
 
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?




Marshall Barton said:
Dan @BCBS said:
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
Click to expand...


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
Click to expand...
 
' 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
 
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]


Dan @BCBS said:
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..

Dan @BCBS said:
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
Click to expand...
Click to expand...

Marshall Barton said:
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
Click to expand...
Click to expand...
 
Back
Top