troubleshooting Question

how to update main form in access 2010

Avatar of PeterBaileyUk
PeterBaileyUk asked on
Microsoft AccessSQL
6 Comments1 Solution299 ViewsLast Modified:
I have an option choice on the main form when this is updated some code triggers that changes the query which is the recordsource of the main form.

the code I have written functions perfectly however how do i get the main form to requery? I have checked the query after the update and it shows the correct records but the form wont.
Private Sub FrameCategory_AfterUpdate()

Dim db As DAO.Database
Dim CurrentQueryStr As String
Dim qd As DAO.QueryDef
Dim CurrentCategory As String
Dim OptionChoiceVal As Integer
Dim carStr As String
Dim CurrentCar As Boolean
Dim CurrentMC As Boolean
Dim SearchStr As String
Dim CatToQuery As String
Set db = CurrentDb

carStr = "<'c'"
bikestr = ">'t'"
lcvstr = ">'c'"

'grab the current sql from the query object
Set qd = db.QueryDefs("QryCWForm")
CurrentQueryStr = qd.SQL

'get user choice from frame
OptionChoiceVal = Me.FrameCategory.Value

If InStr(CurrentQueryStr, carStr) <> 0 Then
    CurrentCar = True
ElseIf InStr(CurrentQueryStr, bikestr) <> 0 Then
    CurrentMC = True
    currentlcv = True
End If

'get search and replace values
If CurrentCar = True Then SearchStr = carStr
If CurrentMC = True Then SearchStr = bikestr
If currentlcv = True Then SearchStr = lcvstr

If OptionChoiceVal = 1 Then CatToQuery = carStr
If OptionChoiceVal = 2 Then CatToQuery = lcvstr
If OptionChoiceVal = 3 Then CatToQuery = bikestr

qd.SQL = Replace(CurrentQueryStr, SearchStr, CatToQuery, 1, 1, vbTextCompare)
'Debug.Print qd.SQL


End Sub
Engineering Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros