Modules & VBA :: Pull Data From Query (or Table)
May 4, 2015
I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.
I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.
Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.
How do I do this?
View Replies
ADVERTISEMENT
Oct 31, 2013
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
View 3 Replies
View Related
Jul 3, 2014
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly
View 14 Replies
View Related
Jul 17, 2015
What I want the form to do is filter on a column where I have combined 3 columns of actives together. So the form will filter if one of those values is any of those three columns. So I have a macro setup right now trying to filter for anything "Like" what has been selected in the combo box. Here is what I have in the where condition of the filter macro:
Code:
((([Active1] & " " & [Active2] & " " & [Active3]) Like "*" & "[me.cboactives]" & "*"))="'" & [Screen].[ActiveControl] & "'"
This is in Access 2013
View 2 Replies
View Related
Jul 27, 2006
I have a table that contains the following:
Code BrandName Lead Free Nickel Free
001 AAAA Yes Yes
002 AAAA Yes No
On a form the user selects the code field and in this table the field is called BRAND. On the same form, I need to display the value in the Nickel Free field so if selects 001, the field on the form needs to be "Yes", if the user selects "002", the value needs to be "No"
View 1 Replies
View Related
Dec 30, 2014
I have two different database files. One is 2010 ".accdb" format where I have created a form and the inputs to the form is getting saved as records to an access.mdb file in a shared path.Now if the users want to edit the existing record I should allow them to search their previously submitted record with a unique ID number.
I know it is possible when we have both the form and table in the same db. But I want to know whether it is possible to search with a unique ID and pull the data from different db in a shared path using a command button?
View 5 Replies
View Related
Apr 28, 2006
Hello Everyone,
I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out.
Thanks so much for any assistance!
Amy (monet1369);)
View 6 Replies
View Related
Mar 29, 2006
v sorry for the basic question, but ive been banging away at access and i cant my head around this..
i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.
heres my table structure...
Table A
Doc ID (PK)
Doc No (manually input and is the search item that users enter)
Table B
Unique ID (PK)
Doc No (FK)
Info (to populate field 1)
Info (to populate field 2)
how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?
thanks guys
View 1 Replies
View Related
Mar 26, 2015
I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.
as i already have data in my DB that i need i cannot just reset any fields
having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database
View 3 Replies
View Related
Nov 10, 2013
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
View 3 Replies
View Related
Sep 16, 2013
Is it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
View 2 Replies
View Related
Feb 5, 2013
I have built a nice database that has a form to enter data which logs in product received, there is a combo box on the same form that is linked through the query builder to auto populate the names from the contacts info table (the receivers of the product received) the contacts info table also contains information that is specific to each name such as locations.
As of now I have created a command button that brings me to the form that shows the information fields I need specific to a name, once I get that I have another command button to bring me back to the main form. How to create an additional list box on the main data entry form so as when the name is entered the new list box or text box (which is best?) will auto populate the information I need on one form instead of going back and forth.
Example:
Requester Name [ auto populate name ] currently linked to contacts info table (working)
(New field) Preferred Location [ need to auto populate location ] from the contacts info table (how do you pull locations specific to a contact name from the same table?
View 3 Replies
View Related
Sep 26, 2007
Hello,
I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working.
I have tried
[forms]![form_name]![subform_name]![textbox_name]
[forms]![form_name]![subform_name].[form]![textbox_name]
[forms]![form_name]![subform_name].[form].[textbox_name]
Nothing works. What is the correct syntax for this? Any help would be great !!! Thank you in advance.
Joe
View 2 Replies
View Related
May 21, 2015
I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:
Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question
Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields
AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field
The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:
Code:
DoCmd.OpenQuery "LoadAuditQuestions"
Me.AuditQuestions.Requery
The SQL for LoadAuditQuestions is
Code:
INSERT INTO AuditQuestions ( AuditKey, QNum, Question )
SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question
FROM Questions
ORDER BY Questions.QNum;
I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly.
The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.
View 3 Replies
View Related
Mar 13, 2014
I have 9 seperate tables - each of the tables has similar headers
Claim #
Agent
Pass/Fail
Request type
Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
View 2 Replies
View Related
Nov 13, 2013
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code:
DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
View 5 Replies
View Related
Dec 27, 2013
I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
View 5 Replies
View Related
Aug 4, 2015
I am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.
Here are the conditions:
Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight
Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable
Good:
Lower Acceptable<AW<Upper Acceptable
The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.
Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..
Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))
View 6 Replies
View Related
Apr 30, 2015
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;
View 5 Replies
View Related
Jun 5, 2013
I have a function that when called transfers a query recordset to an excel spreadsheet then emails it. At the end of the function I use code to write the date sent to a table. Each time the function is called I only need records in the query that have been modified since the last time the function was called. I have a field in the query 'LastModified' with a criteria '>[Enter Date]'. I then look up the date in the table and enter it manually. I know how to look up the last date sent in table using code but getting the >#SomeDate# in the query with VBA.
Code:
DoCmd.TransferSpreadsheet acExport, , "qryUpdateWebmaster", _
"C:SubmarinersUpdates_Sent" & fname & ".xlsx", True, "Webmaster_Update"
View 1 Replies
View Related
Dec 3, 2013
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
[Code].....
View 3 Replies
View Related
Nov 14, 2006
Hi,
I'm totally newbie so please go easy on me, I finally learnt how to make database and tables by reading online tutorials. I'm now able to create a simple database in access, using tables and stuff. I want to ask, say i put 500 entries in my database and then i want to search for some specific data, how do i do that?
Say I'm making a database of model's agencies, and i want to pull all the models working in same agency and stuff like that, how do i do that. Do i have to create queries or forms? i'm confused, please help.
Tania.
View 1 Replies
View Related
Jan 29, 2006
I am trying to have pull down information on my forms so I don't have to reimput my data twice, but I can't remember how this is done.
I am working on a database for my school and I need to input the names once. Then after just the marks.
Any good tutorials on access?
Thanks::cool:
View 3 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Jan 3, 2014
I have a series of IDs in an 'articles' table stored as text, e.g.
hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027
and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.
The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.
Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:
Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))
Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant
[code]....
View 3 Replies
View Related
May 5, 2005
I'm working in Access 97 (though I can use Access2000) and Outlook2000 and am trying to write a process that will go into a specified folder in a user's outlook and pull in the data from the emails there. The data will be in a standard csv format - but my biggest problem is trying to figure out how to systematically go into the folder, import the data from the email, and then move to the next email in the folder...
Any help would be much appreciated!
View 2 Replies
View Related
