Have 2 more questions

[Joe Cilinceon]

I got the syntax error I was working on fixed but here are two more
questions I hope someone has an answer for.

First after I append a new record using the sql method, I would like to get
the transaction number back from that record. This number is an autonumber
so I'm not appending it. I would like to do this as soon as a record is
appended since I will need it to print a receipt.

Second question is how do you set a combo box back to the first choice
(Blank).

Please be patient since I've not done this stuff since Clipper for DOS.

 

[Allen Browne]

Ah, the ol' dbase III days takes us back!

For your 2nd question, just assign Null to the combo, e.g.:
Me.Combo1 = Null

The example below shows how to retrieve the AutoNumber from Access (JET) for
the record you just inserted. Note that:
- You must use the DAO Execute and retrieve the value in the same prodecure
for this to work.
- Just type the OpenRecordset line exactly as is, i.e. it does not matter
what the field is actually called.

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[Joe Cilinceon]

Thanks Allen I got it now.

--

Joe Cilinceon

Ah, the ol' dbase III days takes us back!

For your 2nd question, just assign Null to the combo, e.g.:
Me.Combo1 = Null

The example below shows how to retrieve the AutoNumber from Access (JET) for
the record you just inserted. Note that:
- You must use the DAO Execute and retrieve the value in the same prodecure
for this to work.
- Just type the OpenRecordset line exactly as is, i.e. it does not matter
what the field is actually called.

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function