We help IT Professionals succeed at work.
Get Started

Access form events not quite right.

Last Modified: 2016-02-11
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
Watch Question
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE