Query Definition & Case Select Problem

[Carol57]

I have the following code:

Private Sub Employee_AfterUpdate()

ctYear1.ClearDays

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim Image As String


Set db = CurrentDb
Set qdf = db.QueryDefs("EmployeeAbsenceYear&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm


Set rst = qdf.OpenRecordset(dbOpenDynaset).Clone

If Not ErrorCondition Then
m_bFoundFile = True
Else
m_bFoundFile = False
End If


If m_bFoundFile Then
If rst.RecordCount > 0 Then
rst.MoveFirst

Do While Not rst.EOF
HoldDate = rst!Date


rst.MoveNext
Loop
End If
End If

DBErrorHandler:
'MsgBox " Database not Found!"
ErrorCondition = True


Select Case Image
Case 1
Me.ctYear1.DateBackColor = RGB(0, 0, 160)
Case 2
Me.ctYear1.DateBackColor = RGB(121, 121, 255)
Case 3
Me.ctYear1.DateBackColor = RGB(185, 185, 255)
Case 4
Me.ctYear1.DateBackColor = RGB(0, 0, 160)

Case Else
Me.ctYear1.SelectBackColor = RGB(0, 0, 0)

End Select

End Sub

-------------------

Problem is that I don't think that my Case Select Statement is working. I do not receive any error messages, but only the Case Else line changes.

Basically, I have this query, and in this query is a number field called "Image" For each image, I want to assign it a number and this number then changes the background colour.

Thank you

 

[Isadore]

Carol: This may not help, just an idea (and I would imagine the code wouldn't compile otherwise so you are probably ok here), but, just in case you might replace:

Case 1

..with

Case Is = 1

...again, just a thought. Otherwise, the problem is probably with the CASE ELSE statement (just a guess).

 

[Steve101]

Try replacing your line:

Select Case Image

with:

Select Case rst!Image

That way, you will be referencing the image value from the current record of the rst recordset, instead of a declared but non initialised string called Image.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Carol57]

Thanks Steve 101 - your suggestion worked. Now the only problem is that it is only populating the form with the last record. I wanted all the records to populate. Maybe I don't have my loop set up correctly.

Here is my code:

Private Sub Employee_AfterUpdate()

ctYear1.ClearDays

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim Image As Integer


Set db = CurrentDb
Set qdf = db.QueryDefs("EmployeeAbsenceYear&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm


Set rst = qdf.OpenRecordset(dbOpenDynaset).Clone

If Not ErrorCondition Then
mbFoundFile = True
Else
mbFoundFile = False
End If


If mbFoundFile Then
If rst.RecordCount > 0 Then
rst.MoveFirst

Do While Not rst.EOF
ctYear1.Date = rst!Date
Select Case rst!Image
Case 1
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 2
Me.ctYear1.SelectBackColor = RGB(121, 121, 255)
Case 3
Me.ctYear1.SelectBackColor = RGB(185, 185, 255)
Case 4
Me.ctYear1.SelectBackColor = RGB(64, 128, 128)
Case 5
Me.ctYear1.SelectBackColor = RGB(106, 181, 181)
Case 6
Me.ctYear1.SelectBackColor = RGB(171, 214, 214)
Me.ctYear1.SelectForeColor = RGB(0, 0, 0)
Case 7
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 8
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 9
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 10
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 11
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 12
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 13
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case 14
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case "15"
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case "16"
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case "17"
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case "18"
Me.ctYear1.SelectBackColor = RGB(0, 0, 160)
Case Else
Me.ctYear1.SelectBackColor = RGB(0, 0, 0)

End Select

rst.MoveNext
Loop
End If
End If

DBErrorHandler:
'MsgBox " Database not Found!"
ErrorCondition = True




End Sub

There could be records in each Case Select statement that I want shown. Any suggestions.

 

[Steve101]

Carol,

Reading between the lines, I think that there may be something wrong with the recordsource property associated with the form.

IF the recordsource (table or query) associated with the form includes the Image field, then you should'nt need all of the above querydef / recordset processing. You should be able to dynamically change the background as you change from record to record, using the OnCurrent event.

I think you want to rethink the method that you're using to try to achieve the result.

Keep me posted,

Rgds
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)