Runtime Error 13 Type Mismatch

[James Gaylord via AccessMonster.com]

OK so why do I get a Type mismatch on the openform line?

Private Sub butOpenForm_Click()
Dim stData As String
Dim stWhere As String
' On Error GoTo butOpenForm_Click_Error

txtType = Me.cmbRulesType

txtTable = Me.lstRulesTableName

stInputs = Me.cmbInputs

blnEditForm = chkEditForm

If chkEditForm = True Then
stData = "acFormEdit"
stWhere = "[Rules Table Name] = " & "'" & txtTable & "'"
Else
stData = "acFormAdd"
End If

DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, , stWhere, stData,
OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" & blnEditForm

On Error GoTo 0
Exit Sub

butOpenForm_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
butOpenForm_Click of VBA Document Form_frm:2000RulesTableFilter"

End Sub

It was working fine before I added the where cluase. I double checked my
syntax on that and it looks fine. This clause is passing a string of data,
in this case FABCOL

TIA

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

 

[Stuart McCall]

OK so why do I get a Type mismatch on the openform line?

Private Sub butOpenForm_Click()
Dim stData As String
Dim stWhere As String
' On Error GoTo butOpenForm_Click_Error

txtType = Me.cmbRulesType

txtTable = Me.lstRulesTableName

stInputs = Me.cmbInputs

blnEditForm = chkEditForm

If chkEditForm = True Then
stData = "acFormEdit"
stWhere = "[Rules Table Name] = " & "'" & txtTable & "'"
Else
stData = "acFormAdd"
End If

DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, , stWhere,
stData,
OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" & blnEditForm

On Error GoTo 0
Exit Sub

butOpenForm_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
butOpenForm_Click of VBA Document Form_frm:2000RulesTableFilter"

End Sub

It was working fine before I added the where cluase. I double checked my
syntax on that and it looks fine. This clause is passing a string of
data,
in this case FABCOL

TIA

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

The problem is your declaration and assignment of the stData variable. The
OpenForm call requires a constant of type long in it's DataMode parameter.
So:

Dim stData As Long

....

If chkEditForm = True Then
stData = acFormEdit
stWhere = "[Rules Table Name] = " & "'" & txtTable & "'"
Else
stData = acFormAdd
End If

 

[James Gaylord via AccessMonster.com]

I don't believe that is it. It was workiong fine before I added the stWhere
clause, so the data type of String was working fine. I thought it was the
way I formatted my where clause, but it looks fine compared to a standard SQL
where clause of the same information.

Also, I tried changing this and the type mismatched then happened on the if
statement where I set the value to

OK so why do I get a Type mismatch on the openform line?

[quoted text clipped - 38 lines]

TIA

The problem is your declaration and assignment of the stData variable. The
OpenForm call requires a constant of type long in it's DataMode parameter.
So:

Dim stData As Long

...

If chkEditForm = True Then
stData = acFormEdit
stWhere = "[Rules Table Name] = " & "'" & txtTable & "'"
Else
stData = acFormAdd
End If

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

 

[James Gaylord via AccessMonster.com]

I maade some cfhanges and am now getting a Missing operator error in query
expression 'DataMode:=acFormEdit'. Here is the new code

Private Sub butOpenForm_Click()
Dim stData As String
Dim stWhere As String
' On Error GoTo butOpenForm_Click_Error

txtType = Me.cmbRulesType

txtTable = Me.lstRulesTableName

stInputs = Me.cmbInputs

blnEditForm = chkEditForm

If chkEditForm = True Then
stData = "DataMode:=acFormEdit"
stWhere = "WhereCondition:=[Rules Table Name] = " & "'" & txtTable & "'
and [Rules Table Type=" & "'" & txtType & "'"
Else
stData = "DataMode:=acFormAdd"
End If

DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, stWhere, stData,
OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" & blnEditForm

On Error GoTo 0
Exit Sub

butOpenForm_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
butOpenForm_Click of VBA Document Form_frm:2000RulesTableFilter"

End Sub

I don't believe that is it. It was workiong fine before I added the stWhere
clause, so the data type of String was working fine. I thought it was the
way I formatted my where clause, but it looks fine compared to a standard SQL
where clause of the same information.

Also, I tried changing this and the type mismatched then happened on the if
statement where I set the value to
[quoted text clipped - 16 lines]

stData = acFormAdd
End If

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

 

[Stuart McCall]

I maade some cfhanges and am now getting a Missing operator error in query
expression 'DataMode:=acFormEdit'. Here is the new code

Private Sub butOpenForm_Click()
Dim stData As String
Dim stWhere As String
' On Error GoTo butOpenForm_Click_Error

txtType = Me.cmbRulesType

txtTable = Me.lstRulesTableName

stInputs = Me.cmbInputs

blnEditForm = chkEditForm

If chkEditForm = True Then
stData = "DataMode:=acFormEdit"
stWhere = "WhereCondition:=[Rules Table Name] = " & "'" & txtTable & "'
and [Rules Table Type=" & "'" & txtType & "'"
Else
stData = "DataMode:=acFormAdd"
End If

DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, stWhere,
stData,
OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" & blnEditForm

On Error GoTo 0
Exit Sub

butOpenForm_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
butOpenForm_Click of VBA Document Form_frm:2000RulesTableFilter"

End Sub

I don't believe that is it. It was workiong fine before I added the
stWhere
clause, so the data type of String was working fine. I thought it was the
way I formatted my where clause, but it looks fine compared to a standard
SQL
where clause of the same information.

Also, I tried changing this and the type mismatched then happened on the
if
statement where I set the value to

OK so why do I get a Type mismatch on the openform line?

[quoted text clipped - 16 lines]

stData = acFormAdd
End If

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

I still maintain that you cannot use a string variable to feed a parameter
that is expecting a long value. If you think you have done that in the past
then you are mistaken. From Access help on the OpenForm method:

DataMode Optional AcFormOpenDataMode. The data entry mode for the form.
This applies only to forms opened in Form view or Datasheet view
AcFormOpenDataMode can be one of these AcFormOpenDataMode constants.
acFormAdd The user can add new records but can't edit existing
records.
acFormEdit The user can edit existing records and add new records.
acFormPropertySettings default
acFormReadOnly The user can only view records.


Nowhere does that mention use of a string variable.

Why don't you give up on the idea of using a variable and just have two
OpenForm calls? Something like this:

If chkEditForm = True Then
stWhere = "WhereCondition:=[Rules Table Name] = " & "'" & txtTable & "'
and [Rules Table Type=" & "'" & txtType & "'"
DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, stWhere,
acFormEdit, OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" &
blnEditForm
Else
DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, stWhere,
acFormAdd, OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" &
blnEditForm
End If

 

[Douglas J. Steele]

As Stuart indicated, the DataMode parameter must be a Long Integer. You
cannot pass a string, even if it looks as though it's the same as what you
would have explicitly typed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I maade some cfhanges and am now getting a Missing operator error in query
expression 'DataMode:=acFormEdit'. Here is the new code

Private Sub butOpenForm_Click()
Dim stData As String
Dim stWhere As String
' On Error GoTo butOpenForm_Click_Error

txtType = Me.cmbRulesType

txtTable = Me.lstRulesTableName

stInputs = Me.cmbInputs

blnEditForm = chkEditForm

If chkEditForm = True Then
stData = "DataMode:=acFormEdit"
stWhere = "WhereCondition:=[Rules Table Name] = " & "'" & txtTable & "'
and [Rules Table Type=" & "'" & txtType & "'"
Else
stData = "DataMode:=acFormAdd"
End If

DoCmd.OpenForm "frm:2000 Rules Table Detail", acFormDS, stWhere,
stData,
OpenArgs:=stInputs & ";" & txtType & ";" & txtTable & ";" & blnEditForm

On Error GoTo 0
Exit Sub

butOpenForm_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
butOpenForm_Click of VBA Document Form_frm:2000RulesTableFilter"

End Sub

I don't believe that is it. It was workiong fine before I added the
stWhere
clause, so the data type of String was working fine. I thought it was the
way I formatted my where clause, but it looks fine compared to a standard
SQL
where clause of the same information.

Also, I tried changing this and the type mismatched then happened on the
if
statement where I set the value to

OK so why do I get a Type mismatch on the openform line?

[quoted text clipped - 16 lines]

stData = acFormAdd
End If

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com