Q: Using Access with VB

I'm Wing. I'm working with Access without the data control in VB5.
I'm having problems with the updating and adding new records.
I'm using a counter field as the primary key for a table and I would
like to retrieve the value of the new counter when inserting a record.
So far I have a command button with clears the textboxes so new
details could be added. So when the details are added by pressing
an update/save button the new record would be commited to the
database. Could you give me any suggestions for the following
problems?

My problems are:

1. How to retrieve the new counter so I don't save on top of an existing
record when I press the update/add button?
2. How to reset comboboxes and option buttons so nothing is selected
when the Add button is pressed?
3. How to undo Add or Delete (or something which allows the user to cancel
the action)?


Natali6067@aol.com

A:
 > 1. How to retrieve the new counter so I don't save on top of an existing
 > record when I press the update/add button?

 You can use a automatic counterfield. Or if you don't want to do this, 
 get the highest counter from the table before saving the record. You 
 can use a simple query to get it:

 sql = "SELECT counter FROM table ORDER BY counter DESC"
 set rs = db.OpenRecoprdSet(sql)
 HighestCounter = rs.Fields(0).Value

 Because the output is sorted descending the highest value is alway 
 the first record.

 > 2. How to reset comboboxes and option buttons so nothing is selected
 > when the Add button is pressed?

 use the Forms.Collection to set the property enabled = False:

 Public Sub AllowEntry(yesno As Boolean)
  Dim Crt as Control 

  For Each Crt in Form1.Controls
   If TypeOf Crt is OptionButton Or _
    TypeOf Crt Is ComboBox The
    If yesno Then
     Crt.Enabled = True
    Else
     Crt.Enabled = False
    End If
   End If
  Next Crt
 End Sub

 > 3. How to undo Add or Delete (or something which allows the user to cancel
 > the action)?

 Delete is simple:

 step 1: search for the record (you have the counternumber as an index)

  set rs = db.OpenRecordSet(table)
  set rs.Index = "counter" 
  rs.Seek "=", [counter to search]
  If Not rs.NoMatch Then rs.Delete

 Undo is more work... a search and delete is much simplier and 
 also fast.

 see also the ms access section for more..



Return