Forms :: Loop For Getting Information From Query Not Working
Sep 17, 2014
I am working on setting up a loop that will get information from a query. I can handle the rest of the code once I get the primary piece of information. Which at this point I can not get. I am trying to get to the next record of my query. For some reason I am stuck on just the first record and it is repeated the exact number of times equal to the number of records in the query. Here is what I have so far.
Code:
Dim FSO As Object
Dim FromPath As String
Dim FromPathCheck As String
Dim ToPath As String
Dim FinalDestination As String
[Code]....
Yes I do know that I am doubled up on the
Code:
rs.MoveNext
The reason is because with out this added rs it goes into an infinite loop. Need generating each value in the TRNumber field in the query.
I am using Access 2010. I have a database that on a form uses a multiselect listbox. That part works just fine. The list box is for selecting additional people to email. Now I have had no luck with returning just the email address that are in a hidden column (the persons actual name is seen and "selected"). The names come from a separate table and is used as a forgien key. On that same table are the indivuals email addresses. What I did was loop through to get all of the ID numbers I am getting from the list box (the ID numbers are stored in the table that the form is based on).
Once I have all of the ID Numbers I thought that maybe there was a way to retrieve all of the email address associated with the ID Numbers. This is what I have so far. I know that AllQuery returns the first email address from the list box. I just have no idea if the query is returning more than one record, or if it is how to then go to the next record. I have tried a few things with little to no success.
Code: Dim ListItem As Variant Dim AllItems As String Dim AllQuery As String For Each ListItem In Me.EmailAdditionEgineers.ItemsSelected AllItems = AllItems & Me.EmailAdditionEgineers.ItemData(ListItem) & " or " Next ListItem AllItems = Left(AllItems, Len(AllItems) - 3) AllQuery = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"
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 am getting information on products stored in a query. I want that query to check a couple tables to see if an item is linked through all of them. If it is not listed in the last table (catalog) I want it to be shown.
Code:Catalog tableVolume Prefixprodno Price15 - CE 0218 9.9915 - CE 0722 3.7215 - CF 0218 12.3615 - CF 0091 14.00Source TableSource MediaCM70904 15 - CE
Code:Batches QueryCustomer Number Prefixprodno Source716933 0218 CM70904716933 0408 CM70904
This is the Select Statement I'm using, which gets the 0218 but not the 0408 I want
Code:SELECT (fieldnames......)FROM [Batches] INNER JOIN ([Source] INNER JOIN [Catalog] ON [Source].[Media] = [Catalog].Volume) ON ([Batches].Source = [Source].[Source]) AND ([Batches].Prefixprodno IN ([Catalog].Prefixprodno))WHERE [Batches].[Customer Number]=716933;
If I do NOT IN I get both 0218 and 0408. I just want 0408. I'm sorry if this seems confusing but any help would be amazing at this point =/
I've created a query called "JobHistoryQuery" and a form called "JobHistoryMultiForm", this displays all Jobs related to an Asset and all the information about that job. There are some fields (BilledMonth, BilledYear, BilledValue, Parts Required, PartsUsed), however, that I would only like to see if I need to see them (as there is quite a lot of information on the form and ideally, I'd like to be able to see the key information from each job from that asset with ease). What I would like is to create a button that says "Billing" and "Parts" for each job and it brings up a pop up window displaying that information.
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String stDocName = "FrmPO_Received" stLinkCriteria = "[ItemID]=" & Me.ItemID DoCmd.OpenForm stDocName, , , stLinkCriteria End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
I am trying to get a form to loop through after a click the next. I have looked around in this site, and ive not found the answers im looking for. What im trying to do is I have navigation records for my form, but when I reach the last record, I would like it to loop.
I want to be able to do something to several labels, e.g. make them invisible, using a FOR loop. They are all named similarly, with ascending numbers on the end, e.g. lblName20
Say I want to make lblName20 to lblName40 invisible. I need something along the lines of:
Code: Dim labelname As String For c = 20 To 40 labelname = "lblName" & c labelname.Visible = False Next c
Of course, that doesn't work, because it doesn't recognize the string as a label name. How do I go about this?
I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:
Code: Private Sub btnCalculateWeight_Click()
Dim rst As Recordset Dim weight As Long Dim material As Long Dim subtype As Long Dim locus As Long
[Code] ....
Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.
My application crashes when trying to change the value of a text box in a continuous form. Here is the code:
Code: Private Sub cboPoCurrency_AfterUpdate() On Error GoTo ErrHandler Dim rst As Recordset
[Code].....
If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes! (This is a desktop application with tables linked to SP lists - not a web app)
I have a subform for which I want to loop through all the records and then copy one of the fields for each row into another sub form.
I came up with the bellow code but i get an error when I run it.
The error is an "error 438".
Code is at follows and I am copying the field called price:
Do Until Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].EOF [Roll Out - Sign items added].Form![Price] = [Roll Out - Sign items pick list].Form![Price] Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].MoveNext Loop
Hi I am creating a stock database and I have come across this problem:
I have a stock table, and I have successfully creted a query to insert a record with values selected in a form:
EG. In the form you can input a part no, comments and the quantity you want to add. I have created the stock system so that each individual item of stock is one record in the stock table.
What I can't seem to do is use the quantity field in the form to dictate how many times the form details is entered into the stock table.??
I have a query that includes fields from T_employee and fields from T_courses, which is courses taught by each employee per semester and T_ Additional, which is additional hours added to employees for each semester. The query groups by employee and has a calculation of the teachers total hours per course based on teaching hours and factors for marking and preparation and adds the additional hours from T_additional.
Each teacher cannot have more than 44 hours, so there are additional calculations that calculate the consequence of subtracting one course from the total hours. The record that is closest to 44 without going over is optimal. In some cases, subtracting one course is not enough to get the hours below 44 and an additional course will need to be subtracted and perhaps a 3rd an so on.
I would like to evaluate which course is the most optimal to subtract and if it is not enough, then to loop through and subtract the next course until the hours are below 44, but as close to 44 as possible.Would a Do loop be what I need? I need to output the total number of hours that were subtracted and the number of courses.
I tried creating a query based on this one, grouping by employee and under the NetHours in the totals row, the max nethours <44. This works but there are still a few records where the nethours is >44. I would like to use a Where in the Totals row and in the Criteria somehow indicate that I want the largest number that is less than 44.
I am trying loop through a datasheet so that I can copy the contents of one field into another form.
I have tried using the bellow code to do this on a button click event. However when i run it I get an error telling me that the object doesn't support this property or method. Im not quite sure what this means.
Dim rst As DAO.Recordset Set rst = Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].RecordsetClone Do Until rst.EOF [Roll Out - Sign items added].Form![Code] = [Roll Out - Sign items pick list].[Form]![Item Category]
In a form I have a button which creates (in preview) an outlook message with a pdf report. It opens and displays just as I want it to, but if I chose to save that message in outlook and either send or cancel (i.e. allowing user to save to drafts and send later) then MS Access crashes.To ensure this wasn't to do with any compile / corruption within my full application I mirrored the process in a very simple 1 table / 1 form / 1 report db and still the same - access crashes after saving the outlook message and either sending the message or cancelling out the message.
Form code is as follows;Private Sub Command0_Click() On Error Resume Next DoCmd.SendObject acSendReport, "Table1", acFormatPDF, "someemaddress", , , , "REQUEST FOR INFORMATION" DoEvents On Error GoTo 0 End Sub
In my main application this process is embedded within a loop to send various filters of the report to different recipients, which works fine if 'save' is not used within Outlook..
New to the navigation form: I need several forms in the navigation form to all relate to the same client ID field. I have this information on the main form but the form with in the navigation form will not pick it up.
I have tried =[Forms]![Frm Client Information Navigation Panel]![ClientID] But just cant get it to pick up that information...
I would like to have something like the master link fields and, child link fields but when I add a form to the navigation form this option in not available.
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()
the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?
Code: Set db = CurrentDb Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab") indexx = 0
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 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?
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code: Function BdMbrNumber() Dim qdf As DAO.QueryDef Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry") 'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry") Dim ORG_NAME As String
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?
