Search results

I don't think you need to use VBA. If you put your list of cities in a table, then you can just join that table to your query above and it will return the results for all cities in your list. StepOne

Hi all, This is a follow up to my post from last September. We have an Access process which we run every day (several times) but for some large data sets we get the above error - System Resource Exceeded. The process is split into 3 main subroutines - each of which calls a large number of...

Dear All, Just wanted to say that I tried Compact and Repairing the database, and the difference was incredible ! I ran the routine on one pc and it took 4 minutes 50 secs. I then did Compact and Repair on the database , and when I ran the routine again it was down to 4 seconds - i.e. a 98%...

Hi folks, I have written a VBA procedure (in Excel VBA) to open an access database using the DAO opendatabase method, and then loop through a list of SQL queries, using .copyfromrecordset to copy the query results onto separate tabs in the Excel workbook. This works great on my pc, and, to...

Hi, I have some VBA code sitting in Excel, which opens an Access application, and calls a VBA procedure in the Access database. Code as follows ; Dim acApp As Object Set acApp = CreateObject("Access.Application") acApp.OpenCurrentDatabase (m_strDBCopyPath & m_strDBCopyName) acApp.Visible =...

Hi, I don't think they are - these are update and append queries. They don't open in the Window if you run them manually (by double-clicking on them), so I don't think they actually 'open' when called by VBA either, unless I am missing something. Anyway the VBA closes the Access application...

Hi Dave, I went looking for the disks at one point, and was directed to a cupboard. A rather big cupboard, with lots of shelves, lots of boxes, lots of dust, lots of old pcs, broken printers, various cables, user manuals.... you name it. There were some disks, but none of them were Office...

Hi, I have a Report Template spread-sheet which uses some Excel VBA code to change the data range of a chart, and then copy the worksheet containing the chart to a new, blank, workbook. The code which updates the data range is as follows ; ActiveChart.SeriesCollection(1).Formula = _...

Hi, No, the queries never actually appear to the user. They are all Update and Append queries. They run (the tables are updated and appended), then the database is closed by VBA. No need for the user to do anything. I've checked documentation online, and nowhere does it say that I need to...

Sorry, I just saw the second part of your question. I know that 60 seems a lot. It's true that the number could possibly be reduced, however there are a large number of columns, each of which is updated based on values from a collection of lookup tables, so we really have to do each column one...

Hi, Umm, .... no. The routine is basically as follows ; Sub PrepareSummary() DoCmd.OpenQuery "qryA1" DoCmd.OpenQuery "qryA2" DoCmd.OpenQuery "qryA3" ....... (60 other queries) ....... DoCmd.OpenQuery "qryZ1" DoCmd.OpenQuery "qryZ2" DoCmd.OpenQuery "qryZ3" DoCmd.OpenQuery "qryZ4"...

Hi, I might become a regular poster on here as I have a new job which is supporting a miscellany of Access and Excel applications, something I have not done for a few years now.... One thing I have inherited is an Access database which we use on a daily basis to run reports for customers...

Thanks - I am also confused, but it worked ! Cheers,

Hi there, Yes the form is still open. In fact, while testing, the VBA code opens the query AND outputs to Excel. The query displays fine - with the Approvers name against each record, but in the Excel file it is blank. While searching I have since found a workaround - create an SQL string...

The value does show in the query. It's not stored in the table - it's an unbound text box on my form. When the user wants to export the details to Excel, they put their name in the text box. The 'Approver' column in the query then displays their name against each record. That works fine. But...

Hello, I have a query which outputs the value of a txtbox on my form. So in the query I have a field which looks like this ; Approver: [forms]![frmMain]![txtApprover] This works fine when I run the query, but when I use docmd.outputto to export it to Excel, the Approver column is blank. I...

Hello, I have a form displaying a list of people, with selection criteria in the form header which lets the user search for records. The underlying recordset is based on a Group By query and therefore cannot be updated. The trouble is that I want the user to be able to somehow select a...

You just need to put a me.requery in the AfterUpdate event of the File Number control on the JIP Entry form. StepOne

Hello, I have continuous list form with a list of staff details to allow people to choose a staff member to edit. I want the user to be able to click anywhere on a staff record to open up the frmStaffEdit form. Originally I had a button in the details section to open frmStaffEdit so it...

In other words, create a qry called qryTotalDuration ; SELECT tblProjects.IDCust, tblProjects.IDEmpl, Sum(tblProjects.Duration) AS SumOfDuration FROM tblProjects GROUP BY tblProjects.IDCust, tblProjects.IDEmpl; Then your query is changed slightly to ; SELECT tblCustomers.NameCust...