Populate textboxes on a report from a query

[dccjr]

I am trying to use the recordset from a query to populate textboxes on a report. I have came up with this so far, but I think I am missing something. I was not sure how to reference the form to be used in the With statement. Suggestions?

Private Sub PopForm()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strEventSQL)
DoCmd.RunSQL strEventSQL
    With Form!EventReportExisting
        rtxAreaCell = rs.AreaCell
        rtxEmployee = rs.Employee
        rtxDefectDate = rs.DefectDate
        rtxPartNo = rs.PartNo
        rtxDescription = rs.Description
        rtxWONo = rs.WONo
        rtxQNNo = rs.QualityNo
        rtxOtherID = rs.OtherID
        rtxDisposition = rs.Disposition
        rtxNotes = rs.Notes
        rtxImmediateAction = rs.ImediateAction
        rtxContainment = rs.Containment
        rtxWhy1 = rs.Why1
        rtxWhy2 = rs.Why2
        rtxWhy3 = rs.Why3
        rtxWhy4 = rs.Why4
        rtxWhy5 = rs.Why5
        rtxWhyAdditional = rs.WhyAdditional
        rtxRootCause = rs.RootCause
        rtxActionPlan = rs.ActionPlan
        rtxActionTaken = rs.ActionTaken
        rtxAssignedTo = rs.AssignedTo
        rtxDueDate = rs.DueDate
        rtxStatus = rs.Status
Set rs = Nothing
Set db = Nothing
End Sub

Thanks.

[Beetle]

It's a little unclear what you're attempting to do here. You're opening a recordset based on what we assume to be a SQL string (not included in the posted code), but then you're invoking DoComd.RunSQL, which is for action queries (Append, Delete or Update queries), so this part doesn't really make sense. Plus, I wonder why your'e attempting to write values in code here instead of just binding the form (or report - that's also not clear because you said report in your description but your code is attempting to reference a form) directly to the record source.

[dccjr]

Apologies. I have canged the DoCmd to CurrentDb.Execute. The SQL string is built in another Sub based on selections made from several combo boxes. I do not actually execute the SQL in the Sub where the string is defined. The Form! reference was a blind attempt. I meant to remove it before posting the code. I am doing this in code because the SQL changes based on the selection in the combo boxes. It is basically the same query, but based on a different criteria (one is based on work order number, the other on quality report number). I am also not inserting all of the fields from the query into the report, just certain ones. Sorry for being vague and sloppy with this initial post.

So, Any ideas? Am I even on the right track?

[Beetle]

I have canged the DoCmd to CurrentDb.Execute

That's just two sides of the same coin. The difference between DoCmd.RunSQL and CurrentDb.Execute is that with RunSQL the query will be evaluated by the Access expression service before being passed to the database engine (Ace/Jet), while with CurrentDb.Execute it will be passed directly to Ace/Jet. I prefer the .Execute method because you don't have to code around the Access warning messages. However, they are both for action queries and do not work with select queries, which is what it sounds like you're working with.

If you're going to use an unbound form you could do something like the following. This assumes you're running this from within the form while it is open.

Dim strSQL As String

strSQL = "Select Field1, Field2, Field3 From SomeTable Where X=Y"

With CurrentDb.OpenRecordset(strSQL)
    Me!TextBox1 = !Field1
    Me!TextBox2 = !Field2
    Me!TextBox3 = !Field3
End With

Or, if you have a saved query that is used as the record source of a form, you can rewrite the sql with QueryDefs;

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = CurrentDb.QueryDefs("MySavedQuery")
strSQL = "Select Field1, Field2, Field3 From SomeTable Where X=Y"

qdf.SQL = strSQL

Or, if the selected fields for the record source is not going to change, just the criteria, then you can just apply a filter to the form based on certain criteria;

Me.Filter = "SomeField = SomeValue"
Me.FilterOn = True