making a "next available number generator"

[llaadd]

hi, I am looking to try an make a access database which automatically generates the next available number based on the following:

Fields
Ref start
Ref end
User
Date used
Use Type

Conditions
1) Ref start is the date in format "YYMM" and is based on the month for the invoice (this might be back dated so if "date used" is changed, this is also changed)
UPDATE: I have solved part 1, just need to work out how to do part 2 below

2) Ref end is the end of the reference field which is unique to that month, i.e. 001
2a) if a invoice is backdated, i.e. "date used" is changed, the ref end is based on the next available number in that month/year, not the next number for the current month.

I have made a database and form and understand basic access macro's and VB but cannot figure out how I would go about doing this. I think I need to somehow use a database search/filter to check what records are on the database and somehow from that allocate the next number but am a bit lost!

If you are able to help it will be much appreciated.

Thanks a lot!

llaadd.

 

[llaadd]

ok getting close now, I have a form which updates the Ref start, and I have make a query which groups the Ref start and outputs the last number from each group, i.e.:

a table "Reference Number" with values:

Ref start - Ref end
1005 - 001
1005 - 002
1006 - 001
1006 - 002
1006 - 003
1006 - 004
1006 - 005
1006 - 006
1006 - 007​

produces a query "Last Number" with output:

Ref start - LastOfRef end
1005 - 2
1006 - 7​

How do I read the value of "LastOfRef End" for the required month and use it to generate the next number using VBA?

Thanks

 

[Galaxiom]

If you are also storing a date field, in a properly normalised structure this is done using an EndRef field (Integer). The reference number for display is derived from the two fields.

On your forms and reports use the control source:

=Format([datefield],"YYMM") & " - " & Format([EndRef],"000")

Use a DMax to get the largest existing number in the EndRef field with the condition on the month and year being the same as the current date. Then add one.

 DMax("[EndRef]","tablename", "Format([datefield],'YYMM') = Format(Date(),'YYMM')") + 1

Otherwise to parse the WholeRef field and get a number use :

 CInt(Mid([WholeRef], InStr([WholeRef],"-" + 1)) + 1

Note however you can have concurrency issues when two different users get the number at the same time. If there is any chance of this happening it is better to store the next number available in a single record table and return it with a recordset that locks out other users while it is retrieved and incremented.

 

[llaadd]

Thanks a lot for your help, it worked a treat, did it like this:

Private Sub Date_Used_AfterUpdate()
Form.[Ref start] = Format([Date Used], "yymm")
Form.[Ref end] = DMax("[Ref end]", "Reference Number", "[Reference Number].[Ref start] = Form.[Ref start]") + 1
End Sub

Only thing now, is it possible to have a "on error" clause on it, for example if the month selected has no data, an error will appear, in the event of this, I want it to supress the error and generate "000" so that once 1 is added to it, it becomes 001.

I tried this but then it always generated 001 as the "Ref End" value:

Private Sub Date_Used_AfterUpdate()
Form.[Ref start] = Format([Date Used], "yymm")
On Error GoTo Err_New_Number
Form.[Ref end] = DMax("[Ref end]", "Reference Number", "[Reference Number].[Ref start] = Form.[Ref start]") + 1
Err_New_Number:
Form.[Ref end] = "001"
End Sub

Thanks

 

[Banana]

Just a word of caution:

DMax() is not multi-user safe. If there are two users that happens to be looking at the same set of records and thus try to obtain the number for same set and neither has saved their edits, the last to save will lose out with a duplicate key error.

Do you require it to be sequential?

 

[llaadd]

Just a word of caution:

DMax() is not multi-user safe. If there are two users that happens to be looking at the same set of records and thus try to obtain the number for same set and neither has saved their edits, the last to save will lose out with a duplicate key error.

Do you require it to be sequential?

yes it needs to be sequential

I plan to put this on the server and people can access as an when needed. Is it possible to have a message come up that someone else is using if a second person tried to open the file? (only 8 people here though so it's very unlikely to happen anyway)

Thanks

 

[llaadd]

got it working...woohoo, I just missed out the "Exit sub" after the command which sometimes produced an error:

Private Sub Date_Used_AfterUpdate()
Form.[Ref start] = Format([Date Used], "yymm")
On Error GoTo New_Number
Form.[Ref end] = DMax("[Ref end]", "Reference Number", "[Reference Number].[Ref start] = Form.[Ref start]") + 1
[COLOR=red]Exit Sub[/COLOR]
New_Number:
   Form.[Ref end] = "001"
End Sub

Now just need to do the finnishing touches and all done....thanks a lot for your help!

 

[Banana]

The problem is that nobody & nothing will know if there will be conflict until it's actually attempted to save which may be too late.

IMHO, the best way to handle this is to serialize the transaction. Because Jet does not seem to directly support a serializable isolation, the closest way to do that would be to create a table that stores the months with its highest ref count so far. To obtain the new value do something like that:

Private rs AS DAO.Recordset

Private Sub Form_BeforeUpdate(Cancel As Integer)

Set rs = CurrentDb.OpenRecordset("SELECT ref FROM my_sequence_table WHERE Month = #" & Me.Month & "#;" , dbOpenDynaset, dbDenyWrite + dbSeeChanges)

rs.Edit
rs.Fields("ref") = rs.Fields("ref") + 1
Me!ref = rs.Fields("ref")

End Sub

Private Sub AfterUpdate()

rs.Update
rs.Close

End Sub

(aircode)

Of course, you'll need error handling to re-try the obtaining of new req because a runtime error will be raised if someone try to update the same field that's already opened by someone (hence the option to deny write. I would have liked to use Deny Read but that's not feasible with a linked table). But the idea is that you have the sequence table open in an edit mode and commit only when the record has been successfully committed so you can be certain there's no gaps due to some kind of failure to save the record.

Of course, you need to keep the windows as small as possible. If you already have code in BeforeUpdate for instance, opening recordset should be the last thing to do in that event and be first thing to do in the AfterUpdate. That way, if your code does some kind validation and fails the validation, recordset is never opened at all so there's less chance of conflicts.

HTH.

 

[llaadd]

thanks for your help, just realised I have to start again anyway...justy figured out not everyone has Micrsoft Access so this won't work...re-working in excel now, hopefully can get it done in that, with a VB form

 

[Galaxiom]

Users without Access can use the free Access 2007 Runtime available from the Microsoft site.

 

[llaadd]

Users without Access can use the free Access 2007 Runtime available from the Microsoft site.

thanks a lot, solved a lot of my issues...only one last one!!

do you know how (or if it's possible to) disable the security warning on the computer's using the Runtime instead of the full version of access? it's annoying the users!

thanks

 

[Banana]

Have a look at this. I understand it help with setting up the registry so you won't get that anymore.

 

[llaadd]

Have a look at this. I understand it help with setting up the registry so you won't get that anymore.

thanks a lot...it was looking good at the start, but then after finding how much the network here is, I keeps getting a hell of a lot of errors!

Will keep looking for another way to sort it, thanks a lot though

 

[llaadd]

I managed to get rid of that security message by generating a certificate in access VBA for all the forms and the install them on each PC, and select "Trust all from this publisher"

 

[Banana]

Okay, I'm impressed.

I've been trying to get "Trust from the publisher" to work with little luck. How did you do it? Were you using .mdb format or .accdb format? (I think .mdb format works just as it always has for past versions but .accdb format won't let one use certificate)

 

[llaadd]

Okay, I'm impressed.

I've been trying to get "Trust from the publisher" to work with little luck. How did you do it? Were you using .mdb format or .accdb format? (I think .mdb format works just as it always has for past versions but .accdb format won't let one use certificate)

I think I saved it as mdb as I wasn't originally sure if the people with access had the new or old version. I will check on monday when i get back to work...I tried at home with an older version still in accdb and it just don't work, which is annoying!

Well looks like i'll just stick to 2003 format...works best in most cases!

 

[Banana]

Yes, that's what I thought.

They changed things in ACCDB in regards to the security and for bizarre reasons unknown to me, they decided to not support digital signature of VBA project as has been done in MDB - mind, they substituted this with a "Package and Sign" functionality but that only guarantee that when person get the package, it has been not altered in transmit. Big whoopee - I want signature to remain in effect even after downloading. Ah, well.