Solved Update a sub form on load of main form

[murray83]

this is what i have so far and its not playing nice, a little help please if you dont all mind

Me.AskAudience_SubForm.Form.Filter = Text6 = Me.QuestionNoLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True

but all i get is bunch of blank sub form staring at me, mocking me im not 100% sure but i don't think i need so many = signs

the code is in the form load part

 

[theDBguy]

Hi. The Filter property takes a String value.

 

[murray83]

its a number and this is the whole on load code

Private Sub Form_Load()

Me.QuestionNoLookup.Requery 'Gets the question number
Me.txt_Password.Requery            'Gets the Set of Questions for the quiz called password as a carry over from older form hehe

'subform filtering based on previous text box value
Me.AskAudience_SubForm.Form.Filter = Text6 = Me.QuestionNoLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True

Me.Refresh

End Sub

 

[ontopofmalvern]

Try this

Me.AskAudience_SubForm.Form.Filter = " Text6 = " & Me.QuestionNoLookup.Value

the filter need to be a string so you need to concat the bit you know the exact text for ("Text6 =") with the variable which looks like it come from control QuestionNoLookup

Hope that helps

 

[murray83]

ontopofmalvern, many many thanks

works a charm

 

[theDBguy]

ontopofmalvern, many many thanks

works a charm

Hi. Glad to hear you got it sorted out. I see you may have misunderstood what I said earlier. Cheers!

 

[ontopofmalvern]

Glad, to help, I use this forum a lot but mostly using other peoples help rarely able to give it.

 

[murray83]

ontopofmalvern help

i have an issue which i thought we ( i mean you ) had helped me fix, short of it my quiz now has 2 sets of questions but the ask audience button pulls every question even though it should be filtered please see attached and any help much appreciated, cheers

 

[ontopofmalvern]

That's a very broad question, you need to home in on the piece of code that you are having difficulty with. If you are not already familar with the debugging environment then do a google search on VBA debugging, this link is an okay start but a bit basic

MS Access 2010: VBA Debugging Introduction

This MSAccess tutorial explains the VBA debugging environment in Access 2010 (with screenshots and step-by-step instructions).

www.techonthenet.com

I have found it essential when in difficultly to set break points and run code one line at a time and watching how variables change. Variables can be watched in immediate window or by just simply hoovering over a variable name in any part of running code. Alot of my code has SQL queries created by Concat-ing strings, I have found using 'debug.print' very useful, I can copy and paste my SQL into access query editor to debug SQL.

 

[murray83]

this is the code which dosent seem to be working as should

Private Sub Form_Load()

Me.QuestionNoLookup.Requery
Me.QuizNumberLookup.Requery
Me.AskAudience_SubForm.Form.Filter = " QuestionNo = " & Me.QuestionNoLookup.Value
Me.AskAudience_SubForm.Form.Filter = " QRoundNo = " & Me.QuizNumberLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True
Me.Requery

End Sub

which is found on the form F_AskAudience

 

[Deleted member 147267]

Not sure what doesn't seem to be working means, but try rem'ing out the requery. When you apply the filters the form(s) should display the filtered data, but by requerying you are reloading that form and the current record becomes the first in its recordset. If you have linked child and master fields for this, you're likely causing a situation where the current record and the filtered fields no longer match. If the requery seems needed after you've commented it out, try Refresh instead.

 

[murray83]

ok ill try refresh

and sorry for not being so clear as poss, what i mean by not working, it should just show the AskAudeince data as filtered by the quizNo either 1 or 2 and the question number which can be from 1 to 20 as each quiz has 20 questions

but it shows everything

 

[murray83]

tried refresh and no help same result then i thought prehaps it needs an AND as its 2 filters

so tried this

Private Sub Form_Load()

Me.QuestionNoLookup.Requery
Me.QuizNumberLookup.Requery
Me.AskAudience_SubForm.Form.Filter = " QuestionNo = " & Me.QuestionNoLookup.Value And Me.AskAudience_SubForm.Form.Filter = " QRoundNo = " & Me.QuizNumberLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True
Me.Requery

End Sub

and no change again still the same

 

[Deleted member 147267]

Well, I don't see anything wrong (syntactically) with your code, although I do question why you'd need to requery controls during a form load event. That is the point at which Access is loading the form with records filtered by any data filtering properties that you've set, such as the form recordsource. It makes no sense to me to requery anything at that point.
Regardless, if you do need multiple criteria, you will have to concatenate it properly - you have left the And operator outside of quotes. Maybe
"QuestionNo = " & Me.QuestionNoLookup & " And QRoundNo = " & Me.QuizNumberLookup

Is the attachment in post 8 your db and is it the latest version?

 

[murray83]

yes it is but wont have the AND in as just thought of that this afternoon

 

[murray83]

Micron this bit of code

Regardless, if you do need multiple criteria, you will have to concatenate it properly - you have left the And operator outside of quotes. Maybe
"QuestionNo = " & Me.QuestionNoLookup & " And QRoundNo = " & Me.QuizNumberLookup

i could kiss you, well with on going world wide pandemic prehaps a virtual high five

it only flipping works i think i was being to complicated as this was my code

Original

Private Sub Form_Load()
Me.QuestionNoLookup.Requery
Me.QuizNumberLookup.Requery
Me.AskAudience_SubForm.Form.Filter = " QuestionNo = " & Me.QuestionNoLookup.Value
Me.AskAudience_SubForm.Form.Filter = " QRoundNo = " & Me.QuizNumberLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True
Me.Requery
End Sub

and then you came along like a shinning access vba knight and this is now my code and works a charm

new code

Private Sub Form_Load()
Me.QuestionNoLookup.Requery
Me.QuizNumberLookup.Requery
Me.AskAudience_SubForm.Form.Filter = " QuestionNo = " & Me.QuestionNoLookup.Value & " And QRoundNo = " & Me.QuizNumberLookup.Value
Me.AskAudience_SubForm.Form.FilterOn = True
Me.Requery
End Sub

Yahhhhhh solved again and newer version uploaded

 

[Deleted member 147267]

You don't need .Value as it's the default property of such a control. That's why I left it out. Still not understanding the need for requerying controls on a loading form. It does not function properly if you do not?

Anyway, so glad I could help. Give the kiss to your significant other or mother as the case may be but maybe wait until this all blows over.
A Like or 2 will be fine. 6 more and I get paid 2x as much as I do now.