Forms :: Change Datasource Of Form Field Based On Current Year
Nov 12, 2014
I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
in a continuous form i want to click on one record and have the one field change the background colour to highlight it. When I use the code: Field. BackColor = vbYellow it changes the background on all the records. Is there a code to say only for the record with focus?
I wish to change the backcolor of a field on a current record being dispalyed in a 'multiple items' form. However when using me.A.backcolor = makes refrence to all fields called A on the 'multiple items' form and therefore all fields change colour not just the current record.
My database has a front end that uses forms to allow the user to navigate within it, each year we create a new database which is based on the previous year's database but with all the variable information removed.
Within the database each form has a caption that starts with '2014-15' then goes on to say the form name.
Most of my forms also have a yellow background, (last year it was pink).
Both these areas were set up so that each year our users can distinguish between our databases, as they may have last years and this years open at the same time.
What I would like if possible is to know whether I can get a user to change both these things from within a form. So something along the lines of a button that when clicked would check all form captions and if within the caption it finds 2014-15 then change it to 2015-16 (or whatever the user specifies) but keep the rest of the caption. I would also want the same type of thing to happen with the colour so again within the background of the forms, if you find colour X then replace it with colour Y.
If this can't be done within a front end form, then could it be done behind the scenes, and if so how would I go about doing it?
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
I have an accdb file, holding all the tables on my server. Clients are linked to this one from their workstations. Speed is a constant issue mostly when more users are linked and I'm trying to find ways to make things faster.
Would it be possible to open a recordset when a form is loaded and set it as the form's data source? Would that make operation faster, at all?
The reason this idea came up is that it would be logical to use a small size recordset rather than large tables or queries that takes a while to load during starting form that use tables/queries with large number of records.
Can you have a form that will make a cosmetic change base on the value of a field?
So I have a Member's Detail form and I would like the form to look a little different (say header background a different color) based on the member type. Is this possible?
So I have a combobox that pulls values from a table. I am being requested to do an update to the database which means that I have to filter some values on that combobox based on a field on a previous form.For example:The main form is called "frmcontacts" and has a table field called "txttypeofcustomer" which can have two values: "customer" or "dealer".
The form that contains the combobox is called "frmnewissue" the combobox is called "cmbissuetype". "frmNewIssue" is opened through a button on "frmContacts". This combobox pulls the values from a table "tblissuetype". The values in that combobox are then: "Repair" "Return" "Question" "Replace".
If the value on txtTypeofCustomer is "Dealer" then all the values for the combobox remain visible. If the value of txtTypeOfCustomer is "Customer" then "Replace" cannot be visible or has to be greyed out.
How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
All, using access 2010. How do I display the default value of a date/time field to just the current year instead of using =date() to get the full date.
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
When I have a form called "SiteForm" open and click a button "NewCalloutButton" I would like it to open up a form called "CalloutFormEntry"
I've done this using
Private Sub cmdOpenDetail_Click() DoCmd.OpenForm "CalloutFormEntry", , , "CalloutID = " & Me!CalloutID End Sub
However this brings up all the records linked to the calloutID
What I'm after is to open the CalloutFormEntry from the SiteForm in a dataentry kind of format but to carry over the current SiteID based on which site is open on the SiteForm. That way there's no user error logging callouts to incorrect sites.
I have been looking some information on changing image based on form combo box selection on form.
I manage to do case by case but i need it in a simple code because their will be many employees just to avoid adding case by case code for each one.
Private Sub Emp_IDCombo_AfterUpdate() Select Case Emp_IDCombo.Value Case "AM-001" Imageholder.Picture = "C:UsersAMGDesktopam-001.jpg" Case "AM-002" Imageholder.Picture = "C:UsersAMGOne DocumentsHR & Admin DatabaseEmployee Picturesam-002.jpg" End Select
I have employees table where all images location is saved in text field and i have a combo box on form which is employee id.
Tables relationship Employees_table [PK] to Contracts_table [FK] via field name {emp_ID}
Fields Name Combo Box name on form Emp_IDCombo and row source is SELECT Employees_table.Emp_ID, Employees_table.EmployeeName, Employees_table.Emp_Pics FROM Employees_table;
Text field is located in employees_table called [Emp_Pic] for images location.
I have two combo boxes in the form header and command buttons in the form detail. The combos allow the user to select either a customer or a prospect, then the command buttons open forms that only show records pertaining to the customer or prospect selected. Combos are "4Custcbo" and "4Proscbo".
1.How do I change which command buttons are available depending on whether the user has selected a customer or a prospect? Do I use two different subforms or is there a better way?
2.How do I hide the other combo box once the user has selected either a customer or a prospect?
I tried writing a SQL query to produce the list of details in year wise DESCENDING order for the input - Agent ID.
In addition to that I need to get the sum of amount fields year wise in the report. How to achieve it???.
Find the attached report for better clarification.
First three column in the attachment is the outcome of the below listed query. Whereas the fourth column is the expected result which needs to be included..
Code: SELECT CessioneCredito.Data_Movimento, CessioneCredito.Anno, CessioneCredito.Importo FROM CessioneCredito WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente])) ORDER BY CessioneCredito.Data_Movimento DESC;
How to alter the code in order to provide the SUM of Importo field every year.???
I have a SQL table shown on a form which i wish to filter by current logged in user -
I have a local table storing the currently logged in user and all records in the SQL table have a field called userEmail
so - i have forms that take their datasource from a query which then only shows the user his/her records.
SELECT * FROM userDetails, dbo_TrackerMainData WHERE (((dbo_TrackerMainData.userEmail)=userDetails.user email));
that works fine - after much torment, i figured out that you must use the query as the datasource and not the table as the datasource with a filter, because that just didn't work.
anyhoo.
my problem then is that when i use a form in this way - ie, datasource is a query, i can't then modify any data in the table!
I need to have a few checkboxes on the form that set values - for instance archiveFlag and TestFlag are fields and i need to be able to change their value.
If i set the form datasource to the table (dbo_TrackerMainData) it works ok - my check boxes tick and set -1 (but i see ALL records)
if i set the form datasource to the query i see only 'my' records, but when i try to click the checkboxes nothing happens..
Any way to change the default way a field is selected in a form so that it doesn't highlight all the text when you tab?I have the standard black text on a white background but when the whole field is highlighted it looks ugly and I think is quite difficult to read until you click into it.
I am extremely new to Access. I have my database up and running ok(ish) and would like to know if in my data entry form, I can have the "Date Entered in Database" box display the current date that does not change from day to day. I know you can use the "Now()" function but won't this just change everyday?
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done 2)If it can be done, how can I do it?
I have an unbound listbox on a form which displays a list of staff, both past and present, based on an SQL query.
I want to be able to differentiate between current and past staff using different colors, i.e. Black text for current employees and red for employees who have left, based on a field (True/False) in the original staff table.
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I am making a very simple 'registration' database for a children's event in a couple of weeks.I the table/form there is a checkbox field called 'consent' which, if checked, indicates that a child can leave the event without parental consent.
There is a report printed on each child (a registration page which the leaders get a copy of). I would like on this report a 'red box' to appear if the child cannot leave without permission (i.e. the consent box is not checked). I would also like this 'red box' to appear on the form. I had thought of doing it this way - but I'm not sure if it's the best, or if it's possible:
Have a field in my table called 'consentindicator'. When the 'consent' box is checked, there is a period ('.') placed into the 'consentindicator' field. It is set to turn red when a period is present. That way, when the consent box is checked, a get a red 'box'.
