dynamically set subform source object to xtab

spinkung

Registered User.
Local time
Today, 14:41
Joined
Dec 4, 2006
Messages
267
Hi all,

pulling my hair out with this one.

I want to set a subform recordsource to be a xtab, which you can only do if the xtab has fixed column property set. because i want to refresh the subform every time a new entry is made (therefore a new column) i'm trying to set a sql string to be the record source for my form. the query works fine if i debug and grab the sql output then test it in a query but the subform isn't updating.

here's what i have..
PART 1 - gets the last entered shift no and creatse the number of fixed colums required in a string
Code:
' get the last shift no in the s3 table
    Set rs = CurrentDb.OpenRecordset("select s3_shift from tbl_FLIR_S3 where fh_id = " & fh_id & " group by s3_shift")
    If Not (rs.EOF And rs.BOF) Then
        shInt = rs.RecordCount
    End If
' set variables
    i = 1
    colHead = ""
' Loop through the shifts to create a string that will set the fixed column headers in the xtab query
    For i = i To shInt
        colHead = colHead & """Check " & i & """, "
    Next
    colHead = Left(colHead, Len(colHead) - 2)


PART 2 - creates the xtab with the fixed column string
Code:
' create the xtab query
    sql = "TRANSFORM Max(IIf([tbl_FLIR_S3].[s3_status]=1,""P"",IIf([tbl_FLIR_S3].[s3_status]=2,""F"",""NA""))) AS s3_status " & _
          "SELECT tbl_LOOKUP.lk_description as [Line Check] " & _
          "FROM (tbl_FLIR_S3 LEFT JOIN tbl_LOOKUP ON tbl_FLIR_S3.lk_id = tbl_LOOKUP.lk_id) LEFT JOIN tbl_FLIR_HEADER ON tbl_FLIR_S3.fh_id = tbl_FLIR_HEADER.flir_id " & _
          "GROUP BY tbl_LOOKUP.lk_description, tbl_LOOKUP.lk_sequence " & _
          "ORDER BY tbl_LOOKUP.lk_sequence " & _
          "PIVOT ""Check "" & [tbl_FLIR_S3].[s3_shift_no] In (" & colHead & ")"
'return the query
    s3_history = sql
    Debug.Print s3_history

PART 3 - sets the record source of the subform
Code:
Me.mySFrm.Form.RecordSource = s3_history(Me.flir_id)
Me.mySFrm.Requery

can i actually set a subform to a crosstab where i'm dynamically trying to change the fixed columns or am i wasting life/effort??
 
Although you have added new fields to the recordsource you have not added the corresponding bound controls to display them. Even the datasheet on a form is a display of the controls, not the recordsource query.

Maybe just use the query as the recordsource of the subform. Unfortunately queries don't support any of the formatting, events and other features of a form but it could work if it is just for display.

Adding controls dynamically to a form can be done but has problems and is not usually a good idea.
 
thanks GalaxiomAtHome,

i'm now creating a temp table and using this as read only for my subform(s) source object.

it works perfectly, thanks.
 

Users who are viewing this thread

Back
Top Bottom