getting type mismatch error and can't figure out why

  • Thread starter Thread starter Richard Hollenbeck
  • Start date Start date
R

Richard Hollenbeck

I have DAO 3.6 Object referenced. What else could I be missing? I can't
figure out why I'm getting a type mismatch error. I've tried a bunch of
things I modified this code from VBA Help. Here's the code:

Option Compare Database
Option Explicit

Private Sub cmdAddIngredient_Click()
On Error GoTo Err_cmdAddIngredient_Click

Dim StrSQL As String, NewIngred As String
Dim dbRecipes As DAO.Database
Dim rsIngredients As DAO.Recordset

Set dbRecipes = CurrentDb
Set rsIngredients = dbRecipes.OpenRecordset("tblIngredients",
dbOpenDynaset)
rsIngredients.MoveLast

'Get data from the user.
NewIngred = InputBox("Enter a new ingredient:")
If NewIngred <> "" Then
AddName rsIngredients, NewIngred
End If

Exit_cmdAddIngredient_Click:
Exit Sub

Err_cmdAddIngredient_Click:
MsgBox Err.Description
Resume Exit_cmdAddIngredient_Click

End Sub

Private Function AddName(rstTemp As Recordset, strName As String)

' Adds a new record to a recordset using the data passes
' by the calling procedure. The new record is then made
' the current record

With rstTemp
.AddNew
!IngredientName = strName 'IngredientName is a text field in
tblIngredients
.Update
End With
End Function
 
Hi Richard,

Which line is highlighted when the error occurs?

I don't have any errors when I run your code. One note (which did cause an
error because my table was initially empty) is that you don't need the
rsIngredients.MoveLast statement. It doesn't matter what record is current
when you insert an new record.
 
Here is the troublesome line:
Private Function AddName(rstTemp As Recordset, strName As String)

I changed it to:
Private Function AddName(rstTemp As DAO.Recordset, strName As String)

that solved the problem. Thanks.


Sandra Daigle said:
Hi Richard,

Which line is highlighted when the error occurs?

I don't have any errors when I run your code. One note (which did cause an
error because my table was initially empty) is that you don't need the
rsIngredients.MoveLast statement. It doesn't matter what record is current
when you insert an new record.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Richard said:
I have DAO 3.6 Object referenced. What else could I be missing? I can't
figure out why I'm getting a type mismatch error. I've tried a bunch of
things I modified this code from VBA Help. Here's the code:

Option Compare Database
Option Explicit

Private Sub cmdAddIngredient_Click()
On Error GoTo Err_cmdAddIngredient_Click

Dim StrSQL As String, NewIngred As String
Dim dbRecipes As DAO.Database
Dim rsIngredients As DAO.Recordset

Set dbRecipes = CurrentDb
Set rsIngredients = dbRecipes.OpenRecordset("tblIngredients",
dbOpenDynaset)
rsIngredients.MoveLast

'Get data from the user.
NewIngred = InputBox("Enter a new ingredient:")
If NewIngred <> "" Then
AddName rsIngredients, NewIngred
End If

Exit_cmdAddIngredient_Click:
Exit Sub

Err_cmdAddIngredient_Click:
MsgBox Err.Description
Resume Exit_cmdAddIngredient_Click

End Sub

Private Function AddName(rstTemp As Recordset, strName As String)

' Adds a new record to a recordset using the data passes
' by the calling procedure. The new record is then made
' the current record

With rstTemp
.AddNew
!IngredientName = strName 'IngredientName is a text field in
tblIngredients
.Update
End With
End Function
Click to expand...
Click to expand...
 
Hi Richard,

Glad you solved it! As you probably know by now if still have the ADO
library referenced and listed before the DAO library , without the DAO
prefix on the parameter declaration the recordset was being declared as an
ADO recordset.

It is always preferable to prefix an object with it's library designation.
Even if you only had the DAO libary referenced it is more efficient since
there is no question of which library to use to resolve the declaration.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Richard said:
Here is the troublesome line:
Private Function AddName(rstTemp As Recordset, strName As String)

I changed it to:
Private Function AddName(rstTemp As DAO.Recordset, strName As String)

that solved the problem. Thanks.


Sandra Daigle said:
Hi Richard,

Which line is highlighted when the error occurs?

I don't have any errors when I run your code. One note (which did cause
an error because my table was initially empty) is that you don't need the
rsIngredients.MoveLast statement. It doesn't matter what record is
current when you insert an new record.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Richard said:
I have DAO 3.6 Object referenced. What else could I be missing? I
can't figure out why I'm getting a type mismatch error. I've tried a
bunch of things I modified this code from VBA Help. Here's the code:

Option Compare Database
Option Explicit

Private Sub cmdAddIngredient_Click()
On Error GoTo Err_cmdAddIngredient_Click

Dim StrSQL As String, NewIngred As String
Dim dbRecipes As DAO.Database
Dim rsIngredients As DAO.Recordset

Set dbRecipes = CurrentDb
Set rsIngredients = dbRecipes.OpenRecordset("tblIngredients",
dbOpenDynaset)
rsIngredients.MoveLast

'Get data from the user.
NewIngred = InputBox("Enter a new ingredient:")
If NewIngred <> "" Then
AddName rsIngredients, NewIngred
End If

Exit_cmdAddIngredient_Click:
Exit Sub

Err_cmdAddIngredient_Click:
MsgBox Err.Description
Resume Exit_cmdAddIngredient_Click

End Sub

Private Function AddName(rstTemp As Recordset, strName As String)

' Adds a new record to a recordset using the data passes
' by the calling procedure. The new record is then made
' the current record

With rstTemp
.AddNew
!IngredientName = strName 'IngredientName is a text field in
tblIngredients
.Update
End With
End Function
Click to expand...
Click to expand...
Click to expand...
 
Sandra Daigle said:
Hi Richard,

Which line is highlighted when the error occurs?

I don't have any errors when I run your code. One note (which did cause an
error because my table was initially empty) is that you don't need the
rsIngredients.MoveLast statement. It doesn't matter what record is current
when you insert an new record.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Richard said:
I have DAO 3.6 Object referenced. What else could I be missing? I can't
figure out why I'm getting a type mismatch error. I've tried a bunch of
things I modified this code from VBA Help. Here's the code:

Option Compare Database
Option Explicit

Private Sub cmdAddIngredient_Click()
On Error GoTo Err_cmdAddIngredient_Click

Dim StrSQL As String, NewIngred As String
Dim dbRecipes As DAO.Database
Dim rsIngredients As DAO.Recordset

Set dbRecipes = CurrentDb
Set rsIngredients = dbRecipes.OpenRecordset("tblIngredients",
dbOpenDynaset)
rsIngredients.MoveLast

'Get data from the user.
NewIngred = InputBox("Enter a new ingredient:")
If NewIngred <> "" Then
AddName rsIngredients, NewIngred
End If

Exit_cmdAddIngredient_Click:
Exit Sub

Err_cmdAddIngredient_Click:
MsgBox Err.Description
Resume Exit_cmdAddIngredient_Click

End Sub

Private Function AddName(rstTemp As Recordset, strName As String)

' Adds a new record to a recordset using the data passes
' by the calling procedure. The new record is then made
' the current record

With rstTemp
.AddNew
!IngredientName = strName 'IngredientName is a text field in
tblIngredients
.Update
End With
End Function
Click to expand...
Click to expand...
 
Back
Top