Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-25-2018, 04:22 PM   #1
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
VBA Code to loop thorugh DAO recordset with pre-existing query

Hi all,


I really have tried to figure this out but can't. I think Google may ban me soon!! (jk)


I am building a budget database and have a expense entry form that I want to populate some text controls with data from a pre-existing query to let the user know how much was spent in the category they chose from a combo box so far in the current month. The data pieces I want from the pre-existing query (which works fine) are: the category budget for the month and how much is remaining (or isn't). The query by itself always pulls up at least 40 records so there are plenty of rows to go through.


I have written the code below but it will not populate the controls I put an incrementing counter "i" in to see how far the do while loop gets. When I get an error the counter is at zero o it's never reaching the incrementing assignment. I know there's a hundred ways to skin a cat (can we say that anymore?) but I only need one. Any suggestions would be appreciated.

Here's the code:

Private Sub cboCategoryName_AfterUpdate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim errtext As String
Dim i As Integer
On Error GoTo Err_Name
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryCurrentMonthSpend", dbOpenDynaset)
rst.MoveFirst
i = 0
Do While Not rst.EOF
If rst![Category] = Me.cboCategoryName Then
Me.[txtCategory] = rst![Category]
Me.[txtCurrentSpend] = rst![Spent]
Me.[txtCategoryBudget] = rst![Budget Amount]
Me.[txtBudgetRemaining] = rst![Budget Amount] - rst![Spent]
'we got what was needed. Now exit
Exit Do
rst.MoveNext
i = i + 1
End If
Loop
' Cleanup
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub
Err_Name:
MsgBox ("There was an error! " & "i Value = " & i)
End Sub

Any suggestions? Be kind, I'm still a newbie :-)

Thanks!

-tmguru

tmguru is offline   Reply With Quote
Old 04-25-2018, 06:18 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

You can specifically find the record you want without looping:

Set rst = dbs.OpenRecordset("select category, spent, [budget amount], [budget amount]-spent as exp1 from qryCurrentMonthSpend where category=""" & me.cboCategoryName & """", dbOpenDynaset)
If not (rst.bof and rst.eof) then
Rst.movefirst
Me.[txtCategory] = rst![Category]
Me.[txtCurrentSpend] = rst![Spent]
Me.[txtCategoryBudget] = rst![Budget Amount]
Me.[txtBudgetRemaining] = rst![exp1]
End if
Rst.close
Set rst=nothing
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following 2 Users Say Thank You to arnelgp For This Useful Post:
tmguru (04-29-2018), Voyager (04-28-2018)
Old 04-25-2018, 09:47 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,361
Thanks: 2
Thanked 1,973 Times in 1,929 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

arnelgp gives you a good solution, but if you want to stick with you code, the look at the comments I've added.
Code:
Private Sub cboCategoryName_AfterUpdate()
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim errtext As String
  Dim i As Integer
  'On Error GoTo Err_Name 'Comment out the errorhandler until the code runs okay, else you can't see which codeline cause the problem
  
  Set dbs = CurrentDb()
  Set rst = dbs.OpenRecordset("qryCurrentMonthSpend", dbOpenDynaset)
  'rst.MoveFirst 'You don't need MoveFirst, it would cause an error if the query doesn't return any records
  i = 0
  Do While Not rst.EOF
    If rst![Category] = Me.cboCategoryName Then
      Me.[txtCategory] = rst![Category]
      Me.[txtCurrentSpend] = rst![Spent]
      Me.[txtCategoryBudget] = rst![Budget Amount]
      Me.[txtBudgetRemaining] = rst![Budget Amount] - rst![Spent]
      'we got what was needed. Now exit
      Exit Do
    End If  'You need to have the End IF here, else you'll never move to next record if the first record isn't equal to cboCategoryName
    rst.MoveNext
    i = i + 1
    'End If  'Is in wrong place.
  Loop
  ' Cleanup
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Sub

Err_Name:
  MsgBox ("There was an error! " & "i Value = " & i)
End Sub

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is online now   Reply With Quote
Old 04-26-2018, 05:33 AM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I would definitely go with arnel's solution here, and open exactly the recordset you need, rather than open an overly broad recordset and loop thru it to find what you need. I would approach it like this....
Code:
Private Sub cboCategoryName_AfterUpdate()
    Const SQL As String = _
        "SELECT Category, Spent, BudgetAmount, BudgetAmount - Spent as BudgetRemaining " & _
        "FROM qryCurrentMonthSpend " & _
        "WHERE Category = p0 "
  
    With CurrentDb.CreateQueryDef("", SQL)  'here we use a temp QueryDef
        .Parameters(0) = Me.cboCategoryName 'so we can paramterize the SQL
        With .OpenRecordset                 'and open the exact recordset we need
            If Not .EOF Then                'then, if there is data, we use it.
                Me.txtCategory = !Category
                Me.txtCurrentSpend = !Spent
                Me.txtCategoryBudget = !BudgetAmount
                Me.txtBudgetRemaining = !BudgetRemaining
            End If
            .Close
        End With
        .Close
    End With

End Sub
...but the main thing is I would avoid the loop.
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
tmguru (04-29-2018)
Old 04-26-2018, 06:12 AM   #5
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

arnelgp & JHB

Thank you for your suggestions! I do like the simplicity of arnelgp's idea but I was curious why my code wasn't working so JHB I appreciate you pointing out what was wrong.

Arniegp - I tried your code but got an error 'Too few parameters' - see the attached docx.

JHB - I fixed my code per your suggestions and it ran without error ( I commented out the error handler for now) but it did not populate any of the text fields.

I believe the problem may be related to the actual content of the combo box cboCategoryName. All of my combo boxes were created by using the form wizard and dragging the fields onto the form while combo box was selected in design view. Access created the queries associated with each. In the initial creation of the data base I allowed access to split an Excel spreadsheet into various tables and it also created the relationships and queries. My frmAddExpense form has been working perfectly as it populates several tables and maintains the relationship integrity so I'm loathe to change it but I think the way Access populates the combo box from the dropdown may be preventing the code from working. I'm pretty sure the clue is in the query access created for the dropdown but I'm not knowledgeable enough yet to see it or know why.

Another clue is that if I select a category using only a dropdown choice the code does not execute (I'm assuming it does not recognize that a change or update was made. I know this because arniegp's code (at least for now) breaks out ONLY when I have started an entry by typing but does not break out if I don't type and just use the dropdown. This is consistent with when I was testing my own code before I posted my issue. Even typing the entire Category name using JHB's mods still does not populate the text fields. I have tried OnChange and OnUpdate but neither one made a difference in skipping over the code if only using the dropdown.

When the frmAddExpense form opens I use a macro to create a New Record as that is the only activity this form is used for. After the record is Saved, I also create a New Record.

I've attached a screenshot of the properties view with the row source select code for cboCategoryName combo box. I haven't changed anything on the data properties tab since Access initially created it. Hopefully this will help someone more experienced than I to see what's going on. In case the row query is too small too read in the jpg, here it is:

SELECT [ID] AS xyz_ID_xyz, [Category] AS xyz_DispExpr_xyz, [Category] FROM tblCategory ORDER BY [Category];

In case you wonder what the label inside cboCategoryName tblCategory_ID is I have no idea. It must be something access uses related the query and table tbl Category and the field ID but there is no actual table called tblCategory_ID

Thanks again for your help so far! I think we're very close :-)

-tmguru
Attached Images
File Type: jpg frmAddExpense design view.jpg (97.3 KB, 14 views)
Attached Files
File Type: docx Too Few Parameters.docx (52.9 KB, 11 views)
tmguru is offline   Reply With Quote
Old 04-26-2018, 06:16 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

change it to this:

Set rst = dbs.OpenRecordset("select category, spent, [budget amount], [budget amount]-spent as exp1 from qryCurrentMonthSpend where category=""" & me.cboCategoryName.column(1) & """", dbOpenDynaset)
If not (rst.bof and rst.eof) then
Rst.movefirst
Me.[txtCategory] = rst![Category]
Me.[txtCurrentSpend] = rst![Spent]
Me.[txtCategoryBudget] = rst![Budget Amount]
Me.[txtBudgetRemaining] = rst![exp1]
End if
Rst.close
Set rst=nothing
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 06:32 AM   #7
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Hi arnelgp, I copied and pasted the new code and still got the same error on the same line.

-tmguru

tmguru is offline   Reply With Quote
Old 04-26-2018, 06:33 AM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Also check if qryCurrentMonthSpend query is using Form control as criteria, youll have to open that form for the code to work.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 06:45 AM   #9
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Hi MarkK,

I just saw your reply and tried the code as a verbatim replacement for mine. I got an error similar to the one I got using arnelgp's code. See attached docx.

Thanks!

-tmguru
Attached Files
File Type: docx MarkK.docx (47.6 KB, 5 views)
tmguru is offline   Reply With Quote
Old 04-26-2018, 06:59 AM   #10
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Hi arnelgp,

I'm not exactly familiar with what you are asking for (do you mean a Like statement?). Here's the sql for qryCurrentMonthSpend, which is dependent on qryExpenseDetail so the sql for that is below too. What and where do you suggest I change something?

qryCurrentMonthSpend:

SELECT DISTINCTROW qryExpenseDetail.Category, Format$([qryExpenseDetail].[DatePurchased],'mmm yyyy') AS [Month/Year], Sum(qryExpenseDetail.Price) AS Spent, Max(tblCategory.Budget) AS [Budget Amt]
FROM (tblCategory INNER JOIN tblItems ON tblCategory.[ID] = tblItems.[tblCategory_ID]) INNER JOIN qryExpenseDetail ON tblItems.[ID] = qryExpenseDetail.[tblItems_ID]
WHERE (((qryExpenseDetail.DatePurchased) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY qryExpenseDetail.Category, Format$([qryExpenseDetail].[DatePurchased],'mmm yyyy'), Year([qryExpenseDetail].[DatePurchased]);

qryExpenseDetail:

SELECT Table2.ID, Table2.tblItems_ID, Table2.DatePurchased, tblItems.Item, Table2.Price, tblSupplier.Supplier, tblCategory.Category
FROM (Table2 LEFT JOIN tblSupplier ON Table2.[tblSupplier_ID] = tblSupplier.[ID]) LEFT JOIN (tblItems LEFT JOIN tblCategory ON tblItems.[tblCategory_ID] = tblCategory.[ID]) ON Table2.[tblItems_ID] = tblItems.[ID]
ORDER BY Table2.DatePurchased DESC;

Thanks

-tmguru
tmguru is offline   Reply With Quote
Old 04-26-2018, 07:31 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Me.[txtCategory] = me.cbocategoryname.column(1)
Me.[txtCurrentSpend] = dsum("spent", "qryExpenseDetail", "category='"& me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
Me.[txtCategoryBudget] = dmax("[budget amount]", "qryExpenseDetail", "category='"& me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
Me.[txtBudgetRemaining] = [txtCategoryBudget] - [txtCurrentSpend]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 02:42 PM   #12
tmguru
Newly Registered User
 
Join Date: Apr 2018
Posts: 19
Thanks: 8
Thanked 0 Times in 0 Posts
tmguru is on a distinguished road
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Hi arnelgp,

Unfortunately the procedure is still having the same too few parameters error. I attached a .docx with a screenshot of the error and a screenshot of the debug screen that followed, Here is the full procedure with your latest code:

Private Sub cboCategoryName_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset
Dim exp1 As Currency
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("select category, spent, [budget amount], [budget amount]-spent as exp1 from qryCurrentMonthSpend where category=""" & Me.cboCategoryName.Column(1) & """", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Me.[txtCategory] = Me.cboCategoryName.Column(1)
Me.[txtCurrentSpend] = DSum("spent", "qryExpenseDetail", "category='" & Me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
Me.[txtCategoryBudget] = DMax("[budget amount]", "qryExpenseDetail", "category='" & Me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
Me.[txtBudgetRemaining] = [txtCategoryBudget] - [txtCurrentSpend]
End If
rst.Close
Set rst = Nothing
End Sub

There is one other thing happening in the frmNewExpense form that I wasn't expecting so I thought I'd mention it to see if it is a clue. As soon as an item is entered in the cboItemName combo box, the cboCategoryName combo populates with what I assume is the last CategoryName used for that item. For example if "Batteries" is entered in the cboItemName combo box via the dropdown or typed in, the Category "Home Improvements" automatically populates in the cboCategoryName. This is a time saver but I did nothing to make that happen. Could the code / query associated with cboItemName be conflicting with the procedure in cboCategoryName. I wouldn't think so because whatever is auto-populated can be replaced with something else typed in. I just wanted to mention it.

I really do appreciate your time in helping to try and solve this!

-tmguru
tmguru is offline   Reply With Quote
Old 04-26-2018, 02:53 PM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,251
Thanks: 74
Thanked 1,403 Times in 1,324 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Quote:
Originally Posted by tmguru View Post
Code:
Private Sub cboCategoryName_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset
Dim exp1 As Currency
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("select category, spent, [budget amount], [budget amount]-spent as exp1 from qryCurrentMonthSpend where category=""" & Me.cboCategoryName.Column(1) & """", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
    rst.MoveFirst
    Me.[txtCategory] = Me.cboCategoryName.Column(1)
    Me.[txtCurrentSpend] = DSum("spent", "qryExpenseDetail", "category='" & Me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
    Me.[txtCategoryBudget] = DMax("[budget amount]", "qryExpenseDetail", "category='" & Me.cboCategoryName.Column(1) & "' and datepurchased between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())+1,0)")
    Me.[txtBudgetRemaining] = [txtCategoryBudget] - [txtCurrentSpend]
End If
rst.Close
Set rst = Nothing
End Sub
Why do you open the recordset at all? You don't use it.

I have not looked closely at the code but I am pretty sure the whole process could be done a lot more efficiently in a query rather than all those Domain functions.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
tmguru (04-29-2018)
Old 04-26-2018, 05:47 PM   #14
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

You dont need to use a recordset we already getting the data via dsum() and dmax().
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 08:03 PM   #15
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,361
Thanks: 2
Thanked 1,973 Times in 1,929 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: VBA Code to loop thorugh DAO recordset with pre-existing query

Quote:
Originally Posted by tmguru View Post
.. Even typing the entire Category name using JHB's mods still does not populate the text fields. I have tried OnChange and OnUpdate but neither one made a difference in skipping over the code if only using the dropdown.
..
Could you post your database with some sample data, (do a Compact & Repair before, then Zip it)?

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is online now   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
tmguru (04-29-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Query Data From Access into an Existing Workbook need to tweak existing code SunnyG Modules & VBA 2 09-21-2015 08:19 AM
Loop through recordset and run query CanWest Modules & VBA 1 07-23-2011 09:39 PM
Where clause in a pass thorugh query djcritter Queries 3 07-19-2006 01:02 PM
Query an existing queried recordset Grandchester General 2 11-14-2002 12:47 PM
Query by Recordset in a loop OSCSLeo Queries 10 12-11-2001 11:38 AM




All times are GMT -8. The time now is 03:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World