Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Giving a Cell a name to reference from you can do some neat stuff with the Target parameter passed to the Worksheet_Change function:</p> <pre><code>'Add this function to the sheet that has the cell being '+changed by the user (Sheet 2) Private Sub Worksheet_Change(ByVal Target As Range) Dim strCellName As String strCellName = "ChangeMe" 'If the cell we changed was the ChangeMeCell If Target.Address = Sheet2.Range(strCellName).Address Then 'Store value Dim intLastRow, intValue As Integer intValue = Range(strCellName).Value 'Find the cell in Sheet 1 Column A that matches this month intLastRow = Sheet1.Range("A:A").End(xlDown).Row For Each cl In Sheet1.Range("A1:A" &amp; intLastRow).Cells 'Ensure cell value is date If IsDate(cl.Value) Then 'If date is today's date 'Note that Math.Round(&lt;date&gt;, 0 ) essentially removes the time '+from any date value so #01/02/03 04:05:06# becomes #01/02/03# If Math.Round(cl.Value,0) = Math.Round(Now,0) Then 'Update column B's value Sheet1.Range("B" &amp; cl.Row).Value = intValue End If End If Next End If End Sub </code></pre> <p>This assumes you have the sheet layout with the "invoice values" in Sheet1 and the cell being changed in Sheet2. You need to give that cell a name.</p> <p>Using the cell Name box to the left of the Function bar call the cell that changes "ChangeMe" or anything you wish to change it to, update that cell name in the first line of the function and this function will do all the rest.</p> <p><img src="https://i.stack.imgur.com/nhmzM.png" alt="Where you find the cell name and change the value to &quot;ChangeMe&quot; "></p> <p>It is important to note that the dates must be correctly formatted for your systems region. to make sure it is showing the right month - format them into LongDate so you can see them as 08 March 2013 instead of 03/08/13 which may get confusing the longer it goes on. <em>Speaking as a British programmer, dates are the bane of my life!</em></p> <p><img src="https://i.imgur.com/BSmhIcl.png" alt="This shows the date format being in long date format and the number value of the B column being displayed with formatted Currency"></p> <p><strong>Edit:</strong> I have update the code to compare the dates by the full date minus the time, instead of the previous monthly comparison, if you still need to subtract or add a month to either date value, just use the <code>DateAdd("m", &lt;date&gt;, &lt;value&gt;)</code> to add or subtract the month.</p> <p><strong>Edit:</strong> <a href="http://office.microsoft.com/en-001/access-help/datepart-function-HA001228812.aspx" rel="nofollow noreferrer">DatePart Function page</a> is a useful resource for those wanting to know more about DatePart()</p>
    singulars
    1. This table or related slice is empty.
    1. POcapture data from a cell and paste the value into another cell on a different worksheet
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTom 'Blue' Piddock
    1. USTom 'Blue' Piddock
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POcapture data from a cell and paste the value into another cell on a different worksheet
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthis does work, but I want the number that is keyed in. Not the number that has changed. At least...that's how I imagined it to work. I'm thinking this could work for me if I were to use this when she goes to change the current month. When she does this, the old data that changes can then be placed in the month prior and so on. I need to test it.
      singulars
      1. PO
      1. USdonviti
    2. CO@blue- How would I get the date to look for the prior month? or Better Yet for the value to match the Date of the cell that she is changing. For Example, she is going to change the data in Column B next to the date in Column A. That data feeds other sheets. If I can figure out how to grab that data based on that cell I'm good to go. In essence a vlookup within the macro. Hope that makes sense.
      singulars
      1. PO
      1. USdonviti
    3. COUse dateadd on the Now variable when checking if today's month is the same as the month in column, to go back one month use -1. If DatePart("m", cl.Value) = DatePart("m", DateAdd("m", -1, Now) ) Then
      singulars
      1. PO
      1. USTom 'Blue' Piddock
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload