Extracting specific portions of a string is not difficult (see below).
I'm a little confused about the structure of the table you're searching
through since you haven't mentioned the field(s) where the imported
data resides. Perhaps you'll provide more info.
The description of tblProdBackups sounds as though you're creating
a spreadsheet-style solution rather than a properly normalized table.
The problem with a spreadsheet structure is that it continues to
grow and could potentially expand to where it's unusable.
Instead of multiple fields, consider a variation of this two-field approach:
<code><table>
BackUpID TapeNum
LanBk1 123456
LanBk2 159782
LanBk3 137981
</table></code>
To give yourself a starting point, create the above table and name it
tblTest
Fields: BackUpID (text); TapeNum (integer)
Then copy paste the following code to a new module.
To test, type or copy the following to the debug window, move to the end of the line and press <enter>
? PopulateTheRecord2("tblTest", "The quick Media Label 12345 brown fox jumped Media Label 159782 over the Media Label 137981 lazy dog", "Media Label")
<code>
Function PopulateTheRecord2(theTable As String, ByRef theString As String, theItem As Variant)
'*******************************************
'Name: PopulateTheRecord2 (Function)
'Purpose: Extracts data from a string
' to fill the fields of a record
Dim db As DATABASE
Dim rs As Recordset
Dim texthold As String, textsay As String
Dim fldNameHold As String, strSQL As String
Dim i As Integer
Dim n As Integer
Dim posHold As Integer, numlen As Integer
'
Set db = CurrentDb
'strip theTable
strSQL = "DELETE " & theTable & ".* " _
& " FROM " & theTable & ";"
db.Execute strSQL
'
'open recordset
Set rs = db.OpenRecordset(theTable)
'
'remove extra spaces from the string
texthold = onespace(theString)
'
'count number of occurences of theItem
'in theString
i = StrCount(texthold, theItem)
'
For n = 1 To i
'locate the start of the tape number
posHold = InStr(texthold, theItem) + Len(theItem) + 1
'determine length of tape number
numlen = InStr(Mid(texthold, posHold), " ") - 1
'return the tape number
textsay = Mid(texthold, posHold, numlen)
'
fldNameHold = "LANBK" & Format
With rs
.AddNew
!BackUpID = fldNameHold
!TapeNum = RTrim(textsay)
.Update
End With
Debug.Print fldNameHold; ":"; RTrim(textsay)
texthold = Mid(texthold, InStr(texthold, theItem) + numlen)
Next n
rs.Close
db.Close
Set db = Nothing
'
'create on-the-fly report
'select report source
docmd.SelectObject acTable, theTable, True
'
'create autoreport
docmd.RunCommand acCmdNewObjectAutoReport
End Function
'*******************************************
Function onespace(pstr As String)
'*******************************************
'Name: onespace (Function)
'Purpose: Removes excessive spaces from a string
'Inputs: call onespace(" the quick brown fox")
'Output: "the quick brown fox"
'*******************************************
'
Dim strHold As String
strHold = RTrim(pstr)
Do While InStr(strHold, " ") > 0
strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
Loop
onespace = Trim(strHold)
End Function
'*******************************************
Function StrCount(ByRef TheStr As String, theItem As Variant) As Integer
'------------------------------------------------------------------
' PURPOSE: Counts the numbers of times an item occurs
' in a string.
' ARGUMENTS: TheStr: The string to be searched.
' TheItem: The item to search for.
' RETURNS: The number of occurences as an integer.
'
' NOTES: To test: Type '? StrCount("The quick brown fox jumped over
' the lazy dog", "the") in the debug window.
' The function will return 2.
'------------------------------------------------------------------
Dim strHold As String, itemhold As Variant
Dim placehold As Integer
Dim i As Integer, j As Integer
'
strHold = TheStr
itemhold = theItem
j = 0
'
If InStr(1, strHold, itemhold) > 0 Then
While InStr(1, strHold, itemhold) > 0
placehold = InStr(1, strHold, itemhold)
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
'Debug.Print "StrCount= " & j
End If
StrCount = j
End Function
</code>
