One macro causing another Macro to not finish

[Montez659]

Good morning (afternoon, evening) all!

I will try to explain this best I can, and will try to get a snapshot of what I am talking about here in a few after I figure out how to do it.

I have two forms, one a "list" and the other a details. One is essentially in datasheet view and the other individual records. There is a formula in the list so that if a user clicks on the the word "Open" it will apply a filter to the "details" form so that record will show. Clicking on "open" triggers an On Click event. Halfway through the Macro running as the code is called to open the form, the Macro halts to trigger the On Load event of the "details" form. All works well like this even though as I step through the code it seems like the rest of the "On Click" code doesn't trigger.

When it ceases to work properly is if I try to apply a filter to not show Archived records in the On Load event of the details form. If I do the simple code of ApplyFilter, the only thing that happens when the user clicks on "Open" is that it goes to record 1 instead of the correct record.

I will post the code or screenshots of the code here in a few.

 

[Montez659]

Let me give you a bit of an idea of how they function and what I am looking for.

There are two forms, one that contains a datasheet type of view and one that contains more of the details in a friendly user-interface. There are essentially two things that I am looking at doing.

The first one is that if the Details form is opened on it's own, then I would want to filter out any records marked with "archive" (there are currently about 5000 records in there and no need to page through about 4000 of them as they deal with previous years).

The second thing is to have a "link" on the datasheet view to where the ID would populate as a hyperlink, and once that is clicked then take the user to the Details view to that record.

 

[Norie]

Is it a datasheet or a form designed to look/feel like a datasheet?

I think both of the things you want to do can be done by simply specifying the where argument when using DoCmd.OpenForm.

Actually I've just reread the post and guess what - I'm confused.

How can the Details form be opened with multiple records?

I thought you were using the ID in a hyperlink to open the next form.

PS How are you using a hyperlink? Couldn't you just use a 'standard' button?

 

[Montez659]

I believe it is a form that is designed to look/feel like a datasheet. In design view, the fields are stacked, but show in columns in form view, but there are also other functions (links/buttons) included.

How can the Details form be opened with multiple records?

The Details form is bound to a table, and you only look at one record at a time, but the user is able to navigate to other records using the naviagation controls on the bottom.

I thought you were using the ID in a hyperlink to open the next form.

As far as the hyperlink, since the "List" has a look/feel of a datasheet, one of the columns contains the code

=IIf(IsNull([visitID]),"(New)","Open")

The users can click on the resulting field and an On Click triggers, which would be the pictures that I attached in an earlier response.

PS How are you using a hyperlink? Couldn't you just use a 'standard' button?

This is not of my doing, but just how it was done and although I can change the interface up, I am trying not to do that as the users have been using this interface for several years and are accustomed to it. A lot has changed over the past few weeks and may change in the future. Too much (unneccessary) change may overload and may result in rebellion!

 

[Norie]

Montez

I'm afraidk what I was thinking might change things a bit - perhaps even enough to provoke a workers revolt.

 

[Montez659]

If it is mostly changing the coding or process up a bit, then I am open to it. If not, chances are I can get it to work somehow without altering the UI up too much anyways.

 

[Norie]

I can come up with something if you post some sample data.

Nothing confidential of course, but something that is representative of the data you are working with.

 

[Montez659]

Norie, hadn't forgotten about you. Might be tomorrow before I can get back to this issue. I will get what you need.

 

[Montez659]

Here is some sample data:

assignID...assignTitle...assignDate...estDueDate...dateCompl...assignStatus...assignType...assignDesc...archive
auto...Task 123...4/01/2011...4/15/2011...4/11/2011...Complete...Short Term...description...False(No)
auto...Task 456...4/03/2011...4/05/2011...4/06/2011...Complete...Short Term...description...True(Yes)
auto...Task 789...3/5/2011...7/05/2011...Null...InProgress...Long Term...description...False(No)

I hope this is what you meant by sample data! Let me know if you need more or something different.

 

[Norie]

You should be able to copy records straight from Access to a post here.

Either select the table in the Navigation pane or open it and select the records you want to do.

Whatever, right click, select copy and then paste here.

Something like this.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>ATable</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>assignID</TH><TH bgColor=#c0c0c0 borderColor=#000000>assignTitle</TH><TH bgColor=#c0c0c0 borderColor=#000000>assignDate</TH><TH bgColor=#c0c0c0 borderColor=#000000>estDueDate</TH><TH bgColor=#c0c0c0 borderColor=#000000>dateCompl</TH><TH bgColor=#c0c0c0 borderColor=#000000>assignStatus</TH><TH bgColor=#c0c0c0 borderColor=#000000>assignType</TH><TH bgColor=#c0c0c0 borderColor=#000000>assignDesc</TH><TH bgColor=#c0c0c0 borderColor=#000000>archive</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>auto</TD><TD borderColor=#d0d7e5>Task 123</TD><TD borderColor=#d0d7e5 align=right>01/04/2011</TD><TD borderColor=#d0d7e5 align=right>15/04/2011</TD><TD borderColor=#d0d7e5>11/04/2011</TD><TD borderColor=#d0d7e5>Complete</TD><TD borderColor=#d0d7e5>Short Term</TD><TD borderColor=#d0d7e5>description</TD><TD borderColor=#d0d7e5>False(No)</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>auto</TD><TD borderColor=#d0d7e5>Task 456</TD><TD borderColor=#d0d7e5 align=right>03/04/2011</TD><TD borderColor=#d0d7e5 align=right>05/04/2011</TD><TD borderColor=#d0d7e5>06/04/11</TD><TD borderColor=#d0d7e5>Complete</TD><TD borderColor=#d0d7e5>Short Term</TD><TD borderColor=#d0d7e5>description</TD><TD borderColor=#d0d7e5>True(Yes)</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>auto</TD><TD borderColor=#d0d7e5>Task 789</TD><TD borderColor=#d0d7e5 align=right>05/03/2011</TD><TD borderColor=#d0d7e5 align=right>05/07/2011</TD><TD borderColor=#d0d7e5>Null</TD><TD borderColor=#d0d7e5>InProgress</TD><TD borderColor=#d0d7e5>Long Term</TD><TD borderColor=#d0d7e5>description</TD><TD borderColor=#d0d7e5>False(No)</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

 

[Montez659]

I've never been able to get it to do that, and I am not sure that I would be able to d/l any add ins to help - at least at work. At home, that is a different story. I just never have gotten any of the tools!