btw, although values will always be in order they may not all be output, so it's not just case of odd, even, odd, even (that would be too easy!)
I can retrieve the Previous Record's GroupNo value (via a function) in another text box and compare current GroupNoID with this in the Format Expressions and manage to change the colour, but it won't sustain this new colour for the subsequent same GroupNos, obviously as the values have changed.
So how can I....
Change the colour when the value changes.
Keep this new colour until it the value changes (increases) again.
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
I'd lile to show/hide selected subforms based on the the text in a txtfield. The text in the selection field (txtSelect) I'd like to use for that may be either one of the following:
book-01, book-02, book-03 etc OR, folder-01, folder-02, folder-03 etc OR, record-01, record-02 record-03 etc. OR it can be just a 4 digit number.
If the txtSelect contains "book" I'd like the subfrmBookdetails to be visible. If the txtSelect contains "folder" obviously the subfrmFolderdetails etc.
What I'm struggling with is the If-Then statement for selecting the partial text in txtselect.
I've tried things like this: On current me.txtSelect.setfocus if me.txtSelect.text="book*" then me.subfrmBookdetails.visible=true etc etc.
No joy.
I know how to get this to work in a query with a like-statement but I have not been able to find anything on this in relation to conditional formatting of a form. Any one and good ideas?
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?
I want to get the datediff of the last returned date from vacation and the current vacation start date. But how to get difference from the previous record's date to this current record's date. I want to show date difference in an unbound textbox. I have a contiuous form which shows all vacation list of employees. It shows how many times he went vacations and when he went and when he returned.
I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.
I have the following code:
Private Sub cmdSaveTradeAndExit_Click() DoCmd.RunCommand acCmdSaveRecord 'Save the current record DoCmd.Close 'Close the current form Dim CrId As Integer CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord Forms!frmTransactionMainActivePopUp.Requery DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId End Sub
But I am getting the following error:
Run-time error '2498':
An expression you entered is the wrong data type for one of the arguments
I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:
create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need
I am new to Access. I am using Access 2010. I have searched and found several articles on emailing the current record shown on a form but haven't been able to get any of them to work.
My goal is to have a user input information onto the form and then have one button the user will click that will save the current record, email the current record in pdf, and then close the form.
The form name is: frmCorrectiveActions. The unique identifier for a record is: Corrective Action ID.
The last coding I tried is below. I know something is wrong but have copied this code from another post about emailing forms so I'm not even sure what half of it means.
I am getting the message Run-time error '3075': Syntax error (missing operator) in query expression "Corrective Actions ID = 23' and when I hit debug this is highlighted: qry.SQL = strSQL
form Private Sub Email_Click() Dim qry As DAO.QueryDef Dim strSQL As String Dim ReportQueryName As String ReportQueryName = "qryCorrectiveActions" Set qry = CurrentDb.QueryDefs(ReportQueryName)
I'm trying to update an imported table from an excel spreadsheet with missing details. The table's records are in order so I just need to fill in a blank field with data based on the previous one as shown.
ID Name Location 1 Bob London 2 Larry 3 Harry 4 Jerry Glasgow 5 Paul 6 John Southampton
I need to fill in the location blanks simply with the last location details, so records 2 and 3 with London and 5 with Glasgow. Is this easily done or would I need to pull all the data into an array and work on it there?
I've tried searching for an answer but haven't had any luck.
I have a subform (called NAMES) on a main form (called MAIN) that lists names. Elsewhere on MAIN, I have a second subform (called ASSIGNED) that lists people that have been assigned.
When I double click a name on NAMES, it automatically adds them to the ASSIGNED list (who's record source is a table). What I want the NAMES form to do is conditionally format the NAMES form so that the record that's been added turns green. That way I can see who I've added and who I haven't yet added. Once I've added them all, the NAMES form is a simple list of all-green names. If there's a black name in the list, it's because I haven't yet added them to ASSIGNED.
I've tried a DLookup function, and I've tried a Value= conditional formatting rule, and neither one are working... I already got the 'double click to add' thing working, now I just need the name to turn green when I've added them.
I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form) i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new ext record
I have written some pseudo-code to show what I am trying to accomplish:
if keydown = down-arrow and current cell contents isnumeric then Cval = current cell contents if current record = last record then create new record move down 1 record set cell value of new record to cval+1 else move down 1 record if cell value = null then set cell value of record to cval+1 end if end if
I am creating a database for an annual golf tournament. Part of this is to allocate players to play off together at certain times.
I have a form bound to a table called tblteeofftimes. This form has five fields (continuous form) - four of which are comboboxes whereby user selects each player for the record (who will play together). The other textbox has the tee off time. the first tee off time will be allocated by user eg 7:00 am.
My problem is I need to update each ensuing record with a new time slot at 7 minute intervals. Rather than have the user populate the form themselves I would prefer this be done authomatically as the user moves onto the next record.
Private Sub ChargeReport_Click() On Error GoTo Err_ChargeReport_Click Dim stDocName As String stDocName = "Charges_Report" DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"
[Code] ....
Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.
I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.
I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.
There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.
However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2
I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.
I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.
Can I get a formula that can work this out automatically?
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.
I have three tables; Contacts, Companies, Company_Addresses
Contacts: ContactID (P) CompanyID OfficeName
Companies: CompanyID (P)
Company_Addresses: CompanyID OfficeName (P)
What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.
I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.
I have three tables; Contacts, Companies, Company_Addresses
Contacts: ContactID (P) CompanyID OfficeName
Companies: CompanyID (P)
Company_Addresses: CompanyID OfficeName (P)
What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.
I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.
right, struggling here. Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is: If ([StandardLetterType] = "Homework") Then DoCmd.OpenQuery ("qryHomeworkClearMailMerge") DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID] DoCmd.OpenReport ("rptHomework"), acViewPreview Else ' end if
and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this
Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs]
is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number.
On the form I have the standardletterID and its hidden but there.
Having a problem with a report, I have a form that i use to enter a new record to my database, what I want to do is put a button at the bottom of the form to print the record (via a report)
If I base this on a record that already exists, it works fine. However, If I have just entered the record the query the report runs off doesn't see the new record. To print that record I have to exit the completed record, then go back in to it and print. My aim is to remove this additional part of the process, I'm sure it's something really simple, but can anyone help?
