Loop through a query to show results on form

[Roemer]

Dear all,
I am about to learn MS Access using VBA. Currently I am trying to figure out how Loops etc. are working. The book I bought does not tell me a lot about this topic and I am wondering whether anyone could help me on the following. I a basically want to Loop throug a query (or if not possible trough a table) and show the results in my form. For each record in the table I am doing additional checks when loading the form and showing that result accordingly. I do not want to do these checks in additional queries - I think it is more efficient doing it in the form directly. Here is the code I have:

Dim dbsSR As DAO.Database
Dim rstValQry As DAO.Recordset
Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
With rstValQry
While (Not .EOF)

strCieVal = Me![ValComp] & ";"
strAcctVal = Me![ValAcct] & ";"

If strPCAct = -1 Then
strPCVal = Me![ValPC] & ";"
Else
strPCVal = ""
End If

If strBAAct = -1 Then
strBAVal = Me![ValBA] & ";"
Else
strBAVal = ""
End If

If strLocAct = -1 Then
strLocVal = Me![ValLoc] & ";"
Else
strLocVal = ""
End If
'***** combine individual errors into one error message (for the ones which are relevant / active) *****
strErrSum = strCieVal & strAcctVal & strPCVal & strBAVal & strLocVal
Me![ErrSum] = strErrSum
.MoveNext
Wend
.Close
End With


The Loop seems to work because in debug it is going through it 3 times (the number of records I have in the query), but the result is always the same - it seems the above code is not checking / refreshing based on the individual query records.
What am I doing wrong?

 

[spikepl]

You are doing something very unusual for access. You do not check recordsets. You check records as they are being input. Once a record is stored it is supposed to be a valid record, so no more fiddling is expected or desired at later stage (this to prevent invalid records being stored if people go for a break or drop dead or whatever)..

So please explain in plain English what this is all about.

Your error message, as it is defined here, is concocted from the bits that were filled for the last record reviewed only, so you only get "results" from the last record.

Correction:

Your loop loops through some recordset, but takes values from the form. Is the form bound to that recordset or to some other recordset?

 

[DennisOJensen]

Dim dbsSR As DAO.Database 
Dim rstValQry As DAO.Recordset 

Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")

With rstValQry
    While (Not .EOF)

        strCieVal  = Me![ValComp] & ";"
        strAcctVal = Me![ValAcct] & ";"

        If strPCAct = -1 Then
            strPCVal = Me![ValPC] & ";"
        Else
            strPCVal = ""
        End If

        If strBAAct = -1 Then
            strBAVal = Me![ValBA] & ";"
        Else
            strBAVal = ""
        End If

        If strLocAct = -1 Then
            strLocVal = Me![ValLoc] & ";"
        Else
            strLocVal = ""
        End If

First you do need to outline what the fields are that are associated with rstValQry

Second as spikeepl stated you do not normally update a "Recordset". Recordsets are the results of a query and are thus divorced from the table(s) that it came from. Aka there is no backward connection here. You use the recordset to cycle through the records implementing whatever changes you wish to implement then you load that into an Update or Insert query depending on what it is you are doing with the final result.

 

[Roemer]

Hi guys,
Thanks a lot for your quick Responses. As requested I would like to explain in plain English.
1. I have a query with some results - let's say something like this
Company Check 1 Check 2 Check 3 1000 True False True 2000 True False True

2. In the form I would like to show these results, but based on additional criteria, e.g. for Company 1000 "Check 1 and Check 3" are valid but not "Check 2". For Company 2000 Check 1
=> in the form it should look as follows (one "check" field showing the combined checks from the query and not showing the irrelvant ones:
Company Check 1000 True, True 2000 False, True

My understanding is that the recordset contains the result from the query - is this correct?

I imagine that I can use the values from the recordset (I assume I can inquire them by using the query field names) and show them in the form after having done the "modification".

Does the above Explanation help? I can clearly see that I still have a Problem with the Basic understanding of the Loops and how they can be used.

Any additional Input is much appreciated.
Regards,
Patrick

 

[Roemer]

Sorry, I can just see that inserting tables does not work. Trying it again differently
1. the query
Company Check 1 Check 2 Check 3
1000 True False True
2000 True False True

2. the form
Company Check
1000 True, True
2000 False, True

 

[jdraw]

For clarity, it may be better to start with a table.

Suppose you have
tblPeople
PersonID PK
FirstName text
LastName text

and records
1 John Doe
2 Jane Doe
3 Kodi AkBear
4 Hesa Payne

To show how a loop works (logic)

declare all variables
open the recordset on tblPeople
while records exist
for each record in the recordset
Print the FirstName and LastName
get next record
end

Sub LoopDemo()
    Dim iLoopCount As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPeople")
    iLoopCount = 0
    Debug.Print "Loop" & vbTab & "FirstName" & vbTab & "LastName"
    With rs
        While Not .EOF
            iLoopCount = iLoopCount + 1
            Debug.Print iLoopCount & vbTab & vbTab & !FirstName & vbTab & vbTab & !LastName
            .MoveNext
        Wend
    End With
End Sub

Result:

Loop    FirstName   LastName
1       John        Doe
2       Jane        Doe
3       Kodi        AckBear
4       Hesa        Payne

 

[Roemer]

Hi Jdraw
I think I understand your example and it looks like my understanding of a recordset was not completely wrong.

Still - I wonder how I can ouput the data from the loop on the form. When I normally do something in VBA and put a value into a field then I usually do it as follows.
Me![fieldname] = strSomething (or whatever variable I was declaring before).

In my example at the top I am always getting the value from the very first record of the query.

I could achieve the my Goal by creating an additional query but I would like to avoid this.

Any Suggestion?

 

[jdraw]

You should always use Option Explicit at the top of your module. This will highlight any un declared variables.
If you get the same value each time, then two things come to mind.
First, you are not progressing(iterating) through th""e recordset --but you are.
Second, you may be re-outputting the same info because you have not reset a variable between records.


You can try adding this line after your While.... line

strErrSum =""

Let's see what happens.

Where exactly are you running this code? I don't see any requery or Update statements, so there may be other issues.

 

[Roemer]

Hi Jdraw
Sorry, I have forgotten to answer you.
I am using Option Explicit - I just did not Show that part of my code.
I am running the code on a Form_Load() Event.
Here is what I have - including your proposal (I removed some lines to make it easier for Debugging):

 Private Sub Form_Load()
 Dim strErrSum As String ' this field contains the summarized error messages based on the validation
Dim strCieVal As String ' this field contains the validation result for company code
Dim strAcctVal As String ' this field contains the validation result for account code
Dim strBAVal As String ' this field contains the validation result for business area
Dim strPCVal As String ' this field contains the validation result for profit center
Dim strLocVal As String ' this field contains the validation result for location
Dim strPCAct As String ' contains the result from the check whether the PC is active
Dim strBAAct As String ' contains the result from the check whether the BA is active
Dim strLocAct As String ' contains the result from the check whether the Location is active
 '***** check first which fields are relevant for validation *****
'-1 means active / 0 means inactive
 'profit center
strPCAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 1")
 If strPCAct = -1 Then
    PC.Visible = True
    Desc_PC.Visible = True
Else
    PC.Visible = False
    Desc_PC.Visible = False
End If
 'business area
strBAAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 2")
 If strBAAct = -1 Then
    BA.Visible = True
    Desc_BA.Visible = True
Else
    BA.Visible = False
    Desc_BA.Visible = False
End If
 'location
strLocAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 3")
 If strLocAct = -1 Then
    Loc.Visible = True
    Desc_Loc.Visible = True
Else
    Loc.Visible = False
    Desc_Loc.Visible = False
End If
 '***** gather error messages from query *****

Dim dbsSR As DAO.Database ' SR = Simple Recon
Dim rstValQry As DAO.Recordset ' string which contains the query

Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
 With rstValQry
     While (Not .EOF)
    Me![ErrSum] = ""
     strCieVal = ValComp
     strAcctVal = ValAcct
    '***** combine individual errors into one error message (for the ones which are relevant / active) *****
    Me![ErrSum] = Me![ValComp] & ";" & Me![ValAcct]
     .MoveNext
    Wend
    .Close
End With
 End Sub

 

[DennisOJensen]

Okay I reformatted your code and added few items to help with checking for errors -- which need to be part of every function/subroutine unless you are absolutely 100% positive without a shadow of a doubt sure there could not ever be an error occurring within that function/subroutine.

Glancing over your subroutine and not being able to walk through it in debug mode I have to guess the issue might be in your while loop. I removed the With statement and replaced it in the spots where it would get used to make sure that it is completely obvious where it is getting applied and where it is not getting applied. That along with the two lines that should be at the top of every form/module should help perhaps narrow down any issues. As the values ErrSum, ValComp, and ValAcct do not seem to be declared. If they are part of the recordset my understanding is that they should be preceded by "." as in .ErrSum, .ValComp, and .ValAcct -- aka the reason I removed the With clause. Further I am not understanding if these 3 variables are part of the same recordset then why not create a Calculate Field that does this as part of the table rather than doing this on the fly?

If you still are having problems do you know how to set a break point and walk through the code to see what you ARE getting versus what you think you should be getting at each point in the process?

[FONT="Courier New"]'These to lines should be at the top over every Form/Module
Option Compare Database
Option Explicit

Private Sub Form_Load()
    Dim strErrSum  As String   ' this field contains the summarized error messages based on the validation
    Dim strCieVal  As String   ' this field contains the validation result for company code
    Dim strAcctVal As String   ' this field contains the validation result for account code
    Dim strBAVal   As String   ' this field contains the validation result for business area
    Dim strPCVal   As String   ' this field contains the validation result for profit center
    Dim strLocVal  As String   ' this field contains the validation result for location
    Dim strPCAct   As String   ' contains the result from the check whether the PC is active
    Dim strBAAct   As String   ' contains the result from the check whether the BA is active
    Dim strLocAct  As String   ' contains the result from the check whether the Location is active
    Dim Routine As String
On Error GoTo ErrorHandler
    Routine = "Form_Main.Form_Load"
    '***** Check first which fields are relevant for validation *****
    ' -1 means active / 0 means inactive
    ' Profit Center
    strPCAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 1")
    If strPCAct = -1 Then
        PC.Visible      = True
        Desc_PC.Visible = True
    Else
        PC.Visible      = False
        Desc_PC.Visible = False
    End If
    'Business Area
    strBAAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 2")
    If strBAAct = -1 Then
        BA.Visible      = True
        Desc_BA.Visible = True
    Else
        BA.Visible      = False
        Desc_BA.Visible = False
    End If
    'Location
    strLocAct = DLookup("FldVal", "tbl_frm_field_display", "Id = 3")
    If strLocAct = -1 Then
        Loc.Visible      = True
        Desc_Loc.Visible = True
    Else
        Loc.Visible      = False
        Desc_Loc.Visible = False
    End If
    '***** Gather Error Messages From Query *****
    Dim dbsSR     As DAO.Database   ' SR = Simple Recon
    Dim rstValQry As DAO.Recordset  ' String which contains the query

    Set dbsSR = CurrentDb
    Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
    While (Not rstValQry.EOF)
        Me![ErrSum] = ""
        strCieVal  = ValComp
        strAcctVal = ValAcct
        '***** Combine Individual Errors Into One Error Message (For the Ones Which Are Relevant / Active) *****
        Me![ErrSum] = Me![ValComp] & ";" & Me![ValAcct]
        rstValQry.MoveNext
    Wend
    rstValQry.Close
Exit Sub
ErrorHandler:
    Call LogError(Routine, Err.Number, Err.Description)
End Sub

' Located in Modules.Common
Public Sub LogError(fProcedure As String, fErrNum As Integer, fErrMsg As String)
    Dim ErrMsg As String
On Error GoTo ErrorHandler
    'Logging this to a file could be an option and could replace the MsgBox directly below
    ErrMsg = "An Error Has Occurred in" & fProcedure & "  Error (" & fErrNum & "): " & fErrMsg
    Call MsgBox(ErrMsg, vbOKOnly)
Exit Sub
ErrorHandler:
    Call MsgBox("Error in ErrorHandler", "Critical", vbOKOnly)
End Sub[/FONT]

 

[Roemer]

Thanks Dennis.
Based on your Feedback I was able to see one Problem. I have not correctly determined the fields from the recordset. This is fixed and I can see with debug.print the right values populated. But the field on the form ist still not updated correctly, it is now showing always the last record from my query...

        Me![ErrSum] = ""
        strErrSum = ""
        strCieVal = ""
        strAcctVal = ""
        strCieVal = rstValQry!ValComp
        strAcctVal = rstValQry!ValAcct
        '***** Combine Individual Errors Into One Error Message (For the Ones Which Are Relevant / Active) *****
        strErrSum = strCieVal & ";" & strAcctVal
        Debug.Print strErrSum
        Me![ErrSum] = strErrSum
        rstValQry.MoveNext
        Debug.Print Me![ErrSum]

I must be doing something wrong with the "Me![ErrSum] = strErrSum" Statement.
And yes, I know how to use the Debugger

 

[DennisOJensen]

Okay fairly simple really and not an uncommon mistake try this just before the Debug.Print line instead of what you currently have -- that is if I am understanding correctly what you are trying to do

strErrSum = Me![ErrSum] & strCieVal & ";" & strAcctVal

 

[vbaInet]

Just to clarify some points.

I think it is more efficient doing it in the form directly.

It isn't. Performing your calculations in a query will perform better than looping through a recordset

Me![ErrSum] = Me![ValComp] & ";" & Me![ValAcct]

Such calculated values shouldn't be saved unless you're calculating against a huge table and the resulting value doesn't change often.

If you're doing this as a learning exercise then plough on, otherwise, perform the calculation in a query or in the Control Source of a textbox.

Finally, with regards changing the Visible property of certain controls, if the Default View of your form is Single Form then you'll need to add the same code in the Current event of the form. If the Default View is Datasheet or Continuous Forms then then changing the Visible property just won't work if the controls are in the Detail section of the form.

 

[DennisOJensen]

To clarify a point, because sometimes I get my database software confused, one of Microsoft's products has a data type called "Calculated" which means you can add this to your table and it does the "calculation" every time a new record is added or an update to a record that changes a value used in the calculated field. Of course then you are saving the calculated value which as vbaInet pointed out is not the best of ideas.

Of course if the "calculated" data type is not available in Access vbaInet's statement is still solid just create a query to do the calculation as that is much more efficient.

 

[vbaInet]

Yes Access has a Calculated data type and it's an option, but bear in mind that there are several VBA functions that cannot be used in a Calculated field and it doesn't allow User-Defined Functions as well.

 

[Roemer]

@DennisOJensen
Your proposal is actually not what I want to achieve. With this "code" I would collect all Errors in one field.
On my form there will be three rows (each row represents a row from the query). For each row I want to Show the result from the above code. Each field shall Show a different value.

 

[DennisOJensen]

Okay I was just correcting what you had posted -- as what you posted would only give you the last value as you overwrite the first 2

Okay to collect all the Errors into one field you are either going to have concatenate them or you have to have a field that supports multiple rows of data -- neither of which you were doing. Which in lies the confusion I believe.

Now depending on you you want to present this you either need to use a Listbox/Combobox or you can use a Textbox by putting a <CR> between entries thus giving the illusion of "rows". So it kind of depends on what you plan to do with that data once you have it in the format that you are wanting as to which solution is the best to use.