Hello,
I have a database to value real estate. Each property has a set of attributes, like type of walls, type of basement, etc and each attribute has a different cost.
So, if I want to extract the cost for a basement of type "235", I would use the following statement in the sql builder:
Code:
SELECT
PWCCOST.YEAR,
PWCCOST.grp, PWCCOST.code,
PWCCOST.desc, PWCCOST.csched_01
FROM PWCCOST
WHERE
PWCCOST.grp='BSMT_TYPE' AND
PWCCOST.YEAR=Year([Forms]![CompSearch]![AssessmenTDate]) AND
PWCCOST.code=[Forms]![CompSearch]![SubjectInfo].[Form]![bsmt_type];
csched_01 is the field where the cost is stored.
If I wanted to extract the same cost from a form, I would use the following vba code:
Code:
Dim RST As Recordset
Dim DBS As Database
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("SELECT PWCCOST.YEAR, PWCCOST.grp, PWCCOST.code, " _
& "PWCCOST.desc, PWCCOST.csched_01 FROM PWCCOST " _
& "WHERE( ((PWCCOST.grp)='HEATING') AND " _
& "((PWCCOST.YEAR)=" & year([Forms]![CompSearch]![AssessmenTDate]) _
& "AND (PWCCOST.code)='" _
& [Forms]![CompSearch]![SubjectInfo].[Form]![heating] & "'));")
If RST.RecordCount = 1 Then
Form_SubjectInfo.HeatCost = RST!csched_01
I use the recordcount property just in case I had duplicates.
The code used to work fine until a couple of days ago. The recordcount property returned 4 records each time from the form, but was working fine if i used the same sql statement in the sql builder.
I had not made any changes to the database neither have the users.
Has anyone ever experienced similar issues? Any ideas as tohow to fix this?
Any help would be appreciated.