Getting Uniq No in Multiuser Env.,

unique bill no?

Please explain that.

Create a separate Access Table for Control Values (you could create seperate records for each company.. for company specific data).. and in it place a field called LastBillNoUsed.

Prior to adding a record in the main database, in update mode, read the Control record.. add 1 to the LastBillNoUsed field.. store LastBillNOUsed in the new record.. and then Write the Control record.

Because of Record Locking, only one user at a time will have access to the control record so that BillNo synchronization will NOT be a problem and every Billno will be unique.. <smile>.

Do a

SELECT MAX (bill no) FROM MyTable

i have 3 different sales counters and i have generate bills from each of them. but i want the bill numbers should be unique

Since these are registers, why not just use a sequential incrementing number along with a register number that you configure when you install your software... for example:

when you install your software on the register, ask for a register number from the user (in a textbox) and then do something like this:

   SaveSetting "MyApp","Setup","RegisterID",Text1.Text

Now every time you run your program you can get that register number by doing this:

   Dim RegNum As String
   RegNum = GetSetting "MyApp","Setup","RegisterID",""

If you typed "101" when you installed the software, RegNum will = "101"

Then you can use this to generate your bill numbers... like this:

    Dim BillNumber As String
    Dim iVal As Long

    iVal='Whatever you last bill number was + 1
    BillNumber = RegNum+"-"+format(iVal,"000000")

So, If the last bill on this register was 23 and the register number was 101, then the variable "BillNumber" will be "101-000023"


Does this make sense??


Cheers!®©

mcrider.. while you are at, why not just add the Last Bill Number Used to the registry too?.. Everything all in one place.. and very easy to get.. <smile>.

Personally, I still prefer to store the control information in the Access database along with the data. Yes, it WILL add additional network traffic.. <big groan>.. but at least everything will be local to the server.

mcrider.. your RegisterId prefix suggestion is an excellent one. From an accounting standpoint.. if register bills ever had to be audited, a numerical sequence greatly assists in making sure that you have all pertinent documents, you can use simple math to calculate how many Bills came from a register, and if a printing or technical problem occurred, you could just look right at the bill to know which register it was generated from.

Whats more.. If you decide to go the Access Control Table route, you can set up a control record for each register. Having separate register records, will substantially reduce record contention (update lock) delays.

Bill.

wsh2 (Bill)

The reason I suggested keeping only the RegisterId in the system's registry is... If you keep the "Last Bill Number Used" in the database, then you would be able to use the database for any number of statistical reports... For example:

Jane runs register1 and has performed 150 transactions in the last 8 hours...

Margret Runs register2 and has performed 2 transactions in the last 8 hours...  She's too slow! Fire her! ;-)


Cheers!®©

Set one column of your table as "AutoNumber"!

mcrider (Cheers!®©):
I apologize for being a little vague.. <sign>. I was applauding your registry solution.. and as such.. was just trying to point out to the questioner that he/she could also store the LastBillNumber there as well. If indeed the Bill Numbers do NOT have to be tightly controlled / reported or used across platforms, a pure Registry solution would certainly perform better both by reducing network traffic and database contention.. <smile>.

emoreau:
Yes, Autonumber is also a viable solution.. <smile>. But being the brute force and ignorance kinda guy that I am, I prefer to directly controlling my fate. Additionally, as I do NOT know how.. <guilty look>.. to seed/reseed an Autonumber field from VB.. I have steered away from this type of solution. (Questioner please don't let this comment detract from emoreau's very worthy suggestion.. I am very proud of MY ignorance.. and most willing to pay the price of it.. <smile>)

I use a GUID in my code - it doesn't return a very nice number though, being 32 characters long, but it's Microsofts way of getting a Globally unique number.

viz (code just taken from MSDN, but works OK for me!):

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long

Global Const S_OK = 0 ' return value from CoCreateGuid


Public Function GetGUID() As String
   
        Dim lResult As Long
        Dim lguid As GUID
        Dim MyguidString As String
        Dim MyGuidString1 As String
        Dim MyGuidString2 As String
        Dim MyGuidString3 As String
        Dim DataLen As Integer
        Dim StringLen As Integer
        Dim i%
   
        On Error GoTo error_olemsg
   
        lResult = CoCreateGuid(lguid)
   
        If lResult = S_OK Then
   
           MyGuidString1 = Hex$(lguid.Data1)
           StringLen = Len(MyGuidString1)
           DataLen = Len(lguid.Data1)
           MyGuidString1 = LeadingZeros(2 * DataLen, StringLen) _
              & MyGuidString1 'First 4 bytes (8 hex digits)
   
           MyGuidString2 = Hex$(lguid.Data2)
           StringLen = Len(MyGuidString2)
           DataLen = Len(lguid.Data2)
           MyGuidString2 = LeadingZeros(2 * DataLen, StringLen) _
              & Trim$(MyGuidString2) 'Next 2 bytes (4 hex digits)
   
           MyGuidString3 = Hex$(lguid.Data3)
           StringLen = Len(MyGuidString3)
           DataLen = Len(lguid.Data3)
           MyGuidString3 = LeadingZeros(2 * DataLen, StringLen) _
              & Trim$(MyGuidString3) 'Next 2 bytes (4 hex digits)
   
           GetGUID = _
              MyGuidString1 & MyGuidString2 & MyGuidString3
   
           For i% = 0 To 7
              MyguidString = MyguidString & _
                   Format$(Hex$(lguid.Data4(i%)), "00")
           Next i%
   
           'MyGuidString contains last 8 bytes of Guid (16 hex digits)
           GetGUID = GetGUID & MyguidString
   
        Else
           GetGUID = "00000000" ' return zeros if function unsuccessful
        End If
   
        Exit Function
   
error_olemsg:
         MsgBox "Error " & Str(Err) & ": " & Error$(Err)
         GetGUID = "00000000"
         Exit Function
   
End Function

Public Function LeadingZeros(ExpectedLen As Integer, ActualLen As Integer) _
    As String
   LeadingZeros = String$(ExpectedLen - ActualLen, "0")
End Function

I am using two computers for billing but I want unique bill no (every user gets last billno + 1, same serial) by locking records. Now I am taking bill serial no by

"select * from bill_master_table order by bill_no"
movelast

But this method gives me duplicate bill nos.(Bill No : 1 for both users)

I don't want to use control table
I can't user registry for two computer

Use the Autonumber field attribute. You don't have to give this field a value. Its value is calculated an inserted by the database engine (Access) in the field when you Append or Insert a new record. You won't have duplictes!

ackid32,

you said, "I can't user registry for two computer"

Why can't you??? If you store the register number and last number used in the registry of each computer, then, when you write the receipt number to the database, it will be *GUARANTEED* to be unique....


For example: Computer1's RegisterID has been saved like this when you install the software:

   SaveSetting "MyApp","Setup","RegisterID","R1-"
   SaveSetting "MyApp","Setup","ReceiptNumber","100"

and Computer2's RegisterID has been saved like this when you install the software:

   SaveSetting "MyApp","Setup","RegisterID","R2-"
   SaveSetting "MyApp","Setup","ReceiptNumber","100"
 

Then, when your program is running, you do this:


   Dim RegNum As String
   Dim RecptNum As Long
   Dim BillNumber As String
   
   RegNum = GetSetting("MyApp","Setup","RegisterID","")
   RecptNum = Clng(GetSetting("MyApp","Setup","RegisterID","100"))
   BillNumber = RegNum + format(iVal,"000000")
   SaveSetting "MyApp","Setup","ReceiptNumber", Cstr(RecptNum + 1)



When it is run on register1, the first BillNumber will be "R1-000100" and when it is run on register2, the first BillNumber will be "R2-000100".

The next BillNumber generated on register1 will be "R1-000101" and the next BillNumber generated on register2 will be "R2-000101".

As you see, the numbers are unique and you get a sequential number for each register...

Register1 BillNumbers:

      R1-000101
      R1-000102
      R1-000103
      ...

Register2 BillNumbers:

      R2-000101
      R2-000102
      R2-000103
      ...

You can then save the BillNumbers to your database...



Cheers!®©

Recapping.. No Registry.. No Control Table.. No Access Autonumber... hmmmm... how about DNA testing the invoice then !?!?!?... <lol and a wink>.

wsh2,

DNA testing will not work if the register tape in both register1 and register2 came from the same tree... <VBG>


Cheers!®©

This question has a deletion request Pending

This question no longer is pending deletion

You deleted the question ?!?!?!?!?! Shame on you ackid32.. a lot of people went to a big effort trying to assist you here.. with more than one solution that would work. Somehow I get the feeling you ripped off an answer here.. and are now just too cheap to award the points. At least be man enough to admit it.. or why no answer sufficed.

I have to agree with wsh2 here...  You have received several acceptable answers... You really should select one and grade it fairly...

nothing correct, and not workable to me and i have a solution from my friend that was
open a file using open statement in server and put my last bill no on it that works me fine and so i delete this question.

any way thanks for all to participate this section
bye

This question has a deletion request Pending

This question no longer is pending deletion

GAAAAWWWWWWWWWWWWDDDDDD!!!! <slapping forehead>.. WHY DIDN'T I SEE THAT !!!! <just kidding - lol>

Thank you for responding ackid32.. your Thank you is good enough for me.. <smile>.

Adjusted points from 50 to 100

wsh2 i have one doubt, i have to add customers in multiuser environment. there without autonumber, how i get the current customer number while 3 terminals enter the same.
thanks in advance

Two ways:

1.  Interactive: If while doing entry, no customer number is given, in your program add code to do a customer table lookup by NAME. If the customer is found, then use that key. If the customer is not found, go into your customer record add routine.. a customer record will be created and a number assigned.. allowing your process to continue. As all operators have to go through this process, they will first be given the opportunity to see if the Customer Name / Address exists BEFORE they go through all the work to do an addition. Yes, the chance of a duplicate may be there.. but as your Customer base grows.. more and more likely to occur.

2.  Manual: Establish one person as the Customer master control person. Only he / she may add Customers. Their job is to make certain ALL Customer record data is right and entered into the computer accordingly. If an operator needs a customer number.. they contact this person, who then sets the customer up correctly.

-------------------------------------
Now, go ask a friend, and stiff everyone here on the points.. LOL

Adjusted points from 100 to 150

I want Bill Nos like (for each Godown)


Godown User Name   Bill No  
 G1          A           1    
 G1          B           2
 G1          A           3
 G1          A           4
 G2          C           1
 G2          C           2
 G1          B           5


Each user have seperate Computer

So I cannot use registry,Auto Number

How?

Ok.. to reiterate what I commented above.. set up another Table in your Database. The Table (lets call it Controls) should have two columns.. Godown (Unique Key) and LastBillNumber (Long). Whenever you need a new UNIQUE Bill Number, execute the following SQL..

<----- Code Begin ----->

strSQL _
  = "SELECT [LastBillNumber] " _
  & "  FROM Controls " _
  & " WHERE [Godown} = '" _
  & MyGodownVariable _
  & "'"
rs.openrecordset (strSQL)

MyThisBillNumber = rs!LastBillNumber + 1

rs.Edit
rs!LastBillNumber = MyThisBillNumber
rs.Update

<----- Code End ----->

The MyThisBillNumber is unique and ready to use for your record addition.. <smile>.

not ok.

rs = db.openrecordset("control_table",
     Dbdenywirte+dbdenyread)
find godown number
lbillno = rs("bill_no") + 1
Rs.edit
rs("bill_no") = lbillno
rs.update

rs= db.opentable("billtable")
rs.addnew
rs("bill_no") = lbillno
rs.update

When i run two copies of the same program it gives me duplicate bill nos for same godown.

Your recordset code is bringing in the whole record set from the table and as your find statement is Pseudo, I'm not sure that it is working.. Try This: (I Used Godown and BillNo for field names)

<----- Code Begin ----->

Dim rsControl as Recordset
strSQL = "SELECT Godown, BillNo] " _
         "  FROM Control " _
         " WHERE Godown = " & intGodown
Set rsControl = db.OpenRecordset(strSQL, dbOpenDynaset)

With rsControl
   If .RecordCount < 1 _
   Then
      .AddNew
      !Godown = intGodown
      !BillNo = 1
   Else
      .Edit
      !BillNo = !BillNo + 1
   End If
   .Update
   lblBillNo = !BillNo
   .Close
End With
rsControl = Nothing

<----- Code End ----->

The db.openrecordset and recordset close can be moved to another part of your program (make them module level variables). You would then use the following:

<----- Code Begin ----->

Option Explicit

Private m_db as Database
Private m_rsControl as Recordset


Private Sub Form_Load ()

   Set m_db = OpenDatabase("YourDataBasePathHere.mdb")

   strSQL = "SELECT Godown, BillNo " _
            "  FROM Controls " _
            " WHERE Godown = " & MyGodownNumber
   Set m_rsControl = m_db.OpenRecordset(strSQL, dbOpenDynaset)

   With m_rsControl
      If .RecordCount < 1 _
      Then
         .AddNew
         !Godown = MyGodownNumber
         !BillNo = 0
         .Update
         .Requery
      End If
   End With

End Sub


Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

   m_rsControl.Close
   Set m_rsControl = Nothing
   Set m_db = Nothing

End Sub


Private Sub YourRoutine (ByVal MyGodownNumber as Integer)

   With m_rsControl
      .FindFirst ("Godown = " & MyGodownNumber)
      .Edit
      !BillNo = !BillNo + 1
      .Update
   End With

   lblBillNo = !BillNo

End Sub

<----- Code End ----->

ws2, wait for me to evaluate, sorry for the late evaluation, im out of station

thakns

God! This *Thang* still open?!?