3 Q:List not clickable+Uncheck-all func+para query

[moontear]

Hi folks,

problem 1:
sorry to bother you guys again with a small problem. Take a look at the attached Database. Go to Forms > mnuParticipants > Reports and try to choose a committee from the list - it's just not clickable.
I tried tweaking around with all settings but just can't figure out what I have to change so those items are clickable again.
--------------------------------------
problem 2
How can I have a function to uncheck all checkmarks (sounds good I know) in a table? Let's say the table looks like this
tblTest
Name
Address
Checkmark

and I want to clear all checkmarks with a single button click (and a alert box maybe).
----------------------------------

problem 3:
How do I open a form with a parameter query? I was using a query like this: (((tblSchools.School) Like "*" & ([Please Enter Schoolname:]) & "*")) to search for schools, which works great, but how do I put that into a Docmd.OpenForm command, so that the user gets asked that question?


Thanks in advance
Dennis

[johnhutchison]

Problem 1

Change the ALLOW EDITS property of the form to Yes.

Problem 2

Create an Update query, then have the button open the query. If you create the command button using the wizard, then one of the options in the Miscellaneous group is to open a query. Because this is an action query, opening it will run it.

To Creat the query, make a new query on tblTest, add the field chkMark, then go to the menus and under query choose Update. This will add an Update to line to the query grid. Put No on this line. See picture below.


Problem 3

The easiest way to do this is to make your paramater query the Recordsource of the form, opne the form in Design view and set the RecordSource property of the form to your query. This is no good however fif you want to sometimes open it without the parameter prompt.

The alternative way is this
Dim strDocName as string
Dim strWhere as string
strWhere = "(((tblSchools.School) Like "*" & ([Please Enter Schoolname:]) & "*")) "
strDocname ="frmMyform"
DoCmd.OpenForm strdocname, acNormal, , strWhere

Adding a Where condition into the open form command effectively adds another where condition to the Recordsource of the form.

[HansV]

Re #3:

An even better alternative is to place a combo box on the form from which the other form is opened. The user can enter or select the school name here. For example, if you use a combo box cboSchoolName:
<pre>Dim strDocName as string
Dim strWhere as string
strWhere = "tblSchools.School Like ""*" & Me.cboSchoolName & "*"""
strDocname ="frmMyform"
DoCmd.OpenForm strdocname, acNormal, , strWhere</pre>

Note the use of double double quotes within the string.

[moontear]

thanks john, problem 1+2 solved PERFECT! Thanks alot for your help.

What you suggested for problem 3 just doesn't work. I tried exactly that before and all what happens when clicking on a button is... nothing. Tried now your solution by simple copy n paste (and editing the file names of course) in a new test form - same thing, nothing happens...

[moontear]

Works and I'm gonna use that, thanks - only problem here that the user can't enter a middle part of a school, let's say from George Rathburn High School, entering rathburn in a para query would open up the right form, entering that in that combo field gives me nothing - besides that nice solution.

[HansV]

If you set the Limit to List property of the combo box to No, you can enter part of the name of a school (if Access automatically completes it, just use Delete to remove the extra bits.) I just tested it successfully in an existing database. MAke sure that the quotes are placed correctly.

[moontear]

you are right, it works, thanks alot Hans

[johnhutchison]

Sorry about my mistake before. I just pasted in the code from your query without thinking too carefully.

A couple of extra suggestions.

When you put a combo box on a form to provide criteria for a WHERE clause, you need to deal with people clicking the button without putting anything into the combo. Another possibility, particularly if you allow people to type in their own text, is that nothing matches.

Dim strDocName as string
Dim strWhere as string
Dim intCount as integer
if not isnull(me.cboSchoolName) then
strWhere = "tblSchools.School Like ""*" & Me.cboSchoolName & "*"""
strDocname ="frmMyform"
intCount = Dcount("*",'tblTest",strwhere)
if intCount >0 then
DoCmd.OpenForm strdocname, acNormal, , strWhere
else
msgbox "No schools match criteria entered'
end if
else
msgbox "Select a school or enter some text"
end if

[moontear]

thank you, I need to get more into error handling anyhow so I'll add your suggestion quick!