Solved

Getting Uniq No in Multiuser Env.,

Posted on 2000-03-11
33
201 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
ID: 2607390
unique bill no?

Please explain that.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2607397
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
ID: 2607402
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 14

Expert Comment

by:mcrider
ID: 2607455
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
ID: 2607484
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
ID: 2607639
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 70

Expert Comment

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

Expert Comment

by:wsh2
ID: 2608048
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
ID: 2608683
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
ID: 2609345
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 70

Expert Comment

by:Éric Moreau
ID: 2609539
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
ID: 2611056
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
ID: 2611124
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
ID: 2611151
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
ID: 2643089
This question has a deletion request Pending
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2643180
This question no longer is pending deletion
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2643181
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
ID: 2644478
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
ID: 2648754
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
ID: 2648755
This question has a deletion request Pending
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2649545
This question no longer is pending deletion
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2649546
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
ID: 2653124
Adjusted points from 50 to 100
0
 

Author Comment

by:ackid32
ID: 2653125
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
ID: 2654334
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
ID: 2655994
Adjusted points from 100 to 150
0
 

Author Comment

by:ackid32
ID: 2675925
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
ID: 2676175
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
ID: 2677408
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
ID: 2678145
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
ID: 2699207
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
ID: 2869164
As I see you are active again.. wazzup with this question?.. <smile>.
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2869345
God! This *Thang* still open?!?
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

803 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