Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lock record number?

    I have an Outlook 98 form that we use to submit information. When a form is opened I automaticly assign a number to it. Sometimes, we get two people assigned to the same number. Is there a way that we can prevent this?

    Here is a the code I am using right now.

    Function Item_Open()
    Dim Obe
    Dim MyDB
    Dim Rst
    Dim Rsm
    Dim Counter, CounterStart
    Dim RequestNum
    Dim RqBox

    'On error resume next
    Set Dbe = Application.CreateObject("DAO.DBEngine.35")
    If Err.Number <> 0 Then
    Msg Err.Description & "--- Some functions may not work correctly" _
    & Chr(13) & "Please make sure that Dao 3.5 is installed on this machine"
    Exit Function
    End If

    ' Check to see if this is from scratch

    Set MyDB = Dbe.Workspaces(0).OpenDatabase("tabsproddatabaseed garpromotionpromotion.mdb")
    Set Rst = MyDB.OpenRecordset("task")

    set nms=application.getnamespace("mapi")
    vrUser=nms.currentuser
    'msgbox"User " & vbUser

    If UserProperties.Find("jobnum").Value = 0 Then
    CounterStart = 1
    Counter = Rst.RecordCount + CounterStart
    set nms= application.getnamespace("mapi")
    vrUser=nms.currentuser

    UserProperties.Find("jobnum").Value = Counter
    UserProperties.Find("Reqby").Value = vrUser
    UserProperties.Find("txtstatus").Value = "Submit"
    UserProperties.Find("jobtype").Value = "Request for Processing"

    Rst.AddNew
    Rst("jobnum") = Counter
    Rst("txtstatus") = "Submit"
    Rst("jobtype") = "Request for Processing"
    Rst.fields(13).value=vrUser

    Rst.Update
    Rst.Close
    MyDB.Close
    Else
    RequestNum =UserProperties.Find("jobnum").Value
    Set Rst = MyDB.OpenRecordset("select * from task where jobnum = " & RequestNum)
    UserProperties.Find("txtstatus").Value = rst.Fields(2).Value
    'msgbox "Inside Update"

    If UserProperties.Find("txtstatus").Value = "Work in progress" then
    msgbox"This job has been started already"
    End if

    If UserProperties.Find("txtstatus").Value = "Finished" then
    msgbox"This job has been completed already"
    End if

    rst.close
    mydb.close
    End If
    End Function
    '-=-=-=-=-=-=-=-=-=-

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock record number?

    Assign the numbers from an Access table/database. Even if that is the only purpose of the database, you'll get unique numbers with every added row. They'll be sequential too if that's important.

    If it's not you could code a random number generator to, say, 16 digits and count on the fact that the same number will *probably* never occur twice.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock record number?

    Well, I did not see your code when I responded. You can make a simple change to do the trick. Don't create your counter until after you add a new row to the table! Like this:

    rst.AddNew
    Counter = rst.RecordCount + 1
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •