It's Friday and I should know this! 1

[btj]

I am trying to create a GoTo record search for a form I have. This form has thousands of records so to find one or a group of records (sorted by a Category field) is a pain.

My solution was to create a pop-up form that prompts the user to select a Category from a combo box - at this point, the user can go to the first record with selected Category or they can select a specific record associated with said cateogry in a Simple listbox.

My issue is that since this is a pop-up form, I cannot get the underlying form (that holds the records) to go to the selection made in the Search form. I have unsuccessfully tried GoTo Record and Recordset.FindFirst.

I think that I need to use both these methods to get what I want, but my brain is just not on today. Hopefully, someone there can help.

Thank you, in advance.

 

[WildHare]

B, I don't know if this will handle your problem or not, but I have a simple form demonstratration that allows a user to sort and filter data on a continuous form by clicking either the column heading (to sort) or a value (to filter). It uses two short functions and can be used in any database and any type of form, not just continuous.

Get it here:

http://www.jmhare.com/wildhare2.htm

It's the third sample - filtersort.mdb

It may help..if not, let us know.


Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[NRK]

Jim - that is a great idea. Unfortunately, it does not help me with this particular issue. I have over 1000 records that are associated with nearly 30 categories. It takes the user too long to find one of the last records or one of the last categories, which is why I thought of some type of GoTo command. It seems like your form works extremely well for smaller sized forms but my form has so much information that your form would not completely solve the issue.

I really appreciate your feedback and, as I mentioned, believe that this form is incredible (I will probably try to use it in another capacity on this database). If you have any other ideas, please let me know.

Thank you,
Ben

 

[WildHare]

Ben, do you forsee a P/U with a combo box of categories, and then having the user pick one, and use your "go to" to go to the first record that fits that Category?

That's very easy - just use the Filter method and the FilterOn property - it will automatically filter the underlying recordset.

Sub MyPopUpCombo_After_Update()

forms!MyOtherForm.Filter = "Category = '" & Me!myPopupCombo & "'"
forms!MyOTherForm.FilterON = TRUE
DoCmd.Close acForm, "MyPopUpForm"

end Sub

Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[btj]

Jim - Again, thanks for the feedback. That is closer to what I want, but I would prefer to not filter the form. Rather, I would want to use "GoTo" to just go to the record (similar to Access find control).

Do you know of any way in which I could just go to a selecte record without Filtering?

- Ben

 

[Sidonie]

Ben,
I am no expert, but I have a situation with a Purchase Order database I have written, where the user is limited to using a Supplier Name from a Combo Box when raising a Purchase Order. If the supplier is not in the list, I have a macro hooked up to the "OnNotInList" event which calls up the Supplier Data Entry Form. This allows the user to add or view all other Supplier Records in the database. Both of these forms are PopUp's and it seems to be working in my situation.

Maybe you could use an "OnEvent" procedure to call up your results form and cut out the middle man?

Cheers

 

[btj]

Sidonie
I am unsure if that is what I need in this particular situation.

The form I am using for this issue is primary for Data entry. So, the user can Add a new record or Replicate an existing record. The second option is the reason that I began this Thread. If a user wants to replicate all the record information (except ID, which is generated based on a DMAX function), I want to find an easy method in which they can find the record.

So, my rationale behind jumping to a record when selected from a pop-up form is that the user may wish to use that record or realize that they want to use another. By applying a filter, I would need to program in a Remove Filter code (easy) but this would just complicate the process.

Again, I cannot thank all of you for your feedback and I apologize for not being able to clearly define my issue. I would appreciate your continued assistance and am willing to provide any information you may need.

- Ben

 

[jebry]

Hi Ben!

Try putting a text box on the original form and making it invisible. Then the PopUp form can populate that text box with the choice made by the user. Then all you need to do is open a recordset clone and find the correct record and set the forms bookmark to the recordset bookmark. Like this:

Dim rst As DAO.Recordset

DoCmd.OpenForm "YourPopUpForm", , , , , acDialog

Set rst = Me.RecordsetClone
rst.FindFirst "FormsIDField = '" & Me!YourTextBox & "'"
Me.BookMark = rst.BookMark

Set rst = Nothing

Of course the popup form will need to return the PK for the record chosen. Also the code above assumes that the PK is a text field, if it is numeric then leave out the single quotes.

P.S. Ben, I am still working on the other problem you emailed me about. I've been extremely pinched for time both at work and at home the last couple of months. I am hoping to let you look at a complete system to solve that problem.

Jeff Bridgham
bridgham@purdue.edu

 

[btj]

Jeff - I will try out your solution. Thanks for the input.

In regards to my other issue, I cannot thank you enough for your offer of assistance and your continued dedication. Thanks for the update regarding your schedule.

- Ben

 

[mrf1xa]

As another alternative how about using the first line of code to set the popup forms visible property to False, but leave it open, and only colse it after the relevant record has been found?

Nigel

 

[btj]

jebry
That was exactly what I was looking for...thank you so much.

Also, thanks to everyone else that posted suggestions.

If anyone is interested in how the final code came together, please let me know.