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
PART 2 - creates the xtab with the fixed column string
PART 3 - sets the record source of the subform
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??
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??