Opening a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I keep getting an error message 'Type mismatch' (Error code: 13) when the
code below tries to execute. The code is the Event Procedure for the
AfterUpdate event on a control on my form. What the code is supposed to do
is take the part number information entered, find a previous record where
part type, description and drawing number have already been associated with
that part number, and then plug those values in to other controls on the form.

Private Sub txtbxPN_AfterUpdate()

Dim dbsA As Database
Dim rst As Recordset
Dim strSQL As String

Set dbsA = CurrentDb

strSQL = "SELECT REQUESTED_PARTS.ControlNo, REQUESTED_PARTS.Type, " _
& "REQUESTED_PARTS.[Part #], REQUESTED_PARTS.Drawing, " _
& "REQUESTED_PARTS.Description " _
& "FROM REQUESTED_PARTS " _
& "WHERE (([REQUESTED_PARTS].[Part #])=""" & Me!txtbxPN & """")
" _
& "ORDER BY [REQUESTED_PARTS].[ControlNo]"

Set rst = dbsA.OpenRecordset(strSQL)

' Other code here to process the found record....

End Sub

Notes: The table REQUESTED_PARTS is a linked table. "txtbxPN" is a combo
box on the form. I can paste strSQL into the SQLView of a query and it works
just fine.

The debugger hits the last line (Set rst = dbsA.Open.....), highlights it
yellow and the code stops there. What is most perplexing is I have
successfully run code like this on other Access databases and it works there.
Any ideas???

Any help anyone can give me would be much appreciated.

Thank you,
Dieter
 
This thread is posted twice. Here is the suggestion I wrote for the other
thread:

I had the same problem this week. Almost ripped out my few remaining
hairs. The solution is simple: add "DAO" in front of your objects and you
should be fine.

Dim dbsA As DAO.Database
Dim rst As DAO.Recordset

Make sure you have the DAO reference set: (Tools...References...Microsoft
DAO 3.6 Object Library.

Good luck.
-J
 
tischlda said:
I keep getting an error message 'Type mismatch' (Error code: 13) when the
code below tries to execute. The code is the Event Procedure for the
AfterUpdate event on a control on my form. What the code is supposed to do
is take the part number information entered, find a previous record where
part type, description and drawing number have already been associated with
that part number, and then plug those values in to other controls on the form.

Private Sub txtbxPN_AfterUpdate()

Dim dbsA As Database
Dim rst As Recordset
Dim strSQL As String

Set dbsA = CurrentDb

strSQL = "SELECT REQUESTED_PARTS.ControlNo, REQUESTED_PARTS.Type, " _
& "REQUESTED_PARTS.[Part #], REQUESTED_PARTS.Drawing, " _
& "REQUESTED_PARTS.Description " _
& "FROM REQUESTED_PARTS " _
& "WHERE (([REQUESTED_PARTS].[Part #])=""" & Me!txtbxPN & """")
" _
& "ORDER BY [REQUESTED_PARTS].[ControlNo]"

Set rst = dbsA.OpenRecordset(strSQL)

' Other code here to process the found record....

End Sub

Notes: The table REQUESTED_PARTS is a linked table. "txtbxPN" is a combo
box on the form. I can paste strSQL into the SQLView of a query and it works
just fine.

The debugger hits the last line (Set rst = dbsA.Open.....), highlights it
yellow and the code stops there. What is most perplexing is I have
successfully run code like this on other Access databases and it works there.
Any ideas???
Click to expand...


That error usually means that you've use a text string with
a numeric field. In this case, it looks like this issue
might be the .[Part #] field?? Try using:

. . . .[Part #])=" & Me!txtbxPN & ")" _
 
Back
Top