 |
| Access VBA Discuss using VBA for Access programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
May 16th, 2006, 01:35 PM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Actually, that was only a sample file. Some maybe be upto 44 values long. Thats why I was wondering if I could could the number of array so i can figure out a way to dynamically grab the data without having to code out each
objRS("Field1") = Round(Left(MyArray(1), 23), 2)
objRS("Field2") = Round(Left(MyArray(2), 23), 2)
objRS("Field3") = Round(Left(MyArray(3), 23), 2), etc...
i want to avoid this 44 times...
|
|
May 16th, 2006, 01:54 PM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
I don't think we are on the same page yet. You want to create a single record with all the torque values from each line? You don't have to do that. If you add a line number, you could just create it the way I showed, with another nested loop and a counter.
You would add something like:
dDateTime = Now
i = 0
Do Until i = UBound(MyArray) + 1
objRS.AddNew
objRS("Row") = 1
objRS("Data") = MyArray(i)
objRS("DateTime") = dDateTime
objRS.Update
i = i + 1
Loop
You would nest this inside the loop that is in the code I already sent. That way each row would have its own row with datetime, and you could use either to compare data.
Are we getting closer?
mmcdonal
|
|
May 16th, 2006, 01:58 PM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
hmm, I was thinking that I make one record from each line. Because each value is only related to (1) transmission.
|
|
May 16th, 2006, 02:02 PM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
You could do that, but it makes teasing out the data more difficult when you want to run your comparisons.
Can you break the data into seperate records, since they are discrete torque readings, and then add the reading's place in the line, like this...
dDateTime = Now
i = 0
Do Until i = UBound(MyArray) + 1
objRS.AddNew
objRS("Row") = 1
objRS("Place") = i
objRS("Data") = MyArray(i)
objRS("DateTime") = dDateTime
objRS.Update
i = i + 1
Loop
mmcdonal
|
|
May 17th, 2006, 09:04 AM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
This is what I'm currently doing. I know it looks like crap but it works so far...lol:
Code:
Private Sub Command0_Click()
Set fso = CreateObject("Scripting.FileSystemObject")
'Check for existence of file first
'If the file exists, open a connection to the back end, and a recordset
If fso.FileExists("C:\Program Files\ttermpro\toola.txt") Then
Set objStream = fso.OpenTextFile("C:\Program Files\ttermpro\toola.txt", 1, False, 0)
Set objConn = Application.CurrentProject.Connection
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM TOOL_DATA", objConn, 3, 3
'Start reading the file, break it into lines, and the lines into fields
Do While Not objStream.AtEndOfStream
sLine = objStream.ReadLine
MyArray = Split(sLine, "#")
ListAddItem Me.List1, " Import Process Started......" & vbCrLf
objRS.AddNew
objRS("Field1") = Round(Left(MyArray(1), 23), 2)
'test add item
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(1), 23), 2) & vbCrLf
objRS("Field2") = Round(Left(MyArray(2), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(2), 23), 2) & vbCrLf
objRS("Field3") = Round(Left(MyArray(3), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(3), 23), 2) & vbCrLf
objRS("Field4") = Round(Left(MyArray(4), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(4), 23), 2) & vbCrLf
objRS("Field5") = Round(Left(MyArray(5), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(5), 23), 2) & vbCrLf
objRS("Field6") = Round(Left(MyArray(6), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(6), 23), 2) & vbCrLf
objRS("Field7") = Round(Left(MyArray(7), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(7), 23), 2) & vbCrLf
objRS("Field8") = Round(Left(MyArray(8), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(8), 23), 2) & vbCrLf
objRS("Field9") = Round(Left(MyArray(9), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(9), 23), 2) & vbCrLf
objRS("Field10") = Round(Left(MyArray(10), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(10), 23), 2) & vbCrLf
objRS("Field11") = Round(Left(MyArray(11), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(11), 23), 2) & vbCrLf
objRS("Field12") = Round(Left(MyArray(12), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(12), 23), 2) & vbCrLf
objRS("Field13") = Round(Left(MyArray(13), 23), 2)
ListAddItem Me.List1, " Adding Item ........." & Round(Left(MyArray(13), 23), 2) & vbCrLf
'Me.List1.Top = Me.List1.ListCount - 1
ListAddItem Me.List1, " Import Complete" & vbCrLf & " " & UBound(MyArray) & " values where imported!"
'Me.List1.Top = Me.List1.ListCount - 1
objRS.Update
Loop
End If
End Sub
Sub ListAddItem(LB As ListBox, ByVal strItemToAdd As String)
If LB.RowSource = "" Then
LB.RowSource = IIf(LB.RowSource = "", strItemToAdd, "")
Else
LB.RowSource = LB.RowSource & ";" & strItemToAdd
End If
End Sub
|
|
May 19th, 2006, 07:22 AM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by mmcdonal
You could do that, but it makes teasing out the data more difficult when you want to run your comparisons.
Can you break the data into seperate records, since they are discrete torque readings, and then add the reading's place in the line, like this...
dDateTime = Now
i = 0
Do Until i = UBound(MyArray) + 1
objRS.AddNew
objRS("Row") = 1
objRS("Place") = i
objRS("Data") = MyArray(i)
objRS("DateTime") = dDateTime
objRS.Update
i = i + 1
Loop
mmcdonal
|
I can't get this part to work. It errors out on
I get runtime error 3265. Item cannott be found. Any idea why?
|
|
May 19th, 2006, 07:26 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
It should be PLace = i, not 1.
i is the counter and tells you the place of the reading in the line of text. This is optional.
There needs to be a field called "Place" in your table. Is there one?
mmcdonal
|
|
May 19th, 2006, 07:32 AM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
No, the column headings are Field1, Field2, Field3 as of right now. Maybe we mis communicated somewhere.
Example:
#11111111@#222222222@#3333333333@ would be one record going into Field1, Field2, and Field3.
|
|
May 19th, 2006, 07:48 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Then just get rid of the Place field.
Does that help?
mmcdonal
|
|
May 19th, 2006, 08:04 AM
|
|
Authorized User
|
|
Join Date: Oct 2005
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Same error but now its on: objrs("Row") = 1
|
|
|
