Select statement filter

[Dan25e]

Hi peeps,

I have come up with this code...

Private Sub Attachment_s__AfterUpdate()

Dim AttList As String
Dim db As Database
Dim rs As Recordset
Dim ORID As Integer


ORID = [Forms]![IR Config]![OR IR No]

Set db = CurrentDb

Set rs = db.OpenRecordset("Select [OR IR No], " & "[Attachments.FileName] From qryAtts ;", dbOpenDynaset)

If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
AttList = AttList & ![Attachments.FileName] & " "
.MoveNext
Loop
End With
End If
Set rs = Nothing
Set db = Nothing



Me.OR_Query = Null
Me.OR_Query = AttList

End Sub

It works.... To a point..!

The end result it populating the field OR_Query on the main form with every attachment from every record in the main database table. What I've been trying to do for the last four hours is work out a way to filter out all but the attachments against a particular record or OR_IR_No. I've tried using WHERE in the Select statement but can't quite get the syntax right.

Can anyone help?

 

[Minty]

I would always build your SQL string separately - it makes debugging much simpler. Air code untested ...

Dim sSql as String
Dim iRecNo as Integer

iRecNo = 12345  ' put your record number here... or form control reference 

sSql = "Select [OR IR No], [Attachments.FileName] From qryAtts Where [OR IR No] = " & iRecNo & " ;"

debug.print sSql
Set rs = db.OpenRecordset(sSql, dbSeeChanges)

 

[Dan25e]

Thanks Minty.

I amended...

Set rs = db.OpenRecordset("Select [OR IR No], " & "[Attachments.FileName] From qryAtts Where [OR IR No] = " & ORID & " ;", dbOpenDynaset)

in my original code and it worked first time. That's some useful Air Coding you did there! Thanks again.