Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I have included a simple illustration of my problem in an excel spreadsheet that simulates the how I need the data to work in an access table.

    In the spreadsheet you will see that I have a CDU (Store) and equipment that is being moved between the stores. The equipment ID identifies the equipment which CANNOT be in two places at once. (This is the design problem I am facing).

    As people enter data, it is important that the equipment is FIRST removed from the current store in order to be installed in another store. But people entering the records are forgetting to first enter the remove date before they enter a new record indicating where the equipment is moving to. So now I end up with 1 piece of equipment in two different stores.

    How can I modify the design of the table (or use functions etc) to prevent this problem. It is important that a record cannot be added if the equipment is not "removed" first by entering a removed date.

    Hope my problem is clear. Any help or advice will be appreciated. TX

    [attachment=84430:EquipTracking.xls]
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you need to keep a history? If not, simply create a unique index on the EquipID field and don't bother with the Removed field. Users can simply edit the CDU field to move a piece of equipment from one store to another.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans,

    Yes. A history is necessary for tracking the stores who used the equipment. TX
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could perform a check in the Before Update event of the form, and cancel if the item is in use:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim varCDU As Variant
      If IsNull(Me.Removed) Then
    	varCDU = DLookup("CDU", "tblData", "EquipID=" & Me.EquipID & _
    	  " AND Removed Is Null")
    	If Not IsNull(varCDU) Then
    	  MsgBox "This piece of equipment is in use in store " & varCDU, _
    		vbExclamation
    	  Cancel = True
    	End If
      End If
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Hans,

    The DB in question does not have a form. thats no problem...I'll create one. However... once I add the code to the Before_Update event, do I simply create a new record and assign the equipment...and the code will pop up the msgbox if it sees the equipment I enter does not have a removed date?

    Does the DLookup check the last record of the equipment in question?

    TX.

    TX for your assistance.
    Cheers
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Access has only limited validation at the table level. Anyway, as you are well aware of course, end users shouldn't enter data directly in a table or query, only in a form.

    I see that I omitted an essential bit of code. You need to have a unique identifier in the table, preferably an AutoNumber field. Let's say it is called ID. The code should only check records with a different ID than that of the current record:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim varCDU As Variant
      If IsNull(Me.Removed) Then
    	varCDU = DLookup("CDU", "tblData", "EquipID=" & Me.EquipID & _
    	  " AND Removed Is Null AND ID<>" & Me.ID)
    	If Not IsNull(varCDU) Then
    	  MsgBox "This piece of equipment is in use in store " & varCDU, _
    		vbExclamation
    	  Cancel = True
    	End If
      End If
    End Sub
    The code will run whenever the user edits a record, whether it is a new one or an existing one.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tx Hans. I will give the code a try. Cheers
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hans,

    I have created a sample DB with a simple form. The code you gave runs on the Before_Update event, but produces a strange error. Could you check to see if I have set it up correctly.

    Many thanks.

    [attachment=84442:Test.zip]
    Attached Files Attached Files
    Regards,
    Rudi

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Rudi' post='781834' date='26-Jun-2009 17:11']Hans,

    I have created a sample DB with a simple form. The code you gave runs on the Before_Update event, but produces a strange error. Could you check to see if I have set it up correctly.

    Many thanks.

    [attachment=84442:Test.zip][/quote]
    There is no field in test called CDU which you use in your DLookup.

    I dont think there is a need to check if the ID <> Me.ID as ID is an autonumber.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tx Patt,

    Your observation helped me to fix the error.

    I changed Hans's code to:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim varCDU As Variant
    If IsNull(Me.Removed) Then
    varCDU = DLookup("ID", "Test", "EquipID=" & Me.EquipID & _
    " AND Removed Is Null AND ID<>" & Me.ID)
    If Not IsNull(varCDU) Then
    MsgBox "This piece of equipment is in use in store " & varCDU, _
    vbExclamation
    Cancel = True
    End If
    End If
    End Sub


    It is working great now.

    Big Cheers
    Regards,
    Rudi

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Rudi' post='781856' date='26-Jun-2009 20:49']Tx Patt,

    Your observation helped me to fix the error.

    I changed Hans's code to:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim varCDU As Variant
    If IsNull(Me.Removed) Then
    varCDU = DLookup("ID", "Test", "EquipID=" & Me.EquipID & _
    " AND Removed Is Null AND ID<>" & Me.ID)
    If Not IsNull(varCDU) Then
    MsgBox "This piece of equipment is in use in store " & varCDU, _
    vbExclamation
    Cancel = True
    End If
    End If
    End Sub


    It is working great now.

    Big Cheers[/quote]
    That's fine, i was wrong about the ID exclusion, it needs to be there else the dlookup will find the record about to be written, duh.

Posting Permissions

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