Create Function to Open either Form2 or Form3 from Form1 3

[twilightsilhouette]

I have a button on form1 to open another form. Upon clicking the button, depending on information in the form1, it will either open form2 or form3. Open opening either form it filters to match specific form1 fields- if no matches it needs to create a new record with those fields pre-filled in. I know how to do this for each criteria but was hoping to create a function so I don't have to type it over and over. I can't figure out how to say "Forms!#variablestring#!ID"

For example:

Private Sub cmdOpenSpecificForms_Click()

dim stLinkCriteria as string
dim stID as string
dim stDate as string

stLinkCriteria = "[ID]=" & Me![ID] & "And" & "[Date]=" & "#"
stID = Me!ID
stDate = Me!Date

'then i go into an if for each field called 'classification'
If Me![Classification] = "Avulsion" Then
'this is the filter
DoCmd.OpenForm "AVULSION", , , stLinkCriteria [blue]'
Forms!Avulsion.SetFocus
'this creates the new record if no records matched in the inital filter
If IsNull(Forms!Avulsion!PatientID) Or IsNull(Forms!Avulsion!Date) Then
DoCmd.GoToRecord , , acNewRec
Forms!Avulsion!PatientID = stPatientID
Forms!Avulsion!Date = stDate
Forms!Avulsion![Tooth#] = stTooth
End If[/blue]
Else
If Me![Classification] = "Intrusion" Then
DoCmd.OpenForm "INTRUSION", , , stLinkCriteria
[Blue]Forms!INTRUSION.SetFocus
If IsNull(Forms!INTRUSION!PatientID) Or IsNull(Forms!INTRUSION!Date) Then
DoCmd.GoToRecord , , acNewRec
Forms!INTRUSION!ID = stId
Forms!INTRUSION!Date = stDate
End If[/blue]
etc etc etc
End Sub

Notice the blue parts are so similar. I tried to create stFormName as string, then under each if set it to the proper form name then create a function that uses it. My problem is

Forms!stFormName!ID = stID thinks stFormName is the actual form name. How can I pass a variable to Forms!____!ID in another function???

[blue]Function checkToOpenNewRec()
Forms!____.SetFocus
If IsNull(Forms!____!ID) Or IsNull(stFoForms!____!Date) Then
DoCmd.GoToRecord , , acNewRec
Forms!____!PatientID = stPatientId
Forms!____!Date = stDate
End If
Exit Function[/blue]

 

[RoyVidar]

Hi!

Is this what you're after?

[tt]Forms!Avulsion.PatientID

Forms("Avulsion")("PatientID")

strFormName = "Avulsion"
strControlName = "PatientID"

Forms(strFormName)(strControlName)[/tt]

Roy-Vidar

 

[hneal98]

Function checkToOpenNewRec(FormName as Form)
FormName.SetFocus
If IsNull(FormName!ID) Or IsNull(FormName!Date) Then
DoCmd.GoToRecord , , acNewRec
FormName!PatientID = stPatientId
FormName!Date = stDate
End If
Exit Function

Should work. You call the function like this:

checkToOpenNewRec(Forms!INTRUSION)

 

[twilightsilhouette]

Not sure...

I haven't created functions in a long time.
How do I pass the variables & call the function?
Sub
dim stFormName
dim stId
dim stDate

stFormName="Avulsion"
stID = Me!ID
stDate = Me!Date

checkToOpenNewRec (stFormName And stId And stDate)
End Sub


and then the function

Function checkToOpenNewRec ()
Forms!(stFormName).SetFocus
If IsNull(Forms!(stFormName)!ID) Or IsNull(Forms!(stFormName)!Date) Then
DoCmd.GoToRecord , , acNewRec
Forms!(stFormName)!ID = stId
Forms!(stFormName)!Date = stDate
End If
End Function

By doing it this way I get an error "Type Mismatch

 

[twilightsilhouette]

Type mismatch on Forms![highlight]Avulsion[/highlight]

this is what i have

Function checkToOpenNewRec(FormName As Form, stPatientID As String, stDate As String)
FormName.SetFocus
If IsNull(FormName!PatientID) Or IsNull(FormName!Date) Then
DoCmd.GoToRecord , , acNewRec
FormName!PatientID = stPatientID
FormName!Date = stDate
End If
Exit Function
End Function

calling by
If Me![Classification] = "Avulsion" Then
DoCmd.OpenForm "AVULSION", , , stLinkCriteria
checkToOpenNewRec (Forms![highlight]Avulsion[/highlight] And stPatientID And stDate)


Don't understand the type mismatch error.

 

[hneal98]

In your code, why do you have 'AND' when calling the function? You need to seperate the list using a comma.

 

[twilightsilhouette]

Yes, that makes sense.

But when I do this I get:

Combile Error: Expected: =

This message box shows up at the end of the ')' of the function.

 

[hneal98]

My fault. Change it to a sub instead of a function and remove the ( and ) in the call.

 

[twilightsilhouette]

Still getting the same Compile error as before with it as a sub instead of a function.

 

[hneal98]

Paste your function/Sub and your calling statement again. I just tested it and it worked fine.

 

[twilightsilhouette]

Okay it's kinda long though! I'm just testing it with Avulsion first... What I have in the If section for Intrusion works fine. I guess I can just go back to typing it for each IF if nothing else. The highlight red is where I'm getting the compile error.

Private Sub cmdOpenSpecificInjuryForms_Click()
On Error GoTo Err_cmdOpenSpecificInjuryForms_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stPatientID As String
    Dim stDate As String
    Dim stTooth As String
    Dim stClassification As String
    Dim stRoot As String
    Dim stEnamel As String
    Dim stDentin As String
    Dim stPulp As String
    Dim stCementum As String

        
    stLinkCriteria = "[PatientID]=" & Me![PatientID] & "And" & "[Date]=" & "#" & Me![Date] & "#" & "And" & "[Tooth#]=" & Me![Tooth#]
    stPatientID = Me!PatientID
    stDate = Me!Date
    stTooth = Me![Tooth#]
    stClassification = Me!Classification
    stRoot = Me!RootFracture
    stEnamel = Me!Enamel
    stDentin = Me!Dentin
    stPulp = Me!Pulp
    stCementum = Me!Cementum
  
  
    If Me![Classification] = "Avulsion" Then
            
            DoCmd.OpenForm "AVULSION", , , stLinkCriteria
            
            [red]checkToOpenNewRec (Forms!Avulsion, stPatientID, stDate, stTooth, stClassification, stRoot, stEnamel,stDentin, stPulp, stCementum)[/red]
        Else
            If Me![Classification] = "Intrusion" Then
                DoCmd.OpenForm "INTRUSION", , , stLinkCriteria
                Forms!INTRUSION.SetFocus
            If IsNull(Forms!INTRUSION!PatientID) Or IsNull(Forms!INTRUSION!Date) Then
                DoCmd.GoToRecord , , acNewRec
                Forms!INTRUSION!PatientID = stPatientID
                Forms!INTRUSION!Date = stDate
                Forms!INTRUSION![Tooth#] = stTooth
                Forms!INTRUSION!RootFracture = stRoot
                Forms!INTRUSION!Enamel = stEnamel
                Forms!INTRUSION!Dentin = stDentin
                Forms!INTRUSION!Pulp = stPulp
                Forms!INTRUSION!Cementum = stCementum
            End If
      Else
            If Me![Classification] = "Lateral Luxation" Then
                DoCmd.OpenForm "LATERAL_LUXATION", , , stLinkCriteria
            
    Else
            If Me![Classification] = "Subluxation" Then
                DoCmd.OpenForm "SUBLUXATION", , , stLinkCriteria
            
    Else
            If Me![Classification] = "Lateral Luxation" Then
                DoCmd.OpenForm "LATERAL_LUXATION", , , stLinkCriteria
    Else
            If Me![Classification] = "Concussion" And Me![RootFracture] = False And Me![Enamel] = False And Me![Dentin] = False And Me![Pulp] = False And Me![Cementum] = False Then
                MsgBox "There is no more concussion information needed. " & Chr(13) & _
                "Please either continue to the next tooth, or save and close this form."
           
            End If
            End If
            End If
            End If
            End If
    End If
    
    Sub checkToOpenNewRec(FormName As Form, stPatientID As String, stDate As String, stTooth As String, stClassification As String, stRoot As String, stEnamel As String, stDentin As String, stPulp As String, stCementum As String)
        FormName.SetFocus
        If IsNull(FormName!PatientID) Or IsNull(FormName!Date) Then
            DoCmd.GoToRecord , , acNewRec
            FormName!PatientID = stPatientID
            FormName!Date = stDate
            FormName![Tooth#] = stTooth
            FormName!RootFracture = stRoot
            FormName!Enamel = stEnamel
            FormName!Dentin = stDentin
            FormName!Pulp = stPulp
            FormName!Cementum = stCementum
        End If
    Exit Sub
    
Exit_cmdOpenSpecificInjuryForms_Click:
    Exit Sub

Err_cmdOpenSpecificInjuryForms_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenSpecificInjuryForms_Click
End Sub

 

[hneal98]

The portion that is in red, still has the ( and ). remove them for subs. Not sure if that is what the problem is off hand, but try it while I continue to look it over.

 

[hneal98]

I am sure you did not mean to do this, but you have a sub within a sub. That can not happen. You need to take this whole part:

Sub checkToOpenNewRec(FormName As Form, stPatientID As String, stDate As String, stTooth As String, stClassification As String, stRoot As String, stEnamel As String, stDentin As String, stPulp As String, stCementum As String)
FormName.SetFocus
If IsNull(FormName!PatientID) Or IsNull(FormName!Date) Then
DoCmd.GoToRecord , , acNewRec
FormName!PatientID = stPatientID
FormName!Date = stDate
FormName![Tooth#] = stTooth
FormName!RootFracture = stRoot
FormName!Enamel = stEnamel
FormName!Dentin = stDentin
FormName!Pulp = stPulp
FormName!Cementum = stCementum
End If
Exit Sub

and put it out side of the bounds of the sub/end sub of the your main sub. In otherwords, the above needs to reside in its own space.

Let me know if that does not make sense.

 

[twilightsilhouette]

Yes, I noticed the sub w/in a sub after I posted and changed it to its own section. Still got same error.

I'm not sure what you mean by it still has the ( and )?? Are you saying that the commas still mean and?

 

[twilightsilhouette]

Oh! I feel really dumb! You mean the paranthesis "(" & ")"! Gotcha. I'll try removing that, but how will it send those values to the function? Maybe I'm confusing myself.

 

[twilightsilhouette]

Works perfectly (knock on wood) when I take out the ()! Thanks so much for dealing with me. I really appreciate it!!!!

 

[hneal98]

No problem.

 

[RoyVidar]

twilightsilhouette!

Wouldn't you say a little pinkie to hneal98 would be appropriate?

Here's one from me, good job!

Roy-Vidar

 

[hneal98]

Thanks.