scrapcode
Last Activity: Jun 18 '15, 11:27 AM
Joined: Apr 15 '10
Location:
-
Linking data into existing Excel table
Hey everyone, I’m hoping someone can help. I’m trying to set up a data import from a table in one workbook to one laid out exactly the same in another. However when I try to set up the link for the first time it’s inserting new cells rather than populating the existing table. I know once the data is in I can choose to format data import and choose the option to reuse the existing cells rather than inserting new cells. But I can’t seem to... -
How do I refresh Environ(Username) on workbook open (Excel 2010)?
Hi everyone.
I'm facing a problem with excel 2010 when using a function that worked fine in 2003.
I'm using Environ(usernam e) in the following function in module 1:
In my workbook I have a cell in which is =username().Code:Public Function Username() Username = Environ("UserName") End Function
This brings back my log in id fine. The problem comes... -
Thanks Rabbit but I've figured it out. I realised I was making things far more difficult than was needed.
Instead I've just built the query normally then called it from within the code.
Couldn't see the wood for the trees on that one! -
Thanks for getting back to me. I'll try to clear things up.
The delete query will need to be run in a few different situations so at the moment I just have it triggered from a command button.
As it doesn't form part of a real sub yet I just truncated the code. Here's the full sub as it stands:
...Code:Private Sub Command70_Click() Dim StrQuery As String StrQuery = "Delete FROM TblSubmissions
Leave a comment:
-
Access form delete query not working but no error message.
Hi everyone.
I'm having some bother with a delete query on a form I've built.
On the form is a text box which contains a unique ID. I would like to be able to delete the whole record that corresponds to that unique ID from the table that it is linked to.
So far my code looks like this:
...Code:StrQuery = "Delete FROM TblSubmissions " & "WHERE tblSubmissions.[Unique ID] = "
-
Thanks very much for the solution Mihail, worked a treat.Leave a comment:
-
Thanks very much for the suggested code Mihail. I'll have a look at it over the weekend and see if it does the business.
Cheers.Leave a comment:
-
Macro to copy data to new sheet based on criteria, error 1004.
Hi everyone. I'm trying to write a macro to copy data from one Excel sheet to another in the same workbook when a certain criteria is met.
My data table is an import from an Access DB. After the data has been refreshed to the sheet I would like to pick out all the entries where the value in column I is "High". Once the table has been filtered the values from column A should be pasted to sheet 1("summary").
... -
Thank you so, so much Smiley. I'm sorry it's taken me so long to reply but this is the first of me being back at it from days off.
It worked exactly as I needed it to. Thank you also for not just fixing the problem, but explaining it in a way that means I understand it for future projects.
You rock dude.Leave a comment:
-
cheers Mihail, I'll have a try at that. Using after update crossed my mind briefly, but again I've never used it so was wary. Will that act in the 'instant search' way I'm looking for? That's the main outcome I want. Every key press results in another search being done.Leave a comment:
-
Yes, that makes sense. It seems to fit with what I'm seeing. This just comes from my lack of knowledge in some areas (being self taught).
So if this is the way Acess treats things in the change event, how would you ever get any sub to work in that event? It would always run before it's had a chance for the change in question to take effect.Leave a comment:
-
Actually, after running the code in break mode the only number sub seems to have stopped working entirely. It's just skipping over values that aren't numbers so that's a different issue I need to work through. As it's a different sub then I don't see the issues being connected, unless because the event is "change" it affects the values for items in the textbox?Leave a comment:
-
Sorry Rabbit, I'm not following. What do you mean the value? With the above sub headed with an "change" event, when a value is entered into the text box it debugs with the error message I posted then the code window highlights line 11.
I don't know if it'll help but the "only numbers" part of the code calls a sub that ensures it's only a numerical value that can be entered into the text box.
When...Leave a comment:
-
Sorry, my browser crashed and I was pasting this back in. I must have missed the error part.
Its runtime 3075. It says there is a syntax error in the query expression '[Personnel Number]='.
It highlights the Dlookup when it debugs.Leave a comment:
-
Sub works when on "lost focus" but not on "change"
Hi everyone, I have a sub that works fine when used with the lost focus event, but when I try and use the same sub with the change event I get the above error.
I've searched the net and found lots of information but none of it that I can use.
I'm sure I'm missing something basic so apologies if it's a schoolboy error!
This all applies to an Acess 2003 form. I'm looking to create an instant search that... -
Hi Rabbit, thanks for your reply. I know the count appears there but I need to do 12 different searches each with 4/5 criteria every day. I know that if I can get my code working then one macro will deliver all the results in one go. So it would go something like:
Filter
count
dump result in cell
filter
count
dump new result in different cell
etc.
Then the macro...Leave a comment:
-
scrapcode started a topic How to fill cell value with count of visible rows (Excel).in Visual BasicHow to fill cell value with count of visible rows (Excel).
Hi guys, this should be an easy one.
I have an Excel workbook that I need to run a filter with lots of criteria on a number of different sheets and count the results every day. I'm looking to automate it and I'm almost there. Using VBA i've got the filters to select the criteria I need but I can't get it to count the rows that are visible. the code I've tried so far looks like this:
...Code:cells.Range("B2").Value -
Thanks NeoPa. That was the way I first approached it. However the user will be manually entering a up to 20 staff numbers. The idea behind bringing the name back is to give the user a visual check that they have not made a mistake with the number. If I use the after update then won't the last box not be checked until after the user has hit submit?
Even using the Dlookup on one search at a time does take around 10 seconds. The table...Leave a comment:
-
Thanks for your reply.
What do I need to do to get round this then?
Thanks again.Leave a comment:
No activity results to display
Leave a comment: