Using Multi-Line Text Box To Pull Specific Records To A Report
Oct 23, 2014
I'm trying to have users enter multiple IDs from a table into a text box separated by newline chars, and then (once they press a button) have some sort of macro or code read each line and pull a report I've created based on the ID. Is that possible?
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
I'm a noobie with Access, and on my form which displays Account information, I also want to display the multiple Addresses this account can have. I can't find any type of scrollable text box (something to look like a spreadsheet), and I know it's not a list box I need to use.
How do I create this and what is it called? I tried using a data grid but I dont think that is it either :confused:
Also, my scrolling text icon on the toolbox is greyed out, not sure why.
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
In the Owner Name field, I am looking for a way to pull out all estates. They are abbreviated with "EST" or "Estate". The problem is throughout the database, if you do a simple filter for "contains est" it will include names such as "Forester" or "LHB Investments".
How do I query, or filter this database such that it only returns the estates abbreviated with "EST" or "Estate"?
I have a form where a user can select from a listbox (pulled from a database) and "Add product" - it then goes into a Textbox, each taking a new line when "Add product" button pressed. Then when they press "Add Customer" those details along with other entered details gets stored in my Customer Database..
The textbox store multi-line entries in the "ProductName" field heading in database.
Is there anyway i can use a List instead of the textbox? Adding individual entries to one certain customer? I have an EDIT facility aswell so would be easier to have a "Remove Product" button instead of manually backspacing a whole product entry from another textbox (containing all products, on new lines).
I created a a table with multiple columns. For example the table looks like as follows:
Table name - Observationcode Code Value Description CCH 1000 Heavy Crack CCM 750 Medium Crack CCL 500 Low Crack
I created fields on the subform - frmsubmain1 Field names are as follows:
Observation Code Value Description
I created combobox for a field called "observation code" so that the above mentioned table "observationcode" appear as a pull down menu to select from multiple lists. In the property section I bound the feild "Observation Code" to " column 1" to record the item in the column 1. I reapted this process for the fields "Value" but this time I bound the field to column 2 to show the value rate or value for the selected code. Again I reapeated this process for field name called "Description" but this time I bound the column to the column 3 to record the description for the corresponding code.
On the main form - frmmain
I created a field called Rate to calculate the total value in the subform "frmsubmain1" the validation rule looks like as follows: =sum(Forms![frmsubmain1]![Value])
None of these seems like working except for the first one "Observation code". Observation code is looking into the right column and registering the right code but not the other two fields are not looking to the right columns, instead they are looking into the firts coloumn. I don't know why. Can anyone help me resolve these issues. I appreciate your help. Thanks.
I want to use a Form or Report to have the end user enter say a Customer # or the Customer Last Name and then have Access pull and display that record so that the end user can than print all the saved information from that record.
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title - Document Number (unique identifier) - Responsible - DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset Dim rs3 As DAO.Recordset Dim db As DAO.Database Dim strSQL1 As String Dim strSQL2 As String
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
On my first form I have a pull down box and when I select an item and click the add button, the item appears in a locked text box above the pull down box. After I click the submit form button, it goes to another form. On the other form is a similar pull down box and locked text box. So far I got the item selected on the first form to appear in the second form's locked text box. Again, the second form also has a pull down menu and when I select another item from the pull down box and click the add button on the second form it deletes the first item selected in the first form from the second form's locked text box. The items selected are put in a table. I was wondering if there was some kind of code I can input so the second form's locked text box keeps the first value from the first form and can add additional values when selected through the second form's pull down box. Sorry if this sounds confusing. I really appreciate your help. Thank You.
I have a text field in a Table and on a Query called "Notes" In that field that has data like below:
[04/02/2015:BD] Project is to be assessed by Solutions Planning [03/27/2015:BD] Project prioritized [03/14/15:BR] Entered to system
Im trying to find a way to pull just the most recent line of text, in this case
[04/02/2015:BD] Project is to be assessed by Solutions Planning
into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.
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 & _
I have 2 reports that use a separate query to pull their data. One Query is called "Outdoor Lighting by Model Number Full Query".It is very simple, pulling just 4 pieces of data, SQL code is as follows:
SELECT DISTINCT [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between [Forms]![Date Range Entry]![Start Date] And [Forms]![Date Range Entry]![End Date]) AND (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
I run a report named "Outdoor Lighting by Model Full Report" which takes the above query. The detail portion of the report only has 3 sections:
Model #, Quantity, Percentage. Quantity I have showing "Sum Of Quantity" and Percentage is showing "=[Sum of Quantity]/Sum([Sum of Quantity])" Therefore, for a particular model # it gives me the number and then calculates what percentage that number is of the total. Works fine.
My second report is laid out exactly the same, except it pulls data from a different query that only returns the top five results. The code for that query is as follows:
SELECT TOP 5 [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between Forms![Date Range Entry]![Start Date] And Forms![Date Range Entry]![End Date]) And (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
The second report, appropriately named "Outdoor Lighting by Model Number Top 5 Report" only shows the top 5 and then gives a quantity and calculates a percentage. My problem is that the percentage is calculated based off of the total of just the top 5 query. I need it to calculate based off of the total of the full query.
I currently have percentage doing this "=[Sum of Quantity(Top 5 Query)]/Sum([Sum of Quantity(Top 5 Query)])".What I want it to do is this "=[Sum of Quantity(Top 5 Query]/Sum([Sum of Quantity(Full Report Query)])
Looking for an automation solution currently I have a database for client progress and prescriptions. when the file is opened it is set for data entry so that a clean note form is opened. I have a button that pulls up past notes for their review. it has been requested that there be a way to pull the last narative field forward to the new record for editing as a number of the features of the narative remain the same. Currently the only way is for them to open the last seen note hightlight the field and past into the new note. to high level for some of them. Need to make this an automated feature where they would just click on a button and the process would all occur behind the scean and the note would apper in the new note narative box. I know that I could tell the form to not be for data entry but then they write over the last note. this needs to be a new record. Any suggestions greatly appreciated J
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: 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.
I have an Access database where we grade reports. At the end of the grading form I have a memo field [Comments] for the grader to enter comments about why the report received the grade [Grade]. Each report is graded by multiple people. So for example, ReportNum 08-001 may be graded by 5 people and therefore will have 5 records in the database. I need a report to show all the comments for all the reports where the grade is <80. I know how to do this except for how to display all the comments from the memo field in the 5 records on one report for that ReportNum.
I have a slight problem here with a database program I am designing. I have two excel spreadsheets, one created by one department here, and the other created by a second department.
I have the access program importing the relevant fields from both programs into 2 tables. Table 1 is the listing of employees in the company and their info (minus the location where they work). Table 2 is the listing of employees who are in this facility.
I need to prepare a report of all employees in this facility who appear on the other table.
I have a field in each table (both called F1) with the employee names.
Is there a way I can run a report in Access where it pulls all the info I need into a Report as long as the name appears in both tables F1 fields?
Even if I can create another table based on that info, and then just do a full dump to a report from that new table, that would be great.
I am not familiar with VB or SQL really, but I figure something like this would be doable, I just don't know how to go about writing it :
If Table1.F1 == Table2.F1 Then { Append Table1.* INTO NewTable3 }
Something where if the name on F1 in Table1 matches the name on F1 in Table2, it takes all the data from that record and appends it or dumps it brand new into a NewTable3..
Here is my delima, I have a report that I am able to type in a keyword and it will pull up that report exactly as I type the keyword, however, let's say that I want to pull up someone else's report I cannot. I have to exit out of my report and then go back into it and resubmit the new search.
Hiya all, I know the title is confusing, and so is the problem (to me at least). Basically, I have a database with an invoice form. Each record is an invoice, and on this form there is a subform of additional charges. The tables are linked by InvoiceID. It worked smashingly when I was playing around with it, until I finally tried an invoice with no additional charges. The queries for my reports display no records (because there are none). This is an issue, because I need a summed up amount of the charges to complete the invoice. I cannot do this on the report, because additional expressions are used on the charges.
Basically, my problem comes down to this: I have no additional charges, so certain fields in my query don't exist, therefore the entire query record won't display. I was screwing around with NZ() and IsNull() before I realized that a null record and the absence of a record are different.
I've been searching, but can't seem to find the right keywords to produce a fruitful search. If anyone can point me in the right direction or give me some words of advice, I'd greatly appreciate it. And yes, as far as I know, my database is fully normalized.
