Modules & VBA :: How To Execute Free Text Search In Linked Documents
Apr 10, 2014
I have a table which lists all documents that refer to a certain entity. the table contains the file names and paths. I would like the user to be able to search for text inside these documents.Can I use Windows Search for that by using code? Is there any other way?
The correct way is the following: Inc# 2546 ^ capital I, no space between "c" and "#" but a space AFTER the "#"
Then the 4 digit long number.I was thinking along the lines of trying to identify the number, removing everything else and then placing "Inc# " before it... currently we allow notes, which should be made like such: Inc# 2546 - Notes.however, people have their own method for this too (however most of them are at least after then incident number so that makes things much easier)another issue is on the same database, some manual requests also appear which have different entry format: RQ# GK034LW2052 but that contains a mixture of letters and numbers... so when identifying the request number... as long as I make sure that it is a number and not a string of numbers and letters, I should be ok..
I've been trying to search for all picture files in my documents and copy them to a folder on the desktop. I found this and it work great for searching however I'm having trouble getting the copy to work. [URL] ....
Code: Dim colFiles As New Collection RecursiveDir colFiles, "C:Photos", "*.jpg", True Dim vFile As Variant For Each vFile In colFiles Debug.Print vFile Next vFile
Code:
I replaced the debug.print vfile with my copy function. I've tried copyfile but it needs to specify the destination with the file name. I tried using the FSO filecopy method and can't get it to work (keep getting a compile error "Expected =") I feel the hard part is done and this part should be simple.
I have a simple data entry form with drop down facilities on 2 fields. One of these fields incorporates a drop down list from a table but there are occasions when I wish to make a free text entry for the single record, but do not wish to add it to the drop down list.
I have tried to achieve this with a Combo Box but without success, although I am sure that I have read that it is possible.
I have a linked *.txt file and a qwery that returns the top 1 record from it. (The ideea is to check a value from one column of the file).Both the linked file/table and the query run corectly,I have a vba code on the on open of a form that is checking the query above mentioned (the form is on the Startup option of the database) and when I need to use the Dcount of the Query "
Code:
DCnt = DCount("Den_Fis", "Data_R_Mod_U$G9707014")
I get the "Runtime error 3625: the text file specification..." doesn't exist etc.I pres debug and test the query, I get the "Unknown Jet Error".I press Stop in the VBA and open the Qry - it shows what is was ment to show.I know the workaround is to make a maketable with the needed info and the Dcount on the local table, but it would add one query and one table to the database.
My form used to work and as I kept adding event scripts now when I click on the ID text field, it doesn't open the linked record, but a blank record with 'Filtered' showing on the bottom navigation. I guess a question I have is,
-is there an order on which the procedure scripts run -do they all run everytime, or just the specific event script
=================================== Private Sub Company_Click() DoCmd.OpenForm "frm Company", , , "[ID] = " & [ID] DoCmd.Close acForm, "frmCompanyDirectory" End Sub ===================================
I used to have code on a button on the "frmCompanyDirectory" that added a new record to "frm Company" and that's when the ID = ID stopped working.
i have got the bellow code that filters a listbox which is based on a query, from a textbox. The code works fine and it Filters, but if i put in too many characters that do not exsit in the data( so it cant display any results) i get an error saying Run time error 2105 - You cant go to the Specified record? how i can catch this error so it does nothing apart from display a blank listbox with no error?
code:
Private Sub txtSearch_Change() Me.lstShowSupplier.Requery DoCmd.Requery Me.txtSearch.SetFocus If Not IsNull(Len(Me.txtSearch)) Then Me.txtSearch.SelStart = Len(Me.txtSearch) End If End Sub
Ok I'm building a Text Search box that "Live Filters" the results in a ListBox control on the same form.
After typing in a value (Say, for Customer First Name like "Steven") I want the code to do the following:
1. If there is no value in the listbox that matches when the user presses the enter key....open the New Customer form - THIS WORKS
2. If there is a single value in the list, then open this record in the customer form when the user presses the Enter key Receiving a Syntax Missing Operator error on this line:
However, I use this exact code on a button elsewhere in my project (NOT within an IF Function) and it works perfectly fine!
Code: Private Sub txtSearch_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then If Me.ListCustomers.ListCount = 0 Then CmdNewCustomer_Click
I'm fairly new to Access / VBA and have been trying relentlessly to get a text box / search button on my Access form to pull up a specific record. Although the null command produces the correct error msgBox, it does not show any record if I enter a correct primary key term.
Code: Private Sub SearchButton1_Click() If IsNull(SearchField1) = False Then Me.Recordset.FindFirst "[UniqueAEVRef]='" & SearchField1 & "'" Me!SearchField1 = Null If Me.Recordset.NoMatch Then MsgBox "No record found", vbOKOnly + vbInformation, Me!SearchField1 = Null End If End If End Sub
Populating the table in db3 with a subset of records from db2.
I've gotten as far as opening the db2 and copying the table structure. Can't figure out how to run the query using execute rather than docmd.openquery.
Code: Dim appAccess As Access.Application Set appAccess = New Access.Application appAccess .OpenCurrentDatabase "DbPathString" 'copy the table structure to dbQn, overwrites any previous with same name
I have two unbound text boxes and a search button adjacent each that allows me to search for a user via two methods:
1) Payroll ID 2) Surname
They have the following code:
txtPayrollIDSearch
Code: Private Sub txtPayrollIDSearch_AfterUpdate() Dim sWHERE As String sWHERE = "[PayrollID] = " & Me.txtPayrollIDSearch sWHERE = "[PayrollID] = '" & Me.txtPayrollIDSearch & "'"
[code]...
Then minor differences between the above and the Surname search.I have two questions:
1) How do I make the search more friendly by allowing it to find partial matches, i.e., a user has a surname of 'Smith' but I want to search for 'Smi'?
2) How can I display further error messages if there aren't any matches?
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection With connDB .Provider = "Microsoft.ACE.OLEDB.12.0" .Mode = adModeShareExclusive .Open strdbpath 'path to database End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table If connDB.State = adStateOpen Then Set objRS = New ADODB.Recordset objRS.Open strSQL, connDB, adOpenForwardOnly On Error Resume Next objRS.MoveFirst If Err.Number = 0 Then On Error GoTo 0 fieldCnt = objRS.Fields.Count For fieldNum = 0 To fieldCnt - 1 ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name Next fieldNum ws.Cells(2, 1).CopyFromRecordset objRS End If End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code: Call func1("Z", 8) Public Sub func1(b As String, a As Integer) strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _ & "FROM jabberwocky " _ & "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _ & "HAVING ScanDate=" & J & " and Scanhour=" & a & "" End SUb
I am 2 years into my database. I am trying to find and open a folder based on a text box. The problem is folder could be in many sub folders which is hold on our J: drive.
To further complicate, the folder i am searching may not be exactly as the text box states.
EG. Text box could say 123456 however the folder could be called M123456 etc.
How do i locate a folder or subfolder and open it based on part of a text box...
I am working on a project in which we have to print word documents of each policy so if the policystatus is "Live" then we have to print a document with header(barcode) on it. If the policystatus is "Cancelled" then print off a different sheet. The following code is written that works fine but the only thing is the print order is not right. documents jump up the queue. The documents spooled last get printed off first sometimes. Any other alternator to write the following code so that sheets prints off in order:
Code: Public LivePol As Variant Public CancelPol As Variant Sub GetDataFromDataBase() LivePol = 0 CancelPol = 0 Dim myDataBase As Database
[code]....
If we can write the code in a way so that printing sheets come up in the order.
There is a form where whenever the form is closed, the below code needs to execute:
If IsNull(Me.CostPerPiece1.Value) = True And IsNull(Me.CostPerPiece2.Value) = True And IsNull(Me.CostPerPiece3.Value) = True And IsNull(Me.CostPerPiece4.Value) = True And IsNull(Me.CostPerPiece5.Value) = True Then Me.AllowAdditions = False DoCmd.SetWarnings (0) DoCmd.RunCommand acCmdDeleteRecord DoCmd.SetWarnings (-1) Else Call AppendQuoteCharges End If
The If Then just looks at if certain fields are all null and if so, it deletes the current record. If at least one field is not null, the AppendQuoteCharges routine is called.
The form has 4 or 5 navigation buttons that close this form and send you to a different one. I've added the above code to each of those buttons before it runs the DoCmd.Close Form. I also have a Exit button that runs a DoCmd.Quit. I developed this months back but I'm pretty sure I added the above code under each button's click event rather than a Form On Close event because Form On Close does not execute after a DoCmd.Quit command
In rare cases, the form is being closed without the routine being ran. I think if a user clicks the Close button in the top right of Access (the X), it might be running a DoCmd.Quit which is doesn't run this code.
How can I be sure that whenever the form is closed or exited, the code is ran? Is there a way to tie this code to the user clicking the X in the top right?
I'm using shellexecute in my form whenever a picture is clicked the respective program/ application will open to show the picture. Because I want to see the picture more clearly by zooming it in or out. But the problem is I don't know how to disable the delete button and prev./next button, because I want the user use the program only to zoom it in.
I'm wondering if it is possible to create a module to hide the navigation pane and document tabs? I know this is possible via the Access Current Database options however I'd prefer this to be done via an Auto Exec that runs the sub. (i already have it for the ribbon)...
I have the code below and I want it to open a file from my documents folder. The only problem is that every computers path is different to this folder. Any easy way to work around and open a file in My Documents without the full path?
I want to eliminate the part of the path in red and make it universal because computers will have a different number.
Operating System: Windows 7
Code below:
Public Function AddITARPicOffloadAnalysis() Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") With xlApp Dim wb As Object Dim ws As Object Dim Lastrow As Long
I created a form with a ListBox and a Command Button. The users selects the values in the listbox and then click the button to create word documents. I've written VBA code to accomplish this. But it's not working properly. It opens multiple word documents but all for the same one.
Private Sub Command6_Click() Dim appWord As Object Dim varItem As Variant Dim strPathToTemplateFile As String Dim strPathToProspectiveFile As String Dim strPreferredFileName As String For Each varItem In Me.List0.ItemsSelected
I receive mails through LN and loop through a certain folder, reading DeliveredDate, Body, Subject and writing those items into an Access-table.This works fine, before I go for the loop I put the doc in another folder and remove the current doc from the folder.
Call LNDoc.PutInFolder("SMSBackup", False) Call LNDoc.RemoveFromFolder("SMSResponse") Set LNDoc = LNView.GetNextDocument(LNDoc) Loop
Without any error, without anything abnormal in the mails the code exits after a few loops, sometime after more or sometime after less read mails.The problem ist the RemoveFromFolder, not the PutInFolder function.
So to avoid this behavoir, I changed my code to finish the loop without Call LNDoc.PutInFolder("SMSBackup", False) Call LNDoc.RemoveFromFolder("SMSResponse")
and added after the loop a NotesCollection which should do ALL at once.But this part of the code does nothing at all (allthough found on the web).
With LNCollection .PutAllInFolder "SMSBackup", False .RemoveAllFromFolder "SMSResponse" End With
The complete code looks like this:
Set objNotes = GetObject("", "Notes.NotesSession") Set LNdb = objNotes.GetDatabase("myServer", "MyNSF") If Not LNdb.IsOpen Then LNdb.OpenDatabase If Not (LNdb Is Nothing) Then Set LNView = LNdb.GetView("SMSResponse")
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078 The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
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 am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
I have been searching the forum for this scenario but could not find similar case, which is as follows: I have a Search Form with many Text and Combo boxes, linked to search criteria in a query. There is also a sub-form showing the search results. I am trying to replace one of the combo boxes on the form, with a List Box, so I can make multiple selections. For example instead of showing only "buy" transactions via the combo, I want to see both "buy" and "sell" transactions, which I believe could be done via a List Box.
For the combo box I have the following: In the Field section I have: [TransactionType]=[Forms]![frmSearchTransHist]![cboTransType] Or [Forms]![frmSearchTransHist]![cboTransType] Is Null
For Criteria I have - "True"
So, my question is what should I put on the query in order to make it work with a list box.
I also want to give credit here to Jon K who has an example of database with combos and texts - very helpful example! http://www.access-programmers.co.uk/forums/showthread.php?t=103312