Hi all,
In February I had a similar query on the following thread: https://bytes.com/topic/access/answe...-selected-item
The answer given works fine but is a little clunky - e.g. when the user types multiple keys in a row it executes the change event multiple times so the typing is behind on screen. Is there anyway to make it so that it only queries once when the user has...
DavidAustin
Last Activity: Oct 11 '16, 11:45 AM
Joined: Nov 6 '14
Location:
-
Combobox search while you type
-
DavidAustin replied to Controlling Excel via Access: Runtime 1004 Method 'Intersect of object' Global failedin AccessHi jforbes,
Thanks for having a look. I would have thought that there would always be an intersection being a pivot table with a format that doesn't change. Anyway, I gave the code a go and it works like a charm. Tested it 5 times in a row with no runtime errors and correct results everytime.
Many thanks! -
DavidAustin started a topic Controlling Excel via Access: Runtime 1004 Method 'Intersect of object' Global failedin AccessControlling Excel via Access: Runtime 1004 Method 'Intersect of object' Global failed
Hi all,
I am trying to export query to Excel from Access, manipulate it into a pivot table, format the pivot table and then export into a word document...
I have some code that I have made/taken from other places to do so but I come across a runtime error on one line of code (line 66 in the below code section). This error only happens every other time the code is run which I find odd - e.g. first time works fine, second... -
I'm not going to lie, this looks well outside of my comfort zone for something I thought would be "simple". If I had to use Access for this, how would I make it produce the report in dataform that I need?
All I need is something that is like a pivot table but will show all of my wards on the rows without having a blank dates column at the top!Leave a comment:
-
I've been going through your vba and edited it so that zFP is my file path and zFN is the filename (adhering to the same format as your example). I have also pasted in the SQL from my original go (see below) and then ran the code. I am getting an error in the zWB.Connections .Add 2 section of:
ErrS: VBAProject
ErrN: 438
ErrD: Object doesn't support this property or method
The SQL I've been using (copied from microsoft...Leave a comment:
-
Wow, that looks like it could be what I'm after - does it definitely list all objects even if they have no data attached? EDIT: I have also just realised this is the same website I used a couple of weeks ago to do this and it didn't work in producing the final table as it wouldn't list all of the wards in the pivot table as required
I have started going through the tutorial (selecting the excel files option like you have said) but...Last edited by DavidAustin; Nov 10 '15, 10:07 AM.Leave a comment:
-
Hi zmdb,
The main reason I haven't tried using Access is because every month we get sent an updated spreadsheet with all the discharges for the previous month. The spreadsheet I'm currently working with imports the data from this updated spreadsheet and adds it to my data table. I have had experience of importing data from Excel into Access and have found it to be flaky at the best of times. Once this spreadsheet is made, I'm leaving...Leave a comment:
-
Even quicker and neater than my original fix! Thanks for the input jforbes!Leave a comment:
-
Okay, I managed to solve it! Just in case anyone ever had the same problem, you don't need to have a separate text box, just need to disable the combo box then re-enable it e.g.
...Code:Private Sub cboLookup_Change() Dim sSQL As String Dim sNewLookup As String If Not bLookupKeyPress Then 'If nothing entered, set to empty string sNewLookup = Nz(Me.cboLookup.Text, "")Leave a comment:
-
How to make combo box dropdown disappear
Hi all,
I have a combo box on a form which drops down once the user has typed into it. I want the dropdown to "disappear" if the user backspaces to an empty string and I thought the best way to do so would be to set the focus to a random text box and then return the focus back straight afterwards. Unfortunately, I keep getting an error saying that Access can't move the focus to the random textbox. I'm convinced it is because... -
Yes, Table 1's ward matches table 2's wardname. I have checked and the same values are used in both tables. As previously stated, I only have Excel 2010 not 2013 (which is a shame because I'm sure 2013 would be able to do this!). As there is sensitive patient data, we are restricted in creating databases with the information in otherwise I would definitely have this up and running in Access in no time!Leave a comment:
-
No, it came out with data that was merged randomly between the two tables. Could it be that the tables are not in the same format?
Table 1 is: ID, Name, DoB, ward, discharge date
Table 2 is: wardname, serviceline of ward, locationLeave a comment:
-
Hi zmbd and hvsummer,
I cannot use Access for this project and I am running Excel 2010 so power pivot is not available either.
I have previously tried both of the linked methods and cannot get them to work unfortunately. I've spent quite a lot of time searching for ways to get it working and can't find anything. I tried using MS query to join the tables but in the end pivottable this gives blanks for dates corresponding...Leave a comment:
-
Linking tables to make Pivot Table Excel 2010
Hi all,
I have a spreadsheet which has two data sources on. Table 1 has data relating to patients who have been discharged from the wards at the hospital I work at and Table 2 has the list of all the wards at the hospital. I can make a pivot table from table 1 easily however it will only list the wards present in table 1. Some of the wards may not have discharged patients for some time but are still required on the final report. The... -
#Ref! error - how to solve
Hi all,
I know that a #Ref! error occurs when the cell that is being referenced in a formula is no longer valid (e.g. deleted)
I am trying to make a spreadsheet for my colleague where every month she copies in data from a monthly generated report and the spreadsheet takes the data and pulls out specific information. However, every month she comes back and tells me there's loads of #Ref! errors because when she copies... -
Ah if it goes forward only then it appears I will be stuck. Many thanks for the help as always!Leave a comment:
-
Would there be a way to use the page footer to determine the background colour of the last record and then, if it's the wrong colour, reformat the detail again by starting from the iCount being 1 instead of 0?Leave a comment:
-
I'm still a bit lost with this all I'm afraid. I'm not using the CanGrow property. In my mind, I think I need the procedure to go like this:
Populate page 1 of report -> count number of records on page -> format backcolour of records based on how many records on the page -> Move to page 2 -> reset counter -> populate page 2 etc.
I understand resetting the iCount variable to 0 in each page header - makes sense...Leave a comment:
-
I am probably misunderstandin g this, but wouldn't the DCount count all of the records that are being produced for the query and not just the ones that are going to fit on each page of the report?Leave a comment:
-
Sorry to revive an old thread but it's directly related to the code and question above. Is there a way to do this alternation but ensure that it counts from the bottom upwards? I need the final record on the page to be the alternate colour and then alternate up the page. I'm not sure it will work because I assume it does the formatting of the report going down the page.Leave a comment:
No activity results to display
Leave a comment: