Query to automatically update report

[kathi2005]

I have created a parameter query that uses a combo box form. The people who will be using it when completed do not want to see the results in query form. Is there a way to have the query update a form or report without having to create and reformat each time?

[hertfordkc]

information in your post. If you care to elaborate, I'll take another look.

[kathi2005]

Not sure if I know how to explain. Our inventory can be searched by 10 separate options, the query uses 10 separate tables, of which I created the form with the combo boxes for all ten options. The "search form" works great for filter the data in the query. However, the people who will be using this are not very computer literate. I need to figure out a way where they can use them to be able to use the "search form" but for it to show the results on either a form or report that I have already formatted. There are pictures involved is why I need a form or report, the query will not show the pictures

[hertfordkc]

Not sure if I know how to explain. Our inventory can be searched by 10 separate options, the query uses 10 separate tables, of which I created the form with the combo boxes for all ten options. The "search form" works great for filter the data in the query. However, the people who will be using this are not very computer literate. I need to figure out a way where they can use them to be able to use the "search form" but for it to show the results on either a form or report that I have already formatted. There are pictures involved is why I need a form or report, the query will not show the pictures

your main form and a user may chose any one of the ten combo boxes to generate a query on a particular table. The combo boxes specify specific query parameters for the particular table being queried. (This is my reading. If it isn't correct, give me some more info)
If the form or report design is the same regardless of which combo box is used, then triggering a selection in the combo box will have to set the record source on the form/report. If there is a form/report specific to each combo box, then the combo box will only have to set the filter conditions.
ANOTHER ASSUMPTION -- You have a form with the combo boxes on them and you will open another form/report with the results from one of the boxes being changed.
So, using Visual Basic Access, put the results of the user's selection in hidden (Not visible) text boxes, and DoCmd.OpenForm formname... In the form, the OnOpen event looks at the hidden boxes and puts the needed information in the filter and properties and refreshes itself to give the desired results.

Is this close? Remember...there are probably 10 other ways of doing this sort of thing.

[kathi2005]

your first and third assumptions are correct. It is the one in the middle

I have the query, it works wonderful, I have the initial form with the combo boxes that filters the query. this two are connect

I have a form and a report both based of the query. However when I go to open the form I get the Enter Parameter Value box it does not accept data (but it does accept null values) when you finally click through 10 okay's all the inventory shows up.

I am missing a step somewhere and not sure where I am missing it.

I look forward to your response but going home for the day.

I will check back in the morning

[hertfordkc]

From your post, I'm guessing that you have 10 combo boxes on ==>>
Quote:
Originally Posted by kathi2005
Not sure if I know how to explain. Our inventory can be searched by 10 separate options, the query uses 10 separate tables, of which I created the form with the combo boxes for all ten options. The "search form" works great for filter the data in the query. However, the people who will be using this are not very computer literate. I need to figure out a way where they can use them to be able to use the "search form" but for it to show the results on either a form or report that I have already formatted. There are pictures involved is why I need a form or report, the query will not show the pictures

your main form and a user may chose any one of the ten combo boxes to generate a query on a particular table. The combo boxes specify specific query parameters for the particular table being queried. (This is my reading. If it isn't correct, give me some more info)
If the form or report design is the same regardless of which combo box is used, then triggering a selection in the combo box will have to set the record source on the form/report. If there is a form/report specific to each combo box, then the combo box will only have to set the filter conditions.
ANOTHER ASSUMPTION -- You have a form with the combo boxes on them and you will open another form/report with the results from one of the boxes being changed.
So, using Visual Basic Access, put the results of the user's selection in hidden (Not visible) text boxes, and DoCmd.OpenForm formname... In the form, the OnOpen event looks at the hidden boxes and puts the needed information in the filter and properties and refreshes itself to give the desired results.

your first and third assumptions are correct. It is the one in the middle

Please put red around or on the part that is wrong

I have the query, it works wonderful, I have the initial form with the combo boxes that filters the query. this two are connect
So the combo boxes have done their thing and you have the data displayed in a query datasheet format that you want to display in a form or report?

I have a form and a report both based of the query. Based on the query as stored in the objects or the query as altered by the combo boxes? However when I go to open the form you are manually trying to open the form/report? If so, revisit my last post. I'm suggesting that when the parameters are selected by the combo boxes, you should have VBasic open a form/report based on a generic query and then activate the appropriate filters. I get the Enter Parameter Value box it does not accept data (but it does accept null values) when you finally click through 10 okay's all the inventory shows up. If you are trying to manually open the form/report, you are losing the references to the earlier query results????

I am missing a step somewhere and not sure where I am missing it.

I look forward to your response but going home for the day.

I will check back in the morning

[kathi2005]

your first and third assumptions are correct. It is the one in the middle

Please put red around or on the part that is wrong

I have the query, it works wonderful, I have the initial form with the combo boxes that filters the query. this two are connect
So the combo boxes have done their thing and you have the data displayed in a query datasheet format that you want to display in a form or report? This is correct

I have a form and a report both based of the query. Based on the query as stored in the objects or the query as altered by the combo boxes? Based on the query as stored in objects. I am not able to get it to display based on the altered query However when I go to open the form you are manually trying to open the form/report? Yes If so, revisit my last post. I'm suggesting that when the parameters are selected by the combo boxes, you should have VBasic open a form/report based on a generic query and then activate the appropriate filters. I get the Enter Parameter Value box it does not accept data (but it does accept null values) when you finally click through 10 okay's all the inventory shows up. If you are trying to manually open the form/report, you are losing the references to the earlier query results????
Here is how I have used the VBA code from the search form to the query. Which does work.
What am I missing
Private Sub cmdCancel_Click()
DoCmd.Close acForm, "HDSearch"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenQuery "HDComplete", acViewNormal, acEdit
DoCmd.Close acForm, "HDSearch"
End Sub

So your are saying I need to add a line
DoCmd.OpenForm “HDStock”
If I remove the open query command it no longer filters.

[hertfordkc]

What am I missing
Private Sub cmdCancel_Click()
DoCmd.Close acForm, "HDSearch"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenQuery "HDComplete", acViewNormal, acEdit
DoCmd.Close acForm, "HDSearch"
End Sub

So your are saying I need to add a line
DoCmd.OpenForm “HDStock”
If I remove the open query command it no longer filters.[/QUOTE]

If HDStock is the Form that you have been manually opening, yes, you need to add line or two, probably to cmdOK_Click.

Private Sub cmdOK_Click()
DoCMD.OpenForm "HDStock" ' and other parameters as needed
' Form HDStock has a basic, unfiltered, query on the underlying table
' It should open with unfiltered values if you do nothing else
' Do not close HDSearch
End Sub

Then, in form HDStock, in the Onopen event,
Me.filter = "Field1 = & Forms![HDSearch]![filterfield1] & " AND Field2 =" & Forms![HDSearch]![filterfield2]
This should insert the appropriate filter into HDStock based on the info from HDSearch.
You may have to put a me.requery in the onopen or later event to actually get the filter activated.
Reply with the code portion if it doesn't get the job done for you.

[hertfordkc]

After looking at this example, you might realize there are several simplifications that may be possible. For example, in the simplest case, you wouldn't need any VBA in the second form unless you decided to use the opening or close or some other event in the second form to close the first.
I hope that you can find something useful here. Just click on Frm1 to open the example, try it out, and then go look at the VBA and the rest of the structure. The text box, Text8, was left visible, but when you give it to the users, it would be changed to "Visible=No". Perhaps most importantly, if you are working with a simple case, which this example is, you could put the filter in the "where" field of the Docmd.open form and eliminate some of the VBA.
Good luck.

[kathi2005]

Sorry for the late reply. Obviously building the database is not my only job duty.

I am about to give your suggestion a try. This is the next to last step I need to complete this database to where my co-workers can use it. I hope this works

[kathi2005]

Okay adding the
DoCMD.openForm "HDStock" to the okay button on the HDSearch form
opens the form
But it is not filtered
I have added :
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "Field1 = & Froms![HDSearch]![cboSize]&" And Field2 = "&"
Me.Filter = "Field2 = & Froms![HDSearch]![cboPCD]&" And Field3 = "&"
Me.Filter = "Field3 = & Froms![HDSearch]![cboOffset]&" And Field4 = "&"
Me.Filter = "Field4 = & Froms![HDSearch]![cboColor]&"
Forms![HDSearch]


End Sub
to the on open event to the HDStock form, but I get a syntax error and it returns me to the form. I haev tried several modification but cannot get it to work

[hertfordkc]

Okay adding the
DoCMD.openForm "HDStock" to the okay button on the HDSearch form
opens the form
But it is not filtered
I have added :
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "Field1 =" & Forms![HDSearch]![cboSize] & " And Field2 = " & '"
Me.Filter = "Field2 =" & Forms![HDSearch]![cboPCD] & " And Field3 = " & '"
Me.Filter = "Field3 =" & Forms![HDSearch]![cboOffset]&" And Field4 = " & '"
Me.Filter = "Fi]eld4 =" & Forms![HDSearch]![cboColor] &"Not sure whats going on here
?? Forms![HDSearch] ??
Does the filter end at Forms![HDSearch]![cboColor] ?? Note that I don't think the quote at the end of each line is needed, so I inserted an ' to comment it out..
Finally, I'm not on my Access computer, but Me.Filter might need to be Me!Filter. As long as I've been using Access, I'm still not sure of when .![] are essential.

End Sub
to the on open event to the HDStock form, but I get a syntax error and it returns me to the form. I haev tried several modification but cannot get it to work

I'll be around the rest of the evening.

[kathi2005]

Okay I have made those corrections now I get
Run-Time '2465':
HDSearch can't find the field 'Filter' referred to in your expression

I am still working on it as well

[hertfordkc]

Okay I have made those corrections now I get
Run-Time '2465':
HDSearch can't find the field 'Filter' referred to in your expression

I am still working on it as well

before
me!filter = FilterString

I'm not sure whether the reference should be me.filter or me!filter. However, the error message suggests that you should have used the other reference.