Module to Merge 2 fields in new table issues

[ohi]

Hi, I have the following code I'm using to merge 2 current fields in a new table, along with other data.

It works as it should when there are multiple records, however, when there is only one record, it skips it. I cannot figure out what's wrong, any assistance would be great. I did not write this code myself, I had help.

Private Function usethis()
Dim rstmain As Recordset
Dim rsttbl As Recordset
Dim strfld As String
Dim strcasenbr As String

Set rstmain = CurrentDb.OpenRecordset("ICAC Report")
Set rsttbl = CurrentDb.OpenRecordset("copy of ICAC Export")

rstmain.MoveLast
rstmain.MoveFirst
strfld = ""
strcasenbr = rstmain![Case Number]

Do Until rstmain.EOF


    Do While strcasenbr = rstmain![Case Number]
        strfld = strfld & rstmain![Type] & vbCrLf
        rstmain.MoveNext
        If rstmain.EOF Then
        rstmain.MovePrevious
        GoTo skip
        End If
    Loop
skip:
rstmain.MovePrevious
rsttbl.AddNew
rsttbl!IntakeDate = rstmain![Intake Date]
rsttbl!DateRecent = rstmain![Date of Recent Activity]
rsttbl!TypeReceived = rstmain![Type Received]
rsttbl!NameandItems = rstmain![Subject] & vbcrrlf & strfld
rsttbl!CaseType = rstmain![Secondary Case Type]
rsttbl!CaseNumber = rstmain![Case Number]
rsttbl!Comments = rstmain![Comments]
rsttbl.Update
rstmain.MoveNext
rstmain.MoveNext
If rstmain.EOF Then GoTo iamdone
strcasenbr = rstmain![Case Number]
strfld = ""
Loop
iamdone:

End Function

 

[vbaInet]

Set a breakpoint somewhere, run the code and step through it using F8 or clicking Step Into in the Debug menu.

Here's a clearer version of your code:

Private Function usethis()
dim db as dao.database
Dim rstmain As dao.Recordset
Dim rsttbl As dao.Recordset
Dim strfld As String
Dim strcasenbr As String

set db = currentdb
Set rstmain = Db.OpenRecordset("ICAC Report")
Set rsttbl = Db.OpenRecordset("copy of ICAC Export")

rstmain.MoveLast
rstmain.MoveFirst
strfld = ""
strcasenbr = rstmain![Case Number]

Do Until rstmain.EOF
    Do While strcasenbr = rstmain![Case Number]
        strfld = strfld & rstmain![Type] & vbCrLf
        rstmain.MoveNext
        If rstmain.EOF Then
            rstmain.MovePrevious
            exit do
        End If
    Loop
skip:

    rstmain.MovePrevious
    with rsttbl
        .AddNew
        !IntakeDate = rstmain![Intake Date]
        !DateRecent = rstmain![Date of Recent Activity]
        !TypeReceived = rstmain![Type Received]
        !NameandItems = rstmain![Subject] & vbcrrlf & strfld
        !CaseType = rstmain![Secondary Case Type]
        !CaseNumber = rstmain![Case Number]
        !Comments = rstmain![Comments]
        .Update
     end with
     rstmain.MoveNext
     rstmain.MoveNext
     If rstmain.EOF Then 
        exit do
     end if
     strcasenbr = rstmain![Case Number]
     strfld = ""
Loop
iamdone:

End Function

 

[ohi]

Thanks for the tip, but the problem is I have no idea what i'm looking for...

 

[vbaInet]

That's a BIG problem then.

Post a stripped-down version of your db and if I've got a few mins I will have a look or someone will.