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".

 

[PieterLinden via AccessMonster.com]

Shaun,
I would probably write a Function in SQL Server and then send the arguments
to it, because then you can use something like

CREATE FUNCTION RecordsExist (@pCaseID int)
RETURNS Bit

BEGIN
(
EXISTS (SELECT * FROM Family_Info WHERE idCase = pIDCase) AND
EXISTS (SELECT * FROM Medical_Info WHERE idCase = pIDCase) AND
... <etc... (just change the table name in the select statement)
)';

(Shame on me, I should be better at SQL Server than this...)

but the basic idea is to have everything run on the server. You pass in the
item you are looking for (pIDCase) and you get an answer back (true/false),
and you make *one* trip to the server, not like 10. so that should speed
things up.

 

[shaun franken]

So make this a stored proc on the SQL server side? I think I would have to add a second parameter of table name to that though. I need to know the status of record existence on each table seperatly not as a whole. So therefore I would still be making the 8 trips to the DB. But this might be quicker than the ECount function.
Thanks for the info. I will have to do some googling on using stored procs and running from access vb.



PieterLinden via AccessMonster.com wrote:

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

Shaun
I would probably write a Function in SQL Server and then send the argument
to it, because then you can use something lik

CREATE FUNCTION RecordsExist (@pCaseID int)
RETURNS Bi

BEGI

EXISTS (SELECT * FROM Family_Info WHERE idCase = pIDCase) AN
EXISTS (SELECT * FROM Medical_Info WHERE idCase = pIDCase) AN
... <etc... (just change the table name in the select statement
)'

(Shame on me, I should be better at SQL Server than this...

but the basic idea is to have everything run on the server. You pass in th
item you are looking for (pIDCase) and you get an answer back (true/false)
and you make *one* trip to the server, not like 10. so that should spee
things up

shaun franken wrote

--


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

 

[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

 

[shaun franken]

Isn't "Exists" only usable in subquerys? I gave it a go and am getting an error on the exists portion.

CREATE FUNCTION RecordsExist
(
-- Add the parameters for the function here
@pcaseID int
)
RETURNS Bit
AS
BEGIN

DECLARE @return bit

SELECT @return = EXISTS(Select * FROM Family_Info WHERE idCase = pcaseID)

RETURN @return

END
GO



PieterLinden via AccessMonster.com wrote:

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

Shaun,
I would probably write a Function in SQL Server and then send the arguments
to it, because then you can use something like

CREATE FUNCTION RecordsExist (@pCaseID int)
RETURNS Bit

BEGIN
(
EXISTS (SELECT * FROM Family_Info WHERE idCase = pIDCase) AND
EXISTS (SELECT * FROM Medical_Info WHERE idCase = pIDCase) AND
... <etc... (just change the table name in the select statement)
)';

(Shame on me, I should be better at SQL Server than this...)

but the basic idea is to have everything run on the server. You pass in the
item you are looking for (pIDCase) and you get an answer back (true/false),
and you make *one* trip to the server, not like 10. so that should speed
things up.


shaun franken wrote:

--


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