Module to Find Records in a Table

[GarethW]

Hi Guys,

I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.

The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.

My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.

Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-

Function CopyTest()
On Error GoTo CopyTest_Err

Dim strMeterRef As String

DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNext
strMeterRef = Meter_Reference
DoCmd.Close acForm, "Faults"
DoCmd.OpenForm "Faults", acNormal, "", "", acNormal
Cells.Find(What:=strMeterRef, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
DoCmd.SetWarnings True


CopyTest_Exit:
Exit Function
CopyTest_Err:
MsgBox Error$
Resume CopyTest_Exit
End Function
----------------------------------------------------------------------

As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.

I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.

I'd really appreciate some help getting this to work.

Cheers
Gareth

 

[plog]

I stopped reading after your 2nd paragraph, because I don't think this is the way to go about tracking your data. Generally, action queries (UPDATE, Make Table) and relying on macros/code to maintain your data correctly are a sign of an improper design structure. Moving data hither and yon is also a another big sign.

Meter Status should not be a field in your Meter table, its actually a field you can calculate, so you shouldn't store it, you should calculated it. Further, you shouldn't have a Closed Faults table, you should have a field in your regular Faults table that designates if a fault has been closed (which you say you have).

So, I would stop moving data to the Closed Faults table and just use the presence of a value in the Fault Closed field in your regular Faults table to see if a Meter is working or not. I'd create a query of all meters with a blank Fault Closed date (call this 'OpenFaults'), then I would create another query based on your Meters table and that OpenFaults query to determine the status of all meters--if there's a match between the two data sources the meter is Not Working, if no match its Working.

Now you won't have to mess with reading record sets or moving data. Just reference that query and you will know.

 

[GarethW]

Plog,

Thanks for the reply. Sorry for the delay in replying but I've been on holiday.

While you are correct that your method would be simplest and will probably be the one I go with, I would still like to know how to get the macro to jump to the right record as I would like to get back into some coding.

Thanks,

Gareth