purchase order db

  • Thread starter Thread starter Lapchien
  • Start date Start date
L

Lapchien

Ok, so I've stopped using autonumber for my purchase order numbers, with:

Me.Ref = Nz(DMax("Ref", "tbl_POD"), 0) + 1

on my forms Before update event.

However (!) I'd like the purchase order number (Ref) to be a random and
unique (in the table) number. Is this possible?

Thanks,
Lap
 
Its quite tricky to produce a random and unique number. However you can set
Autonumbers to be random, and if you put a unique index on that field, it
virtually guarantees they are unique as well.

Another way is to fill a table with sequential numbers, and select one of
them at random. After that one is used, remove it from the table. This way
you can select the range of numbers returned, just by putting that range in
the table initially.

Something like ( aircode )

Function GetRandomNo() As Long

Dim rRecord As Long
Dim endnum As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblRandom")
Randomize Timer 'seed random number generator
rs.Edit
rs.MoveLast 'ensure recordcount is updated
endnum = rs.RecordCount 'get recordcount
rs.MoveFirst
rRecord = endnum * Rnd(1) 'get random number between 0 and end of table
rs.Move rRecord 'move to that record
GetRandomNo = rs!rNumber 'get number from that record
rs.Delete 'delete number from table

rs.Close
Set rs = Nothing
End Function

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top