Modules & VBA :: Get All Values For Field In Query
Dec 2, 2013
I've got the following code on a form. The entirety of the sub is listed just in case. The relevant portion is below.
Code:
Private Sub Form_Timer() 'timer set to fire once per minute
'AutoPurge old records (>7yrs) and take action if Hotword and/or NANU expire
Dim Minute As String
Minute = Right(Format(TimeValue(Now()), "hh:mm"), 1)
'Only run these every 10 minutes
[Code] .....
The part I'm having trouble with follows:
Code:
If DCount("*", "qryNotifyExpiredNANU") = 1 Then
MsgBox "The NANU " & DLookup("NANUnumber", "qryNotifyExpiredNANU") & " has expired. It has been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
ElseIf DCount("*", "qryNotifyExpiredNANU") > 1 Then
MsgBox "The NANUs " & !!!!RETRIEVED VALUE HERE!!!! & " and " & !!!!RETRIEVED VALUE HERE!!!! & " have expired. They Have been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
End If
The intent of that (incomplete) ElseIf is to provide a list of all NANUnumbers that have expired. When a single NANU expires, the DLookup works perfectly since the query only has one record. But I'm stumped on dealing with anything more than one value.
The desired result is that the value from the NANUnumber field of each resulting record in qryNotifyExpiredNANU would be used to build a string for use in the MsgBox.
Using DLookup is out (I think) because I can't identify which records the query returned to apply a WHERE clause.
I also looked into using Loop with a RecordSet, but can't figure out how to approach it.
So to sum it up, I don't know how to grab each value present in the query and I also don't know how to concatenate a string with a dynamic number of values.
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
I'm trying to update the account number via an input box with a do loop but I don't have the code right. The at the input box, the user will type in a 4-8 digit number and it will update all the account number fields in a table that are null.
The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.
Code: rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic j = rs.fields(0).Value
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
I have a script that sends a mail using outlook to a different person depending on a value in a field. I want to add values from fields to the subject and body of the mail it creates. This is what I have so far:
Function Mail() On Error GoTo Mail_Err If (Forms![Manage Packages]![Customer] = "MSC") Then DoCmd.SendObject , "", "", "email", "email", "", "[ID] - [Package Name] - Delivered", "Dear," & vbNewLine
[Code] .....
In the subject and body the field ID's I want to add are marked as [ID], [Package name] etc.
I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code: Private Sub ProductID_AfterUpdate() Dim qflPrice As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlQry As String Dim instID As Integer
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box). Are there any ways to query fields with a multi-selection? Thanks, Niels
I am using MS Access 2010 to export data into MS Excel 2010 spreadsheets.
I am just wondering: Is there a way to set the Parameter value for the query via VBA?
For example: There are 10 regional managers. When I click a button on a form, 10 sets of data per manager are going to be exported into Excel spreadsheets.
I have created a saved query named [For exporting] with a parameter [Manager Name] for the field [Master Table].[Manager].
The VBA for the button has 2 subs: 1) Sub 1 for the loop for 10 managers
Code: ... strQuery = "SELECT DISTINCT [Master Table].[Manager] FROM [Master Table] WHERE ((([Master Table].[Manager]) Is Not Null));" Set rstStores = CurrentDb.OpenRecordset(strQuery)
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user 2) Search that Part Number into Table1 3) Take returned Parent Values associated with Part Number and store in Array 4) Modify Array values 5) Search Array values using a For Each loop into the query 6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query. 7) Also return the total rows in a specific column (Order Numbers)
Code: Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Set db = CurrentDb strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN
[Code] ....
The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
I have an Access 2000 project and am trying in a query view to join 3 values into one. 2 of the values come from tables, the third comes from another query view (all linked in the query design screen)
Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty. All values are strings.
I have tried this formula in the query design grid:
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
How can I pass two (2) values to a saved query ? These values are in a form that has a listbox with two (2) columns. The name of the form is 'Previous Evaluation Form'. I'm able to retrieve the values from both columns of the listbox in the form and I've already created the query. Both are working fine, but can figure out how to pass the query's criteria to select records for 'Name' and 'Date' columns of the query. Below is what I had in the 'Criteria:' of the query. What wrong with the code that is placed in the query's 'Criteria:' ?
I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load() If Me.RecordSource <> "" Then If Me.Recordset.RecordCount = 0 Then Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
I am writing a sports database and have a query that displays a seasons fixture list in a form. One of the fields shows the points from each game played. (either 3 for a win, 1 for a draw etc). I can not get these points to be added up and displayed in a form along side the fixture list.
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code: UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = "" WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.
I have tried:
1306 or 1307 or 1308 1306, 1307, 1308 "1306 or 1307 or 1308"
I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.
So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?
I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:
Code:
Option Compare Database Option Explicit Public Function CreateQCChartsforReports() As Boolean Dim qdf As DAO.QueryDef Dim strSQLStatic As String Dim BookName As String Dim BookName2 As String Dim intCounter As Integer Dim cboCode As ComboBox
I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields... Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again. Or, by asking if I can search for field values querying by field name, am I any clearer?
