Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Cincinnati, Ober, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lock a record in a table (97/2000)

    Hello,

    We have two tables containing project information - one related to the engineering time spent on a project, the other relating to the installation time spent on the project. Each is updated to reflect the activity of whichever department is working on the project.

    The project code in each table is drawn from a third table, to which we have added a status (Completed Yes/No) field. We want the 1st two tables to lock the row(s) related to a project when the status of that project is marked Completed Yes.

    Is this a VB oriented solution, or is there another solution that can be applied?

    Thanks in advance!

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock a record in a table (97/2000)

    You can't do this at the table level in Access, but you can "lock/unlock" records in a form. You should only let end users view/edit records in a form anyway, never directly in a table or query.

    The way to do it in a form would be to write code in the After Update event of the control bound to the Completed field, and in the On Current event of the form.

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Cincinnati, Ober, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock a record in a table (97/2000)

    Hans,

    Of course, I did mean to say form, rather than table - it's been a long week and it's only Wednesday!

    Would anyone have some sample VB code around that might accomplish this?

    A programmer, I'm not<g>.

    Regards,

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock a record in a table (97/2000)

    Do you have stand-alone forms for the two tables, or do you always edit them in subforms of a form bound to the third table?

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    Cincinnati, Ober, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock a record in a table (97/2000)

    Hans,

    Each table has a stand alone form.

    Mike

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock a record in a table (97/2000)

    So the project status is not part of the record source of the form. Try something like this in the On Current event of the forms. You must substitute the appropriate table and field names.

    Private Sub Form_Current()
    Me.AllowEdits = Not DLookup("Completed", "tblProjects", "[ProjectID] = " & Me.[ProjectID])
    End Sub

    tblProjects is the name of the third table, containing the Completed field.
    ProjectID is the name of the field identifying the project, and linking the third table to the first and second tables. I have assumed here that it is a number field; if it is a text field, change the Where-condition part of DLookup to

    "[ProjectID] = " & Chr(34) & Me.[ProjectID] & Chr(34)

    Chr(34) is the code equivalent of double quotes.

Posting Permissions

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