Thank you to everyone who took the time to look at this. I tried running the code on a new, blank worksheet and didn't get an error, so I did some extra troubleshooting and figured out what I was missing.
I apologize--my original question didn't have all the information anyone would have needed to figure out the problem.
My worksheet is protected and VBA unprotects it to make changes then re-protects it before closing....
gnawoncents
Last Activity: Jul 19 '19, 06:54 PM
Joined: May 31 '10
Location:
-
gnawoncents started a topic Error 1004 on second line trying to set value or formatting of cell or rangein ExcelError 1004 on second line trying to set value or formatting of cell or range
I seem to be missing an unstated rule here and am looking for some insight.
I am running Excel 2016 on Windows 10 Enterprise and am getting an error when I try to set the value or formatting of a cell (or cells). The error itself (Run-time error '1004': Application-defined or object-defined error) doesn't tell me much.
Key notes:
- I get the error always on the SECOND line of code. The first works fine.
... -
Use a string to represent a range object
Greetings,
I am trying to find out if something is possible in Excel VBA. I would like to use a string or integer to modify/represent the name of a range object. Please note that I do not have a range stored in string format.
For example, I have the following range objects already set: rng1, rng2, rng3, etc.
I am looping through rows and want to change the associated range with each iteration, using either... -
Eric,
First, remember to put your code in tags.
In regards to your locking issue, it's not something your code is missing, rather, it's something extra it has. There are a number of ways to update a record. The simplest is to display the actual record in the form, then it automatically updates as changes are made. I will assume that this doesn't work for you for some reason, which is why you didn't take that approach. ...Leave a comment:
-
Welcome to Bytes, cberos!
I've read your post a few times, but am not sure I fully understand what you're trying to do. If you are saying that the ACTNUM filed has records ABCDE, FGHIKD, and ZADET and you want to create a string with them all, then that's simple enough. Please explain in a little more detail and give an example of starting records and what your expected outcome is. Thank you.Leave a comment:
-
gnawoncents replied to How to write query to extract date only for different customers on the same day.in AccessWelcome to Bytes, privateguy!
Your answer will depend on how you are trying to do this (VBA or Query/SQL). Regardless, you will have to do a two-step process that first finds all unique records, combining ShowDate and Customer. Next, based on those results, count how many are for any given day (anything over 1 means that at least two different people where shown properties on that day). Below is some SQL you can plug into two queries, if you...Leave a comment:
-
Liam,
Welcome to Bytes! There are a couple issues keeping you from the answer you want. First, anytime all the numbers are the same (not just zeroes), the code won't be able to handle it because you're filtering out all the variables. You can solve this by counting how many times the numbers are the same--if they are always the same, just force insert what you want into the field (since there is no second-highest number, I'm not sure what this...Leave a comment:
-
Eric,
You could write a procedure that checks the state of each toggle. If all have been clicked, then the Save Record button is enabled. If not, it is disabled. Call it when the form opens (disabling the button) and then in the After Update event of each toggle button. That way when each has been clicked, the button will enable.Leave a comment:
-
You are quite welcome!Leave a comment:
-
Try this as the SQL for your Query:
Code:SELECT Table2.ID, DLookUp([Table2].[Opts],"Table1","[Table1].[ID] = " & [Table2].[ID]) AS [Values] FROM Table2;
Leave a comment:
-
gnawoncents replied to divide a table into equal groups, using a calculated field based on total recordsin AccessAngela,
We understand your concern. In your first iteration, 10% would be fine. The only problem is if you then took 10% of the remaining 90%, you're actually only getting 9% of the total records for the second iteration. In that sense, yes, you need the original number saved somewhere. Or, the code I suggested earlier could get you there all at once and be ready for future use. If, however, you need it saved in a separate query, but automatically...Leave a comment:
-
@Narender, as I read it, Stephcar wants the number of years between the current date and some past date. Dividing by 365.25 is one way of getting the difference converted into years.Leave a comment:
-
@Rabbit already pointed out one option, which will work if, as already stated, the QID is "always sequential starting from 1 for any chapter."
In order to find the best solution, however, we need some more information on how the results will be used.
- Will the results be displayed immediately to a form/report? If so, you could just create/modify the form's/report's filter.
- Will the results be used to do further...Leave a comment:
-
Welcome to Bytes, Stephcar!
Writing your code that way is actually giving you 12 divided by 31 divided by 2012 minus CurrentDate divided by 365.25. That is why it's returning a negative value. if 12/31/2012 is a fixed date, you're probably looking for something like the code below.
If it is not a set date and can change, check out NeoPa's caution on setting up dates...Code:(Date - DateValue("12-31-2012")) / 365.25Leave a comment:
-
So, just to make sure I understand, the user selects a ChapID and you want the query to return the top X records from MyTable where X = the NoOfRecs that correspond to ChapID, regardless of QID.
If so, and you are already using QueryDef to update the "Top X" records, all you need to do is replace X with a Dlookup to find the number of records you want to return. If you haven't already built all this, there are other options...Leave a comment:
-
NeoPa,
Thank you for the clarification. I will be more careful with dates and strings from here on out!Leave a comment:
-
When you say:
...are you saying that the user will input/select the ChapID?
Also, is there any reason the NoOfRecs couldn't go on the same original table? That would make it a little simpler. Either way, you could modify your query using a dlookup to find the number of records based on the selected ChapID.
I get the feeling though that there is something here I'm missing. Could you explain in a little more...Leave a comment:
-
NeoPa,
Thank you for the warning. I read your linked article. Just to make sure I understand correctly--you're saying that the date format needs to be identified inside the command line? Would it be different depending on where one is in the world or only on how it is already stored in the database? I don't want to lead anyone astray and appreciate the insight!Leave a comment:
-
gnawoncents replied to divide a table into equal groups, using a calculated field based on total recordsin AccessAngela,
Below is some code you may find useful and can modify to meet your needs.
Assumptions:
- There will be no changes to the inventory items (no record additions or deletions) once imported
- You wish to inventory 10% of the total records every month
- After 10 inventories, you want to have inventoried 100% of total inventory
- You want to inventory a new random 10% set for each of months 11...Leave a comment:
No activity results to display
Leave a comment: