Access form events not quite right.

I have a form that will allow the user to set some conditions and store those conditions so that a dynamic query can be made that reflects the selections.

I have 12 fields which are represented with the left most option group, when the user clicks a fieldchoice option ie bhp etc the code generates the sql for the combo. the combo default will be all periods but the option group creates the query that tells the combo the periods of data batches that are available for that data. the sql works and the combo is populated correctly but unless i select a period after the combo is selected the option group control freezes. and I am not sure why.

I want the combo sql to update but its not obligatory to go for a batch.

here is my option group code
Private Sub FrmFieldChoice_Click()
Dim db As Database
Set db = CurrentDb
Dim RstCapFieldConditions As dao.Recordset
Dim SearchCriteria As String

Dim StrQuerySelect As String
Dim StrQueryFrom As String
Dim StrQueryGroup As String
Dim StrQueryOrder As String

Set RstCapFieldConditions = db.OpenRecordset("SELECT TblClientFields.FieldName, TblClientFields.CustomVarianceTotal, TblClientFields.Client," _
& " TblClientFields.[Field Condition], TblClientFields.[Output Field], TblClientFields.Period" _
& " FROM TblClientFields" _
& " WHERE (((TblClientFields.Client)=""Cap""));")

Select Case Me.FrmFieldChoice

Case 1
TblSelected = "BHP"

Case 2
TblSelected = "CC"

Case 3
TblSelected = "Co2"

Case 4
TblSelected = "Cylinders"

Case 5
TblSelected = "Doors"

Case 6
TblSelected = "FWdGears"

Case 7
TblSelected = "GVW"

Case 8
TblSelected = "KW"

Case 9
TblSelected = "Nom"

Case 10
TblSelected = "Seats"

Case 11
TblSelected = "Valves"

Case 12
TblSelected = "WBaseType"

End Select

StrQuerySelect = "SELECT Tbl" & TblSelected & ".ChangeYearMonth"
StrQueryFrom = " FROM Tbl" & TblSelected
StrQueryGroup = " GROUP BY Tbl" & TblSelected & ".ChangeYearMonth"
StrQueryOrder = " ORDER BY Tbl" & TblSelected & ".ChangeYearMonth DESC;"

StrQuery = StrQuerySelect & StrQueryFrom & StrQueryGroup & StrQueryOrder
'Debug.Print StrQuery
Me.CBYM.RowSource = StrQuery

End Sub

Open in new window

maybe someone could advise how I deal with this.

Ive attached the db its small but if your on a mobile you might not want to download it.

frmgate and cap edit settings is the tab to look at.
the db is storing data edit changes in the individual tables and the interface is a way to look at relevant data that has changed

I am using vba
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
from my iPad, so cannot download the file.

I'm not sure I understand the problem.  You say the "option group control freezes", what does that mean?

Your code looks sufficient to update the combo box based on the selection in the Option group, assuming that the left column of radio buttons are the only ones actually bound to the group.

If I were doing it , without knowing what the data in all of those various tables looks like, I would probably add a DISTINCT predicate in your SELECT statement, so you don't get duplicate values for the ChangeYearMonth field

StrQuerySelect = "SELECT DISTINCT Tbl" & TblSelected & ".ChangeYearMonth"

But this should perform the function of updating the combo.  So what doesn't work,  Is there something else besides updating the combo?
PeterBaileyUkAuthor Commented:
If I select cc (lets say) the combo updates for sure but if I dont actually go to the combo and decide to then click Co2 for example the option box selection is stuck.
Dale FyeOwner, Dev-Soln LLCCommented:

I don't see anything which would cause the option group to "stick"

What is the purpose of lines 13-16?

You appear to be opening a recordset, but you never use that recordset or close it for that matter?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PeterBaileyUkAuthor Commented:
I have a table called tblclientfields and that holds the various data that will be set from here in this case its taking the field name bhp from the option group for example then looks for that row in the case so it can set the right data to the right table. so depending on the option itll get to the right table tblbhp tblcc. in these tables is the differences in data between each successive month. so if bhp previously was 100 and now is 105 it records that change this interface is used to select certain conditions ie period, size of change and create a report from that.
PeterBaileyUkAuthor Commented:
this way i only need one control for all the fields
Dale FyeOwner, Dev-Soln LLCCommented:

My point was that lines 13-16 open a recordset that never gets used in this code segment.  Ist there more code in the frame_click event than what you have posted?  Do you also have code in the frames AfterUpdate event?

Or is there perhaps code in the forms time event that is causing the problem you mention?

I'll be home shortly, so I will be able to open the file you posted.
PeterBaileyUkAuthor Commented:
aha ok i get what you mean yes it was used to set values in the table previously i have just taken that set out now. the behaviour is still as described, yes if you could take a peak as the code doesnt rely on the linked tables for this particular question.
PeterBaileyUkAuthor Commented:
ive attached a new db with that rst out
I'm wondering why you created so many similar tables (1 for each category) rather than a single table that included a field to identify the category.  That way you would only have a single query rather than one for each table.

PeterBaileyUkAuthor Commented:
its for vehicle data and this way when the table gets to big to be contained in this db I can port them off somewhere. at present its only holding 2 months worth but the tables just get far too big with millions of records.
Dale FyeOwner, Dev-Soln LLCCommented:

I honestly cannot tell you what is going on.  All three of those option groups (frames) down the left side of that form are acting that way.  When you make a selection in the frame, it will not let you change that selection until you click on another radio button in another frame or in the combo box.  Then they function properly.

I created a new option group, imported all of the radio buttons from that to left frame into it, and copied your code into the After_Update event of that new frame, and when I did that, the option group and the code worked properly.

You might simply try that, but at this point, I think I would recommend creating a new database and importing everything into it, before moving on.  Check whether that makes a difference (it would if your database or form has gotten corrupted).  If not, then create new frames to replace the three you currently have.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
thank you
Dale FyeOwner, Dev-Soln LLCCommented:

Glad to help.

Did you end up replacing the option groups, or did importing into another database solve your problem?
PeterBaileyUkAuthor Commented:
I replaced the option groups and now so far its working fine..thank you once again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.