Code needs to be in a general module, not behind report.
Report would NOT use multi-Columns settings.
SQL is missing
FROM keyword.
There is no TableTEMP. Might want to use name GreyTEMP. If this will be a multi-user split db, GreyTEMP must be in frontend. Some developers prefer to create and delete table each time procedure runs instead of just deleting records.
Probably need to include OfferID in GreyTEMP records so can join to Grey_Offer instead of using Grey_Shade table. Or use GreyTEMP in a linked subreport since you need Grey_Shade data for Sum() calc.
Data shows only one Offer record but I assume there can be more. This will require adjustment in code to make sure correct OfferID is saved with record. Now that I have some realistic data, I find other issues with suggested code.
Label caption
Shade & Meters needs to be
Shade && Meters or use
and instead of
&.
Might need to fix report width.
What is this db for? Selvage is a term for fabric edge.
Revised code:
Code:
Sub DataToFourColumns()
Dim db As DAO.Database, rs As DAO.Recordset
Dim x As Integer, r As Integer
Dim s1 As String, s2 As String, s3 As String, s4 As String, intOID As Integer, strSM As String
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE")
r = 1
CurrentDb.Execute "DELETE FROM GreyTEMP"
Do While Not rs.EOF
intOID = rs!offerid
For x = 1 To 4
If Not rs.EOF Then
strSM = rs!SHADE & vbCrLf & rs!METER
If intOID = rs!offerid Then
If x = 1 Then s1 = strSM
If x = 2 Then s2 = strSM
If x = 3 Then s3 = strSM
If x = 4 Then s4 = strSM
rs.MoveNext
End If
End If
Next
db.Execute "INSERT INTO GreyTEMP(RowNum, OfferID, Label, Col1, Col2, Col3, Col4) " & _
"VALUES(" & r & "," & intOID & ",'Shade' & Chr(13) & Chr(10) & 'Meter','" & s1 & "','" & s2 & "','" & s3 & "','" & s4 & "')"
r = r + 1
s1 = ""
s2 = ""
s3 = ""
s4 = ""
Loop
End Sub