Access Form launch, Autonumber field for SQL Server Back End

Question

Hi MVP (including previous posts)

Some assistance please on our situation - post migration, 

I have read this in brief detail, but certainly a lot of issues post migration

https://www.codeproject.com/Tips/646080/Migrating-Access-Jet-Data-to-SQL-Server

and 

ttps://regina-whipp.com/blog/?p=371

We currently have crosstab query, to which we double click on a particular matrix area.

This launches a form via vba code that is triggered on the doubl click event

     DoCmd.OpenForm frmName, WhereCondition:="ID=" & 0

The form that is launched has ID (autonumber).

The field is blank, which used to get auto populated with the next id.

At this point I wish to just populate the autonumber field (without saving the form), as there are other fields that need to be filled in, or the user may simply cancel the form entries, and just click a cancel/close button on the form.  It does not matter if the autonumber field increments through this entry/'cancel, 

because there is further VBA code that populates the next field.

Forms(frmName).Controls.Item("Job Number").Value = IIf(IsNull([Forms]![Dashboard Details]![Job Number]), "A" & 100000 + [Forms]![Dashboard Details]![ID], "")

Is it such that we put the following code (not quite sure the entire structured syntax)... if there is a little example i can follow please

rs. AddNew

rs. Update

rs.Move 0, rs.LastModified

lngNewID = rs!ID

Thank you kindly.

References:

In that article above: 

7. that autonumber/GUID fields in SQL Server only update on COMMIT, rather than when first created as in

Access. That has made using a bound form to add new records very tricky indeed! (Not sure this is entirely true, but difficult

to test.) In the end, I used a Stored Procedure and an associated Access pass-through query to add records and return the

allocated GUID to the application (as a Recordset).

14.

A variation on the problems related to items 7 and 9 (in the article): If you have a bound sub-form (even just to a table) and want to

prevent the user simply typing in the offered blank new record, you can turn off the "Allow Additions" property. However,

this also means that you cannot - directly - add a record using VBA by manipulating the recordset associated with the form.

Instead you must use a separate recordset, Stored Procedure, etc. to add a suitable blank/prefilled new record and then

refresh the sub-form.

If you use VBA to add a record to a linked table with an IDENTITY field or GUID, then to make the new record current in

the recordset ("rs" in this example) used you need to add the following commands after using .AddNew and amending

any fields:

rs.Update

rs.Move 0,rs.lastModified

If the table or a query built with it is bound to a form and you are attempting to add a new record whilst viewing the form,

then...

Me.Requery

...is also required to force the form to update.

Answer 1

That's the direction I think you should take - create that "sequence" number when needed, and NOT when the user moves onto a new record (which could potentially be discarded).

Answer 2

You can certainly store all the "root" values needed to create a Job Number like "A300" or "A300-1". I'd think you'd store a JobType (the "A"), the Job Number (the "300") and the JobSequence (the "1").

You could grab those values when the user needs to see that Job Number, and create it - like when you're launching that form.

You do NOT need an AutoNumber to do this, and in fact it would seem an AutoNumber would complicate things. The references you mention from UtterAccess should show you how to create an auto-incrementing value, or you can refer to this:

https://www.experts-exchange.com/questions/28035706/access-problem-using-auto-numbers-for-invoices.html#a38902303

Be warned that DMax is not the recommended way to do this in a multiuser environment. A table-based solution is a better approach with multiple users.

Answer 3

I'd look at this NOW rather than later. This is something that hints at fundamental design issues.

I'd also encourage you to avoid macros in production systems. They don't lend themselves well to fault tolerance, and can be very limiting in their scope and actions. I've rarely seen macros in professional Access applications, and then only very, very sparingly. You can't really handle errors with macros (like you can with VBA), which is the biggest problem.

Answer 4

You are correct Scott, 

The forms and macros were initially designed for the Call Tracker

We enhanced them and put a Job Number field which was derived in VBA Code from the ID number.  

We could not put it as a formula in the formula area of the text box, as there were certain rules required.

I have got around the conversion changes with a save, then a vba calculation for the job number, then a second save.  I will look later how I can redesign this.

But here is the macro code for anybody interested.  Thank you

Answer 5

Why would it matter if the AutoNumber field is populated? I'm not sure I understand ...

Regardless, the fact is the fact - with SQL Server you won't get that AutoNumber value until the record is committed. If you simply must have it, you'd have to "save" the record, get the value, and the Delete the record if the user decides to cancel. Seems counter-intuitive to me.