Using the [Count] function and Opening a query within an input form

[sherrygroves]

Hi!!

I know this should be a very simple function but for some it is not giving me an accurate count. I have report forms I designed and want to include a count of items at the end of the report. I don't know why the [COUNT] function is not working.

The second question is I have two tables (My Notes and Reporting). They are 'linked' by state names. I have created in input form for My Notes. I have created buttons for print, find and add new records on this form. I want to include a button that will open a report date query for the current State record in My Note. In other words, if I am looking at the California state record, I want a button that will give me the report dates for California.

Thanks!


Sherry

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/wherecondition.htm

[sherrygroves]

Thanks for the reply. I can't access your site http://www.baldyweb.com/wherecondition.htm.

[June7]

Odd, the site page pops up immediately for me. Here is the content:

Often you might have a form or control that displays summary information, and you want to select one of those items and open a second form that displays the detailed information. The technique I use most often is the wherecondition argument of DoCmd.OpenForm (or DoCmd.OpenReport). It is sort of like saying "open this form but only show the record(s) that meets this criteria". Using this technique, the second form can be based on the table itself or a query that returns all records. It looks like this for a numeric value:
DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName
Where SecondFormName is the name of the form being opened, FieldName is the field in that form's recordsource the restriction is based on, and ControlName is the name of the control on the current form that contains the value to be shown on the second form. As you will find throughout VBA, text and date values are treated differently. For text:
DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'" '(note, that's a double quote, single quote and double quote at the end)
For a date value, use # instead of the single quote:
DoCmd.OpenForm "SecondFormName", , , "FieldName = #" & Me.ControlName & "#"
As noted above, this technique can also be used when opening reports.

[sherrygroves]

Hi! Okay, I can open your site today. Thanks!! I will try that. However, it is a query I want to open in this form, not another form. I have a My Notes table and created a My Notes form to input/manage the data. This form has one record per state. I have a second table (Reporting) that has report dates due for each state. So, one state may have as many as 25 (or more) reports (records). Instead of having to go to the Reporting Table, if I want just a list of report dates, I would like to create a button in the My Notes form to give me those dates. So we could be looking at as few as 1 date (record) or as many 25 dates (records). Will this still work?

By the way, I did figure out the COUNT issue!

Thanks,
Sherry

[pbaldy]

I personally never expose tables or queries to users, only forms and reports, so I'd one of them. If you want to open a query, it would probably have to use your form in the criteria.

[June7]

Why not a subform or a listbox?

[sherrygroves]

Thanks! A form would be great but two things. The first thing is I can create a button to open, print, view, etc a form, query or report. BUT how do I put in the criteria to get the My Notes state record and only that state's report dates (say I'm looking at the Texas record in the My Notes table, I want only the report dates for Texas from the Reporting Table.) And secondly, like I said a form would be great but I don't know how to create a form to get multiple records on it. I haven't had a reason to try and figure that out, but will start seeing what I can do on that end.

Again, Thanks for your input!!
Sherry

[June7]

You say 'looking at the Texas record in the My Notes table'. Are you actually looking at a form?

A form in Datasheet or Continuous view will display multiple records.

Main form in Single view bound to My Notes table (one record per state?). Then subform in Datasheet or Continuous view bound to Reporting table. Set subform container control Master/Child Links properties to the state ID. This will synchronize related records.

[sherrygroves]

Moderator,

I just read your post, a list box sounds good as well. I have lots of list boxes in my forms, but the disconnect for me in whatever I can use is getting only the report dates from the Reporting Table for the state record in the My Notes table. I am adding/modifying the data with a My Notes Form. I have the subform in the table. How would I get that in my form?

[June7]

You mean you see a subtable when looking at the My Notes table?

I describe building form/subform in previous post. What exactly is not clear?

[sherrygroves]

First THANK YOU to both of you that are replying! Some very good options/ideas!! Yes I am using a form. The My Notes and Reporting tables are related by state 1 to many. In the My Notes datasheet view I can expand the state and see the Report Dates. (That is actually what gave me the thought to put that in my form).

Okay, added a subform to my main form and am getting the results I want but I don't want it displayed until/if the user clicks/wants to see it. That's my thought behind the 'button'.

[sherrygroves]

Yes, subtable is in my form, but like I said, I don't want it visable unless/until I want to see it.

[sherrygroves]

In other words, I want to be able to 'click' on whatever my control is (button, etc) and display the subform, not have it displayed all the time. Like expanding/collapsing it in the datasheet view.

[burrina]

I am really not following this thread but maybe this will help with the last part. Of course modify to your needs.

If Me.NewRecord Then 'OnCurrentEvent
Me![Subform1].Visible = False
End If


Me![Subform1].Visible = True 'AfterUpdateEvent