Type Mismatch

G

Guest

I am trying to create a search field [Combo41] on a form and keep running
into a Type mismatch error. I have based the combo box on a Simple Query
("CustomerQuery") (that part works) and am running the following code on the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's when I
get the error. I am using the same code in two other databases and it works
fine, so I'm not sure what is different about this one. Thanks for any help
I can get.
 
K

Ken Snell \(MVP\)

Try using RecordsetClone instead of Recordset.Clone (I also have use the
With .. End With structure because it runs a bit quicker, per MS Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub
 
G

Guest

It's still giving me the same error: Run-time error '13' Type mismatch on
the following line:

..FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))

Any other ideas?
--
Miranda


Ken Snell (MVP) said:
Try using RecordsetClone instead of Recordset.Clone (I also have use the
With .. End With structure because it runs a bit quicker, per MS Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>



Miranda said:
I am trying to create a search field [Combo41] on a form and keep running
into a Type mismatch error. I have based the combo box on a Simple Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's when
I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
 
G

Guest

It's still giving me the same error: Run-time error '13' Type mismatch on
the following line:

..FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))

Any other ideas?
--
Miranda


Ken Snell (MVP) said:
Try using RecordsetClone instead of Recordset.Clone (I also have use the
With .. End With structure because it runs a bit quicker, per MS Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>



Miranda said:
I am trying to create a search field [Combo41] on a form and keep running
into a Type mismatch error. I have based the combo box on a Simple Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's when
I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What's the data type of CustomerID?

If it's a text field, you need quotes around the value:

..FindFirst "[CustomerID] = '" & Str(Nz(Me![Combo41], 0)) & "'"

That assumes that there will never be any apostrophes in the value. If there
might be, use

..FindFirst "[CustomerID] = """ & Str(Nz(Me![Combo41], 0)) & """"

or

..FindFirst "[CustomerID] = " & Chr$(34) & Str(Nz(Me![Combo41], 0)) &
Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Miranda said:
It's still giving me the same error: Run-time error '13' Type mismatch
on
the following line:

.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))

Any other ideas?
--
Miranda


Ken Snell (MVP) said:
Try using RecordsetClone instead of Recordset.Clone (I also have use the
With .. End With structure because it runs a bit quicker, per MS Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>



Miranda said:
I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code
on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when
I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It's an AutoNumber field (long integer)
--
Miranda


Douglas J. Steele said:
What's the data type of CustomerID?

If it's a text field, you need quotes around the value:

..FindFirst "[CustomerID] = '" & Str(Nz(Me![Combo41], 0)) & "'"

That assumes that there will never be any apostrophes in the value. If there
might be, use

..FindFirst "[CustomerID] = """ & Str(Nz(Me![Combo41], 0)) & """"

or

..FindFirst "[CustomerID] = " & Chr$(34) & Str(Nz(Me![Combo41], 0)) &
Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Miranda said:
It's still giving me the same error: Run-time error '13' Type mismatch
on
the following line:

.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))

Any other ideas?
--
Miranda


Ken Snell (MVP) said:
Try using RecordsetClone instead of Recordset.Clone (I also have use the
With .. End With structure because it runs a bit quicker, per MS Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>



I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code
on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when
I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Then get rid of the Str function:

..FindFirst "[CustomerID] = " & Nz(Me![Combo41], 0)

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
It's an AutoNumber field (long integer)
--
Miranda


Douglas J. Steele said:
What's the data type of CustomerID?

If it's a text field, you need quotes around the value:

..FindFirst "[CustomerID] = '" & Str(Nz(Me![Combo41], 0)) & "'"

That assumes that there will never be any apostrophes in the value. If
there
might be, use

..FindFirst "[CustomerID] = """ & Str(Nz(Me![Combo41], 0)) & """"

or

..FindFirst "[CustomerID] = " & Chr$(34) & Str(Nz(Me![Combo41], 0)) &
Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Miranda said:
It's still giving me the same error: Run-time error '13' Type
mismatch
on
the following line:

.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))

Any other ideas?
--
Miranda


:

Try using RecordsetClone instead of Recordset.Clone (I also have use
the
With .. End With structure because it runs a bit quicker, per MS
Help):

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>



I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following
code
on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when
I
get the error. I am using the same code in two other databases and
it
works
fine, so I'm not sure what is different about this one. Thanks for
any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
 
G

Guest

It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
 
K

Ken Snell \(MVP\)

Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


Miranda said:
I am trying to create a search field [Combo41] on a form and keep running
into a Type mismatch error. I have based the combo box on a Simple Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's when
I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

By the way, the code should be this:

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>




Ken Snell (MVP) said:
Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


Miranda said:
I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK. Here's what it looks like now:

Private Sub Combo41_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

The syntax error is still occurring on the second line (.FindFirst.....) and
says missing operator in expression

--
Miranda


Ken Snell (MVP) said:
By the way, the code should be this:

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>




Ken Snell (MVP) said:
Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


:

I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK. Here's what it looks like now:

Private Sub Combo41_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

The syntax error is still occurring on the second line (.FindFirst.....) and
says missing operator in expression

--
Miranda


Ken Snell (MVP) said:
By the way, the code should be this:

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>




Ken Snell (MVP) said:
Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


:

I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when I
get the error. I am using the same code in two other databases and it
works
fine, so I'm not sure what is different about this one. Thanks for any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

OK - something is not set up correctly in your form. Please provide the
following information:

For the combobox:
1) SQL statement of the Row Source query
2) Bound Column
3) Column Count
4) Column Widths

For the form:
1) SQL statement of the Record Source query

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
OK. Here's what it looks like now:

Private Sub Combo41_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

The syntax error is still occurring on the second line (.FindFirst.....)
and
says missing operator in expression

--
Miranda


Ken Snell (MVP) said:
By the way, the code should be this:

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>




Ken Snell (MVP) said:
Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


:

I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code
on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when I
get the error. I am using the same code in two other databases and
it
works
fine, so I'm not sure what is different about this one. Thanks for
any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

As I was gathering the information, I spotted my error in the column
count/width rows. It works now. Thank you for all of your help and
patience.

--
Miranda


Ken Snell (MVP) said:
OK - something is not set up correctly in your form. Please provide the
following information:

For the combobox:
1) SQL statement of the Row Source query
2) Bound Column
3) Column Count
4) Column Widths

For the form:
1) SQL statement of the Record Source query

--

Ken Snell
<MS ACCESS MVP>


Miranda said:
OK. Here's what it looks like now:

Private Sub Combo41_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

The syntax error is still occurring on the second line (.FindFirst.....)
and
says missing operator in expression

--
Miranda


Ken Snell (MVP) said:
By the way, the code should be this:

With Me.RecordsetClone
..FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>




Syntax error on which line?

--

Ken Snell
<MS ACCESS MVP>


It now looks like this:

Private Sub Combo43_AfterUpdate()

Dim rs As Object

With Me.RecordsetClone
.FindFirst "[CustomerID]= " & Nz(Me![Combo41], 0)
If Not .EOF Then Me.Bookmark = .Bookmark
End With
End Sub

and it gives me a Syntax error.
--
Miranda


:

I am trying to create a search field [Combo41] on a form and keep
running
into a Type mismatch error. I have based the combo box on a Simple
Query
("CustomerQuery") (that part works) and am running the following code
on
the
AfterUpdate property:

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo41], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I locate the record I want from the query and select it, that's
when I
get the error. I am using the same code in two other databases and
it
works
fine, so I'm not sure what is different about this one. Thanks for
any
help
I can get.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

keyboard shortcut from header to detail of form 3
Cobo Box lookup record on a form 1
VB Runtime error 3159 5
Goto Record 1
Bookmark feature on a data entry form 1
Combo Box not working right! 3
Run time error 3077 1
Combobox / Filter 2

Top