I used an option group to classify a record in my database. It works great for this purpose, but I am at the point now of creating a query, and I would like to query based on these classifications (or options). Is this possible to do?
Also, for the option buttons, the value is numeric. When I create the Query (or a report off of that query) I would like the actual text values to display, not the data value Access is using.
Hi
I've got an Option Group with 3 options; Yes, No and Future.
I also have a range of Check Boxes; 1 for every day of the week.
When one of the check boxes is selected I want the Option Group to change to Yes.
Currently, the Option Group defaults to Future and No will only be available by manually selecting it. This doesn't need to be changed.
Any help will be greatly appreciated!
Cheers
I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.I need to count each of these entries for a report which will run them into another formula.I am trying to use the below - obv I'm doing something wrong.
Code:
Police: Sum(IIf([OType],1,0))
And this is the original SQL for my queries if needed:
qry_BLT_RCCounts1
Code:
SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
[code]....
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies View RelatedHi all,
My problem is as follows:
I am trying to generate a report, which is based on a query, which references a form that has an option group containing four options.
I want to sort the data according which option group radio button has been selected (they the values of 1,2,3,4).
Originally there were only 2 options and the query said (in SQL view):
ORDER BY IIf(Forms!frm_08_reportsort!opt_sort_by=1,TBL_Acco untManager.Name,TBL_SiteContactDetails.SchoolName) ,
This works fine for 2 options, but I can't figure out what command i should use for more than 2 options :confused:
Any help would be much appreciated :)
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
View 6 Replies View RelatedIs there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form
Field: Option group
Value = 1
Value = 2
Value = 3
Field 2:
Field 3:
Field 4:
and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
I’m really stuck on how to create: Check Boxes/Option button/Option Group in VBA.
Could someone help on either all of them or some of them please?
Thanks
Richard
I am using an Access database for a foreign language dictionary project.
One of the forms I use is populated by a query (qryLemmaTable) to retrieve information from tblLEMMA.
This form works precisely as I intend it to work, but there is a glitch on it that is driving me mad.
The form in question has an option group with 11 radio buttons that correspond to the parts of speech associated with each word in tblLEMMA.
When I click on the button optAdjective I apply a filter and the only records that are displayed are adjectives. (The same applies for Nouns, Verbs, Prepositions etc.)
Here is the glitch: even though the radio buttons apply the filter correctly, no “black spot” appears in the button. Other signals on the screen let me know what word type is selected, so I can accomplish what I want, but I want the “black spots” too!
Perhaps this will be a clue: For each radio button I use the following code in the GotFocus event.
Me.FilterOn = False
Me.Filter = "wordtype = 'A'" ‘(or N, etc)
Me.FilterOn = True
And this for the Lost Focus event
Me.FilterOn = False
Thanks for the help.
I have several comboboxes on forms that are based on a query which checks the selection of an option group from other form (all are open in same tab container). However, the query stills prompt me for parameter, which tells me it's not pulling the value though there is a selection already made in the option group, and I'm sure the path is correct.
Does anyone know how to make sure that the query "remembers" the value? Would it be better done using a public variable? (I don't know if SQL is capable of accessing a public variable, though)
SQL:
SELECT tblCounty.CountyID, tblCounty.County
FROM tblCounty
WHERE (((IIf([Forms]![frmMainForm].[Form]![frmOption]![optOptions]=4,[tblCounty].[OfficeID]>0,[tblCounty].[OfficeID]=[Forms]![frmMainForm].[Form]![frmOption]![optOptions] Or [tblCounty].[OfficeID]=4))=True))
ORDER BY tblCounty.County;
(The logic is that if you select a office location, the combobox will then filter to only counties that office covers plus two generic placeholders in "4", or allow you to select all available counties if you choose so).
I have a main form named as FOTOFILM and a child form named as FOTOFILM_Subform ( Datasheet view, and datasource is a query builted on FotoFilm_table). they don't have relationship but the datasource for both is the same (FotoFilm_table).On the main form FOTOFILM, I have an option group named optChoose and 5 options within. Options are;
The last week,
The last month
The last 3 months
The last 6 months
the last 12 months
I would like to query / filter FOTOFILM_Subform by clicking any option.The field name ( to be filtered) IstekTrh (Date/Time-ShortDate)
I have a query that i need to run from a form. From form I need to pass criteria
1 for canada
2 for us
1 and 2 for us and Canada
The criteria needs to go into field Pricing Type. If user selects 1 then only Pricing Type 1 will display. If user selects 2 than only Pricing Type 2 should display. I user selects both , Pricing Type 1 and 2 should be listed.
Can this be achieved with option group?
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.
I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.
I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.
Basically, what this does is show me the average profitablity of the top 10% in each grouping.
WaterSourceType | Crop | ProfitPerBushel
Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel
At present I have a form which is used to select a date range then using one of three buttons I can select one of three reports. I want to use an Option Group with three options to replace the three buttons. I have fiddled around using three separate Option Buttons and they work ok but in my opinion they are not the way to go. I need some assistance with the code as the Option Group is slightly different to the Option Buttons.
View 5 Replies View RelatedI have a very simple option group, status 1, 2 , 3 or 4. When the status of the group is changed i have the following code used to label txtStatusChangeAudit which lables who changed it based on the log in name. I have this code for each of the four options. The code works great but when i leave the form and come back to it the txtStatusChangeAudit that holds the code is now blank. When i click an option it again shows the information i want.
How do i get the form to save this information so when i leave the form and come back to it the last person to change the status will still be present?
Private Sub OpenOptionButton_GotFocus()
Me.StatusChangeAudit = "Status Open By; " & User.UserName & Space(1) & (Now)
End Sub
Thanks a million..
Fen
This should be an easy one.... I had an existing form. Needed a second form. But the two were so close I just added and option group to make certian fields visible when that option was picked. Now.... The problem is sorting or doing a query by the value of the option group. What I am thinking is I need to store the value of the option to a field. ie My option group is named "PickWO" the value is "1" or "2", so I added a field to my WO table... so how do I store the value of "1" or "2" to the new field? Hope this makes sense!
View 9 Replies View RelatedI have created an option group. There is a question and the response is yes, no or N/A. If the answer is no or N/A then I want to skip to the next question. If the answer is yes then I want to ask a follow up question. How would I make it so that when the answer is no or na and the user hits tab it would skip right over the follow up portion and move on to the next question?
View 1 Replies View RelatedHi,
I have a option group with several buttons. Is it possible to apply a msgbox to one of the buttons where it will ask the user "Are you sure you want to generate report to excel". I would like the user click ok to continue or cancel. I am not sure how to get this message box prior to the code running when the option button is pressed. Thanks you for your time.
Will someone look at my option group in my form (frmcustomer) and help me figure out why the option group selections for existing records nulls out (visually you cannot see which button had been previously selected).
The option group is working fine in that it is adding the appropriate selection to the record, but when you open the form up in edit mode the selection has been grayed out.
I have an Option Group with 3 choices...1,2 and 3. The default value is 2 as setup in the table default value setting.
I have a subform that has a check box and when checked I want the Option Group value to be "1" otherwise I want the default value of "2" or "3" to remain in the table. I can get all this to work through a Afterupdate code in the subform check box except the part where I want the value left at what
ever was in the option group part. Here is my code...
Private Sub Paperwork_Returned_AfterUpdate()
If Me.Paperwork_Returned = "yes" Then
Me.Parent.paperworkstatus = "1"
Else
Me.Parent.paperworkstatus = False
End If
End Subvalue
I think the problem is in the "Else" part. The if trus seems to work but if false it will not leave the previouse value in the field.
This last little thing is the last thing I need to finish up. This board has taught me so much...most of all it has taught me that when somebody ask me if I am an advanced access user, I will have to say is anyone? This program is so powerful and I can take a lifetime to master, or so it seems.
Thank you!
I have an option group set up on my form that represents four different sheets. I want to be able to select one and have a certin function occur (docmd.openquery) according to which button is selected. Any ideas? :confused:
View 1 Replies View RelatedI have an option group, with two options buttons. I need to to be a required field. (I cant) make this required at the table level, as there are already 56K records).
I've tried on the before update on the form, making the frame tag = required. For some reason this is not working. Any idea's
Dim blnContinue As Boolean
Dim ctl As Control
blnContinue = True
For Each ctl In Me.Controls
If ctl.Tag = "Required" Then
If IsNull(ctl) Then
MsgBox "Required Information Missing", vbCritical + vbOKOnly + vbDefaultButton1, "You must fill in the field indicated with red"
Cancel = True
ctl.SetFocus
Exit For
End If
End If
Next ctl
Set ctl = Nothing
End Sub
Hi, all--
Perhaps a silly question:
I want to use an option group with a seperate check box for yes and no for a series of yes/no fields. I find that when I do this, once a record is started all have these items have "no" checked. I tried defaulting the value to null and other such things, but to no avail.
Any ideas (while retaining the concept)?
Thanks.
Paul
Hello Everyone,
I'm not sure if this is possible. I have a form (link to a table) with option boxes that when I click on one of the option boxes sets a value to a field in the table. The problem is that when I check a different option box I have to manually uncheck the other option box that was previously checked. Is there a way to have this done automatically? Meaning that if I check box 1 and then box 2, when I check box 2, then box 1 has to get Unchecked.
I also tried using a group option with different option boxes and that seems to work fine as far keeping only one option box checked, but don't know how to make the checked box to store its value to a table. Please help!
:confused:
Thanks!