Modules & VBA :: Loop Through A Query To Show Results On Form
Jun 28, 2015
I basically want to Loop through 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)
[Code] ....
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.
I am trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlStr As String
[Code] .....
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
We have a button running a SQL query via VBA, how can I make it so the results doesn't show up in a table/preview?Also, I know it's for the SQL forum but how can I make a text box to show the results of a query to display it on screen?
I have a form that I am trying to show in a text box the results of a count query. The query has two fields in which one is a check box. The criteria for the text box field is set to true to list all that are checked. I want the query to show all the records that are true. This works if I run the query, but the result isn't showing in my text box on the form. On the form text box properties in Control source I have =DLookUp("[Run Number]","Control Room Drumctqry"). Run Number is the field that I am counting, Control Room Drumctqry is the query. Any idea as to what I am doing wrong? Thanks,
I have a form that I am trying to show in a text box the results of a count query. The query has two fields in which one is a check box. The criteria for the text box field is set to true to list all that are checked. I want the query to show all the records that are true. This works if I run the query, but the result isn't showing in my text box on the form. On the form text box properties in Control source I have =DLookUp("[Run Number]","Control Room Drumctqry"). Run Number is the field that I am counting, Control Room Drumctqry is the query. Any idea as to what I am doing wrong? Thanks,
I created a search form. It has 6 unbound text boxes and 2 combo boxes in the header. Users can select values from the combo boxes and/or enter names in the other text boxes. These values all go into a filter on my Main table and the filtered results show up in the detail section. That used to work fine.
Now, I've been trying to convert the filter into a query and show the query results in the detail section instead. (Why? Because of the ever-changing business requirements, of course!)
For some reason, the detail section went blank. All white. When I change the Data Entry property to No, it fixes that.
However, the text boxes for entering the search criteria will not accept any values anymore. It's like they are disabled.
When I change Data Entry property to Yes, I can enter text into the text boxes again. But the detail section blanks out again.
I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"
To elaborate: CB1 contains the following values
Code : elephant, giraffe, bufffalo, tiger, lion
Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals
Code : **SELECT elephant, tiger FROM dbo_animals**
Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as
tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)
It looks like
Code : SELECT Elephant from dbo_animals
This is how I want. But, since i am looping through CB controls, I have it set up as
Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"
and this shows up as
Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals
Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.
This makes sense, since it is unable to evaluate the text as a reference.
How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
A search form, to search into those tables (It searches by "User")
The search form contains a listbox that shows results (listPC)
And the following code:
Option Compare Database Dim strUserPC As String, strUserTel As String Dim db As DAO.Database, rsUserPC As DAO.Recordset ', rsUserTel As DAO.Recordset Private Sub txtSearch_LostFocus() strUserPC = "" strUserTel = ""
[Code] .....
It works, but I have one problem. It only shows telephones or desktop, not both. It deppends on which line inside UpdateList is first.
That example searches into 2 tables:
-Telephones -Desktops
And searches by "user".
I want to list all telephones and desktops that a user has assigned.
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
I have a list-box(Category) and a 'ok' button on the form.
My OK button has the following [Event Procedure]: Private Sub OK_Click() Me.Visible = False DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];" End Sub
First of all, is my syntax correct in the above codes? Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?
creating a searching form and to show the results inside the form! It's like a Library type searching. u want to search for some type of monument or so and it shows the results, all the info, photos and that!
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
I have a query that is search for fields in a table that are either
1 - High 2 - Medium 3 - Low
I have a query that counts these and then puts the results into pie charts on a report.
However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.
This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.
Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.
I have about 25 text boxes that get populated based on a financial transaction; quite often about half of them are empty. i wanted to gray them out if they are emtpy (.enabled = false). I could put a series of IF statements in the vba for each one, but its bulky and time-consuming. I've done for each record in tables and recordsets. Is there a way to do a for each textbox on a form?
In my database, I have a continuous form with a Name, a Date and a Yes/No field.
When the form opens, I want to look at the date of every record on the form and show a message box if it is before the current day.
The code I have is this;
Private Sub Form_Load() With Me.RecordsetClone While Not .EOF If Me.Date1 < Date Then MsgBox "" & Me.Person & "" End If If Not .EOF Then .MoveNext Wend End With End Sub
However, it loops just the first record the amount of times there is of records (i.e., it will only show the first person's name in the message box, and will show 3 times if there are 3 records).
We have a survey form we are trying to get some information from, and I think I need a loop to get the information, but there isn't a ton of information that I have found for what I need to do.
I have a six question survey that has a range of answers and a comments section (I didn't write the survey):
I have a simple query that will separate out the overall Positive and Negative reviews. The overall "Positive" or "Negative" ranking is manually assigned via a combo box on a form from a value list. Poor comments in the comments field can equate to a negative survey, even if questions 1-6 are neutral or better (good, very good, etc.)
What we would like to do is to poll through the responses to the survey and see how many of question 1-6 are consistently receiving Neutral or lower marks.
In my head, it makes sense to count through all the surveys and get a number of those responses. The response table (tblResponses) is setup with a combo box from a look up table that has the above responses. Actually, there are two lookup tables for this...one for question one, and one for questions two - six.
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName FROM ItemBatch WHERE (((ItemBatch.Finished)=False) AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:
Code: Private Sub doDataSegm_Click() Dim dbs As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb() Set rs = dbs.OpenRecordset("Table1", dbOpenTable)
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.
Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2
Dim strsql As String Dim strsql2 As String Dim strfilename As String Dim strpath As String strpath = "C:UsersDesktopHome"
I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.
So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days)) 2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register' SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register') INSERT INTO tbl_Register ( PostDate, Description, Amount ) SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];
And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop 2) A button to start the process
It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).
I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.
Public Sub InsertTransactionsBttn_Click() Dim db As Database Dim qdfNew As QueryDef Dim qdfInsertTransactions As QueryDef Set db = CurrentDb()
