I am fairly new to message boards so any help would be appreciated. I am trying to create some code that will read the value of one column to the end of another and take that data and place it into a new table.

Example

Field 1: 1
Field 2: 49

All values between 1 and 49 will be plugged into the new table.
All my code is below; If you need any more info please let me know.



Private Sub Command3_Enter()

Dim DBS As Database
Dim RST As Recordset
Dim Book As Single
Dim FirstDoc As Single
Dim LastDoc As Single
Dim I As Single
Dim INTableName As String
Dim OutTableName As String

INTableName = "Ocean_Missings_1"
OutTableName = "Ocean_Complete_Missings"

Set DBS = CurrentDb
Set RST = DBS.OpenRecordset(INTableName)
If RST.BOF = False Then RST.MoveFirst
While RST.EOF = False

    Book = Left(RST.Fields("Book").Value, 4)
    FirstDoc = Right(RST.Fields("Start").Value, 4)
    If IsNull(RST.Fields("Last").Value) = False Then
        LastDoc = Right(RST.Fields("Last").Value, 4)
        DoCmd.SetWarnings True
            For I = FirstDoc To LastDoc
                DoCmd.RunSQL ("INSERT INTO" & OutTableName & "(Book, Start) Values (" & Book & "'" & I & ")")

            Next I

        Else

            DoCmd.RunSQL ("Insert Into " & OutTableName & "(Book, Start) VALUES (" & Book & "'" & Right(RST.Fields("Book").Value, 5) & ")")
        End If

        RST.MoveNext
        I = 0
    Wend
    DoCmd.SetWarnings True

End Sub

Unfortunately you haven't told us what the error or problem is.

But I do see errors in your Insert statement.

DoCmd.RunSQL ("INSERT INTO" & OutTableName & "(Book, Start) Values (" & Book & "'" & I & ")")

should be

DoCmd.RunSQL ("INSERT INTO " & OutTableName & " (Book, Start) Values ('" & Book & "', " & I & ")")

And

DoCmd.RunSQL ("Insert Into " & OutTableName & "(Book, Start) VALUES (" & Book & "'" & Right(RST.Fields("Book").Value, 5) & ")")

should be

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Book, Start) VALUES ('" & Book & "', " & Right(RST.Fields("Book").Value, 5) & ")")

I'm assuming that the field Book doesn't contain any apostrophies ('). If so you need to change the apostrophies to double quotes ("") like this:

DoCmd.RunSQL ("Insert Into " & OutTableName & " (Book, Start) VALUES (""" & Book & """, " & Right(RST.Fields("Book").Value, 5) & ")")

Of course that syntax assumes that Book doesn't contain any quotes (").

Also, on the last, I'm assuming that Right(RST.Fields("Book").Value, 5) is a numeric value.

Recommendation: Instead of trying to write the RunSQL statement altogther, try this for debugging,

Dim sql as String

sql = "INSERT INTO " & OutTableName & " (Book, Start) Values ('" & Book & "', " & I & ")"

Debug.Print sql

DoCmd.RunSQL sql

The Debug.Print will show the Insert statement that is executing. It will be a little more obvious what the problem is.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thank you for your help sometime all you need is a second set of eyes.

When you open a recordset, it is always positioned at the 1st record if any records are returned.

So is unnecessary.



    is much better as


The .Value property is the default, so could be (a little easier to read). Additionally, the .Fields collection is the default property of a recordset object, so it could be (even easier to read), or better (since your field name has no spaces in it), you could use the syntax

I think that you will probably get the results you want with Randall’s suggestions. Your code as posted didn’t put spaces between elements that needed them, for one thing, and didn't delineate literal strings in the SQL from references in the SQL to tables, fields, and so on.

For instance, the first point that Randall makes: should be is because your code would evaluate to (if Book equaled "John Henry" and I equaled 1), whereas Randall’s repair of the code evaluates to
Is what you want to do to make a whole series of records with the same book title, but a sequential series of numbers?