Modules & VBA :: Using Combo Box To Pull Data Using (Like)
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 Replies
ADVERTISEMENT
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 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
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
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
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
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
Oct 17, 2004
I would like to know if Access can pull data and information from specific websites automatically without me having to enter the data myself each day.
View 6 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
Jul 9, 2013
I posted the following code on a button (report to a first sergeant). I can't pull e-mail addresses from the report (rptLateByUnit).
=========
DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
[Code]......
View 9 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
Jan 23, 2015
I need to first generate a random number between 1-4.
Then I would like to use that number to pull that many records out of the database; the records pulled can be random, or whatever.
For example, we have 4 people and my goal is to choose a random # of records to pull for each person. So, for person #1, it might only pull 1 record, then for person #3, it may pull 2 records, etc.
Ideally, it wouldn't pull the same # twice when run for that "session".
After this, I'm hoping to generate all those results into emails (eg for person 3 it would generate 3 emails, etc)
Currently it's all done manually and I think it's doable with Access.
I have some code already for generating "1" email message, but do not know how to pull X number of emails all at once. (X is the random #).
View 2 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
Jan 13, 2007
We have a user that wants to pull data from multiple oracle tables into one access table. She will probably want to run this process daily. The tables will join easy enough, but I wasn't sure what the best way to go about doing this was.
I haven't used access very much. Again, this is a member of the user community and she is wanting to do this thru access.
Ideally, if it could be automated some way, or set up in some way where the user would not have to do much more than start the process (and not have to redo any "select" statements each time). I would assume some kind of combination of links and import (maybe using a macro?), but again I know very little about access.
Any guidance will be appreciated!
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
May 6, 2013
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile
Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20
Area2FloorPrep (tblFloorPrep)
Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
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
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
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
Jul 17, 2015
We're looking at a way that we can easily display what stage our clients' email marketing campaigns are at - in one section of our CRM our Campaign Manager will enter information on whether the campaign has broadcast (ie: emailed out to the required circulation list(s)), if we're waiting for artwork from the client or if we're chasing for that information.
I have four text boxes (date format) hidden on a subform that I need to pull ONE max date from.
So, as an example:
Email1 (our first email to the client chasing for artwork) = 01/01/2015
Email2 (our second email to the client chasing artwork) = 08/01/2015
Email3 (our third chase email) = 15/01/2015
Broadcast (the date the email campaign was finally sent) = 29/01/2015
I would need some code to show in an unbound textbox "Broadcast: 29/01/2015"
But, on the other hand, if the dates looked like this:
Email1 = 01/01/2015
Email2 = 08/01/2015
Email3 = Null
Broadcast = Null
I would need some code to show the following in an unbound textbox "Last Chased: 08/01/2015"
So we can easily see the status of our marketing campaigns at any stage in the process.
I've tried using IF THEN ELSE statements in the subform's On Current event, but that populates the information from the record you've selected across all the other records on the sub as well.
So I'm wondering if it's worth setting the Control Source of the unbound textbox I want to display the campaign status in as a bunch of nested IIF statements instead?
View 14 Replies
View Related
Sep 4, 2014
I have a form named CustomerForm.I have a query named CustomerQ. On my form I have a combo box named combo6.Combo 6 lists all of the company names from my customer table and includes the autokey field which is hidden.When I click on a value in my combo6 I want the values on my form to then be based on the value from combo6 whereas at the moment I select a value in combo6 and nothing happens other than combo6 now displays a different value. how to refresh/ repopulate the data based on combo6 without having to create more forms and queries.
View 14 Replies
View Related
Jun 15, 2006
I need to pull data from a master project list to auto update other forms. When someone enters a project number I need it to pull the data for that project into another form so they dont need to keep typing details in. The other forms are trackers for our processes to complete the projects.
Let me know what you think...would a subform pull the info automatically somehow?
View 3 Replies
View Related