open up second window on click

[Ofina]

I have an Access tool written in Access 97 (don't ask). It basically searches our main database for sale items based on filters used by user.

For each sale item that comes up based on the filters, I have a button that pulls up all the sales history of that item. It basically runs a query that populates in a new window.

What I would like to know is this. Is it possible, if I have already hit that history button and already a new window is populated, to have a second click of the history button open up yet another window, instead of refreshing the window that was already open?

 

[sxschech]

Would the second window be relying on the same query that is used by the window that is already open?

 

[Ofina]

Yes.

 

[sxschech]

If it is using the same query, then probably can't be done as is. If this is a regular thing and you plan on a fixed number of "new" windows, then you might be able to do something with code, but would require creating temporary queries and or tables to do it.

 

[Andrzejek]

You are using the same query, but do you pass any parameters to that query? So if you move from record to another record, you do use the query, but the outcome will be different because you point to a different record.

Is that correct?

Have fun.

---- Andy

 

[Ofina]

Yes, that is correct. It passes the item's unique ID to the query and the query filters the history based on that.

 

[Andrzejek]

Well, right now - what prevents you from clicking on a button again, once you have the sales history of the first item displayed?

Can you have sales history displayed and choose another item from the *first* form?

Have fun.

---- Andy

 

[Ofina]

Yes, but it reuses the window that was already there.

 

[sxschech]

Back to my question. Will you be displaying the query only twice or will you need more than two query windows displayed. Also, you mentioned a second click, the assumption being the user clicks once and sees the first query and then clicks once a second time? Or do you mean that the user will click once and then double click the button?

 

[Ofina]

Sorry, didn't detect a question in your last post.
I think that it's likely it will be only once or twice, but I'm not the user who requested this functionality, so I'm not sure.
Twice would be better than only once though, for sure.

The user will search for an item, then click the History button to see its history. They want to compare it with another item, so they search for that other item and click on *its* History button. Thus seeing both histories in separate windows side by side...if I can get this to work that is.

 

[randy700]

Although I've never used one, this sounds like a job for a multi-select list box. Once your user has selected 1 or more items, the click of a button could produce a list in a continuous form. Just create a query that provides the desired information and use it as the record source for a form or subform.

Sorry, but I can't help you with how to get the input from the list box.

Randy

 

[sxschech]

I tested the code below based on the user clicking the same button, perhaps can be modified for your situation. Code determines if the query is already open and if so, copies it (the assumption being that criteria are pulled from the form itself, yes?) and opens up the copy so user can see both instances of the query. If the query isn't already opened, the original query is opened without making a copy.

Put this in its own module (not the form's module)

'[URL unfurl="true"]http://www.dbforums.com/microsoft-access/1118531-simple-thing-query-def-open.html[/URL]
Public Function isOpen(Name As String, _
Optional ObjectType As AcObjectType = acForm) _
As Boolean
isOpen = (SysCmd(acSysCmdGetObjectState, ObjectType, Name) <> 0)
End Function

Put something like this code in the on click event of the form button. Changine YourQuery to the actual query name.

If isOpen("YourQuery", acQuery) Then
    DoCmd.SetWarnings False
    DoCmd.CopyObject , "QryCopy", acQuery, "YourQuery"
    DoCmd.SetWarnings True
    DoCmd.OpenQuery "QryCopy"
Else
    DoCmd.OpenQuery "YourQuery"
End If


The downside of this approach is that if the user switches to design view in the original query and then back to datasheet view, it will reflect the new criteria.