Setting Query as RecordSource

[londonkiwi]

Any advice appreciated.

Having problems getting image to load in an unbound object frame, based on selction made in combo box.

Tried this code in Combo UafterUpdate event:
Private Sub cboBridgeID_AfterUpdate()

Dim rs As DAO.Recordset
Dim db As DAO.Recordset
Dim strSQL As String

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[BridgeID] = " & Me![cboBridgeID]
Me.Bookmark = Me.RecordsetClone.Bookmark

Set db = CurrentDb
' have access load and run the qry
strSQL = "Select * from qryBridgePhotos where qry.ID=" & Me!cboBridgeID & ";"

Set rs = db.OpenRecordset(strSQL)
If rs.BOF And rs.EOF Then ' nothing to do
Else
'On Error Resume Next
Me![ImageFrame1].Picture = rs.Fields("9798_pht1&quot
End If

End Sub

Get type mismatch error(13). Look forward to your comments.

 

[hsitz]

Is your [BridgeID] a text or number field? If text, then you'll need to get one line in form of "[BridgeID] = '" & Me![cboBridgeID] & "'", with single quotes around the bridgeid number.

Do you not need to create a new recordset and assign the recordset clone to it?:

dim rsclone as dao.recordset
set rsclone = me.recordsetclone

I've never seen it done the way you're doing it, which is slick if it works and isn't part of what's causing the problem. -- Herb

 

[JPeters]

From your title, it sounds like you want the query to be the recordset. I didn't really understand the question, but this is what I do when I want to accomplish the question in your title.

On Error GoTo Errhandler
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("qrysendtest&quot

qrysendtest is the query name. It works find for me. Hope that this helps you a bit. If not, hope I didn't waste your time.

-Josh -Happen609
Wasting more of your valuable time...

 

[londonkiwi]

Thanks for the advice. BridgeID is numeric (double).

Still getting the error msg. cboBridgeID has a row source type of Table/Query, with the following Row SQL

SELECT DISTINCT qryBridgePhotos.ID, qryBridgePhotos.BridgeID, qryBridgePhotos.RoadNo, qryBridgePhotos.Name FROM qryBridgePhotos

Could this be causing a conflict??

cheers

 

[londonkiwi]

Oops, just got your msg happen609 after aI posted mine.

I any trying to have access load and run the qry (qryBridgePhotos) after the users selected the bridge they want to look at from the combo box. eg. I'm trying to make sure that the qry referenced is loaded as the roecordsource of the form. An imges will be loaded into ImageFrame1, with its source a field (holding the image path) in qryBridgePhotos

Will try your advice. Anything else to add???

cheers

 

[londonkiwi]

On stepping through the code it fails at Set db = Currentdb() - withe the result that it = nothing. Weird - what did i miss here??.

 

[JPeters]

If you're getting errors with the CurrentDb, you can just do this instead and not set the database.

Dim rst As Recordset
Set rst = Me.RecordsetClone

This takes the form's source table data and makes it the recordset data (I think, correct me if I'm wrong anyone).

So what you're trying to do is have a combo box that the user can select a type of picture or a picture, and it loads up the picture in the Object box based on the selection made in the combo box? I'm working on something similar to this right now if you read my posts. I'm still a bit confused also. I'll keep looking around and see what I can find, also feel free to correct me if I am wrong about what you are trying to do with the code - and hsitz is a MUCH better programmer than me, so I would try his advice first. Wish I could do more to help.

-Josh -Happen609
Wasting more of your valuable time...

 

[londonkiwi]

Josh - I'm also trying for hlp in the

http://www.vbforums.com

/ databases area. You could post there??.

Please let me know how you go (hugh.manson@sdc.govt.nz) and I'll do the same if you wish

cheers