On a previous version of my database I created a form with a button. On click Visual Basic would run a module.
Code:
Private Sub cmdTranspose_Click()
Module1.Transpose
End Sub
And the modulecode:
Code:
Sub Transpose()
' requires reference to Microsoft DAO
Dim rs As DAO.Recordset
Dim Counter As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [TableXYZ]")
With DoCmd
.SetWarnings False
For Counter = 5 To rs.Fields.Count
.RunSQL "INSERT INTO [PermanentTable] ([DATE],[USER],[XYZ],[APP], [ORRR], [GRADE]) " & _
"SELECT [DATE],[USER],[XYZ],[APP], '" & rs.Fields(Counter - 1).Name & "', " & _
"[" & rs.Fields(Counter - 1).Name & "] " & _
"FROM [TableXYZ]"
Next
.SetWarnings True
End With
rs.Close
Set rs = Nothing
MsgBox "Done"
End Sub
For some reason when i'm on the old version of the database this button is working.
On the new version when I click I don't get anything.
The code is exactly the same and I have no idea where to look for an explanation to this problem.
This is really problematic as I can no longer move the information from my tableXYZ to the PermanentTable which is I would say the base for every query/form/report i'm using.
I have no idea what change as this wasn't updated.
Any idea where to start looking for a culprit?
I have updated the code from the button to this:
Code:
Private Sub cmdTranspose_Click()
On Error GoTo Err_cmdTranspose_Click
Module1.Transpose
Exit_cmdTranspose_Click:
Exit Sub
Err_cmdTranspose_Click:
MsgBox Err.Description
Resume Exit_cmdTranspose_Click
End Sub
And it's returning the following error: "Too few parameters. Expected 2"
EDIT:
Just to add a little bit more information here the expected result
From TableXYZ:
Date User XYZ APP A1A A2A A3A A4A
05/12/2018 001 003 127462 5 2 1 5
04/12/2018 002 003 457215 3 1 2 5
What it looks like in Permanent Table:
Date User XYZ APP ORRR Grade
05/12/2018 001 003 127462 A1A 5
05/12/2018 001 003 127462 A2A 2
05/12/2018 001 003 127462 A3A 1
05/12/2018 001 003 127462 A4A 5
04/12/2018 002 003 457215 A1A 3
04/12/2018 002 003 457215 A2A 1
04/12/2018 002 003 457215 A3A 2
04/12/2018 002 003 457215 A4A 5
If anyone has a better idea on how to achieve this.
It needs to remain an "onclick" thing I don't want this to happend all the time as this slows down my database considerably.