HELP! Selecting various forms from a combo box

[Kath via AccessMonster.com]

Hello, I am in dire need of some help. I have a combo box that when a user
selects it, it is supposed to do two things:
1) Open the appropriate form referenced in a case statement
2) Pass the PI_ID and PI_SITE_ID fields to new records in the form OR
2a) if existing record go to the record for that PI entry based on the PI_ID
and the PI_SITE_ID.

I cannot get it to work at all. I am not sure what the problem may be or if
there is an easier way to achieve this? Any help would be GREATLY
appreciated.

~Kath


The code being used is as follows:
-----------------------------------------------
Private Sub cboPI_ID_Change()
Call openForm
End Sub
------------------------------------------------
Private Sub openForm()
Dim pstrSQL As String
Dim pobjRS As ADODB.Recordset

Select Case Me.cboPI_ID
Case "ADM-1"
Call openTheForm("frmADM_1", "tblADM_1")
Case "ADM-2"
Call openTheForm("frmADM_2", "tblADM_2")
Case "ADM-3"
Call openTheForm("frmADM_3", "tblADM_3")
Case "ADM-4"
Call openTheForm("frmADM_4", "tblADM_4")
End Select
'*This goes on for all the PI codes.
End Sub
-----------------------------------------------------------
Private Sub openTheForm(astrFormName As String, astrTableName As String)

If recordExists(astrTableName) Then
'open in readonly mode and filtered
Call DoCmd.openForm(astrFormName, , , "PI_E_ID = " & Me.PI_E_ID & "
and PI_SITE_ID = '" & Me.PI_SITE_ID & "'", , acDialog, Forms!frmPIM_E.PI_E_ID
& "," & Forms!frmPIM_E.PI_SITE_ID)
Else
'open in add mode
Call DoCmd.openForm(astrFormName, , , , , acDialog, Forms!frmPIM_E.
PI_E_ID & "," & Forms!frmPIM_E.PI_SITE_ID)
End If

End Sub
------------------------------------------------------------------------------
--------------------
Private Function recordExists(astrTableName As String) As Boolean

On Error GoTo Err

Dim pobjConn As ADODB.Connection
Dim pobjRS As ADODB.Recordset
Dim pblnRetval As Boolean

pblnRetval = False

Set pobjConn = CurrentProject.AccessConnection
Set pobjRS = New ADODB.Recordset

pstrSQL = "select count(*) from " & astrTableName & " where PI_E_ID = " &
Me.PI_E_ID
pstrSQL = pstrSQL & " and PI_SITE_ID = '" & Me.PI_SITE_ID & "' "
Call pobjRS.Open(pstrSQL, pobjConn, adOpenStatic, adLockReadOnly)

pblnRetval = (pobjRS(0) > 0)

ProcExit:
recordExists = pblnRetval
If pobjRS.State = ADODB.adStateOpen Then
pobjRS.Close
End If
Set pobjRS = Nothing
Exit Function
Err:
Call MsgBox(Err.Description, vbOKOnly, "Error in Program")
Resume ProcExit
End Function

----------------------------------------------------------------
'On the each form to be opened there is on the load event:
-----------------------------------------------------
Private Sub Form_Load()
Dim pstrArgs As String
Dim paryArgs() As String
DoCmd.Maximize

pstrArgs = Me.OpenArgs
paryArgs = Split(pstrArgs, ",")

Forms!frmCA_1_2.PI_E_ID = paryArgs(0)
Forms!frmCA_1_2.PI_SITE_ID = paryArgs(1)
End Sub

 

[kathieglassel via AccessMonster.com]

Nevermind on this one....redid the forms to make it work.