Automatically adding new order numbers? (1 Viewer)

[Esjaye]

Ok, so i am trying desperately to update a database that was built in access 95 i have got it all imported over and am now working on it in access 365 but i have a bit that isnt working and im pulling my hair out with it and really hoping someone here will be able to help me out because im now desperate!

There is a form with a drop down where you select the persons name and the address automatically appears - so far so good. there is a button to press to generate a new order for that person that should automatically create a new order number for them 1 higher than the previous order number but the code isnt working on access 365 and just gives me a compile error!

Here is the code - can anyone tell me what is wrong please!!!!!! i havent done this sort of stuff since i was in uni and im really out of my depth

Public Function New_Order_Number() As String
Dim s As String
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("_Company", dbOpenDynaset)

rst.MoveFirst

s = rst.[OrderNumber Prefix]
s = s & FillString(6 - Len(rst.[OrderNumber]), "0")
s = s & rst.[OrderNumber]

rst.Edit
rst.[OrderNumber] = rst.[OrderNumber] + 1
rst.Update

rst.Close

Set dbs = Nothing

New_Order_Number = s

End Function


Huge amount of thanks to anyone who can make head or tail of this.

 

[Ranman256]

The compile should show you the Error line.
We're is it?

One idea, don't DIM rst as recordset, just use variant....
Dim rst

 

[Esjaye]

s = rst.[OrderNumber Prefix] is the bit that is highlighted blue when the whole thing falls appart so im guessing that is the error line.

will try removing record set now.

i didnt do the original code or the original database so i will be honest i dont have a clue what half of it is referring to.

 

[Esjaye]

IT WORKED!!!!!! i love you!!!!!!!!!

 

[Gasman]

One idea, don't DIM rst as recordset, just use variant....
Dim rst

Ranman256, may I ask as to why use a variant.?

I am keen to learn the nuances of Access and I would use a recordset as that is the only way I know how.?


TIA