locking records

[toffa]

i am wanting a check box that will lock the record so i dont accidently make changes then when i look up that record on the form if i want to make changes i have to uncheck or check the check box.

What would be the way to go about this.

Chris

 

[PHV]

Test your checkbox value in the BeforeUpdate event procedure of the form, playing with the MsgBox function and the Cancel parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[toffa]

i dont understand what you are saying here
could you explain in a bit more detail please
Chris

 

[TheAceMan1]

How are ya toffa . . . . .

Is the form is [blue]Single[/blue] or [blue]Continuous[/blue] view?

What [blue]version[/blue] Access ya using?

See Ya! . . . . . .

 

[grobermatic]

You could disable all the controls on your form using the Enabled property. Then using a checkbox (which would always remain enabled) you can toggle the property of all the controls on the form to be enabled or disabled.

HTH,

Craig

--------------------------------------------------------------------------------------------------------
"Time-traveling is just too dangerous. Better that I devote myself to study the other great mystery of the universe: Women!" .. Dr E. Brown (1985)

 

[TheAceMan1]

OK toffa . . . . .

Took a bit of digging in my solutions database. Note: in any code that follows, you substitute names in [purple]purple[/purple].

1) Add a CheckBox to the table holding the records of interest. Name it [blue]LockEdit[/blue].

2) Make sure the CheckBox is included in the [blue]RecordSource[/blue] of the form! It doesn't have to be on the form unless you intend to use it as the editing control.

3) Add the following code to the [blue]On Current[/blue] Event of the form:

[blue]   If Me.NewRecord Or Not Me!LockEdit Then
      Me.AllowEdits = True
   Else
      Me.AllowEdits = False
   End If[/blue]

4) Add the following code to the [blue]Code Module[/blue] of the form:

[blue]Public Sub SavRec()
   DoCmd.RunCommand acCmdSaveRecord
End Sub[/blue]

5) Add the following to a module in the module window:

[blue]Public Function tglEdit()
   
   If IsOpenFrm("[purple][b]MainFormName[/b][/purple]") Then
      Dim frm As Form
      
      Set frm = Forms![purple][b]MainFormName[/b][/purple]

      [green][b]'Add the following line only if controlling subForm:[/b][/green]
      Set frm = frm![purple][b]SubFormName[/b][/purple].Form
      
      If Not frm.NewRecord Then
         If frm!LockEdit Then
            frm.AllowEdits = True
            frm!LockEdit = False
            Call frm.SavRec
         Else
            frm!LockEdit = True
            Call frm.SavRec
            frm.AllowEdits = False
         End If
      End If
   End If
      
End Function

Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function
[/blue]

[blue]Using the CheckBox as Edit Control (checkbox is on form):[/blue]
If your going to use the CheckBox on form as the edit control, then in the [blue]AfterUpdate[/blue] Event of the CheckBox add the following code:

[blue]Call tglEdit[/blue]

[blue]Alternate Edit Control Options:[/blue]
You can use a Command Button, a key combination macro in AutoKeys, or call the routine [blue]tglEdit[/blue] from anywhere you like. I perfer the HotKey method.

Special Note: If you have [blue]Access 2000 or higher[/blue], [blue]Conditional Formatting[/blue] will provide a nice visual que of those records that are locked!

[blue]Cheers! . . . .[/blue]

See Ya! . . . . . .

 

[toffa]

I think i have done what you have advised me to do however when i open the form i can not remove the check box to edit the record in the form.
have you any ideas what i have done wrong.

Chris

 

[TheAceMan1]

toffa . . . . .

Yeah . . . your right. Once its locked ya can't turn it off.
So its either gonna be a Command Button or a HotKey . . . .

If Command Button just put [purple]Call tglEdit[/purple] in the [blue]On Click Event[/blue] in the VBE window (you may wanna do this just for test purposes).

If a HotKey, goto the Macro Window & open a new macro. It should look like the following (using [blue]Ctrl + L[/blue] as HotKey):

Macro Name   Action    Function Name
----------  ---------  -------------
^L          RunCode    tglEdit()
            StopMacro

Close the macro and name it [blue]AutoKeys[/blue]. Open the form and check it out . . . . .

See Ya! . . . . . .

 

[toffa]

i have gone with the command button and it works great top job
Chris

 

[toffa]

I have just found a problem with this i have a search box on the form that allows me to search records the lockedit is not allowing this function to work or maybe i have stuffed up. Anyway when i hit the command button to edit the record then try to search for a new record i can not search.
Would you be able to explain what i am doing wrong here

Chris

 

[toffa]

Change of thought on design, i have created a search form that seaches the records, how whould i open the record in form veiw from the previous form

ie i seach and find the record i want then i press a button or something and it opens the full record.

The search from only has 2 fields from the main table

the second form has all the relevent info

Chris

 

[toffa]

dont worry worked it out with command button toolbox wizard

 

[TheAceMan1]

toffa . . . .

Good too go . . . .

If you have [blue]Access 2K[/blue] or higher, have you tried [blue]Conditional Formatting[/blue] to visually tag the locked records (you could remove the checkbox from the form)?

You should try it! . . . . [blue]Really Cool![/blue]

See Ya! . . . . . .

 

[toffa]

i dont understand how this works with the whole form
Chris

 

[TheAceMan1]

toffa . . . . .

Just a quick question before I explain.

Is this a [blue]Continuous View[/blue] form?

See Ya! . . . . . .

 

[toffa]

no single form

 

[TheAceMan1]

OK toffa . . . . .

The explanation will be for a continuous form, although you'll easily realize the results for a Single Form.

In a [blue]Continuous Form[/blue], change the background color, foreground color, Font, ect, of a textbox and they all change in each record.

[blue]Conditional Formatting is a means of assigning individual control of a textbox for each record via some criteria. The best part is that no VBA is required.[/blue]

So . . . imagine a [blue]Continuous Form[/blue] where the textboxes of locked records have a [highlight]light yellow background[/highlight] or the text is in bold or both!

In [blue]Single View[/blue] you simply see the individual records. So as you as page thru them, you'll have a visual que of who's locked without have to specifically focus on the checkbox!

As a text of one field perform the following:

Special Note: The [blue]BackStyle[/blue] property of textboxes used, has to be set to [blue]Normal![/blue]

1) Open the form in [blue]design view[/blue].

2) Put the cursor in a textbox in the detail section.

3) Click Format - Conditional Formatting.

4) In the Combobox, select [blue]Expression Is[/blue].

5) To the right of it add [blue][LockEdit]=True[/blue]

6) In the same section select how you want the control to look if its locked, then click OK.

Open the form and page thru the records! When you satisfied with a format, do the rest of the textboxes the same.

Let me know if any problems . . . . .

See Ya! . . . . . .

 

[toffa]

works a treat you are great
While i have you i want a command button that creates a folder according to the ID of the main form.

Whats the code i would use for this.

Chris

 

[TheAceMan1]

toffa . . . . . .

Try this (you substitute the path in [purple]purple[/purple]):

[blue]   Dim Msg As String, Style As Integer
   Dim Title As String, FolderSpec As String, fs As Object
   
   Set fs = CreateObject("Scripting.FileSystemObject")
   FolderSpec = "[purple][b]C:\Windows\TheAceMan1[/b][/purple]"
   
   If fs.FolderExists(FolderSpec) Then
      Msg = "Folder '" & FolderSpec & "' already exist!"
      Style = vbInformation + vbOKOnly
      Title = "Folder Exist Notice! . . . ."
      MsgBox Msg, Style, Title
   Else
      fs.createfolder (FolderSpec)
   End If

   Set fs = Nothing[/blue]

See Ya! . . . . . .

 

[toffa]

made a slight change and added & ID this obviously sets up the the folder with id number and works great
Chris