Write to a specific record with VBA?

[BDCarrillo]

Here's the info:

tblShop_Names
Shop_Name
Shop_Code
Locked - Yes/No
Locker - Text (used to store username)

I've written the following code:

'check if shop is locked
If Me.Locked.Value = 0 Then
'open shop_form And [Shop_Code] = 502, load global variable
DoCmd.OpenForm "Shop_Form", acNormal, , "[Shop_Code] = 502", , acDialog
ShopCodeTemp = "502"
'lock shop code and record user
Me.Locked.Value = 1
Me.Locker.Value = Environ("Username")
Else
MsgBox ("User" & Me.Locker.Value & "is currently editing the selected shop.")
End If

I just can't figure out how to have it only look at and edit the locked/locker values from the record in tblShop_Names that matches the selected shop/shop code. I have 12 shops to work with, each with a unique code/name.

 

[dhookom]

Is there a reason why you are opening a form? I would consider replacing the OpenForm stuff with:

  Dim strSQL as String
  strSQL = "UDPATE tblShop_Names " & _
    "SET [Locked] = 1, Locker ='" & Environ("UserName") & _
    "' WHERE Shop_Code = '502'"
  CurrentDb.Execute strSQL, dbFailOnError

This assumes Shop_Code is a text field.

I also prefer to use an API to get the network login vs the Environ() function.

Duane
Hook'D on Access
MS Access MVP

 

[BDCarrillo]

The form that's being opened is the primary user interface, once they select their shop from the main menu.

I'm utilizing a "lock" and "check" per shop to prevent multiple users from interacting with the same records.

Basically it checks for and writes a "1" to the Locked field if no one else has opened the interface with the same Shop_Code, and opens the interface. Upon closing the interface I'll put in addition code to set "Locked=0

 

[PHV]

What about this ?

'check if shop is locked
If Me!Locked = 0 Then
  'lock shop code and record user
  Me!Locked = 1
  Me!Locker = Environ("Username")
  Me.Dirty = False
  'open shop_form And [Shop_Code] = 502, load global variable
  ShopCodeTemp = "502"
  DoCmd.OpenForm "Shop_Form", acNormal, , "Shop_Code=502", , acDialog
  'unlock shop code
  Me!Locked = 0
  Me.Dirty = False
Else
  MsgBox ("User " & Me!Locker & " is currently editing the selected shop.")
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BDCarrillo]

That second chunk of code didn't match up the shop_code.

I worked around the issue by changing from a grid of buttons to one combo box. Code is below:

If Me.cmbSel.Value = "Select a shop" Then
MsgBox ("Please select a shop first.")
Else
 If Me.cmbSel.Column(1) = 0 Then
 Me.Locked.Value = 1
 Me.Locker = Environ("UserName")
 DoCmd.OpenForm "Shop_Form", acNormal, , "[Shop_Code] =" & Me.cmbSel.Column(3) & "", , acDialog
 Me.Locked.Value = 0
 Me.Locker = ""
 Else
 MsgBox ("User " & Me.Locker & " is currently editing this shop.")
 End If
End If

"Select a shop" is the default value of the cmbSel combo box. I did away with the global variable. The Combo box sees Shop_Name, Locked, Locker, and Shop_Code as its data sources, and pulls the list from the Shop_Name field.

Still not sure on how to execute a similar solution by using the original 12 button layout, but the combo box is a bit more streamlined.

 

[Andrzejek]

Isn’t the field [tt]Locked [/tt]a little redundant since you already have a field [tt]Locker[/tt]?

If [tt]Locker[/tt] field have any value at all in your DB, the record is locked and you know who locked it, if it is empty, the record is available and not Locked.

Why do you need [tt]Locked[/tt] field?

It's always either:[tt]
Locker JoeBrown
Locked 1[/tt]
or[tt]
Locker <empty>
Locked 0[/tt]

Have fun.

---- Andy

 

[BDCarrillo]

Absolutely, it is redundant... I started with the "Locked" field and added "Locker" much later to help generate a spiffy message box to help clue users as to who might be working on their shop, and help me figure out who force-closed access.

Redundancy removed, and code is a lot cleaner now... down from 4 lines per button, 12 buttons.

 

[BDCarrillo]

Well I found out that my code wasn't working exactly as intended.

It will write in the persons user name for "locker" but it will do it in the first record of the table, instead of the record that matches the combo box.

'Force a user selection
If Me.cmbSel.Value = "Select a shop" Then
MsgBox ("Please select a shop first.")
Else
'Forces a re-look at the table 
 Me.Refresh
 Me.Requery
 'Check for blank user name
 If Me.cmbSel.Column(1) = "" Then
 'Line that needs to write to the record whose shop code=Me.cmbSel.Column(2)
 Me.Locker = Environ("UserName")
 DoCmd.RunCommand acCmdSaveRecord
 DoCmd.OpenForm "Shop_Form", acNormal, , "[Shop_Code] =" & Me.cmbSel.Column(2) & "", , acDialog
 'Line that needs to write to the record whose shop code=Me.cmbSel.Column(2)
 Me.Locker = ""
 DoCmd.RunCommand acCmdSaveRecord
 Else
 MsgBox ("User " & Me.Locker & " is currently editing this shop.")
 End If
 
End If

 

[BDCarrillo]

Got it... Put in a command to "go to record that matches the combo box" in the after update of it...

Duh moment!