retrieve autonumber during myCN.Execute

Two questions:

1) in a procedure with ADO, I am using the myCN.Execute method to add records to a table. The table has a column YEAR, and the new records are just a copy of the same records entered for the prior year.

Is it possible to assign the autonumber value
of the newly added record to a variable? I need this
number for a similar copying routine in a related table.
I can get it using a recordset, but it would be much more
efficient if I could just assign it to a variable.

Code:

---

Private Sub GenerateIndices()

Dim i As Single
Dim selYear

'prompt user to enter desired year
ShowBoxAgain:
selYear = InputBox("Generate indices for the year...")

If selYear = "" Then Exit Sub

If Not IsNumeric(selYear) Then
    MsgBox "'" & selYear & "' is not a valid year.", vbExclamation
    GoTo ShowBoxAgain 'show input box again
End If

'open the table Areas
rsAreas.Open "Select * From Areas Where YEAR = " & selYear - 1, _
  myCN, adOpenKeyset, adLockOptimistic

Me.MousePointer = vbHourglass
DoEvents

'generate error if no records exist for prior year
If rsAreas.RecordCount = 0 Then
'generate error message if no areas exist for prior year
    MsgBox "No areas exist for the prior year.", vbExclamation

Else
    With rsAreas
        .MoveFirst
        Do Until .EOF
'this is the added record for which I will need the autonumber value
            myCN.Execute "INSERT INTO Areas (YEAR, AREA) VALUES (" & _
              selYear & ", '" & rsAreas.Fields("AREA").Value & "')"
             
            .MoveNext
        Loop
    End With
End If

---

Question 2: similar to question 1: how could I use the
myCN.Execute or something similar if I want to assign the
value of any field to a variable? Example: if I have a
table USERS with a field ACCESS_LEVEL, how could I assign
the value of ACCESS_LEVEL to a variable UserAccessLevel
without connecting to the table through a recordset? The
value of UserAccessLevel would have something to do with
the result of

Code:

---

myCN.Execute "Select ACCESS_LEVEL From Users Where USER = '" & txtUser.Text & "'"

---

Is this possible or am I just swinging from the nut tree
for even thinking it?


[Edited by DrewDog_21 on 09-14-2000 at 01:28 PM]

I think , altough I'm not a pro, 4 your second question that this can't be done without a recordset.
4 the 1 one I would write it like this.

rsAreas.Open "Select * From Areas Where YEAR = " & selYear - 1, _
myCN, adOpenKeyset, adLockOptimistic

With rsAreas
.Add
.YEAR = selYear
.AREA = rsAreas.Fields("AREA").Value
.Update
NewNumber = .Autonumber
End With
rsAreas.Close

That's what I thought, although it was worth asking.
Making a recordset just to retrieve one number is such a
waste of space though.

Anyone else have any insight?

sounds as if you were using an Access database. if you want to simplify similar tasks, switch to SQL Server or Oracle. there you can create stored procedures. they can return values.

best regards

Sascha

Yes sascha, you are right. I am using Access and would love
to be able to switch to Oracle or SQL server 7, since I have
heard that stored procedures can simplify data access
greatly. Unfortunately I don't have the dinero to invest in
either of those right now, so it looks like I am stuck
unless someone else has an answer.