Interesting Problem (1 Viewer)

[Geordie2008]

Does anyone have any thoughts on how to do the following:

I currently have code that loops through all of the fields on my form and sets them to enabled or diabled.

I would prefer to hold this logic in a seperate table and loop through the table. All field names will be in the table and an "enabled" column will be set to either Y or N and the code will then set the logic dependent upon this.... (makes it easier to maintain than code as the user keeps adding in fields and the VBA maintanence is torturing me!)

Anyone got any database example that do anything similar....

I like the idea, but have no idea how to start!

Thank you.

 

[DCrake]

Simple Software Solutions

Here goes



Create Table TblFlags
Add Two Fields

fldName (Text)
fldFlag (YesNo)


Only add the field names for the matching items tyou want to change

Here is the Code

Public Function SetBooleanFlags(Criteria As String)
Dim RsFlags As DAO.Recordset
Dim RsDest As DAO.Recordset

Set RsFlags = CurrentDb.OpenRecordset("TblFlags")
Set RsDest = CurrentDb.OpenRecordset("Select * From YourTbl Where YourField=" & Criteria)

If Not RsDest.EOF And Not RsDest.BOF Then

  For I = 0 To RsDest.Fields.Count -1
      RsFlags.MoveFirst
      Do Until RsFlags.EOF
        

          If RsDest(I).Name = RsFlags("fldName") Then
              RsDest.Edit
              RsDest = RsFlags("FldFlag")
              RsDest.Update
          End If
          RsFlags.MoveNext
      Loop
  Next I

End If

Set RsFlags = Nothing
Set RsDest = Nothing

End Function

Summary:

Basically you have a table with the boolean type field names in it with the correct True/False value

Next you select the record in the source table whose values you want to update using the RsDest line

Then you loop through all the fields in the recordset and then loop though the records in the TblFlags file to find the matching field name, once found it edits the main table and updates it with the default value from the flags table.

The advantable of this is that you can add/remove fields form the main table and aslong as the changes are reflected in the flags tabel the code does not need any maintenance.

To use this function

Place the above code in a module.
On your form add a button or use the AfterUpdate Property and add the following code

Call SetBooleanFlags("???")

Where ??? is the criteria used to select the specific record in the table.

CodeMaster:

 

[Geordie2008]

Wow... thanks! I've just been landed with a heap of work, but will have a play with this hopefully beginning of next week and let you know how I get on.

Looks great thow, exactly what I was after doing.

Thank you,
Have a nice weekend.
Mand.

 

[DCrake]

Simple Software Solutions

Ok Mand

If you have any problems let me know. If it works feel free to update my reputation.

David