I work for a doctor, and I am creating a database to assist with surgery scheduling. There are a lot of pieces and parts to this process, and I am building a "bird's eye view" of tasks to be completed, such as "Notified OR," "Sent Surgery Packet," and "Scheduled Post-Op Appt." Each one of these fields has a lookup attached to it with three choices: "Yes," "No," and "Not Needed."
What I want is for the drop-down box with the three choices to be (a) automatically defaulted to "No," (b) immediately turn red if "No" is displayed or immediately turn green if "Yes" or "Not Needed" is displayed. In this way, the goal for our scheduler will be to get all the drop-downs to turn green (an easy visual cue that the patient is ready for surgery) by the date of the procedure.
I have a button on a Form which opens a report, passing in two WHERE clauses which are driven by two comboboxes on the Form (one for ProductType and one for ProductCollection).
Everything works great but I needed to add an "All" option to the comboboxes so that the user can get all Producttypes from a specific Collection or vice versa.
I've managed to add the "All" option to one of the comboboxes (using the UNION query method on the rowsource). I'm a bit stumped though, as to how I would actually tweak the function below to do a select * where the combobox value is found to be = All.
Code: Private Sub ProduceReport_Click() Dim strWhere As String strWhere = "producttype = " & Me.ProductTypeForReport strWhere = strWhere & " AND ProductCollectionName = '" & Me.CollectionForReport strWhere = strWhere & "'" Debug.Print strWhere DoCmd.OpenReport "Products Report", acViewReport, , strWhere End Sub
I've searched and can only find information setting the visible property when selections are made on an open form--I want to set the visible property when the form is opened depending on which avenue the user chooses from the navigation form.
I have a navigation form (Form A) and a second form with a subform (Form B). The user will choose from a combobox, either an existing record or a new record on Form A. On Form A there are two buttons, one that will take the user to the correct record on Form B for editing and one that will open to a new record on Form B. Once Form B is open, all controls will be blank (if a new record is chosen) or with certain controls prepopulated if an existing record is chosen.
What I want to do is to hide controls on the main form (not the subform) of Form B if the user chooses an existing record. Form A's buttons work correctly to open Form B right now. I want to be able to hide prepopulated controls on Form B if the user chooses an existing record from Form A.
Here's the code I'm using to open Form B to an existing record. I'd like to set the visible property here if at all possible.
NOTE: "Form A" and "Form B" are not the actual names of my forms--it's just easier for illustration purposes here in my question.
How do I conditionally change the Back Color and Alternate Back Color based on the value of notes.FollowUpDate? I have a continuous form using the following code:
Code: Private Sub b_all_past_Click() Me.resultsFrame.SourceObject = "FollowUp_bystaff" Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE (((Notes.FollowUpDate) < Date()+1) And ((Notes.followup_person_id) = GetCurrentUserId()))ORDER BY notes.priority,(notes.FollowUpDate) DESC;" ' End Sub
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....
I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.
I am trying to enter an experssion into the grid for age groups. The result will be a text value based on the value in a column named Age. There are three groups, '<18', '18-30', and '31+'. I tried using the following but the result that is returned is #Error.
age_grp: Switch([age]<18,'<18',[age]>=18 And [age]<31,'18 -30',[age]>30,'31+')
Do I have the syntax wrong? or maybe I'm going about it completely wrong.
I have also tried a nested iif statement Age2:= iif([age]<18,'<18', iif([age] >=18 And < 31, '18-30'), iif([age] >30, '31+')). I get the same error message.
This worked for displaying the value on a report =IIf([age]>=18 And [age]<=30,"18-30",'31+'). However, I need to sort and group by the value so I can count the number in each group, so I wanted to put it into a query.
The following problem. Within my main form I have a status field. Dependent of the status of the main field I would like to lock the total record in my sub form at once. Within which event of the sub form could I achieve this and which property locks a total record?
I have a subform which has loads of check boxes for each record but i want to suppress certain boxes depending on criteria in each record. I think i need to do a For..Each statement but, as my user name suggests, I'm buggered!.
I have built a code database where right now I display my sample code in a textbox on my main form. I was hoping to replace the text box with a Rich Text Box. I could then format the code to sort of match what would be shown in the VB/VBA IDE.
I know the Rich Text Box was disabled in Access 2003 for security reasons. I also know that there is another free RTF control. I was hoping to find out how Microsoft intended for us to replace the Rich Text Box. I could use a Web Browser Control but I was not sure of the best way to add this to my form and then feed it the data that was originally bound to the text box.
Thanks for any insight you would be able to provide. Steve
Can anyone help me out here. I need to use an unbound text control to return me a value if a field is populated else "Local" if the field is not populated. Can anyone tell me if this is possible and show me the syntax if it is. Help appreciated. Andy
I am trying to write an expression that calculates a new field in a query. The new field is simply recoding values from another field (survey question responses) using IIf statements. This new field, let's call it RecodedResponse, will eventually be summed in a totals query to get the total score on that survey for each participant. Each record is a question response and there are multiple records for each participant (16 items on this particular survey). The problem is that for 2 of the questions on this 16 item survey, the responses are supposed to be eventually combined and recoded based on each other. So, my question is:
Is there a way to do a conditional expression where the result from the previous record is used? Or do I need to first do a crosstab query so that all the responses for one participant are on one line?
I am pretty sure I can do a workaround to make it work, probably doing a crosstab, then recoding in another query based on the crosstab, but it would be cleaner (I think) to do this some other way. Any ideas? Or is this too vague?
Is there a way to select a query field based on conditions? There is a table at work that I need to use for my reporting in which the field names are the months of the year. Since my report is monthly for that specific month only, I have to change my field in the query every single month. I would like to be able to put in some type of code that will automatically select the proper month field.
Is there anyway that access could create a drop down box within a drop down box? For instance, when a selection is made in a drop down box, it opens another drop down box with choices.And is it possible to link an inserted image from a form to open in MSPaint that when it opens, the image is already there?
I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.
For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.
To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.
It was simple enough to do this one column at a time...
UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"
WHERE ((([Capture history intermediate].[1971]) Is Null))
But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.
So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately?
Hi I'm a beginner using access and I was wondering if it's possible to choose a value from a combo box in a form (like a customer #) and when chosen the rest of the fields would get the corresponding values from table CUSTOMER. I have made the customer info a sub-form in my main form called ORDER your help will be greatly appreciated
I'm having an issue getting all of my conditional formatting rules to run on my forms. For some reason it is only running the rule in the top position. I'm running different versions of this rule. Changing the field or table as needed to highlight duplicates in different colors. As you can see on page two of the attached PDF the entry for 33K6-4-3176-1 should be highlighted (yellow) because it is a duplicate. It only works if I change the order of presidance of my rules but then that negates my other rule. I'm not sure what is causing the conflict here.
If the field is red then the "Auth" field is a duplicte within that form if the firld is yellow then the entry in the "Auth" field appears on both forms. Red should take precidance.
I have a drop down box on a continuous form called (cmbItem).When the users choose Option 6 (Other) and on the open event I wish to show another field txtOther which allows the user to give a description of what 'other' means but I only want to show the field for that record. (the user could potentially have more than one record as other)Using conditional formatting I have managed to change the colours of the box so that the background is the same colour as the form unless other is chosen when it reverts to white. However I can't do the same with the border as this is not one of the options provided.
I'm running Access 2003 and my question relates to delivery notes that are produced for drivers as reports. These delivery notes have a blank payment box for the driver to complete if the customer pays upon delivery.
As more and more customers are prepaying by credit cards, I'd like this payment box to be automatically filled with a "PAID" text which will let the driver know the order has already been paid for.
The delivery note is produced from an "Orders" table which has a "Paid" field whose value is automatically set to "Yes" when the order has been paid for.
I was hoping to be able to place a "PAID" label on the report which is only visible when the Paid field of the underlying query is Yes.
As things are at the moment I cannot see any way to put any conditional statement onto this Text/Label. The only options for the "Is Visible" property of the text are Yes or No.
Have a generic dashboard report with multiple charts in it. One in particular is a bar chart, but depending on what the dashboard is produced for, the bar chart may have too many bars in it to make it legible. Therefore I'd like to conditionally determine the number of bars (basically the number of records in the query) and then make the chart go from one page and continue onto another.
I'm producing PDF reports in a VBA routine in Access through the DoCmd.OutputTo acOutputReport.
The routine works great, however i have now a new requirement and i need to change the report header so to if Country X is selected in a drop-down menu, then only image X is shown on the report header; if country Y is selected then only image Y is shown on report header.
I was simply thinking to make this through the "visible" option of the image, however it doesn't work because i get error "report must be open".
I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code: SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound FROM MyTable WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
Is there a way in which i can link three drop down boxes together?
I'll create an example of what I mean...
I'll attach a Excel file which contains two identical filters, one of them where the filter is turned on.
If I select the "Serviced" filter, I am left with three options for "Make" and two options for "color". If I select the "color" as "White" I am left with just one "Make" - "MG".
I understand that some sort of "hierarchy" needs to be in place so I will Make it so that "Serviced" Must be entered first, then "Color" then "Make" so that if you select a different "Service", the "Color" and "Make" will repopulate with the appropriate values.
This is certainly possible with a REDICULOUS amount of coding that would map each value to its corresponding values however is this possible some other (easier) way?
I can create a dropdown list to filter my form (text) however im struggling to get back to showing all the data prior to my drop down selection.
ive tried refreshing and requerying with no success.
I would either like to get a drop down with the addition of "all records" in it or a refresh button.
the form that this is on is a sub form.
I could reference the drop down to a number if required.
Additionally: When I have selected my choice from the drop down box it blacks out "selects all". Is there away of making the list just select and lose focus so to speak.
After choosing a dropdown value and filing in other required fields, I click Add Record. Is there a way to retain the same value in the dropdown list assuming it will often be the SAME AS dropdown value (e.g., the same customer) of the record I just added?
