checking for records need a better way

[shaun franken]

Hello,
I am currently trying to think of a better way to do something for performance reasons. What happens is I have a main form that my users are on alot, which has buttons that go to 9 other forms. For every record they have to fill out 3-4 of those other forms depending on the request. They tend to get interrupted and lose place with all the phone calls coming in so I made some code to turn the buttons blue when it has been filled out. The code I use this is posted below. The only problem is this seems to be a performance issue due to using a domain function. Any ideas on a better way to do this? I am using Access 2007 frontend with linked tables and a SQL Server 2005 backend.
Thanks,
Shaun

If IsNull(Me.idCase) Then
Me.Command47.ForeColor = vbBlack
Me.Command53.ForeColor = vbBlack
Me.Command55.ForeColor = vbBlack
Me.Command56.ForeColor = vbBlack
Me.Command57.ForeColor = vbBlack
Me.Command58.ForeColor = vbBlack
Me.Command59.ForeColor = vbBlack
Me.Command60.ForeColor = vbBlack
Else
If ECount("*", "dbo_Family_Info", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command47.ForeColor = vbBlue
Else
Me.Command47.ForeColor = vbBlack
End If
If ECount("*", "dbo_Medical_Info", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command53.ForeColor = vbBlue
Else
Me.Command53.ForeColor = vbBlack
End If
If ECount("*", "dbo_Clin_Services", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command55.ForeColor = vbBlue
Else
Me.Command55.ForeColor = vbBlack
End If
If ECount("*", "dbo_Clin_Medication", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command56.ForeColor = vbBlue
Else
Me.Command56.ForeColor = vbBlack
End If
If ECount("*", "dbo_Equipment", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command57.ForeColor = vbBlue
Else
Me.Command57.ForeColor = vbBlack
End If
If ECount("*", "dbo_Displacement_Travel", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command58.ForeColor = vbBlue
Else
Me.Command58.ForeColor = vbBlack
End If
If ECount("*", "dbo_Displacement_Food", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command59.ForeColor = vbBlue
Else
Me.Command59.ForeColor = vbBlack
End If
If ECount("*", "dbo_Displacement_Lodging", "idCase = " & Forms!CaseData!idCase) >= 1 Then
Me.Command60.ForeColor = vbBlue
Else
Me.Command60.ForeColor = vbBlack
End If
End If

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Dorian]

You should be able to keep track of the forms filled out in global variables
which you reset each time you navigate to a new record.
It's not advisable to be reading all those tables each time, but if you
must, at least make sure the column being checked is indexed.
Also, why do a count which requires reading every row. Dont you only want to
know if there is a single row?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[shaun franken]

Correct I only want to know if something exists. I did a count cause I am still learning and that was the way I came up with. I think we switch between records to much for the global vars but maybe I am not understanding you right.



Dorian wrote:

RE: checking for records need a better way
30-Sep-09

You should be able to keep track of the forms filled out in global variables
which you reset each time you navigate to a new record
It's not advisable to be reading all those tables each time, but if you
must, at least make sure the column being checked is indexed
Also, why do a count which requires reading every row. Dont you only want to
know if there is a single row?
-- Doria
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime"

:

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx