Syntax Error

[mattc66 via AccessMonster.com]

I am getting a run-time error 3075 - Syntax error in string in query
expression '2000 ". I dont understand why it is finding the record #.

Set rs1 = Db.OpenRecordset("select * from tblAssembly where " & Me.WO_NUM
& " ' ")

 

[AccessVandal via AccessMonster.com]

Er, missing quote and incorrect syntax.

("select * from tblAssembly where WO_NUM = " & "'" & Me.WO_NUM & "'")

 

[mattc66 via AccessMonster.com]

That error went away and now I have a new one. Type mis-match. Its text for
both. Not sure how it's miss matched.

 

[mattc66 via AccessMonster.com]

Below is my code. What I am trying to do is create from one tblAssembly
multiple records into tblWIP.

tblAssembly
WO_NUM ASSEMBLY QTY LABOR_DIFF
2000 5990-100 25 0.25

For every WO you have 4 transactions that occure. I need to create this data
that will eventually be used in an ASCII upload to a legacy application.

tblWIP
ADJ_TYPE WO_NUM COMP_ITEM QTY
K 2000 25
D 2000 6999-96 0.25
D 2000 6999-99 0.25
F 2000 25

This is a command button on my form that is based on the tblAssembly.
Private Sub cmdUpdate_Click()

Dim Db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set Db = CurrentDb
Set rs1 = Db.OpenRecordset("select * from tblAssembly where WO_NUM = " &
"'" & Me.WO_NUM & "'")
Set rs2 = Db.OpenRecordset("select * from tblWIP")

rs1.MoveFirst
While Not rs1.EOF
With rs2
rs2.AddNew
rs2.Fields("ADJ_TYPE") = "K"
rs2.Fields("WO_NUM") = WO_NUM
rs2.Fields("QTY") = QTY
rs2.Update

rs2.AddNew
rs2.Fields("ADJ_TYPE") = "D"
rs2.Fields("WO_NUM") = WO_NUM
rs2.Fields("COMP_ITEM") = "6999-96"
rs2.Fields("QTY") = LABOR_DIFF
rs2.Update

rs2.AddNew
rs2.Fields("ADJ_TYPE") = "D"
rs2.Fields("WO_NUM") = WO_NUM
rs2.Fields("COMP_ITEM") = "6999-99"
rs2.Fields("QTY") = LABOR_DIFF
rs2.Update

rs2.AddNew
rs2.Fields("ADJ_TYPE") = "F"
rs2.Fields("WO_NUM") = WO_NUM
rs2.Fields("QTY") = QTY
rs2.Update

End With
rs1.MoveNext
Wend
Set rs1 = Nothing
Set rs2 = Nothing

End Sub

That error went away and now I have a new one. Type mis-match. Its text for
both. Not sure how it's miss matched.

Er, missing quote and incorrect syntax.

[quoted text clipped - 5 lines]

 

[AccessVandal via AccessMonster.com]

Where did the error occured in your code and which line?

What is the datatype for "QTY" in both the tables?

 

[mattc66 via AccessMonster.com]

Run Time Error 13 - Type mismatch

Stops on this line: Set rs1 = Db.OpenRecordset("select * from tblAssembly
where WO_NUM = " & "'" & Me.WO_NUM & "'")

Where did the error occured in your code and which line?

What is the datatype for "QTY" in both the tables?

Below is my code. What I am trying to do is create from one tblAssembly
multiple records into tblWIP.

[quoted text clipped - 61 lines]

End Sub

 

[AccessVandal via AccessMonster.com]

Are you sure that the datatype for "WO_NUM" is text?

Try it without the quotes.

("select * from tblAssembly where WO_NUM = " & Me.WO_NUM)

Run Time Error 13 - Type mismatch

Stops on this line: Set rs1 = Db.OpenRecordset("select * from tblAssembly
where WO_NUM = " & "'" & Me.WO_NUM & "'")

Where did the error occured in your code and which line?

[quoted text clipped - 5 lines]

 

[mattc66 via AccessMonster.com]

When I look at the tables they show test for the type on WO_NUM. I went ahead
and changed the code as you suggested. I got a error on the same line of code.
Run-Time error '3464': Data type mismatch in criteria expression.

Are you sure that the datatype for "WO_NUM" is text?

Try it without the quotes.

("select * from tblAssembly where WO_NUM = " & Me.WO_NUM)

Run Time Error 13 - Type mismatch

[quoted text clipped - 6 lines]

 

[Dirk Goldgar]

Run Time Error 13 - Type mismatch

Stops on this line: Set rs1 = Db.OpenRecordset("select * from
tblAssembly
where WO_NUM = " & "'" & Me.WO_NUM & "'")

Most likely due to the ambiguity of these declarations:

Dim rs1 As Recordset
Dim rs2 As Recordset

Those could be DAO recordsets, or they could be ADODB recordsets (which are
not compatible). Disambiguate the declarations by specifying that you want
DAO recordsets:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

 

[mattc66 via AccessMonster.com]

I meant to type Text not test.

When I look at the tables they show test for the type on WO_NUM. I went ahead
and changed the code as you suggested. I got a error on the same line of code.
Run-Time error '3464': Data type mismatch in criteria expression.

Are you sure that the datatype for "WO_NUM" is text?

[quoted text clipped - 6 lines]

 

[mattc66 via AccessMonster.com]

Thanks Dirk that seemed to be the issue.

Most likely due to the ambiguity of these declarations:


Those could be DAO recordsets, or they could be ADODB recordsets (which are
not compatible). Disambiguate the declarations by specifying that you want
DAO recordsets:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

 

[AccessVandal via AccessMonster.com]

Thanks Dirk,

Should have asked about the DAO referances when I saw that.