2 Forms Questions

[wjoc1]

Hi,

I have a couple of questions that I hope someone might be able to answer because frankly I'm lost!

Q1. I have a continuous form which can list a couple of hundred records, obviously each line represents an individual line/record from a table. Now one of the fields displayed in each record is a checkbox . On the form I have a button which allows the user to specify a group of records he would like to 'check' ( i.e. the button checks off the relevant checkboxes). When this happens I want that record/records to appear in the visible area of the screen. I want the continuous form to somehow scroll down to the first 'ticked' checkbox so that the user doesn't have to scroll down a very long list themselves. Is this possible?

Q2. Next I have a query/form question. I have a custom dialog box which a query gets it's information from. I still want the query to run if the user doesn't enter some of the information in the fields in the dialog box. For example if the user doesn't enter text in a particular field, when the OK button on the dialog box is clicked I have the following code:


code:--------------------------------------------------------------------------------Me![some_field].value = "*"'code after this opens the form and runs the query

--------------------------------------------------------------------------------


I thought the query would then us this * value as it's criteria for that field but that doesn't seem to be the case. Is there some way I can pass this type of "generic" criteria to an underlying query?


Hope this all makes sense !

Liam

 

[Jeff Bailey]

Hi

Q1. One approach would be to 'check' the relevant records and then change the recordsource of the form to be a query that has TRUE as it's criterion for that field. That way you would not see any records that are unchecked. You'd maybe want a way to restore the original recordsource.

Another possible approach would be to sort on the 'checked' field. This ought to place all the checked records at the top (True evaluates to -1, False to 0)

Q2. I usually use this (as the criterion in a query) ...

Like IIF(IsNull(Forms!MyForm!MyControl), "*", Forms!MyForm!MyControl)

which returns all records if the control is blank or only matching records if it isn't.

 

[wjoc1]

Thanks for the reply! however I have one more query and it's regarding the criteria for queries question.

I tried to use this for a date field using Between...And. Unfortunately I couldn't get it to work. However I think Access did recognise what I was trying to do because after I saved the changes to the query Access added extra "stuff".

My criteria was as follows

Between(Like IIF(IsNull(Forms!MyForm!FirstDate), "*", Forms!MyForm!FirstDate))And(Like IIF(IsNull(Forms!MyForm!SecondDate), "*", Forms!MyForm!SecondDate))

But Access automatically changed this to:

Between([TABLE_NAME].[FIELD_NAME] Like IIF(IsNull(Forms!MyForm!FirstDate), "*", Forms!MyForm!FirstDate))And([TABLE_NAME].[FIELD_NAME] Like IIF(IsNull(Forms!MyForm!SecondDate), "*", Forms!MyForm!SecondDate))

By the by, FIELD_NAME is simply the filed I am specifying the criteria for and TABLE_NAME is the table the field belongs to. So it seems to recognise what I'm doing but I get no records back. Similarly, now, even when I specify two dates I still get no records back.

I'm so close but what's the problem. Thanks again for the help.

Liam

 

[Jeff Bailey]

This worked for me in testing ...

Between IIf(IsNull([forms]![frmTest]![xDate1]),#01/01/1950#,[forms]![frmTest]![xDate1]) And IIf(IsNull([forms]![frmTest]![xDate2]),#31/12/2010#,[forms]![frmTest]![xDate2])

What I have done is chosen 2 arbitrary dates as my start and end dates which I am certain will encompass all the dates in my table (i.e. making it equivalent to *.*)

HTH

Jeff

ps UK date format (dd/mm/yyyy)!