conditional formatting on continuous forms


I have an accounting database with queues that list each requisition's important information (ID, vendor, dates, etc) plus the its status (not yet submitted, approved, ordered, delivered, filed, etc).

I'd like the font of the status field to change color depending on its value. I can't use the standard conditional formatting because there are more than 3 statuses.

I've tried an If/Then statement, but the form only looks at the status of the first record, then colors the font of the status fields for the consecutive records that same color no matter what their value. How do I get it to evaluate per record As in, if the first record has a Submitted status, its font is blue, but the next record is Ordered so its font is red.

Here's my code, I used it in the OnOpen event for the form:
If req_process_status_rec_id = 2 Then
process_status.ForeColor = 13209
ElseIf req_process_status_rec_id = 1 Then
process_status.ForeColor = 8388608
ElseIf req_process_status_rec_id = 3 Then
process_status.ForeColor = 8388736
ElseIf req_process_status_rec_id = 4 Then
process_status.ForeColor = 16737843
ElseIf req_process_status_rec_id = 5 Then
process_status.ForeColor = 52479
ElseIf req_process_status_rec_id = 7 Then
process_status.ForeColor = 6723891
ElseIf req_process_status_rec_id = 8 Then
process_status.ForeColor = 16711935
End If

Posted On: Monday 22nd of October 2012 05:45:50 AM Total Views:  112
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




"Conditional formatting"

I have a continuous form named frm1. In this form i have two fields named lastname and salary. Lastname field is a textbox and it comes from the table tbl1. Salary field is a combo box and it comes from the table tbl2. At tbl1 i have another field called price and it takes 2 values(like a combobox): "TRI" και "KAL". I inserted text values at the field salary of table tbl2. So, from the combobox i will get all the values i inserted. These values are not connected to any of the fields of table tbl1. I want for every record of the form, for the record's lastname (of table tbl1) to check if the value of field price is "TRI", and if it is, then at the frm1, to turn the text color of a specific value of the field salary into red color. (i.e the value "PL"). If the value of field price is "KAL", then at the frm1, i want to turn the text color of a specific value of the field salary into red color. (i.e the value "KP"). So, when i click on the combo box salary at the lastname Johnson (for example), at whom the field price has the value KAL, the value PL will be red. The function that will move to the next record of the form is: Docmd.GotoRecord, ,acFirst Do while Me.CurrentRecord -1 < Me.RecordsetClone.RecordCount sample code do.cmd.GotoRecord, , acNext Loop
VIEWS ON THIS POST

72

Posted on:

Wednesday 17th October 2012
View Replies!

Date formatting

I'm working on a bar code project and need to add a prefix to my query results. The Access 2003 query runs from an SQL ERP database. My query results look good except for the date. Example: [Part] and "241"&[Part] return the correct R-910 and 241R-910 in the fields. But the [Date] and "17"&[Date] return 05152008 and 175/15/2008. I can't get the prefix expression to recognize the mmddyyyy format and produce the 1705152008 result I'm looking for. I've even tried to DatePart the month, day, and year to different fields and add them back up to no avail. I'm not too big on SQL, but can send the SQL view if needed.
VIEWS ON THIS POST

104

Posted on:

Wednesday 17th October 2012
View Replies!

Help with add button on forms

hi.. i am currently having a problem with the add button on my "frmEmployee2". once the button is clicked, a message "you can't go to a specified record" appears. what seems to be the problem any advice is welcome and appreciated. thanks in advance. here is the link to my ms access file: http://www.gigafiles.co.uk/files/636/HRIS/human%20resource%20info%20system_2006-09-27.zip thanks a lot...
VIEWS ON THIS POST

187

Posted on:

Thursday 18th October 2012
View Replies!

Problem: Reports linked to forms

Heres the situation.. I have an input form where all the data entered gets transferred to a single table. I also have a report created so I can print it off and view whatever information has been entered onto the form At the moment when I go to view the report it is showing every record that I have previously entered using that form but I want it to only show the record I am currently entering. The record source for the report is the table where the data is being stored. Hope this makes sense to you .
VIEWS ON THIS POST

185

Posted on:

Thursday 18th October 2012
View Replies!

Urgent help needed in formatting a Date??

I have a column called date, right now it is in m/dd/yyyy format and i want to convert this to dd-mmm-yyyy format how do i do this thx for any help
VIEWS ON THIS POST

199

Posted on:

Thursday 18th October 2012
View Replies!

Filtering sub forms?

I would like to be able to apply more than one filter to a subform. It seems no matter how I try I can only have one filter affecting the records in my subform. I have had zero luck getting the child / master link on the form to return a satisfactory result in the subform as soon as i try to apply a second master / child filter. It seems the filters I'm trying to apply aren't 'stacking' and I seem to be getting either one or the other judging from the reports that are being listed in my sub form. My data table consists of a few details concerning offices. Namely what Region the office is in, how many staff at particular grades, the office location / address contact details, and wether or not they reside in a 'strategic zone'. I've used check boxes on my data table to indicate wether an office is inside a strategic zone or outside a strategic zone. My combo box is currently working great, it filters out all the records and only shows the offices that match the value in the 'Region' field in the subform. I just can't figure out how to get additional filters to work at the same time. Ideally I would like to have the combo box which is working great, then a couple more conditions that the user can select to apply additional filter, especially the 'inside strategic zone' check box and the 'outside strategic zone' check box. I would also dearly like to have another combo box that will let users select 'Clusters' that an office may belong to and filter out those records that don't match. I can use the right click on a field in my form and 'apply filter by selection' and of course that works great! Filtering all the way down until I'm left with a very short list that matches the same value of many filtered fields I've right clicked upon. But ewwww...that is not what anyone wants to have to rely on to give them data on the screen that conforms with the filter criteria they wish to apply. Secondly, (sorry almost done!) how can I show the number of records that have a particular Yes/No check box ticked. Was thinking along the lines of having a couple of text boxes that count all the 'inside strategic zone' check boxes that are ticked and the same for 'outside strategic zone' How can I do this As I'm sure you will have picked up on, I am just setting out on my Access Database Learning curve, I have very limited experience but I am hoping to develop my own skills over the next few years. I have been humbled by the knowledge of the patrons here and warmed by the goodwill shown to helping the novices like myself who come here asking for your assistance.
VIEWS ON THIS POST

159

Posted on:

Thursday 18th October 2012
View Replies!

Telephone number - formatting issue

I am trying to get a "formatted phone" number to appear on a "make table" query. I have revised the input mask properties to format the field to parantheses around the area code and a dash after the first three digits. The query works fine as long as I run a "Select Query" but when I change the query to a "Make Table" query, the new table does not come across with the telephone number in the correct ( )xxx-xxxx format! Using the Input Mask wizard, I've tried both options of storing the data and still get the same results. I do NOT want to have to go into this new table each time and reformat the telephone number as I'm sending the table directly to a switchboard in a different database. Any ideas on how to keep the formatted phone number intact
VIEWS ON THIS POST

91

Posted on:

Thursday 18th October 2012
View Replies!

populating the same field in 2 forms

I have 2 forms. Information from one field links both forms (though it is not the primary key). Using a "open form" button I want the field on the second form to be populated with the contents from the same field on the first form, when a user selects the open form button. I imagine all of this is very doable in SQL, however I am using Access GUI to construct my database and have no time to learn SQL at this stage. Any ideas would be greatly appreciated.
VIEWS ON THIS POST

184

Posted on:

Thursday 18th October 2012
View Replies!

Exporting MS Access to Ms Excel with Excel formatting

Ive read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel. Problem Im having is that I dont know which one would work best for me, so I thought since Ive gotten such great advice from this form I will ask. I have a query that updates a table in access. What I would like to have happen in the export from access to excel Currently I have a number of queries that updates number of table daily. Then I manual export the tables to a excel files Then I spend 15 -20 minutes formatting the excel fine. I format all the table to excel files that same way. Once this is done I email it out in excel format to different managers that need to make commits and then email the spread sheet back to me then I import the spread sheets and record there comments. If at all possible I would like all the files to import into one spread sheet just into different tabs. I have a bout 4 different group. Below are some of the articles Ive read about exporting from access to excel. Looking for any advice example ect http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm http://www.vb123.com/toolshed/98docs/excelbe.htm http://www.tek-tips.com/viewthread.cfmqid=1201057 http://zmey.1977.ru/Access_To_Excel.htm http://www.databaselessons.com/access-data-to-excel-2.php
VIEWS ON THIS POST

232

Posted on:

Monday 22nd October 2012
View Replies!

Reformatting my data

Hi . I'm pretty new to Access. I have a script that outputs data in tab delimited format, using the following scheme Year Name Title P1 P2 P3 xxxx xxxxxxx xxx xx xxx xx where P1, P2, P3 are each data points for the given row. That is, each set of data has multiple P's, and they are stored in this format (each as another column). The data, when imported into access, looks just like this. What I want to do is set it up like this Year Name Title P xxxx xxxx xxxx (this part refers to a list of all P's) but I'm having a tough time getting the data to this format. How do I get all my data to refer properly
VIEWS ON THIS POST

125

Posted on:

Monday 22nd October 2012
View Replies!

Refreshing subforms - as you type

Hi- I have a form that I use as a look up with a subform which contains query results. The text boxes on my form update the subform after the field is updated (after update). I am using the following code: =Forms!lookup.[document look-up (sub)].Form.requery This works perfectly to refresh/requery the subform after I update the textbox field. Is it possible to have the subform refresh/requery everytime I type a letter in For example, if I type in the word "apple", the results would start to filter as soon as I start typing the letter "a" and then even further with the other letters. I have a lot of records I am querying and sometimes a few letters get me the results I need and other times it takes a couple words. That's why I feel it would be helpful to have it refresh on each keystroke.
VIEWS ON THIS POST

144

Posted on:

Monday 22nd October 2012
View Replies!

Problem with mousewheel.dll and disabling scroll record on multiple forms

So I wanted to disable scroll records on all my forms so I did what Microsoft suggested here: http://support.microsoft.com/default.aspxscid=kb;EN-US;q278379 It seems to work most of the time, but sometimes it messes up and I get the following error. Code: Runtime error '91' object variable or with block variable not set The following line is highlighted when I try to debug it. Code: clsMouseWheel.SubClassUnHookForm I put the following code in just about every form I have because many of them consists of data entry. Code: Option Compare Database Option Explicit Private WithEvents clsMouseWheel As MouseWheel.CMouseWheel Private Sub Form_Load() Set clsMouseWheel = New MouseWheel.CMouseWheel Set clsMouseWheel.Form = Me clsMouseWheel.SubClassHookForm End Sub Private Sub Form_Close() clsMouseWheel.SubClassUnHookForm Set clsMouseWheel.Form = Nothing Set clsMouseWheel = Nothing End Sub Private Sub clsMouseWheel_MouseWheel(Cancel As Integer) MsgBox "You cannot use the mouse wheel to scroll records." Cancel = True End Sub I think the problem lies in the fact that sometimes these forms are open simultaneously or something. Anyone know why and how to fix this
VIEWS ON THIS POST

161

Posted on:

Monday 22nd October 2012
View Replies!

Function only working for some rows on continuous form

: I have a function that I created that returns the single sample weight of each material that is selected My function accepts 5 arguments: 1)matType-The material the user has selected 2)batchWeight-The batchweight of each material used 3)SumCem-Sum of the cement material batchweights used 4)CemSingWt- Sum of cement single sample weight 5)pigPercent- the percent of pigments being used Here is my function: Public Function GetSingleWeight(matType As Integer, BatchWeight As Double, SumCem As Double, CemSingWt As Double, PigPerc As Double) As Double 'returns the single sample weight of each material in the 'DM_SingleSampWt' calculation Select Case matType 'Cement Case 1 GetSingleWeight = BatchWeight / 164.5 Case 2, 3 'Course, Fine GetSingleWeight = CemSingWt / SumCem * BatchWeight Case 4 'Pigment GetSingleWeight = CemSingWt * 453.5924 * PigPerc End Select End Function I have an unbound text box on my continuous subform called DM_SingleSampWt. the control source for this text box is: =GetSingleWeight([cbo_matTypeID],[matBatchWeight],[txtCemSum],[txtCemSing],[txtPigPercent]) The problem is that only some of my Cases are being calculated. Cases 1, 2 and 3 do not work. I am receiving a #Error, error. If you notice, all of my cases do not use the same arguments, but I know it's necessary to have them when I call my function. If you can offer any assistance, I would appreciate it. Thank You
VIEWS ON THIS POST

52

Posted on:

Monday 22nd October 2012
View Replies!

TreeView to open forms

, I am here again. I have found on the web a great code to create treeviews in access and have been able to create my own but still need a final help. I would like to add a code to the click event so that when a selection (node) is made in the treeview, it will open a form which is based on a query which has a criteria to a txtfield on my forms. Ex. Treeview: -General + Form1 + Form2 -Other +Report1 +Report1 When I click on Form1, it should open a form names Form1, using a criteria that a type in a txtbox. Can anyone help me in finding a solution
VIEWS ON THIS POST

173

Posted on:

Monday 22nd October 2012
View Replies!

Switchboard stops my forms' VBA code from working?

I have several forms that I've built... Most of them have some sort of VBA code built in somewhere, like:Select a record with the key-value control, and the rest of the controls position to that record"OnChange event: Set bound control equal to the value of unbound control"...etc. Straightforward stuff. The record positioning code was created by Access's wizard for creating a combobox, for example. These forms work perfectly when I launch them from the Database window. However, I just created a switchboard, and when I launch them from the switchboard, the forms no longer work properly -for example, if I select a key value, the remaining controls remain blank. Is there a simple reason I hate being new to this... I think I'd take fewer blows in a fight with Tyson... (well, certainly I'd give up quicker! )
VIEWS ON THIS POST

109

Posted on:

Monday 22nd October 2012
View Replies!

Required fields in subforms?

I have what I think would be an easy problem, but I'm new to Access (and databases in general, I have to admit), and I'm getting nowhere on my own. I have a database with a main form that corresponds to a table of contacts. This main form has a number of subforms containing secondary information for each contact: correspondence records, secondary addresses, education information, and the like. I'd like several fields in some subforms--or the subforms themselves, if possible--to be required before a new entry can be saved. In other words, I don't want users saving records without certain key information--for instance, a contact's year of college graduation (a field within an "education" subform). I've tried setting fields on the table level to Required (& setting ow Zero Length to No), but Access will only display a dialog box and require data entry if a user clicks on a subform that includes such required fields. Otherwise--if they fill out only the fields on the main form (first name, last name, company, etc)--they will not be prompted to fill out required subform fields, and the record will be saved with a number of blank fields. Is there any way to make these subforms, or certain fields within them, required
VIEWS ON THIS POST

195

Posted on:

Monday 22nd October 2012
View Replies!

Popup forms

Hi , I have a main form showing a number if information for one project. Within this form I have yes/no buttons that when clicked, open a popup form where you can add additional data. The problem I'm having is that when opened, the record # for the pop form does not match the record # for the main form. Example: Let's say I'm in the main form and showing record #5. When I click the yes/no button to add extra info, the popup form opens but it shows record #1, not #5. Any ideas how to tell the popup form to link with the corresponding record would be very much appreaciated.
VIEWS ON THIS POST

116

Posted on:

Monday 22nd October 2012
View Replies!

Updating oracle backend tables from MS access forms(dynamically)??

, My requirement is this... I have to use MS Access as front end with ODBC connection to Oracle 9i DB. The application(forms) should be able to update, delete ,insert records into oracle tables(backend). i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to 1) search for the unique id in the main form such that the subform displays all its matches 2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE\t similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table. the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted.. How should i do this\t i am new to MS access .VBA, any help would be highly appreciated!
VIEWS ON THIS POST

288

Posted on:

Monday 22nd October 2012
View Replies!

Syncronising 2 forms

I have two forms in a maintenance database. The first allows users to enter details of the maintenance request and the second allows the administrator to enter cost details of materials etc needed. I want the administrator to be able to click a cmd button and the second forms opens automatically at the current record in the first form. I am fairly new at this gane so some clear guidance would be very much appreciated. Beeky
VIEWS ON THIS POST

169

Posted on:

Monday 22nd October 2012
View Replies!

Novice question about forms...

, I am pretty new to Access. I am able to create simple forms that will interact with tables, reports, etc.. I am trying to go a little farther and customize a form that will automatically be displayed when the user opens the file. I have read that you must have 'modal' properties set to yes. I have done this and when I enter then .mdb file, I still see the default menu. Do I have to write an expression within the properties And if so, on which command Also, I would like to become more familiar with customizing forms. Is anyone familiar with a web based step-by-step tutorial out there that will walk a person through the process I am not satisfied with the Help menu.
VIEWS ON THIS POST

109

Posted on:

Monday 22nd October 2012
View Replies!