Error 13

  • Thread starter Thread starter JimH
  • Start date Start date
J

JimH

I am trying to get a record using a combo Box, but i keep getting Error 13.
Here is the code:

Private Sub Text105_AfterUpdate()
' Find the record that matches Text105.

Dim rms As Object

' On Error GoTo HandleErr

Set rms = Me.Recordset.Clone
rms.FindFirst "[MODEL NUMBER] = " & Str(Me![Text105])
Me.Bookmark = rms.Bookmark

' Set the focus to the last record in the Billing Appointment table.
Forms![PARTS DB]![MODEL NUMBER].SetFocus
DoCmd.GoToControl "STOCK SUB FORM"
DoCmd.GoToRecord , , acLast

My "MODEL NUMBER" and Text105 are both text.
I have read the help file, but I don't know where the mismatch occurs, and
why.
Access help shows " rms.FindFirst "[MODEL NUMBER] = " & Str(Me![Text105])"
as the problem.

I ill appreciate any help.

Jim
 
Try this:

rms.FindFirst "[MODEL NUMBER] = '" & Str(Me![Text105]) & "'"

That is after the equalsign place a single quote and then a double quote.
After the last & place a double quote, single quote and a double quote again.

Maurice
 
If it's already a string, all you need is:

rms.FindFirst "[MODEL NUMBER] = '" & Me![Text105]) & "'"
 
Maurice said:
Try this:

rms.FindFirst "[MODEL NUMBER] = '" & Str(Me![Text105]) & "'"
Click to expand...

Maurice,

Thanks for your help.

I think we are on the right track.
Error 13 is gone, but The form does not get updated to the record I selected
"Model Number".
It just stays on the first record of DB.

I know its hard to troubleshoot a newbie's creation!!! without seeing
thewhole mess, but any ideas?

Thanks.
 
Maurice has provided the answer for how to fix the "type mismatch" error
that results from leaving out the ' delimiters for the text values, as
you've noted elsethread.

You should use RecordsetClone instead of Recordset.Clone in your code. I've
noted many instances where trying to set a form's Bookmark to the Bookmark
value from a Recordset.Clone causes a "type mismatch" error -- this error
doesn't occur using RecordsetClone in the code. Recordset.Clone often
results in an ADO recordset, while RecordsetClone results in a DAO
recordset. DAO is what you want for your code to work.

Additionally, I would "Dim" rms variable as a DAO.Recordset instead of an
Object -- explicit declaration shouldn't be necessary, but I like to do it
when I can so that I'm sure I'm getting the desired variable type:

Dim rms As DAO.Recordset

I also would add a code step after the step that sets the Bookmark of the
form to release the rms variable:

Set rms = Nothing


It's not clear what you want to do here. Your code says you're moving a
form's recordset to the record that matches the MODEL NUMBER value, and then
your code moves "PARTS DB" form (is that the same form?) to its last record.
Please describe your setup in more detail -- are you using one form, or two?
What are the names? What actions are you wanting to execute / do?
 
Ken

You guys are great.

Working in Access 2003
I created a form "PARTS FORM", all the details, "MODEL NUMBER",.... ARE IN
DETAIL SECTION.
In Header section, I created a combo Box "Text105" which gets the rows from a
query "All Model Numbers".

I want to be able to choose a "MODEL NUMBER" from the combo box, and the form
gets populated with the data for that record. The code under the combo box is:

Dim rms As Object
' Dim rms As DAO.Recordset
' On Error GoTo HandleErr

Set rms = Me.RecordsetClone
rms.FindFirst "[MODEL NUMBER] = '" & Str(Me![Text105]) & "'"
Me.Bookmark = rms.Bookmark


' Set the focus to the last record in the Billing Appointment table.
Forms![PARTS FORM]![MODEL NUMBER].SetFocus
Forms![PARTS FORM]![MODEL NUMBER] = rms.Bookmark
DoCmd.GoToControl "MODEL NUMBER"
Set rms = Nothing

I know its easy, I've seen similar codes and done it in access 2000 but I am
stuck on this one.
 
I just wanted to thank all you good people for your help.

I undrestand its close to impossible to correct somebody's mess without even
seeing it, but all your comments and advice got me to the right direction.

I fixed the problem with this code:

' Find the record that matches Text105.

Dim rms As String


On Error GoTo HandleErr
rms = Me.Text105

' Set the focus to the last record in the Billing Appointment table.
Forms![PARTS FORM]![MODEL NUMBER].SetFocus
DoCmd.GoToControl "[MODEL NUMBER]"
DoCmd.FindRecord rms, , True, , True

Without you guys I wouldn't even get close to a solution.

Jim
 
JimH said:
Set rms = Me.Recordset.Clone
Click to expand...

There is never any reason to set a recordset variable for mere
record navigation. Instead, use a WITH block:

With Me.RecordsetClone
.FindFirst ...
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Much easier, and doesn't require any cleanup.

(I've never understood why people go to the trouble of creating a
recordset variable for a recordset that already exists and can be
referred to directly)
 
You need a completely different approach to do what you seek.

First, you need to select the combo box value. Second, you need to have the
form's RecordSource query be filtered to that value. Third, you need to move
to the "last" record in the form's data.

Let's tackle number two first. Create a query (call it "qryPartsForm") in
design view. Select the Parts table (or whichever table you're wanting to
use) for the query. Put all the fields in the grid that you want to see/edit
on the form. Under the MODEL NUMBER field, in the Criteria box, put this
expression:
Forms![PARTS FORM]![Text105]

(Side note: Are you sure Text105 is a combo box?)

Save and close the query.

Now open the PARTS FORM in design view. Open the Properties window, click on
Data tab, and select qryPartsForm as the Record Source for the form.

Now, put this code as the Event Procedure of the AfterUpdate event of the
Text105 control:

Private Sub Text105_AfterUpdate()
Me.Requery
If Me.RecordsetClone.RecordCount > 0 Then
Me.Recordset.MoveLast
Me.[MODEL NUMBER].SetFocus
Else
MsgBox "No records."
End If
End Sub

Save and close the form.

Now, here is what will happen. When you open the PARTS FORM, the Detail
section will be empty. Select an item from Text105. The form will fill with
the data records and move to the last record and the focus will be put on
the MODEL NUMBER control.
 
Back
Top