If statements directing to different forms and populating field

[jheintz57]

I have this script below which is supposed to open on form if "[solid_waste?]" is true and another if false. Both forms might not have a record in the required table yet and I need to be able to put the Manifest_ID into each if that is the case. Access is saying their is a data type mismatch but won't show me where so I need some help.

Thanks

Private Sub Command24_Click()

On Error GoTo Err_Command24_Click
Dim stForm As String
Dim stDocName As String
Dim stTable As String
Dim stID As String
Dim stLinkCriteria As String
Dim intCounter As Integer

If Me.[solid_waste?] = True Then

stForm = "frm_RCRA" And stTable = "tbl_RCRA" And stID = "RCRA_ID"

Else

stForm = "frm_DOT" And stTable = "tbl_DOT" And stID = "DOT_ID"

End If

intCounter = IIf(IsNull(DCount("[stID]", "stTable", "[manifest_id]=" & Me.Manifest_ID)), 0, DCount("[stID]", "stTable", "[manifest_id]=" & Me.Manifest_ID))
Select Case intCounter
Case Is = 0
DoCmd.OpenForm "stForm", , , , acFormAdd


Forms!stForm!Manifest_ID = Me.Manifest_ID
Case Is >= 1

stDocName = "stForm"

stLinkCriteria = "[manifest_id]=" & Me![manfiest_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Select
Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

[RuralGuy]

I'll get you started but you may have other issues. Try:
stForm = "stTable = 'tbl_RCRA' And stID = " & RCRA_ID
This is just a guess.

[jheintz57]

Are there missing" etc in the line you types or should I replace the entire
stForm = "frm_RCRA" And stTable = "tbl_RCRA" And stID = "RCRA_ID"

with

stForm = "stTable = 'tbl_RCRA' And stID = " & RCRA_ID

??

[RuralGuy]

Replace the entire line to start with. There are *no* missing quotes in the line I supplied.

[jheintz57]

There are 2 different forms. What you typed wouldn't open one form instead of the other.

[RuralGuy]

What are the names of both forms - exactly please.

[jheintz57]

they are in the script above
"frm_RCRA" and "frm_DOT"

[RuralGuy]

In which case all you need is:

If Me.[solid_waste?] = True Then
   stForm = "frm_RCRA" 
Else
   stForm = "frm_DOT" 
End If

...and:

DoCmd.OpenForm stForm, , , , acFormAdd

[jheintz57]

Yes, that takes care of the opening different forms but the challenge I am having is the bottom part of the code...if there isn't already a record in the table (ie tbl_RCRA) that is connected to the record of the previous form, I need the manifest_ID to populate the soon-to-be-created new record in the form (ie in the frm_RCRA). And if there is already a record I need it to open to it.

[RuralGuy]

Paul wrote this one just for me and you: http://www.baldyweb.com/OpenArgs.htm

[RuralGuy]

I'm gone for the rest of the afternoon so I'll check back in tonight to see how you made out.

[jheintz57]

I have been playing with that code and the openargs really only works if you select a record in the previous form. I am not selecting a record from a list or anything, merely carrying the manifest_id from the previous form over to the next form...it is not an id that will change in the first form.

[RuralGuy]

You lost me. You can fill the OpenArgs arguments any way you want.

[jheintz57]

Yeah, I see that now. and I got it for the first part...where there isn't already a record in the table to makes up the second form. Now I am having a problem with the second part...where the Case IS >=1...there is a record...right now this code opens it to a completely blank record.

Private Sub Manifest_Continue_Click()

Dim stForm As String
Dim stDocName As String
Dim stTable As String
Dim stID As String
Dim intCounter As Integer


If Me.[solid_waste?] = True Then

stForm = "frm_RCRA"
stTable = "tbl_RCRA_inventory"
stID = "RCRA_ID"

Else

stForm = "frm_DOT"
stTable = "tbl_DOT_inventory"
stID = "DOT_ID"

End If


intCounter = IIf(IsNull(DCount(stID, stTable, "[Manifest_id]=" & Me.Manifest_ID)), 0, DCount(stID, stTable, "[Manifest_id]=" & Me.Manifest_ID))

Select Case intCounter

Case Is = 0
DoCmd.OpenForm stForm, , , , , acFormAdd, Me.Manifest_ID


Case Is >= 1
DoCmd.OpenForm stForm, , , [Manifest_ID] = " & Me![Manifest_ID]"

End Select

Exit_select_site_Click:
Exit Sub

End Sub

It may be a problem with my receiving form which right how has a code on the formload:

Private Sub Form_Load()
If Me.OpenArgs <> vbNullString Then
Me.Manifest_ID = Me.OpenArgs
End If
End Sub

[RuralGuy]

Your receiving form code should be:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
   Me.Manifest_ID = Me.OpenArgs
End If
End Sub