Verifying Records

[Adrianna]

Yeah...I always have questions as deadlines approach!

I have an archive form that allows the manager to archive records and recover them in the future. At the moment, the records are archive to an archive table within the same database that the current records are held in. I was going to just mark them ARCHIVED so that they would now show in current data calls, but I figured that moving them would be just as easy.

So the user enters a record number and the department name. The department name comes from a drop down list if all of the departments in the system. The record number is an unbound text box (there would be to may to list).

Then the user can select either the Archive or Recover command button. The problem is that not all of the users remember the record number completely and I want to be able to check the [RecNo2Process] against the [RecNo] in both tables. Possibly using something like :

If ([RecNo2Process] = [Tables].[MasterSystemsTbl].[RecNo]) Then
MsgBox "TheRecord Number you entered is a current system. Press the Archive button to archive the data for this system."
Else
If ([RecNo2Process] = [Tables].[MasterSysArchTbl].[RecNo]) Then
MsgBox "TheRecord Number you entered is an archived system. Press the Recovery button to recover the system data."
Else
MsgBox "TheRecord Number that you have entered is not a validRecord Number, or does not belong to the Department that you've selected."
End If
End If


I need to be able to check both tables from the same unbound text boxes to prevent the users from getting confused when they successfully archive or recover 0 records.

 

[RV]

Just an idea..

you could use the DCount function in an On Click event (pushing of the button).

There are a lot of posts about DCount, it's described rather good in Access Help.

Come back if you need any (more) help.

RV

 

[Adrianna]

RV,
I'm not trying to count anything. I'm trying to verify that the unbound field entry exits in one of the two tables.

So, I want to first look up the entry in the [MasterSystemsTbl].

If the field matches then I provide a message box telling them to proceed.

Else, I want to check to see if the record is in the [MasterSysArchTbl].

If the field matches, then I want to provide a message box telling them to proceed.

Else, I need to provide them with an error message so that they will try again.


The problem is a runtime error 2465 which says the it can't find the reference '|'. The corde is below. I'll recheck my statement to make sure that I didn't over look something, but until then...I need advice. If no one has any suggestions, I will just rearrange the form. I know another way of doing it, but I wanted to try this first.

 

[RV]

> I'm not trying to count anything. I'm trying to verify that the unbound field entry exits in one of the two tables. <

That's exactly what you can use the DCount function for....
You can use it to check whether an entered RecordNo does exist in your underlaying table / query.

If you're not familiair with DCount, just ask around.

RV