Private Sub CrossTab_Click2()
Set MyRecset = New ADODB.Recordset
If MyRecset.State = 1 Then
MyRecset.Close
Set MyRecset = Nothing
End If
With MyRecset
.ActiveConnection = MyCon
.CursorLocation = adUseServer
.LockType = adLockPessimistic
.CursorType = adOpenStatic
.Source = "select * from tblForm48C"
.Open
End With
MyCon.CursorLocation = adUseClient
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = MyCon
cmd.CommandText = "TRANSFORM " _
& "Format$(Min([fldTime]),'h:Nn:Ss AM/PM') AS [Time] " _
& "SELECT " _
& "CStr([fldMachineID]) AS [MachineID]," _
& "Format$([fldDate],'mmmm, yyyy') AS [Month/Year]," _
& "Format$([fldDate],'d') AS [D]," _
& "Format$([fldDate],'dddd') AS [DD] " _
& "FROM [tblForm48B] " _
& "GROUP BY [fldMachineID], [fldDate] " _
& "ORDER BY [fldMachineID] ASC, [fldDate] ASC " _
& "PIVOT [fldRem]"
cmd.CommandType = adCmdText
Set MyRecSetF48 = cmd.Execute
With FlexReport
Set .DataSource = MyRecSetF48
MyRecSetF48.Close
For I = 0 To .Cols - 1
If I = 2 Then
.ColWidth(I) = 300
Else
.ColWidth(I) = 1200
End If
Next
End With
'Start Saving CrossTab data to DB table.
SaveCrossTab
End Sub
My SaveCrossTab
Code:
Set MyRecset = New ADODB.Recordset
If MyRecset.State = 1 Then
MyRecset.Close
Set MyRecset = Nothing
End If
With MyRecset
.ActiveConnection = MyCon
.CursorLocation = adUseServer
.LockType = adLockPessimistic
.CursorType = adOpenStatic
.Source = "select * from tblForm48C"
.Open
End With
For fRow = 0 To FlexReport.Rows - 1
MyRecset.AddNew
x1Id = FlexReport.TextMatrix(fRow, 0)
x1MonthYear = Format(FlexReport.TextMatrix(fRow, 1), "M, YYYY")
x1DayNum = Format(FlexReport.TextMatrix(fRow, 2), "D")
x1logIn1 = Format(FlexReport.TextMatrix(fRow, 4), "h:Nn:Ss AM/PM")
x1LogOut2 = Format(FlexReport.TextMatrix(fRow, 5), "h:Nn:Ss AM/PM")
x1LogIn3 = Format(FlexReport.TextMatrix(fRow, 6), "h:Nn:Ss AM/PM")
x1LogOut4 = Format(FlexReport.TextMatrix(fRow, 7), "h:Nn:Ss AM/PM")
MyRecset!fldMachineId = CInt(x1Id)
MyRecset!fldMonthYear = CDate(x1MonthYear)
MyRecset!fldDayNum = CDate(x1DayNum)
MyRecset!fldLogIn1 = CDate(x1logIn1)
MyRecset!fldLogOut2 = CDate(x1LogOut2)
MyRecset!fldLogIn3 = CDate(x1LogIn3)
MyRecset!fldLogOut4 = CDate(x1LogOut4)
MyRecset.Update
MyRecset.MoveNext
Screen.MousePointer = vbHourglass
Next fRow
Screen.MousePointer = Default
MsgBox "Final data is been prepared.", vbOKOnly
End Sub
This is the error i got.
This is the data im trying to save..
Last edited by robinx1578; Jun 19th, 2015 at 12:39 AM.
Reason: Adding additional info
Since the field accepts null values i would have any problem if the fields are empty.. However im not sure if the code reads the content of flexreport.
Dim x1Id As Integer
Dim x1MonthYear As Date
Dim x1DayNum As Date
Dim x1logIn1 As Date
Dim x1LogOut2 As Date
Dim x1LogIn3 As Date
Dim x1LogOut4 As Date
Dim fRow As Integer
... However im not sure if the code reads the content of flexreport.
I think you need to check your variable data types and see if your gets the values from your grid to your variables.
Comment other succeeding codes to test.
No, Arnoudtv is telling you this:
x1Id = FlexReport.TextMatrix(fRow, 0)
is incorrect.
xLID is an Integer, yet you are trying to put a String (FlexReport.TextMatrix(frow, 0)) into it. Even though you SEE
a number in your grid, it is actually a string.
Your line should read: xlID = CInt(FlexReport.TextMatrix(fRow, 0))
On this line? x1Id = CInt(FlexReport.TextMatrix(fRow, 0)?
If so, what is the value of fRow when you get the error? AND, what is the value of the cell (fRow, 0) at that time? It has to be a number, otherwise, yes, you will receive an error.
You set cell 1,1 to "2" Or 2,
try it with 0,0 as that is the cell you say you are on (that is, you say fRow is 0).
It sure seems to me that cell (0,0) is NOT a number.
Also all the conversions for the "date" values will fail too.
These columns are no date values.
You better rethink what you want to achieve.
You are pulling a pivot table using a complex query with formatted text from a table, then you are going to parse the formatted text back to numeric values to be put in some other table.
Why put it in human readable format in a grid first?
Why not directly convert the table from one to another??
Why not directly convert the table from one to another??
I love too but i dont know how :-( I also put that same problem in my other thread. If i can do convert the pivot table to another table that would be more easier. However my knowledge isnt enough :-)
I love too but i dont know how :-( I also put that same problem in my other thread. If i can do convert the pivot table to another table that would be more easier. However my knowledge isnt enough :-)
In your code, you wrote:
Code:
'/ prev code
cmd.CommandType = adCmdText
Set MyRecSetF48 = cmd.Execute
'/ other code
After this, instead do put data in flexgrid, you have to use MyRecSetF48 to save data:
Code:
Do While Not MyRecSetF48.Eof
'
' put here the code using a ADODB.Command to save each record in your table
'
MyRecSetF48.MoveNext
Loop
First you have to set the correct parameter of MyRecSetF48 recordset.
Code:
MyRecSetF48.CursorLocation = adUseClient
MyRecSetF48.CursorType =adOpenDynamic
MyRecSetF48.LockType = adLockPessimistic
'
' your ADODB.Command code
'
'After execute the command:
Set MyRecSetF48 = cmd.Execute
Now you can copy data directly from MyRecSetF48 to MyRecset (doesn't need to use flexgrid):
Code:
Do While Not MyRecSetF48.Eof
MyRecset.AddNew
MyRecset!fldIDMachine = MyRecset.MachineID
MyRecset!fldMonthYear = MyRecset.Fields("[Month/Year]").Value ''' It's terrible this field name!
MyRecset!fldDayNum = MyRecset!D
' and so on...
MyRecset.Update
MyRecSetF48.MoveNext
Loop
♫
Turn around, look at what you see
In her face the mirror of your dreams
Make believe I'm everywhere
Given in the lines, written on the pages
Is the answer to a neverending story...
♫
Seriously guys, 31 posts... and just in this thread?
I despair of any learning taking place here either. We seem to have a box jockey trying to play programmer, copy/pasting scraps of begged code. I suspect that low traffic here combined with boredom is the only reason anyone is replying at all.
Hire a programmer and be done with it, they are well worth the money.
Rant aside, is there some reason you don't use use append queries instead of trying to breathe life back into dead data? By "reporting" data you have killed it, losing all sorts of fidelity just beginning with nullability. Displayed data is dead data.
Here's a small standalone example you should be able to just run. It creates a database and inserts data from sample text files.
Thanks for all those who reply and share their knowledge. especially to arnoutdv, gibra and SamOscarBrown.
@dilettante Im sorry but i dont like what you said .. as you have notice im not a "PROGRAMMER" like you. I just read books and try to comprehend it. It just so happen that i required to do this program for the the benefit of all public schools in my area (IF WE HAVE FUNDS, I WOULD REALLY PAY FOR THE PROGRAMMER.). I started using vb last 20 years ago and i never practice it after passing the subject. Im more on hardware side rather than coding side.
If this post reach 32 im really sorry to disappoint you. Im not "BEGGING" for code, im just asking "HELP", like any guy who run into this site and i dont play "PROGRAMMER" AND NEVER ACT AS PROGRAMMER. If you want to help, just help and explained rather than criticizing the "THREAD STARTER" and other "MEMBERS" of the forum. I dont think arnoutdv, gibra and SamOscarBrown reply not because they are bored, they just want to help, letting me how to do it properly. If this thread reach 30+ then sorry.
Sorry for my words sir, If i offended you then forgive me. You're good and your always come to my thread. I really appreciate it. You have my respect. Next time if you see my post n want to help then be it, but please "THINK" before you say those "WORDS". Your really good your "CODE" always come in "HANDY" and giving me much space to "LEARN". If you just help and explained your line of codes many will be happy.
I think I have to close this thread now. Thanks for all your support. TILL NEXT TIME!
ADMIN: Im not trying to offend dilettante, Im just expressing my side. Hope I never violate some rules here :-)
Don't worry, if anyone is going to get slapped here it'll be me.
:-) nahhh, you wont coz your good. your answer and codesearch new thread give me idea on how to solve my problem in other way. But im afraid i need to start another thread. :-)
MyCon.CursorLocation = adUseClient
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = MyCon
cmd.CommandText = "TRANSFORM " _
& "Format$(Min([fldTime]),'h:Nn:Ss AM/PM') AS [Time] " _
& "SELECT " _
& "CStr([fldMachineID]) AS [fldMachineID]," _
& "Format$([fldDate],'yyyy') AS [fldYear]," _
& "Format$([fldDate],'mmmm') AS [fldMonth]," _
& "Format$([fldDate],'d') AS [fldDate]," _
& "Format$([fldDate],'dddd') AS [fldDayName] " _
& "FROM [tblForm48B] " _
& "GROUP BY [fldMachineID], [fldDate] " _
& "ORDER BY [fldMachineID] ASC, [fldDate] ASC " _
& "PIVOT [fldRem]IN ('LogIn1','LogOut2','LogIn3','LogOut4')"
'Debug.Print cmd.CommandText
cmd.CommandType = adCmdText
Set RsSource1 = cmd.Execute
'FlexReport.FixedCols = 0
'Set FlexReport.DataSource = RsSource1
'FlexReport.ColWidth(-1) = 1200
'GetData
Set Rs2F48 = New ADODB.Recordset
If Rs2F48.State = 1 Then
Rs2F48.Close
Set Rs2F48 = Nothing
End If
With Rs2F48
.ActiveConnection = MyCon
.CursorLocation = adUseServer
.LockType = adLockPessimistic
.CursorType = adOpenStatic
.Source = "select * from tblRs2F48"
.Open
End With
If Not RsSource1.RecordCount = 0 Then
RsSource1.MoveFirst
Do Until RsSource1.EOF
Rs2F48.AddNew
xMachId = CInt(RsSource1!fldMachineId)
xYear = CStr(RsSource1!fldYear)
xMonth = CStr(RsSource1!fldMonth)
xDate = CStr(RsSource1!fldDate)
xDayName = CStr(RsSource1!fldDayName)
xLogIn1 = CStr(RsSource1!LogIn1)
xLogOut2 = CStr(RsSource1!LogOut2)
xlogIn3 = CStr(RsSource1!LogIn3)
xlogOut4 = CStr(RsSource1!LogOut4)
Rs2F48!fldMachineId = CInt(xMachId)
Rs2F48!fldDate0 = xYear
Rs2F48!fldDate1 = xMonth
Rs2F48!flddate2 = xDate
Rs2F48!flddate3 = xDayName
Rs2F48!fldLogIn1 = xLogIn1
Rs2F48!fldLogOut2 = xLogOut2
Rs2F48!fldLogIn3 = xlogIn3
Rs2F48!fldLogOut4 = xlogOut4
Rs2F48.Update
RsSource1.MoveNext
Screen.MousePointer = vbHourglass
Loop
Screen.MousePointer = Default
MsgBox "Data has been loaded to Table tblRs2F48.", vbOKOnly
End If
Thank you very much guys..
Thanks for Schmidt for detailed explanation on my other thread, dilettante for his cross tab query, SamOscarBrown for picking .text to db, gibra for his explanation in copying table from crosstab to table, arnoutdv for his explanation too and to the rest of the guys who contributed in this thread of mine.
This is not my last Thread Guys so bear with me next time.
[RESOLVED] Saving MSHFLEXGRID data to table
Reply With Quote
Originally Posted by robinx1578
