I need a search function that will not just open files based on their names but also drill into the documents and search based on what's in it. The only way I can see now is to use the Window's search function (the one at the start button). Is it possible to call that function into access?how do I re-create the search style of the windows search bar for a specific folder? Something like streamreader
View RepliesI'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP
Office 2003 on WinXP
Office 2010 on WinXP
Office 2000 on Win7
Office 2003 on Win7
Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer
(2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
On the Windows 7 Developer desktop, the Windows Theme is set to Windows 7.On the Citrix server (for users) the Windows Theme appears to be Windows 95. It is low graphics.The boxes are square instead of rounded. The selected Form Tabs just barely highlight with a thin line while on Windows 7, the selected Tab completely changes color. A basic Message box in Windows 95 looks square and flat. The title bar is very low graphics as one would expect when graphics were not great. Is there a VBA call that can display the OS Theme that affects MS Access? URL...
View 2 Replies View RelatedI know its not possible. But Just wanted to confirm. Not at all possible?
Code:
Function PreImport(BookToImport As Workbook)
For Each cell In BookToImport.Sheets(1).Rows(1).Cells
cell.Value = Trim(cell.Value)
Next cell
BookToImport.Sheets(1).UsedRange.Rows(1).Replace ".", "_"
End Function
I'm looking to use the shell function to dynamically call different pdfs that are in a directory. However I'm getting run time error 5 "Invalid procedure or call argument".
Here is the code (very basic I know)..
Private Sub Liste_Documentation_DblClick(Cancel As Integer)
Dim PathName As String
PathName = Me.Liste_Documentation.Column(2)
' Debug.Print PathName
Shell PathName
End Sub
A typical filepath name is as follows..
S:VenteVendeursMarcCRMDOCSDiligences KYC - LABFT - V 2013 04 23.pdf
I have always had solid read access to the Windows registry running on Windows XP with Access 2007. I am utilizing code from here in a separate VBA Module to obtain access to the Windows registry:
"Change registry settings in VBA"
[URL]
On Windows 7 x64 / Access 2010 x86, I am seeing a 0 value in lngKeyHandle. Also, m_lngRetVal has a value of 2 after the RegOpenKey LOC.
Code:
' --------------------------------------------------------------
' Query the key path
' --------------------------------------------------------------
m_lngRetVal = RegOpenKey(lngRootKey, strRegKeyPath, lngKeyHandle)
' --------------------------------------------------------------
' If no key handle was found then there is no key. Leave here.
' --------------------------------------------------------------
If lngKeyHandle = 0 Then
regQuery_A_Key = vbNullString
m_lngRetVal = RegCloseKey(lngKeyHandle) ' always close the handle
Exit Function
End If
I have verified that I have the key successfully in the registry... installed via the .reg file that works on Windows XP / Access 2007 systems.
I have a form with First name, Last name, Employee number, Information. The information box is a drop down menu with four options (contact, emergency, company, personal) I would like to be able to enter in search criteria for an employee using one of last name first name or employee number then choose an option from drop down box click a button to search and have it pull up just the info I needed.
I think I have two options but i am not sure which would be more efficient.
1. creating a query and using the criteria from design mode to have it filter the information. The only problem with this one is I do not know how to set it up with the drop down box from the form. Or would I need multiple querys for each option or just one with all of it?
2. writing code for the search button on the form that will bring up the appropriate query (I think I would then have to create a query for each drop down option)...
I have a form that I allow my users to view but not to change any of the records.
As the number of records has grown it's become necessary to add a search function.
My problem is that because the form is locked it cannot be searched.
Hi there,
I would like to run a query which will allow me to search a specific column in a table. Any help would be greatly appreciated!!
Thanks
Access 2007
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()
[Code] .....
I did some searching and found this function posted by someone in response to the exact same problem I'm now encountering.
But how do I call this in my query?
Function StripZeros(pstr As String) As String
Dim n As Integer
n = 1
Do While Mid(pstr, n, 1) = "0"
n = n + 1
Loop
n = IIf(n > 1, n, 1)
StripZeros = Mid(pstr, n)
End Function
Hi everyone,
Thanks for taking the time to read this.
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
How do I get this to happen?
Thankyou for your help
Jess
:confused:
Hi all!
I have a query that returns some fields with "#Error" ...
The reason is that I call a function in the query, and the function chokes if there are no matches in my query criteria and therefore no argument is sent to the function.
I tried putting this in the function:
var = Iif(IsNull(var) = True, 0, var)
-but I still get "#Error" when there are no matching records.
I presume this is because not even Null is given to the function as an arg.
Should I make the arg optional or how can I solve this??
Thanks :)
I have linked an excel workbook as different tables in Access.
The workbook contains a Microsoft Query. I have now found out how I can open and edit the workbook from access, how can I Update/refresh the MSQuery before I close it?
Here is what I have:
Private Sub cmdTest1_Click()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
Set MyXLSheet = GetObject("c: est.xls")
MyXL.Application.Visible = True
MyXLSheet.Parent.Windows(1).Visible = True
MyXL.Application.Cells(1, 1).Value = Now()
MyXLSheet.Close SaveChanges:=True
MyXL.Application.Quit
Set MyXL = Nothing
End Sub
eroness
I’m having problems trying to construct a database for my company. They need the database to enter information about certain insurance policies that our company has. We have a table setup and ready to go. I need to allow multiple users to add records to the database so I want to setup a Data Access Page to accomplish this. However, there’s a problem I’m still stuck on.
In the Data Access Page, I need the ability to search and display a record designated by the primary key field (policy number). I found code to do this, however when I click the button, input a known policy number at the prompt, and click ok, it displays Error: 3265 the item cannot be found in the collection corresponding to the requested name or ordinal. Here’s the code from http://office.microsoft.com/en-us/assistance/HA010345491033.aspx
<!-- I took this out as i did not know what to do with it, it could be causing the problem
<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>
-->
<SCRIPT language=vbscript event=onclick for=Command0>
<!--
' Clone the recordset.
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone
On error resume next
' This line assumes that the value you are filtering on is an integer.
' If the search value is a string, use slightly different syntax.
' For example, "CustomerID = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
' rs.find "ProductID=" & cLng(inputbox("Enter a ProductID","Find"))
rs.find "CustomerID = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
' Custom error handling.
If (err.number <> 0) Then
Msgbox "Error: " & err.number & " " & err.description,,"Invalid Search"
Exit Sub
End If
' Check search results for success.
If (rs.bof) or (rs.eof) Then
Msgbox "No Product found",,"Search Done"
Exit Sub
End If
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
-->
Hi All,
This is my problem -
My customer has Access programs that work on their Windows 98 OS PC's, but when they try to use them on Windows 2000 OS PC's they will not work.
The programs I believe were originally written in Access 97 and now they have Access 2000 on the PC's.
By not working I mean the following -
The Access program allows them to enter a document name and then the file opens in Word - BUT - when they use it in Windows 2000 it does not open the document at all. It says file not found in Windows 2000 and the file does exist.
** The reason they have Access opening files is that Access keeps track of them for auditing purposes.
Now here is something that I want to mention. On PC's that were upgraded from Windows 98 to Windows 2000 the programs work.
Can anyone here tell me what is missing from 98 to 2000 that would do this.
Thank you very much for your time.
http://www.access-programmers.co.uk/forums/showthread.php?t=123538
Following on from that thread, I would really like to tidy up other areas of my front end. I am using the idea that Roy suggested and it works very well.
At the moment, I have a form, that has buttons on it that people click when they want to generate specific sheets from excel using data from access using VBA on the form. These work fine, however, now that the database is going live and all features need to be added, I think it would be nice to have one small form, with a list box.
I have converted a copy of the code on the form and placed in a module by declaring then functions instead of private subs.
At this stage, I tried doing it with macros running the code by using a series of runcode. I then created a table that has the macro name stored in it along with a decriptive bit of text detailing the excel chart that gets produced to make it easy for the user.
The list box populates itself correctly, but the problem I am having is I do not know how to make the 'go' button look at the list box, pull the hidden macro/function name from the list, then go and run the macro/function.
I have tried looking about, but have not found anyhthing.
Dear Access Query Expert
I have created a query which has a function call as the criteria for one of the numerical fields. The function returns a string expression such as .....
1) 132 OR 142 OR 156
2) 132 OR 142
3) 132
..... into the criteria section of the numerical field.
Unfortunately, the query doesn't work if the criteria is generated by the function call. However, if I hardcode the criteria (don't send a function call but directly write 132 OR 142 OR 156) the query works.
I am puzzled and I am not sure how to solve this problem.
Thank you so much.
Is it possible to call a function from a field on a form, ie, in the
control source put = FunctionName(abc)? Then I want to concantenate
it to another variable so it would look like:
=FunctionName(abc) & TextVariable
I tried this and got the ?Name on the form, like it couldn't find the field
name, which is really a function I'm calling.
Is this possible and if so, what am I doing wrong?
Thanks in advance for any help.
I have the following in Form and it works OK
Call RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
I want to build a combo box with several different RunForm variations as follows:
RunForm("frmProviderLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmCustomerLookup", "", "Edit", "", "", "Normal", "Normal")
RunForm("frmVendorLookup", "", "Edit", "", "", "Normal", "Normal")
etc, there are many of these
When I select one the variations from the combo box the RunForm statement is stored in strAction varaible..How do I code the Call strAction line? (I want only one call statement).In other words how to call a Function from a variable...The start of my module Function works OK and looks like this:
Public Function RunForm(FormName As String, _
Optional WhereCondition As String = "", _
Optional xMode As String = "", _
Optional filterName As String = "", _
Optional Args As String = "", _
Optional WindowMode As String = "", _
Optional View As String = "")
' Parameters (all optional except parameter 1)
' 1 - Form Name
' 2 - Where condition
' 3 - Mode [Add, Edit, View(Read Only), ""(PropertySettings), DS(Datasheet Edit)]
' 4 - Filter Name
' 5 - Opening Arguments
' 6 - Window Mode
' 7 - Form View
With the script in the form that is part of MS-Access. I wonder how do I access/call the written function that are in the DLL file I made. I appreciate your help on this one...
View 2 Replies View Relatedis it possible to call a function in a query with the input of a control element?
I have a form, and there is a list box in which I can choose the calculate method. In the query, a field should calculated with the choosen function.
Example:
Code : SELECT tblTest.price, tblTest.Date, forms![formtest] As Calc
...
The function has the same name as I can choose in the listbox. But, if I run the query a error appears.
Code : "Function forms![formtest]![lstChosenFunction] could not be found"
how I can call a function in SQL with the input of an listbox.
I have a access 2003 database, and i am wondering if i can't setup a search function in a form that will open a file in windows explorer?
What i would like to do is have a test box that i enter in a number for example: 1234 then have a button named "Search" hit that button and it opens a corresponding file in windows explorer named 1234, or just opens that folder directly..
I started using Access recently and I have an inquiry about VBA codes and sql queries. I have function which has the following signature: RAG(Stability as Integer, DS_SYNC_RATE as Integer, Profile as String) as String.
I would like to call this function in Insert sql query in order to calculate and add a new column to my table. I tried this basic query:
Insert INTO my_table VALUES (RAG(my_table.stability,my_table.ds_sync_rate, my_table.profile))
but it doesn't seem to work...
I have an Outlook Function that i am trying to run from Access.What i am looking for is to Open Outlook through Access and run the Outlook function from Access.
View 7 Replies View RelatedI managed to create a form that is able to search keywords on Access 2007. However, the keyword search must be exact to what is contained in the table.
For example, I stored "red blue yellow" into the database, and I can only search "red blue yellow", "red blue" or "blue yellow" in order to call that data.
How do I make it such that I am able to search "blue red yellow", "yellow blue red" or "red yellow blue"?
These are my current codes:
Code:
Private Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long
[Code] ....