set warnings to warning on: NO
and
runCode to Function Name: cmdImport_Click()
where cmdImport_Click() is a Private Sub in a module within the same database.
when i try to run the macro i get an error that stated
"the expression you entered has a function name that MS Access can't find."
but if i open the module called ImportMSExcelFile and set my cursor on the Private Sub cmdImport_Click() and select the green arrow to run the procedure it runs fine.
I got a problem with my access database. I'm using the code as below in a access function.
Code:Dim rst As ADODB.RecordsetSet rst = New ADODB.RecordsetstrSQL = "select * from tbl1 where fldDate between #08/22/2005# and #08/28/2005#" rst.Open strSQL, CurrentProject.Connection, adOpenStatic
When I get to the last line, It will cause a "catastrophic failure" on my computer, whilst on others it will run just nicely.
I'm thinking some sort of version problem, but wouldn't know where to look...
I get a syntax error when I run this query from an Access Module but I don't get an error when I run it as a straight query. Can anyone see what's wrong with it? I am using Access 2003
Dim sqlStmt As String
sqlStmt = "SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight " sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight " sqlStmt = sqlStmt + "FROM CCCWords " sqlStmt = sqlStmt + "WHERE Word IN ( Select word from CBCWords where vendor = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO,COUNT(*) * 25 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPostalCode " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "]. AS dupWeight " sqlStmt = sqlStmt + "WHERE dupWeight.ESTBLMT_NO = [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "GROUP BY [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "HAVING SUM(subWeight) >= 60 " sqlStmt = sqlStmt + "ORDER BY SUM(subWeight) DESC"
DoCmd.OpenForm "Show Probabilities", , , sqlStmt
Here is the code that runs ok in a straight query:
SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight FROM CCCWords WHERE Word IN ( Select word from CBCWords where vendor = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 25 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPostalCode WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO ]. AS dupWeight WHERE dupWeight.ESTBLMT_NO=[CCC Companies].ESTBLMT_NO GROUP BY [CCC Companies].ESTBLMT_NO HAVING SUM(subWeight)>=60 ORDER BY SUM(subWeight) DESC;
I am trying to populate an Excel Shreadsheet (Template in Effect) with Data from Access. This is going ok no problem. Although I need to run an excel macro, which does some formatting to the WorkSheet after each entry in the Access Recordset. I've tried copying the VB over, but getting some errors, and frankly, I think it's easier if I just call the macro itself, rather than try to adapt it for the Access context. Though, I accept it would be a cleaner approach. Can I do this?
We have been calling a main form to manage yacht races using a hyperlink/macro from a selection form which lists all the yacht races ..The main form was too big to fit on some screens so we changed it to pop up mode to allow it to be resized and use scroll bars. this works well when the form is opened manually and all functions run just fine
the hyperlinik/macro for race selection had originally been set up to call the main form in dialog mode but we have had to change this as it disabled the resizing of the form and the scroll bars..The macro tool doesn't offer pop up mode so i selected window mode = normal and the main form opens with a resizable window and scroll bars but all the functions called from this module now fail (error message = use of null value or similar and all the functions go into debug mode - the server is down and i can't access the system to get the exact details)
If i change the macro in the selection form back to window mode = dialog the functions work but i lose the resizing of the main form and and the scroll bars.
I've got a Macro that runs well over 100 queries as well as sub-macros. I'd like to know what query the Macro is running. My best solution so far was to create a bunch of forms that open and close with the name of each query, but that's kind of sloppy. I don't think I can use the msgbox because it prompts me to press 'OK' to continue. Any thoughts?
I was hoping that someone could tell me if it is possible to run an Excel macro from an Access database. I am importing 3 Excel worksheets into Access and this macro needs to be run on all 3 evertime that this file is imported by the user (twice a week). If this can be done could someone please tell me how ??
I have 2 forms and a macro in Form1 which runs throught every record to update the records, however.I would like to run this macro from the main form (Form2)
I have a MS Access append query that updates a table from a linked Excel spreadsheet. Im trying to automate the running of the query during the night.
Ive tried a couple of ways to schedule the task with Windows Task Scheduler, although each time, it appears to start the job, but then just hang (task never completes, doesnt run the query or macro and when I log back into Windows, the msaccess.exe process is running).
Methods Ive used are:
- command line with /x macroname
- vbs script which runs the query / macro
Code: 'Read from a Microsoft ACCESS database Option Explicit dim oaccess 'execute another Access macro set oaccess = createobject("access.application") oaccess.opencurrentdatabase "H:Database.mdb" oaccess.docmd.setwarnings false oaccess.DoCmd.RunMacro "Macro1" oaccess.docmd.setwarnings true oaccess.closecurrentdatabase oaccess.quit set oaccess=nothing WScript.Quit(0)
- .bat file which just has the command line in it.They all run fine when I am logged in, although it seems to hang when I am logged off the network. I have the correct permissions as Ive scheduled a Excel macro to run and its fine.
I have 3 different "buttons" on my form that run individual update queries. I know these update queries are running correctly. Decided to make a macro that will allow me to run all these queries by clicking one macro button.
My question is: Is there some way to stop the pop up msg that advises I am about to run an update query and the next pop up msg that informs me of how many rows I am going to update?
These pop up boxes are starting to drive me crazy when I run the macro.
I can manually stop the AutoExec macro from running by holding down the shift button - how do you do it by VBA? I'm opening the db from Excel, and I don't want the Switchboard to display ...
I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.
I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.
Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?
I am creating an form in a database and whenever one of my procedure's run it creates this error message:
The expression ON Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure]. *There may have been an error evaluating the function, event, or macro.
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
have a DB which is working fine on most PCs (approx 15 users) I do however have a problem sending emails from some machines (using a macro to do the send object as example below) The mail program used is Outlook 2000 / 2003
DoCmd.SendObject acReport, "Returns Notice", "SnapshotFormat(*.snp)", "to a defind list", True, "" This works fine BUT on most machines but on a couple we get a fail message when attempting to send, this message is unspecific. I cannot find any different settings on the machines in question, the op system is 2000 / XP.
I operate the same program on my PC running XP prof servpk 2 and although 99% of the time the emails go through without issue I do sometimes have the same problem. Re-booting does not resolve it! and I can find no trend as to why I have the problem. Any thought or advise welcome.
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?
I'm fairly new to Access, so maybe someone can school me here...
I have a issue tracking database query reporting a serial number in the first column, an open issue date in the second column and close issue date in the third column. There can be several instances of each serial number.
I already figured out how to use DateDiff to get a time to fix column...
I want to have a column reporting the difference between the previous close date to the next open date (grouped by serial number).
Example S/N Open Date Close Date Time to Fix MTBF 1234 1/10/2007 1/13/2007 3 5 1234 1/1/2007 1/5/2007 4 2222 1/2/2007 1/3/2007 1
When I highlight a combo box, the default hotkeys should be alt+down or f4 to drop the menu down.The issue I'm having is that while alt+down works, f4 does not. I just got a new computer in my office, and f4 worked on the old one.My keyboard does not have a f-lock key on it. That seems to be the internet's general response to my problem, but that doesn't work.
hi There I Have A Database Trying To Convert It To MDE I Get The Following Message -------------------------------------------------------------------------- This error is usually associated with compiling a large database into an MDE file. Due to the method used to compile the database, a considerable number of TableID references are created for each table. The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
I admin an Access 2007 ADP that is distributed to our user base as an ADE. The back end is MSSQL 2005. Recently, we have begun deploying this software to employees in the field who connect to the corporate network via VPN. Because our field employees are sometimes connecting from some seriously flaky Internet connections, we have seen an uptick in Connection Failure errors.
I've been researching ways to detect and reestablish a failed network connection from VBA so that the user doesn't encounter the errors, or the need to close and reopen the application to continue working. This post appears to get me most of the way there, in that it both suggests a way to detect the failure and reestablish a connection on the fly:
[URL] ....
Basically, it suggests replacing the call to CurrentProject.Connection with a call to your own custom function, looking something like this:
Code: Function fCurrentConnection () As ADODB.Connection If CurrentProject.Connection.State = 0 Then CurrentProject.OpenConnection "YourConnectionString" End If Set fCurrentConnection = CurrentProject.OpenConnection End Function
My problem is that I define my connection in the built in server properties of the project. My users connect using Windows NT Integrated security. I don't know what I should put in the "YourConnectionString" area. Is there a way to tell Access to use the stored settings to reconnect, or alternatively, any example string that connects to a server name and database name using Windows NT Integrated security?
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
