We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

build string in vba

PeterBaileyUk
on
Medium Priority
356 Views
Last Modified: 2012-05-11
I have written some code that goes through some codes where tests failed and attemps to build a string telling me what failed.

I cannot see where to move the code that writes the string to the table as if a client code fails with more than one test the output string is built up incorrectly.

AbiCodeMvris              AbiCode         TestDescription      TestResult      strResult
04048501M3BSJ      04048501          BHP                            0                   BHP,
04048501M3BSJ      04048501          Door                            0            BHP, Door,

the strresult should read: BHP, Door, in both cases

if i move outside the loop then it wont work either and within the loop it does this.
i am in access 2010 vba
Public Function getFailureString()
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strQuery As String
Dim strSelectOuter As String
Dim strFromOuter As String
Dim strGroupByOuter As String
Dim strHavingOuter As String
Dim strQueryOuter As String
Dim strOutput As String
Dim db As Database
Set db = CurrentDb
Dim RstOuter As DAO.Recordset
Dim RStFailures As DAO.Recordset
Dim ClientCodeMvris As String
Dim rcount As Long
Dim RstResultTable As DAO.Recordset

strSelectOuter = "SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult"
strFromOuter = " FROM TblAbiTestResults"
strGroupByOuter = " GROUP BY TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult"
strHavingOuter = " HAVING (((TblAbiTestResults.TestResult)=0));"



strQueryOuter = strSelectOuter & strFromOuter & strGroupByOuter & strHavingOuter


Set RstOuter = db.OpenRecordset(strQueryOuter)

Set RstResultTable = db.OpenRecordset("TblAbiTestResults")


'inner
strSelect = "SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.TestResult, TblAbiTestResults.TestDescription, TblAbiTestResults.strResult"
strFrom = " FROM TblAbiTestResults"











'get record count to later deal with comma at end issue ie if next record end dont add comma to string
RstOuter.MoveLast
rcount = RstOuter.RecordCount
RstOuter.MoveFirst

With RstOuter
.MoveFirst
'loop records in outer
Do While .EOF <> True
    'get the clientcodemvris combination
     ClientCodeMvris = .Fields("AbiCodeMvris").Value
'     Debug.Print .Fields("AbiCodeMvris").Value
    'create query based on clientcode
     strWhere = " WHERE (((TblAbiTestResults.AbiCodeMvris)=""" & ClientCodeMvris & """) AND ((TblAbiTestResults.TestResult)=0));"
    'create string
    strQuery = strSelect & strFrom & strWhere
'     Debug.Print strQuery
     Set RStFailures = db.OpenRecordset(strQuery, dbOpenDynaset)

'    Debug.Print strQuery
        With RStFailures
           .MoveFirst
           'check for no records
           If RStFailures.BOF And RStFailures.EOF Then
           
        Else
        
           Do While .EOF <> True
                .Edit
                'get textual description of why test failed and add to string
                
               strOutput = strOutput & .Fields("testdescription").Value & ", "
               
                .Fields("strresult").Value = strOutput
                .Update
                'because the string is being built in stages records with more than one test failure show string build up
'               Debug.Print strOutput
               .MoveNext
           Loop
            
          


           End If

        End With
 
    strOutput = ""
 
    .MoveNext
Loop

End With





End Function

Open in new window

Comment
Watch Question

The logic when building the string is wrong.

Replace lines 76 - 89 with the code attached.
Do While .EOF <> True
    strOutput = strOutput & .Fields("testdescription").Value & ", "
    .MoveNext
Loop

.MoveFirstDo While .EOF <> True
    .Edit
    .Fields("strresult").Value = strOutput
    .Update
    .MoveNext
Loop

Open in new window

EDIT: Use this one...
Do While .EOF <> True
    strOutput = strOutput & .Fields("testdescription").Value & ", "
    .MoveNext
Loop
.MoveFirst
Do While .EOF <> True
    .Edit
    .Fields("strresult").Value = strOutput
    .Update
    .MoveNext
Loop

Open in new window

Author

Commented:
is it possible for it not too add a comma on when it writes the last part of the string

BHP,
BHP, Transmission,

should become
BHP
BHP, Transmission
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.