Code to loop through records

[Guest]

I need some help. I am way new to the VB coding syntax. I need to take
variables from one table and separate a text field and save variables into
the other table text fields. I cannot find out how to save programmatically,
either. Not using SQL tables, using Access 2000 tables/database. I cannot
figure out if I need a Sub or a Function, either. Anyone got any ideas?

 

[Guest]

See Recordset in the VBA help menu.

But ... can this not be done in SQL. Looping thru records is very costly.

 

[Guest]

This is not a SQL table.

--
thanks,
foxydev77

See Recordset in the VBA help menu.

But ... can this not be done in SQL. Looping thru records is very costly.

 

[Guest]

This is something that requires code. I can't find any in the HELP menu or
anywhere else. If it is non-existent, please let me know.

 

[Rick Brandt]

This is something that requires code. I can't find any in the HELP
menu or anywhere else. If it is non-existent, please let me know.

Please post a few lines of example data and what you need to do with it.
Many times stuff that people "think" requires looping through records in
code can actually be done with SQL.

 

[Guest]

This what I have so far:

Private Sub cmdFacScor_Click()

Dim strComs As String, strSpc As String
Dim intComsPos As Integer, intSpPos As Integer
Dim strSbnm As String, strSbLst As String, strSbfrst As String
Dim db As Database
Set db = CurrentDb()
DoCmd.OpenTable ("ImpFacEval")
strSbnm = "FSubName"
strSpPos = " "
strComs = ","
intComsPos = 0
intSpPos = 0
intComsPos = InStr(1, strSbnm, strComs)
intComs
strSbLst = Left(strSbnm, intComsPos)
strSbfrst = Right(strSbnm, intComsPos)

Set [ImpFacEval]![FSubFirst] = strSbfrst ' This is not saving to record'
Set [ImpFacEval]![FSubLast] = strSbLst

End Sub

 

[Rick Brandt]

This what I have so far:

Private Sub cmdFacScor_Click()

Dim strComs As String, strSpc As String
Dim intComsPos As Integer, intSpPos As Integer
Dim strSbnm As String, strSbLst As String, strSbfrst As String
Dim db As Database
Set db = CurrentDb()
DoCmd.OpenTable ("ImpFacEval")
strSbnm = "FSubName"
strSpPos = " "
strComs = ","
intComsPos = 0
intSpPos = 0
intComsPos = InStr(1, strSbnm, strComs)
intComs
strSbLst = Left(strSbnm, intComsPos)
strSbfrst = Right(strSbnm, intComsPos)

Set [ImpFacEval]![FSubFirst] = strSbfrst ' This is not saving to
record' Set [ImpFacEval]![FSubLast] = strSbLst

End Sub

Well, there's LOTS wrong with that.

That is not how you set open a Recordset
That is not how you set a value in a Recordset
Your Right() statement will not return what you want it to.

You can do this with a straight UPDATE query instead of using a Recordset loop.
All you want to do is split a LastName, FirstName field into separate FirstName
and LastName fields right?

UPDATE ImpFacEval
SET strSbLst = Left(FSubName, InStr(1, FSubName, ",")-1),
strSbfrst = Trim(Mid(FSubName, InStr(1, FSubName, ",")+1))

(untested, but that's the jist of it)

 

[Guest]

Rick was asking for an example of the *data* not the code.

Please post the name of the field in the one table and an example of the data.

Please post the names of the fields in the other table and an example os
what should be in these fields.


This will allow everyone to see what you have and what you want, so instead
of guessing, someone might be able to give you the SQL or VB code to do
exactly what you want (or get moving in the right direction.)


Don't give up....

 

[Guest]

I have to download data from a website. It is excel format. I need to import
this table, separate the name fields in order to match records for ID
numbers. These records come without ID numbers. I end up putting them in
manually, 'cause I can't figure out how to separate the names. It would be
so nice to click a button and have the work done for me. I am trying to code
a button to accomplish this. Then I have to take the individual responses
and add them up and average them to report on them. I just can't see a SQL
statement doing that for me. I have 198 records, so far and I won't need all
of them every month. I can't give up : ).

 

[Guest]

Again, post some examples of the text you want to split, of the excel
spreadsheet data, of HOW you determine which ID numbers to use.

No one can see your MDB or your excel spreadsheet except you.!!!

I think what you want to do is going to take a fair amount of coding. And
there are many ways to do what you want.

- You could *link* the spreadsheet... then it acts like a table. You can
read and write to it. You can separate the name fields by using the Split()
function. But what criteria do you use to split the names??

- You could also save the spreadsheet in CSV format, then open it as a text
file and read it line by line, parsing the input string as you go.

- You could import the spreadsheet into a temp table and .......... you get
the idea.

 

[Guest]

Parsing record by record is the idea I had.
Here are some lines in the import table:

FName | FCompdt | FQx1 | FQx2 | FQx3 | FQx4 | FQx5|
Jones, Al | 3/2/2007 | 2.5 | 2 | 1.7 | 2.1 | 2.6 |
Smith, John | 2/28/2007| 1.9| 3 | 2.5 | 1.5 | 2 |
Harris, Angela|2/18/2007 | 2 | 2 | 2 | 2 | 2 |

The table for reporting is like:

ResID | Firstname | Lastname | Qx1 | Qx2 | Qx3 | Qx4 | Qx5 |
3206 | Mary | Alberts | 2.3 | 1.5 | 2.6 | 3 | 1.7 |
4509 | Fred | Villa | 3 | 3 | 3 | 3 | 3 |

PS. The ID's come from a different table.

I really appreciate all your help!

 

[Guest]

Hey everybody! I finally got the answer and sorry to say it was not from
this community:


If Not rsFtbl.EOF Then rsFtbl.MoveFirst
Do While Not rsFtbl.EOF

strFSubnm = rsFtbl!FSubName
strFSubLst = Left(strFSubnm, InStr(strFSubnm, ",") - 1)
strFSubFst = Trim(Mid(strFSubnm, InStr(strFSubnm, ",") + 2))
strFFirt = Left(strFSubFst, InStr(strFSubFst, " "))

rsFtbl.Edit

If strFFirt = "" Then
rsFtbl!FSubFirst = strFSubFst
Else
rsFtbl!FSubFirst = strFFirt
End If

rsFtbl!FSubLast = strFSubLst

rsFtbl.Update
rsFtbl.MoveNext
Loop

This loop took 7 miliseconds. Only 198 recs. : )
This code takes care of middle names, which I do not need