Design problem…advice needed…pleeeaasse

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Hi Hans,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Tx Hans. I will give the code a try. Cheers

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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

Reply | Quote

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

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.

Reply | Quote