Need Field To Add Up How Many Times Another Field Is Changed. Is This Possible?
Aug 29, 2005
I have Microsoft Access 2002 sp3
I have created a query that can used for tracking how many times a file is looked at or reviewed. My question is if I have a Field labled "Activity Date", can another field be created to add up how many time the date is changed in the "Activity Date" field. I am new to Access and have been learning on my own so any help would be greatly appreciated.
I have created a query that can used for tracking how many times a file is looked at or reviewed. My question is if I have a Field labled "Activity Date", can another field be created to add up how many time the date is changed in the "Activity Date" field. I am new to Access and have been learning on my own so any help would be greatly appreciated.
Could someone look at my code and tell me why my “Me.TargetDate.Undo” won’t work?
First, let me say that the TargetDate field has an On-Click event that calls a module written by Allen Browne), which is shown below and works great.
=CalendarFor([TargetDate],"Select a date from the calendar ")
What happens is that the Me.TargetDate.Undo event always keeps the date selected in the calendar.
Based on something I read from Rual Guy on another issue, I’ve used the TargetDate_LostFocus routine to check for some additional data entry errors. I’ve tried moving the code in “Private Sub TargetDate_Exit(Cancel As Integer)” to the LostFocus, but it doesn’t make any difference to the Undo. I’ve also tried “I’ve tried “Private Sub TargetDate_Change(),Me.Undo…” but that doesn’t work either. :confused:
Private Sub TargetDate_Enter()
If Not IsNull(Me.TargetDate) Then OldTarget = Me.TargetDate End If
End Sub
Private Sub TargetDate_Exit(Cancel As Integer)
If Not IsNull(Me.TargetDate) Then If DateDiff("d", Me.DateOpened, Me.TargetDate) < 0 Then MsgBox "You have selected a date that is before…bla bla" Me.TargetDate.Undo Me.TargetDate.SetFocus ElseIf DateDiff("d", Date, Me.TargetDate) < 0 Then MsgBox "You have selected a date that is prior to today's date…bla bla" Me.TargetDate.Undo Me.TargetDate.SetFocus ElseIf Weekday(Me.TargetDate) = 1 Or Weekday(Me.TargetDate) = 7 Then MsgBox "You have selected a date that falls on a weekend..bla bla" Me.TargetDate.Undo Me.TargetDate.SetFocus End If End If End Sub
Private Sub TargetDate_LostFocus() If Me.TargetDate > OldTarget And Me.cboAssignToID <> OldAssignee Then MsgBox "The target date can only be changed if …bla bla" Me.TargetDate.Undo Me.TargetDate.SetFocus End If End Sub
Within my main table / form I have six status drop down boxes and would like to record with a date and time ever time they are changed and save this data in a second table.
If I cant do this with the six drop downs, can I do it if any change has happened to the recorded.
I understand the problems that can be brought upon ones self by creating the table with lookup fields, But if the table was orginally designed with them and then the Field is then changed to a Text box instead of a Combo box will the inherent problems associated with the lookup within a table disappear?
Im a relative access newbie, and am updating an already created form. I want the user to be able to change the value in a field , then when they exit the field, for it to be greyed out, so that no more editing of the field is possible.
I know how to lock the field in design view, but I want the field to lock after the user has amended it. Is this possible?
The database contains the records of a collection of thousands of photographs and negatives.
One of the fields contains information on the subject matter of each pic and can sometimes be very long. The field was changed from text to memo so as to hold more characters but they appeared in one long string, which means a lot of scrolling to see the information. That has been changed back to text and we are adding a second or third record such as xxx-xxx-xxx cont1 xxx-xxx-xxx cont2 so as to get shorter strings. Is there a way to make the text wrap onto a second or third line automatically after a specific number of characters have been entered or can a carriage return be put in to force the text to a new line?
Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.
Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.
I created a database with 10 tables and 8 relationships. Then, I learned about the Autonumber field type and decided that I wanted that to be the primary key of one of my existing tables, for a field that was already linked to 2 other tables. I had to delete the relationships and the original field to make the change, and Access said it had to delete an index, too. Now, Access won't allow me to re-establish those relationships.
I found that I had to change the fields in the other tables to match the Autonumber type and name of the original field, but now I am getting an error message stating, "Invalid field definition 'CustomerID' in definition of index or relationship. What does that mean? The field types and names are all the same.
On my form have a status field with about 7 options to choose from a combo box, i have these values set in another table. What i would like is for my other field, which is a date, to automatically change whenever the status field is changed. So i know how long ago the status field was set. So basically if i change the status to complete i want the date to then set the date and time now.
I Have made a change to a field in my tables. it was was based on ethnic background and originally i had just created the field but had not added in the options ( via adding it into the row sources).
So now the tables field have been updated but unfortunately on the form it has not updated into the dropdown i had created containing the options..
i have a form that shows payments (checks) that have been issued. sometimes those checks need to be voided and i want 2 fields (Updated By and Updated Date) to pop up when the payment distribution field is changed to a void status.
payment distribution: "I" for issued and "V" for void
i've gotten the On Change Event to work with VBA when the payment distribution changes from "I" to "V" and the 2 new fields pop up but if i exit the form and go back in to look at that record, the fields are gone. Is there a way to make the fields permanently if the payment is "V" on the form?
this is what i have so far for the On Change Event:
If [payment distribution] = "V" Then me.cmbo_UpdatedBy = True me.txt_UpdatedDate = True Else me.cmbo_UpdatedBy = False me.txt_UpdatedDate = False End If End Sub
A user wanted to key in the data in a single form where she can select where a particular procedure is located. The procedure could be duplicated and placed in a few departments.
My tables are as follows:- tbl_Proc_Dept:- ProcNo - Text (Primary) TrgConducted - Yes/No DeptAbbv - Text (Primary)
tbl_Dept:- DeptAbbv:- Text (Primary) DeptDetails:- Text
I created a form where i have the following fields in the form:- 1. ProcedureNo - Text 2. TrgConducted - CheckBox 3. MainDept- ComboBox (DeptAbbv data field) 4. Finance – CheckBox (DeptAbbv data field?) 5. Admin – CheckBox (DeptAbbv data field?) 6. Purchasing – CheckBox (DeptAbbv data field?) 7. Facilities – CheckBox (DeptAbbv data field?) 8. MIS – CheckBox (DeptAbbv data field?)
How can I add multiple deptabbv field in a single form?
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc. -If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout). -I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
I want to use to count how many times a record is displayed in the period and lesson field, in another table, so that this data can be used to show how many people are attending a class. e.g how many times people are attending art 1, by counting how many times art one appeared in the first table.
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
I have a question related with a report i`m using to print labels. I am not familiar with Access and this is quite a challenge to do it alone.
In the attached file there is a report called "MICRA", when started it asks that you want to select (default is 1 and special select in this case is 11) and next it asks "SPS", the value entered there is printed in the bottom right corner of the report.
My question is is it possible if in the SPS field is entered a special value (for example "MASS") to print 30 labels of each selected label with text in this fiels = "val.1"; 15 with text in the field = "val.2"; 10 with "val.3" and 10 with "val.4" and after that to print next label with same rules...
And if not entered "MASS" to print just 4 copies of every label.
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
In my Access 2010 database there is a form with a large number of checkboxes enabling me to select/deselect certain record details.
Each click on one of the checkboxes triggers a VBA routine setting true/false values in one of several tables linked to the main table.
A query over all records is filtered by the true/false values of the linked tables and the resulting set of records is displayed in the (continuous) form.
The header of the form contains a text field with the value "=GetRecCount()" displaying (correctly, but randomly fast) the number of records selected:
Code: Function GetRecCount() As String Dim NumRecs As Long Dim rs As Object On Error Resume Next Set rs = Forms![Super Search].RecordsetClone rs.MoveLast NumRecs = rs.RecordCount Set rs = Nothing GetRecCount = Format(NumRecs, "##,###") End Function
Now, the funny thing is that each click on one of the boxes results in 1 call of the VBA routine setting table values, but 21 calls (!) of the text field "=GetRecCount()" in the form. I have established this by incrementing a public variable at each pass.
Why a field in a form is updated 21 times when a filter is applied?
I have a onclick tied to a label (for decoration purposes) that when clicked it launches VBA that essentially updates a form. All that part works except it will not recognize any changed value of the field I was last in?
Just to try to explain best as I can what happens.
- Form gets opened - I change field (quantity field) - I click the Label - It reverts to pre-existing value.
if I click off of the text field first then do the onclick - it recognizes just fine.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
