Accessing the value in an underlying Query's field

[Ali Edwards]

I'm struggling with a bit of code here. What I'm trying to do is make an object in a form visible if a certain value in the underlying query exists. So I have the query called "Spare Keys" and the form based on that query. The only field in the "Spare Keys" query is called "Spare Key". So if there is a 'Spare Key' value of "001" in the query I want the "Image1" to be visible in the form. If not then "Image1" should not be visible. Then if there is a value for "002" in the query "Image2" should be visible and so on.

This is what I have done for "Image1" but it doesn't work:

Private Sub Form_Current()
Dim stdocname As String
Dim Stlinkcriteria As String
stdocname = "Spare Keys"


If [Spare Key] = "001" Then
Image1.Visible = True

Else
Image1.Visible = False
End If


End Sub

Can anyone tell me what I'm doing wrong?

Many thanks

 

[Fizzio]

How I have done it is to make an non-visible control on the form of the value I want to interrogate. In yours, make its control source the [Spare Keys] field. In the form, set both image1 and image2 to non visible by default. Then on Form_Current

If Me("Spare Keys") = "001" then
Image1.Visible = True
ElseIf Me("Spare Keys") = "002" then
Image2.Visible = True
etc..
End If

If you have a lot of images, you could rename your images to Image001 etc and try

Private Sub Form_Current()
Dim ImageName as String
ImageName = "Image" & Me("Spare Keys")
Me(ImageName).Visible = True
End Sub

I think this will work (Dont have access to Access at the minute)

 

[Ali Edwards]

Thank you for your reply. Unfortunately it doesn't work as the form shows only one record at a time. If I scroll through the records the image becomes visible when the record "001" does but then the image stays visible.

Is there a way of having a form show all the records? To be more specific in what I'm trying to achieve, I want a form showing numbers 001 to 200 (as labels). Under each number I want an image or box or anything that becomes visible if there is a record for the corresponding number in the underlying query. If I could have a control show all the values of the 'Spare Key' field then I think it would be easy to get the code to check that control for a value and make the image visible.

I have tried using 'Continuous forms' and have also tried to do this with a report.

Any way, thanks again.