Solved

Getting Uniq No in Multiuser Env.,

Posted on 2000-03-11
33
193 Views
Last Modified: 2010-05-18
I am using VB6 with Access.In multiuser environment  How can i get a unique bill no?
0
Comment
Question by:ackid32
  • 12
  • 11
  • 6
  • +3
33 Comments
 
LVL 4

Expert Comment

by:CoolAss
Comment Utility
unique bill no?

Please explain that.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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
0
 

Author Comment

by:ackid32
Comment Utility
i have 3 different sales counters and i have generate bills from each of them. but i want the bill numbers should be unique
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
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!®©
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
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!®©

0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Set one column of your table as "AutoNumber"!
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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>)
0
 
LVL 6

Expert Comment

by:andyclap
Comment Utility
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
0
 

Author Comment

by:ackid32
Comment Utility
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

0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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!
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
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!®©



0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Recapping.. No Registry.. No Control Table.. No Access Autonumber... hmmmm... how about DNA testing the invoice then !?!?!?... <lol and a wink>.
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
wsh2,

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


Cheers!®©
0
 

Author Comment

by:ackid32
Comment Utility
This question has a deletion request Pending
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
This question no longer is pending deletion
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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.

0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
I have to agree with wsh2 here...  You have received several acceptable answers... You really should select one and grade it fairly...
0
 

Author Comment

by:ackid32
Comment Utility
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
0
 

Author Comment

by:ackid32
Comment Utility
This question has a deletion request Pending
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
This question no longer is pending deletion
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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>.
0
 

Author Comment

by:ackid32
Comment Utility
Adjusted points from 50 to 100
0
 

Author Comment

by:ackid32
Comment Utility
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
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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
0
 

Author Comment

by:ackid32
Comment Utility
Adjusted points from 100 to 150
0
 

Author Comment

by:ackid32
Comment Utility
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?
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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>.
0
 

Author Comment

by:ackid32
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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 ----->
0
 

Author Comment

by:ackid32
Comment Utility
ws2, wait for me to evaluate, sorry for the late evaluation, im out of station

thakns
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 150 total points
Comment Utility
As I see you are active again.. wazzup with this question?.. <smile>.
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
God! This *Thang* still open?!?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now