I hope that this, my first post at UA, is worthy of your replies...

I've built a database of book information (I'm a librarian) and am stuck at this particular module. I'm trying to transfer in some text from a tab deliminated text file and replace some extra characters (such as $c) from most of the fields. The TransferText correctly puts the next data into a temporary table (tblMARCImports), but I can't get the looping and the replace function to work. The code has compiled without errors, and even when I run through it line by line, no errors occur. But, still the data does not get updated. When I run the code (F5), I get a blank error box. I keep thinking that there's something simple and/or obvious I'm missing here, but haven't seen it yet. Any ideas, oh wise ones???

Function modMARC()
Dim fld As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo modMARCActions_Err
DoCmd.TransferText acImportDelim, "IMPORT", "tblMARCImports", "C:\import.txt", False, ""

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMARCImports", dbOpenTable)
For Each fld In rst.Fields
fldTitle = Replace(fldTitle, "$c", " ")
Next fld

modMARC_Err:
MsgBox "An error occured. The error was " & _
"error number " & Err & ": " & Err.Description, vbOKOnly
Exit Function

End Function

Hello Erica and Welcome to UtterAccess,

You should really use Option Explicit, it will help you ferret out errors.

Try this instead (warning untested code):

I highlighted the changes in red

Function modMARC()
Dim x As Integer
Dim fld As DAO.Field
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo modMARC_Err
DoCmd.TransferText acImportDelim, "IMPORT", "tblMARCImports", "C:\import.txt", False

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMARCImports", dbOpenTable)

Do Until rst.EOF
For x = 0 To rst.Fields.Count - 1
rst.Edit
rst.Fields(x).Value = Replace(rst.Fields(x).Value, "$c", "")
rst.Update
Next x

rst.MoveNext
Loop


modMARC_Err:
MsgBox "An error occured. The error was " & _
"error number " & Err & ": " & Err.Description, vbOKOnly
Exit Function

End Function

Let me know how it works out.

Edited by: DataDay on Tue Apr 19 20:42:39 EDT 2005.

Unfortunately, with the For Loop that you have you are simply cyclicng thru the FIELDS on the FIRST record of the RecordSet itself ONLY and that is IT !!.

I assume you are actually wanting to cycle thru the RECORDS in the recordset and update the field NAMED 'fldTitle' accordingly.

Therefore (not tested) but the follwoing should do it for you


.... Your code up to here ......

Set rst = db.OpenRecordset("tblMARCImports", dbOpenTable)

' Read FIRST record in set

rst.MoveFirst

Do UNTIL rst.EOF = TRUE
rst.EDIT
rst!fldTitle = Replace(rst!fldTitle, "$c", " ")
rst.UPDATE

rst.MoveNext ' Read NEXT record in Recordset
LOOP

MsgBox "Update finished'

Exit Function ' You ALSO need this to stop the code dropping thru to the error paragraph at ALL times when the process is finished.


..... now YOUR error paragraph

Edit .... I actually just spotted that you DID say that you wanted the Replace to be done over a NUMBER of fields, so what DataDay has posted (in which we were on similar paths anyway), is the one to GO WITH as it DOES step thru EVERY field in EVERY record in the RecordSet.


Edited by: r_cubed on Tue Apr 19 20:49:46 EDT 2005.

Hi -



This code is not looping thru the recordset but rather thru the list of fieldnames.
Unless a fieldname includes the target string (e.g. "$c"), which I'd assume is not the
case, you're not going to see any results since you never get to the data.

Consider also that it's only necessary to test text and memo fields since they're the
only ones that could potentially contain the target string.

Are your field names and data types consistent from transfer to transfer? If so, you
may want to look at a procedure that takes the fieldname from a paramarray and search/
replaces any instances of the target string, then moves on to the next item in the paramarray.

A little more info would be helpful.

Best wishes - Bob

I would use an update query instead since it's faster than looping thru the recordset.

Update Table1 Set Field1=Replace(Field1,"$c",""), Field2=Replace(Field2,"$c",""), ...

Hope this helps.

Wow! Thanks for the responses!! I got my new (modified, below) script working. Since Bob requested more information about what I'm trying to do, here goes. I'll always be transfering the same type of information (title, author, publisher, etc.) and 7 of the fields will always have the same extra characters in them ($a, $b, $c, etc.) What I'm trying to do is import the data (sometimes up to several hundred records) and clean it up in the temp table (tblMARCImports) before then appending it to the end of the permenant table (tblBooks). I used to use a Marcro to do all this, but I had to manually edit each record, so this'll definitely speed things up. Though the following script now works (again, THANKS!), I'll start exploring ParamArrays and update queries to speed up the processing even more.

Also Bob, is it possible to limit the testing and replacing to 7 out of 29 fields? If so, how?


Function modMARC()
Dim x As Integer
Dim fld As DAO.Field
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rsqry As String

On Error GoTo modMARC_Err
DoCmd.TransferText acImportDelim, "IMPORT", "tblMARCImports", "C:\import.txt", False, ""


Set db = CurrentDb()
'Set rsqry = "SELECT fldTitle, fldAuthor, fldPublisher, fldYear, fldISBN, fldListPrice, fldFinalPrice, fldMajor, fldRequester, fldCall FROM tblMARCImports"
Set rst = db.OpenRecordset("SELECT fldTitle, fldAuthor, fldPublisher, fldYear, fldISBN, fldListPrice, fldFinalPrice, fldMajor, fldRequester, fldCall FROM tblMARCImports")

Do Until rst.EOF
For x = 0 To rst.Fields.Count - 1
rst.Edit
rst.Fields(x).Value = Replace(rst.Fields(x).Value, "$a", " ")
rst.Fields(x).Value = Replace(rst.Fields(x).Value, "$b", " ")
rst.Fields(x).Value = Replace(rst.Fields(x).Value, "$c", " ")
rst!fldTitle = Replace(rst!fldTitle, "The ", "")
rst!fldTitle = Replace(rst!fldTitle, "A ", "")
rst!fldTitle = Replace(rst!fldTitle, "An ", "")
rst!fldYear = Replace(rst!fldYear, " ", "")
rst!fldYear = Replace(rst!fldYear, ".", "")
If IsNull(rst!fldListPrice) Then
rst!fldListPrice = "$0.00"
End If
Select Case rst!fldCall
Case "BC" To "BEZ", "BH" To "BJZ"
rst!fldMajor = "Philosophy"
Case "BF" To "BFZ", "RC435" To "RC571", "RJ498" To "RJ508"
rst!fldMajor = "Psychology"
Case "BL" To "BZ"
rst!fldMajor = "Christian Studies"
Case "D" To "FZ"
rst!fldMajor = "History"
Case "GV" To "GVZ", "RC86" To "RC89"
rst!fldMajor = "Kinesiology"
Case "H" To "HM"
rst!fldMajor = "Business Administration"
Case "HN" To "HQZ"
rst!fldMajor = "Behavioral Science"
Case "HV" To "HVZ"
rst!fldMajor = "Criminal Justice Administration"
Case "J" To "JZZ"
rst!fldMajor = "Political Science"
Case "L" To "LZZZ"
rst!fldMajor = "Liberal Studies"
Case "M" To "MZZZ"
rst!fldMajor = "Music"
Case "N" To "NZZZ", "TR" To "TRZ"
rst!fldMajor = "Visual Arts"
Case "P" To "PN1599", "PN3309" To "PN4000", "PN4357" To "PN4698", "PN5652" To "PZZZ"
rst!fldMajor = "English"
Case "PN1600" To "PN3308", "PN4001" To "PN4356", "PN4699" To "PN5651"
rst!fldMajor = "Communication Arts"
Case "QA" To "QAZ"
rst!fldMajor = "Mathematics"
Case "R" To "RC85", "RC90" To "RC434", "RC572" To "RJ498", "RJ508" To "RZZZ"
rst!fldMajor = "Biology"
End Select

rst.Update
Next x

rst.MoveNext
Loop

MsgBox "Update Finished"

modMARC_Err:
If Err.Number <> 0 Then
MsgBox "An error occured. The error was " & _
"error number " & Err & ": " & Err.Description, vbOKOnly
Exit Function
End If
End Function