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]