vbams-accessassign

How to randomly assign a unique ID VBA Access

I have here a simple form with a button that is supposed to assign a unique ID of a selected fruit with a Status of 'New' to a FruitPicker (person) every click. Unique ID that will be assigned (may be ascending or just random ID as long as it has the same name of the selected fruit) should have New Status and empty FruitPicker.

Code:

Option Compare Database
Option Explicit
Private Sub btnAssignFruit_Click()
Me.Refresh

txtUserName.Value = CreateObject("wscript.shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\Common\UserInfo\UserName")

Dim statusNew As String
Dim FruitPickerNameBlank As String
statusNew = "New"
FruitPickerNameBlank = ""

         CurrentDb.Execute "Update tblFruit Set FruitPicker = '" & txtUserName.Value & "', Fruit = '" & cmbFruit.Value & "' where Status = '" & statusNew & "' And Fruitpicker = '" & FruitPickerNameBlank & "'"
         
         'txtFruit field will then show the fruit id number and fruit name after execution of assigning of fruit
         
         MsgBox "     Fruit assigned."
End Sub
Private Sub btnClearAll_Click()
clearAll
End Sub
Private Sub btnRefresh_Click()
Me.Refresh
End Sub
Private Sub Form_Open(Cancel As Integer)
clearAll
End Sub
Sub clearAll()
txtUserName.Value = ""
cmbFruit.Value = ""
cmbDeploymentDate.Value = ""
End Sub

Table DataTypes:

datatypes

Selecting fruit:

form1

Selecting Deployment Date:

form2

The result was just right for the Name of FruitPicker field and the MsgBox display however, my name is not assigned in the FruitPicker field. It is like it is skipping the query.

data

This is the expected result after Assign Fruit button is clicked:

result

Expected table result:

expectedTable

Your help is greatly appreciated.

Note: I really do apologize if I cannot include the raw data like in Sheet1 of this link as it is broken in my view here. Even my profile picture is not showing and my other uploaded images in this question don't show (using company pc) but when I visit sstatic.net, uploaded images show.

Update:

I tried this below and it does assign but assigns to all that has New status:

CurrentDb.Execute "Update tblFruit Set FruitPicker = '" & Username & "' where Fruitpicker IS Null And Fruit = '" & cmbFruit.Value & "' And Status = '" & statusNew & "'"

How to make it like:

CurrentDb.Execute "Update tblFruit Set FruitPicker = '" & Username & "' where Fruitpicker IS Null And Fruit = '" & cmbFruit.Value & "' And Status = '" & statusNew & "' *AND ID is Distinct*"
Solution

  • I tried this and it worked:

    Private Sub btnAssignFruit_Click()
    Dim Username As String
    Username = CreateObject("WScript.Network").Username
    Dim rs1 As DAO.Recordset
    Dim sq1, statusInprogress As String
    statusInprogress = "In Progress"
    sq1 = "SELECT ID from tblFruit where Fruitpicker = '" & Username & "' And Status = '" & statusInprogress & "'"
    Set rs1 = CurrentDb.OpenRecordset(sq1)
    If Not rs1.EOF Then
        MsgBox "     You still have in progress ticket."
    Else
       Dim statusNew As String
       Dim FruitPickerNameBlank As String    
        Me!txtUserName.Value = Username
        statusNew = "New"
        Dim rs As DAO.Recordset
        Dim sq As String
        sq = "SELECT ID from tblFruit where Fruitpicker IS Null And Fruit = '" & cmbFruit.Value & "' And Status = '" & statusNew & "'"
        Set rs = CurrentDb.OpenRecordset(sq)
        
        If Not rs.EOF Then
        Dim test  As String
        
        test = rs.Fields(0)
        CurrentDb.Execute "Update tblFruit Set FruitPicker = '" & Username & "' where Fruitpicker IS Null And Fruit = '" & cmbFruit.Value & "' And Status = '" & statusNew & "' AND ID =" & rs.Fields(0)
        CurrentDb.Execute "Update tblFruit Set Status = '" & statusInprogress & "' where Fruitpicker = '" & Username & "' And Status = '" & statusNew & "'"
        End If
        MsgBox "     Fruit assigned."
        Me.Refresh
    End If
End Sub