Getting the Autonumber into the Form after inserting new record

[bloody_football]

I really hope I can explain this right

When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).

I have tried the following code -

TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'")

"Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.

Regards, James

 

[WayneRyan]

bloody,

I don't understand here.

On any bound form, you can have the AutoNumber field (visible or not) and
always have it at hand.

You say your problems stem when you INSERT it into the Transactions table?
How do you insert it?

If a recordset:

rst.AddNew
rst!SomeField = "Something" <-- Here rst!AutoNumberField has a value
rst!OtherField = "Other"   <-- Here rst!AutoNumberField has a value
rst.Update   <-- Here rst!AutoNumberField IS NOT your value

It depends on when you reference the field.

If you're INSERTING with a query, then you'll have to use another
query (or a DLookUp) to retrieve it.

hth,
Wayne

 

[bloody_football]

I'll try to explain some more -

When this is being done I am in a Form called 'frmBuyCards', when someone buys the card they start a transaction. When a transaction is started the 'tblTransaction' table gives the transaction an autonumber, how do I get this transaction number back into the 'frmBuyCards'?

I insert the code into the transaction table via the following code -

DoCmd.RunSQL "INSERT INTO transactions (customerID, DateOfPurchase)" & _
 "VALUES ('" & TempID & "', Date() )"

There may be easier ways to do it but this is how I was taught

James

 

[bloody_football]

Okay, I admit I have botched it up
I cannot find the value of the autonumber that the transaction was given. As I explained before when a transaction is started on the 'buy' form it is given an autonumber (OrderNumber) from the 'transaction' table, I simply cannot find that value

I have tried -
OrderNumber
Me.OrderNumber
[Transactions]!OrderNumber
Table![Transactions]![OrderNumber]
and several others

Regards, James

 

[WayneRyan]

James,

Pat's right, a "bound" form would make things a lot easier! The AutoNumber
control would be right on your form and easily available.

Using an "unbound" form, see my earlier post ...

Inserting with a query will make it tougher to retrieve the value. Which
record was it? If there was something unique about it, you could get
it with a DLookUp.

Use a recordset, and you can retrieve the value after the .AddNew but
before the .Update.

Wayne

 

[bloody_football]

Thanks for the replies, I'll keep trying Just bounding the forms now!