Insertion Question Continue

[spacejones]

This is not working. I was at firstgetting a method not found when I
used db.createrecordset, now when I use db.openrecordset I get a tye
mismatch. A couple questions: am I using this function properly for
what I am doing? Also, do you see anywhere there might be a type
mismatch? I checked out all the data types in the tables and they are
all set to text. Except for NumOrdered. Any help out there? Thx.

-Chris


********Code Starts Here********
Dim mysql As String
Dim stModel As String
Dim stVer As String
Dim stCover As String
Dim stStatus As String
Dim intNum As Integer

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Rem Set rs = db.createrecordset("SELECT NumOrdered, Model, Ver,
Cover from OrderedUpHolstery ", dbOpenDynaset)
Set rs = db.OpenRecordset("OrderedUpholstery", dbOpenDynaset)
rs.MoveFirst

Do While Not rs.EOF

stModel = rs!Model
stVer = rs!Ver
stCover = rs!Cover
stStatus = rs!Status
intNum = rs!NumOrdered

For i = 1 To intNum
mysql = "INSERT INTO InventoryUp (Model, Ver, Cover,
Status) "
mysql = mysql & "SELECT stModel, stVer, stCover,
stStatus; "
DoCmd.RunSQL mysql
Next
rs.MoveNext
Loop


Set db = Nothing
Set rs = Nothing

 

[Ron Hinds]

createrecordset is not a method of CurrentDb. Get rid of that line and
change the OpenRecordset method like so:

Set rs = db.OpenRecordset("SELECT NumOrdered, Model, Ver, Cover FROM
OrderedUpholstery ", dbOpenDynaset)

Next, you need to change your INSERT statement:

mysql = "INSERT INTO InventoryUp (Model, Ver, Cover, Status) VALUES ('" &
stModel & "', '" & stVer & "', '" & stCover & "', '" & stStatus & "');"

DoCmd.RunSQL mysql

 

[Ron Hinds]

Oops - your OpenRecordset will also need to include the column Status:

Set rs = db.OpenRecordset("SELECT NumOrdered, Model, Ver, Cover, Status FROM
OrderedUpholstery ", dbOpenDynaset)

 

[spacejones]

I made the corrections and I added ADO to my dim statements. Now I am
getting Item not found in this collection.

*********Code Starts here*************
Dim mysql As String
Dim stModel As String
Dim stVer As String
Dim stCover As String
Dim stStatus As String
Dim intNum As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT NumOrdered, Model, Ver, Cover
FROM OrderedUpholstery ", dbOpenDynaset)
rs.MoveFirst

Do While Not rs.EOF

stModel = rs!Model
stVer = rs!Ver
stCover = rs!Cover
stStatus = rs!Status
intNum = rs!NumOrdered

For i = 1 To intNum
mysql = "INSERT INTO InventoryUp (Model, Ver, Cover,
Status) VALUES ('" & stModel & "', '" & stVer & "', '" & stCover & "',
'" & stStatus & "');"
DoCmd.RunSQL mysql
Next
rs.MoveNext
Loop

Set db = Nothing
Set rs = Nothing

 

[Ron Hinds]

Per my second reply above - forgot the column Status in the SELECT
statement.

Set rs = db.OpenRecordset("SELECT NumOrdered, Model, Ver, Cover, Status FROM
OrderedUpholstery ", dbOpenDynaset)

 

[spacejones]

Should there be a semicolon in the openrecord line after FROM
OrderedUpholstery; ?

Set rs = db.OpenRecordset("SELECT NumOrdered, Model, Ver, Cover
FROM OrderedUpholstery ", dbOpenDynaset)

 

[Ron Hinds]

It isn't necessary.