Using DMax to generate an Order Number on a network... 2

[ImStuk]

I have a form that uses DMax in the Before_Insert event of my form. The table is a network, but users all have their own copy of the front on their PC's. How can I requery the table right after the Order Number is generated. right now, If two users are trying to enter data at the same time they end of with the same order number. If I requery the form, then it returns to the first record. What is recommended? Am I going to have to lock others out of using that Form while somebody else is entering an order?

 

[hermanlaksko]

I like that handle

I use a table to store my var numbers for invoices, vouchers, invoices etc. to do the I use this function:

Function NytFaktNummer(NrPlus)
On Error GoTo Err_NytFaktNummer
Dim MyDB As Dao.Database, MyRe As Dao.Recordset
B_Nummer = 1
Set MyDB = CurrentDb
Set MyRe = MyDB.OpenRecordset("Select Invoice FROM Comp Where CompID = '" & YrWhereInfo & "'", dbOpenDynaset, dbSeeChanges)
If MyRe.RecordCount > 0 Then
MyRe.Edit
B_Nummer = MyRe!Invoice
If IsNull(B_Nummer) Then B_Nummer = 1000
MyRe!Invoice = B_Nummer + NrPlus
MyRe.Update
End If
NytFaktNummer = B_Nummer

Exit_NytFaktNummer:
Exit Function

Err_NytFaktNummer:
MsgBox Error$, , GetMsg("Sys_101&quot
Resume Exit_NytFaktNummer

End Function

To trigger the above I use :

FB_Nr = Forms!YrForm!Invoice
If IsNull(FB_Nr) Then FB_Nr = 0
If FB_Nr = 0 Then FB_Nr = NytFaktNummer(1)
Forms!YrForm!Invoice= FB_Nr

The (1) in the NytFaktNummer is the jump in number I want to use for each new invoicenumber.

Rgds
Herman

 

[MichaelRed]

Another approach is shown in faq700-184. To be SURE that a unique value is retrieved, you need to lock the source (table) for the duration of the procedure which generate the value. You can often go for a (short) while using a non-locking approach, and always do so while there is only the single user. Alas, you WILL get 'burned' at some point with multi-user apps / systems. But, when using a locked source, you then get 'denial' errors, so also need the delay / retry approach.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ImStuk]

Wow...I will explore both idea's. Thanks for all the help.

 

[ImStuk]

Michael, I am new to this and seem to be getting errors involving the queries "Basic Data" and "qSelQiMax". I made a query that does the Max value for my Unique ID and named it "qSelQiMax", but I am not sure what the "Basic Data" is supposed to represent. I am not an experienced programmer, but I seem to be real close on this. I am calling this function from the Before_Insert event of my form 'Call NewQi_Num'. Is this the correct place to do this? My table is tblOrders, and the field for the unique ID is OrderNumber. Thanks for the help.

 

[MichaelRed]

hmmmmmmm ... mmmmmm been a bit since I really looked at this. As I Recall, "BasicDatq" it the table which is where the 'new' Id value is to be placed. I 'assume' that this is your [tblOrders]. I cannot re-check the procedure at the moment, but if this doesn't help, re-post and I'll try to find the time / opportunity to review (perhaps I can even embillish the 'doc'?).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ImStuk]

I think my inexperience is holding me back on this one. I just don't understand the code. I have a form called "frmNewOrder" where the source is [tblOrders] that has a field called "OrderNumber" that needs to be unique. Right now I use a DMax function in the Before_Insert event of the form. But now, there are 10-15 users on this database, and there is going to be a point where two or three people try and start an Order at the same time. I just need a simple way to lock the Table or even quickly requery so that number wont be available as quickly as possible. Users will hold these orders open for a while some times. Any help you can give would be great.

 

[MichaelRed]

You will need to explain a bit more re the approach you are attempting. Is it to use the referenced faq? If so, what error are you getting and where does it occur and when does it occur? Can you post the part of your code which is calling the procedure in the faq? Have you created the seperate table for the 'ordernumber'? What changes have you made to the code posted?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ImStuk]

I tried to use everything from your FAQ as close as possible. All I eliminated was what I thought were the Date and Time issues. I made a table [tblNewQiNum] and a Query that finds the highest number from that table called [qSelQiMax]. I don't know how to trigger this at all so in my Order Forms Before_Insert I put me.OrderNumber = NewQiNum

I got this error: 3265 Item not found in collection.

I hope I am close at least, but here is the code. Pardon my lack of VB knowledge if there is something silly I am missing.

Option Compare Database
Option Explicit

Public Function NewQi_Num() As Long
On Error GoTo NewQiNum_Err
Dim PCHSKnxFrmUserEd1 As Database
Dim BaseData As Recordset
Dim tblNewQiNum As Recordset
Dim qSelQIMax As Recordset

Const RiErr = 3000
Const LockErr = 3260
Const InUseErr = 3262
Const NumReTries = 20#

Dim NumLocks As Integer
Dim lngX As Long

Dim QINum As Long
Dim lngOldQiNum As Long
Dim lngNewQiNum As Long
Dim lngBigQiNum As Long

Set PCHSKnxFrmUserEd1 = CurrentDb()
Set BaseData = PCHSKnxFrmUserEd1.OpenRecordset("tblOrders&quot

Set qSelQIMax = PCHSKnxFrmUserEd1.OpenRecordset("qSelQiMax", dbDenyRead)
Set tblNewQiNum = PCHSKnxFrmUserEd1.OpenRecordset("tblNewQiNum", dbDenyRead)

lngOldQiNum = qSelQIMax!QINum
lngNewQiNum = tblNewQiNum!QINum
lngBigQiNum = lngNewQiNum

If (lngOldQiNum > lngBigQiNum) Then
lngBigQiNum = lngOldQiNum
End If

If (QINum > lngBigQiNum) Then
lngBigQiNum = QINum
End If

lngBigQiNum = lngBigQiNum + 1

With tblNewQiNum
.Edit
!QINum = lngBigQiNum
.Update
End With
NewQi_Num = lngBigQiNum

NormExit:
Set BaseData = Nothing
Set PCHSKnxFrmUserEd1 = Nothing

Exit Function

NewQiNum_Err:
If ((Err = InUseErr) Or (Err = LockErr) Or (Err = RiErr)) Then
NumLocks = NumLocks + 1
If (NumLocks < NumReTries) Then
For lngX = 1 To NumLocks ^ 2 * Int(Rnd * 20 + 5)
DoEvents
Next lngX
Resume Next
Else
End If
Else
MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbOKOnly & vbCritical, &quot;Get Qi Number&quot;
GoTo NormExit
End If
End Function

 

[MichaelRed]

With a very few caveats, the procedure works just fine. With NO changes to the procedure, I was able to get the procedure to return the 'sequential value(s).

Then, there are the caveats:

The err, which you mention is generated by the query. I SUSPECT (rather strongly) that you have the FIELD QiNum, but that you are taking the MAX of it. I will show three (3 versions of the query:

First
SELECT Max(tblNewQiNum.QiNum) AS MaxOfQiNum
FROM tblNewQiNum
WITH OWNERACCESS OPTION;

Second
SELECT Max(tblNewQiNum.QiNum) AS QiNum
FROM tblNewQiNum
WITH OWNERACCESS OPTION;

Third
SELECT tblNewQiNum.QiNum
FROM tblNewQiNum
WITH OWNERACCESS OPTION;

Some explination here:
The First is (probably) what yous looks like. Notice that the NAME of the field is NOT QiMax, but MaxOfQiMax. The procedure doesn't recognize the field, as it was 'automatically' alaised by the aggregate function &quot;MAX&quot;.

Either the second or third queries would be reasonable, as either returns the correct field name. Strictly speaking, the LAST (i.e. third one) is technically the correct approach.

If you follow through the code, you will note that we NEVER add a record to this table, the single record is only ever updated, so Max, Min, Avg, Top ... predicates would (presumably) only ever find the single record / value. On the OFF chance that some muckety err could corrupt the scheme, Max (properly alaised -back to &quot;QiNum&quot is acceptable.

Next, one must realize that at least the table with the value (QiNum) MUST have a record for the query to find. the value of QiNum must be available to Increment.

Otherwise, it 'looks like' a good job (at least I can't see any problems from here (your house is too far away for me to even see).

Some curiosities are evident in some of your renaming. Specifically database name &quot;PCHSKnxFrmUserEd1&quot; seems a bit extreme. It is NOT wrong, however, it is common practice to simply use &quot;rst&quot; or even &quot;rs&quot;, as the name -in this sense/usage- is relevant ONLY to the internal processes of the procedure. No 'human' (except the programmer) will ever need to see or use it, and even other procedures do not really need to access this name.

Please post more if there are additional questions.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ImStuk]

Okay, I am close, ever so close. Without changing the Function I changed the query to your third selection. On the form I am trying to get the Next highest QINum, I have done:

Before_Insert
Me.OrderNumber = NewQi_Num

I am on the brink of success. What the above did was provide me with the highest number and added 1. But then I go to do another order and it provided me with the same number again. Then I tried again and it added 1 again. So it seems that it will give me the same number for two orders in a row before adding one.

I looked at the table, and the table is holding the last highest number in two records?????

I'm close...I can feel it. I really appreciate your patients with me. Thank you.

 

[MichaelRed]

the TABLE should not have two records. HOW it got there (more than the single record) is a problem and a mystery. Revert to the SECOND query SQL stmt. Delete ARecord (hopefully the one w/ the lower value). Try again.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ImStuk]

Alright...it works! I kept the same query, but deleted the second record and now I have success. I must have put two records in there when I was setting the starting number. Thank you very much for all your help.