[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

build string in vba

Posted on 2011-04-27
5
Medium Priority
?
330 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

0
Comment
Question by:PeterBaileyUk
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:richardburwood
ID: 35482004
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

0
 
LVL 6

Expert Comment

by:richardburwood
ID: 35482013
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

0
 

Author Comment

by:PeterBaileyUk
ID: 35482425
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
0
 
LVL 6

Accepted Solution

by:
richardburwood earned 2000 total points
ID: 35483008
Sure, a bit outside the original scope of the question but...
Do While .EOF <> True
    strOutput = strOutput & .Fields("testdescription").Value & ", "
    .MoveNext
Loop
If len(strOutput) >0 Then
    strOutput = Left(strOutput, Len(strOutput)-1)
End If
.MoveFirst
Do While .EOF <> True
    .Edit
    .Fields("strresult").Value = strOutput
    .Update
    .MoveNext
Loop

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 35483523
thank you
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question