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 have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
I have a database with a filter on the form which users can apply I know at the bottom of the form it highlights it as filtered but I want something a bit more prominent displayed on the form when a filter is active.
I am trying to place some code in the "expression is" section of conditional formatting. Need some help converting from code to conditional formatting format.
I have set up a check box "Check83" next to a standard control box "Title(O)". Below is the code I am currently using. This code is in the after update section of the check box, and in the oncurrent section of the form.
This code turns the box blue when I click the check box.
Private Sub Check83_AfterUpdate() If Me.Check83 Then Me.[Title(O)].BackColor = vbBlue Else Me.[Title(O)].BackColor = vbWhite End If End Sub
My form has 46 records and all have check boxes linked to the control box with the code above. When the box is blue in record 1, I don't want there to be a blue box in record 2. Can't figure out how to do this. Tried to do in with code, but failed. Thought it may be possible using conditional formatting.
Because they are more efficient, I am using hyperlinks to connect my forms. I want to be able to deactivate hyperlinks based on criteria that are selected.
I have a report with a list of employee names (and other information). A handful of them need to be a different color (other than default). There will always be more than 3, so I can't use the conditional formatting tool.
I have a continuous form in Access 2003 listing products for return for a customers. I am in the process of creating a completion form which will just have the product number, model number and then a command button called complete.
What I'm doing is have it so when the person clicks the button it adds a value to a field saying Complete. OK. But, what I then want it to do is for any record thats associated with this customer where the field = complete to have the command button gray out.
Is this possible? I know I can conditional format a text box to do this but can't see a way to do it with a command button? Any ideas?
I have though of layering a transparent button and having a textbox behind this with complete in and having this change colour but not sure if thats the best way?
Could some kind person out there help me out please? I have been 'dabbling in' the area of "conditional formatting", particularly in the area of changing the colour of my active form based on the result of whats input on a text box. I have had some success in this area but where I am getting confused is getting the colour of my form to change on the basis of what is entered into a text box control that does not match exactly. My form background changes to the required colour when its value is 'set exactly' or matches, but what I am trying to achieve is to change it according to the first letter of entry regardless of what follows thereafter? It always starts with a letter, either a ‘D’ or ‘V’ to indicate whether its ‘Vital’ or ‘Desirable’ and is subsequently followed by no more than 6 numbers and finally a letter. Basically I need my form to change colour based on the whether it is either a ‘V’ or a ‘D’ irrespective of what follows???
What I have been using so far is:
Private Sub Model_Number_AfterUpdate() If Me.Part_Number = "V111145K" Then Me.Section(acDetail).BackColor = 12632256 'Light Red Else Me.Section(acDetail).BackColor = 8421631 'Blah Gray End If End Sub
Private Sub Form_Current() If Me.Part_Number = "V111145K" Then Me.Section(acDetail).BackColor = 12632256 'Light Red Else Me.Section(acDetail).BackColor = 8421631 'Blah Gray End If End Sub
Your help would be gratefully appreciated:confused:
I have a form that has invisible fields. Depending on the navigation to the form, those fields are turned on/off using the visible command.
However, the tab order on the form always takes the user to the first field visible on the form.
How can I tell access to default me a specific field first.
i.e. I have chosen to amend a training file via payroll number. I would like the payroll number data entry box to be the active field when the form opens.
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 am a newbie to Access. I have a check box on my form that defaults to checked, meaning the record is active, what I want to do is when the box is unchecked, not have the record appear as an active record and just be on the table. I have the check box on the table and when I uncheck it on the form it unchecks on the table but don't know how to stop it from appearing in the active records. Thanks for your help, April
hi I want to set record selector on the form with a record number that I am founding it with this code: Code:Dim con As ADODB.ConnectionDim cmd As ADODB.CommandDim rst As ADODB.RecordsetSet con = New ADODB.ConnectionSet cmd = New ADODB.CommandSet rst = New ADODB.Recordsetcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "hazineh-bime.mdb" & ";Persist Security Info=False" cmd.CommandText = "select * from table1 where salp=" & Val(Trim(Form_mainhazineh.sal.Value)) & " and mahp=" & (Trim(Form_mainhazineh.mah.Value)) & " " Set cmd.ActiveConnection = con rst.Open cmd, , 1, adLockReadOnly If rst.RecordCount = 1 Then ' found the record else end if I mean I want to change the active record on the form with another one . how can I do it?
Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013 row 2 user Y from 15/11/2013 row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
I have created a form to select multiple records for a transmittal report. I added a button to the form which I would like to open the report to view the current record. In the On Click event I added the following code:
Private Sub cmdPrint_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[TransmittalID] = """ & Me.[TBLTransmittal.TransmittalID] & """" DoCmd.OpenReport "RPTDwgTransmittal", acViewPreview, , strWhere End If End Sub
The debugger keeps highlighting the row above shown in red. I've used this same code before on other forms and it worked but not sure why it isn't working this time. The only difference with this form and previous ones is that this form contains a multiple values combo checkbox (hope that's the correct term). The query that the report is based on changed the names of the field names so that TransmittalID on the form is TBLTransmittal.TransmittalID in the query.
I've tried the code with and without the extended name but each time the "Do.Cmd..." is highlighted.
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code: Private Sub cmdNewProject_Click() 'set focus to sbfProject sbfProject.SetFocus 'navigate to new record DoCmd.GoToRecord , "", acNewRec End Sub
I have text boxes that combine two fields into one, for example the control source is =IIf(IsNull([BuckC])=True And IsNull([BoostC])=True,"",[BuckC] & " , " & [BoostC]) . For simple its just =[Buckc] + ": "+[BoostC]. I am trying to set a condition that if one of them exceeds a certain number the background color will change to yellow. I know how to do this if its only for one field but I am wondering if it is possible to do it with multiple fields?
Hi all, sorry to bother you all, especially if this has already been answered, but I can't seem to find the solution to it. Assuming that what I've been doing has been correct that is.
I have three (3) tables with the following fields (where PK is primary key); [tbl Ingred]: IngredID (PK), ProdID_fk, CompCode, IngredName, Weight, Symbol, RP01, RP02, RP03, RP04, RP05, RP06, RP07, RP08, RP09, RP10 [tbl Prod]: ProdID (PK), ProdName, FP, BP, SBU, ProdNotes [tbl RPCalc]: RPs, RPs_long (PK), Sym, Limit, Sym_1, RP_1, Comp_1, Add, RP_01, RPNotes
[tbl Prod] has a one-to-many relationship with [tbl Ingred]; that is, for one ProdID, there can be many IngredIDs. The tables are designed so that the final user-displayed form is 'user-friendly'.
I've made a qry called [qry Qcalc] used to calculate the the Quotation (Q) of each of the 10 RPs (RP01 - RP10). Which simply for the first one is; QRP01: [Weight]/[tbl RPCalc_1].Limit, where the [tbl Ingred].[RP01] is linked to a corresponding [tbl RPCalc], by the [RPs] field. As for one [RPs] there may be multiple [RPs_long]. The way it is set up is so that the [RPs] and [RPs_long] are mapped correctly, to be used for calculations.
However, I am now stuck with a problem. I need to sum up these QRP values ([QRP01] - [QRP10]), based on (at least?) two conditions. The user can enter in their RPs; e.g. R10, R20, R21 in any of the [RP01] to [RP10]'s, and in any order.
What I need is to be able to obtain a sum of the Q values for each [RPs_long] (e.g. R10, R20, R21), rather than being the sum of the fields [RP01] to [RP10]. Also, each [RPs_long] may or may not be able to be summed up, depending on another field [Add] which has a "Yes" or "No". "Yes" is for it being able to be summed up, and "No" for it not being additive.
So the end result would be; for each [ProdID], there will be multiple [IngredID] where each will have up to ten (10) "RPs" (that being the [RP01] to [RP10] fields). These inputted "RPs" are then related to [tbl RPCalc] to be able to obtain the Q-values. If the Q-Value is not greater or equal to 1 (>= 1), then the sum of the Q-values for each [RPs_long], provided that the [Add] field is "Yes", will then be able to determine what are the final output RPs to be used, i.e. for the sum of the Q-values are greater or equal to 1 (>= 1), that [RPs_long] will be used.
I've been stuck on this for quite some time, and I know that if I change the whole table structure (like having a field for each RPs in the [tbl Ingred]), I might be able to make it much easier for myself. However, by doing so, it will not be able to offer as much flexibility in the calculations, and also, may not make it as 'user-friendly'. Another thought was maybe to create another table with just the [CompCodeID] (based on [tbl Ingred].[CompCode]), [Weight], [RP], but I'm not too sure how this will work either, but I might give it a shot if this current method doesn't work.
I hope that this made sense, as it is all quite complex (and confusing) to me. Any help on this matter will be much appreciated. Thanks in advance.
Basically I have a form which records primary and secondary reasons for people writing off money, and in the secondary reasons I have an option for 'other', in case any of the other options aren't applicable. There is also a comments section on the form which is optional.
My issue is, I want the comments section to become mandatory if the 'other' option has been selected. I don't want it mandatory all the time, and I don't want it disabled if 'other' isn't selected, but I need to force people to write at least something in there if they have selected other.
I tried variations on code I found from other places but was unable to get this to function, and I am not sure if this can be done with validation or something like that.
I have a field on a report that I was able to make bold using conditional formatting. I want to make the other fields in the row bold as well if the value of the 1st field equals a set value.
I put conditional formatting on the Contractor field
EX: Field Value Is equal to "Estimations"
I was Bid Amount, Sq Footage, =[bid amount]/[sq footage] and =[bid amount]/[estimate] fields to also be bold if the Contractor field is equal to Estimations.
Have a continuous form with 2 date fields in each record .
If dateField1 is empty ,Then I want a way to make DateField2 Take its Place .
I know how to make things happen with conditional formatting on one field by UsingIsNull ,I have tried sending datefield2 to back.But cant work out how to bring it to front If DateField1 IsNull.