Currently I have a written some code that opens a form with record info based on another form that is open in the back ground. However, it get the record information to pull up in the new form I had to run the "Find/Find and Replace" command.
Code:
If State = "AZ" Then
DoCmd.OpenForm "AZ Fees"
DoCmd.RunMacro "Find_on_click_TS2" *This opens and runs a find command

My question is: How do I get the Find box to close without closing the Form. Right now I can close the Find box by hitting the escape key, but I'd like to automate it so it closes on itself.
I've seen recommendation of "SendKeys "{ESC}"", but also read that it this is not best practices.

Any help on this matter would be greatly greatly appreciated.

Thank you,

David92595

Hi David92595,

(IMG:style_emoticons/default/welcome2UA.gif)

Are you using Access? If so, what version? Is your code in VB or VBA? What exactly are you trying to accomplish? Perhaps there's no need to use the Find/Replace dialog box.

Just my 2 cents...

I am using Windows 7 with Access 2007, and writing in VBA

The code is supposed to to bring up and compare a TS# (Primary key) in form based on the same TS# from a different form. Each Form uses a different table.
Part of the code is supposed to compare the two TS#'s (primary keys) and if a primary key does not exist in the second Form/Table it opens a msgbox asking if you would like to create a new record for the TS# (Primary key).

The code:
DoCmd.OpenForm "AZ Fees" 'opens the form
DoCmd.RunMacro "Find_on_click_TS2" 'runs a macro to search for a TS# (Primary Key).

My problem is that when I run the macro to bring up the TS# I'm left with the Find box (Ctrl +F) open on the screen.

What I would like is to find a way to close this box without having to physically click the exit button.

Hope this helps!

Hi,


What I am trying to understand is what you are exactly trying to accomplish with your form because I suspect that we can do it without having to run the macro to open the Find (Ctrl+F) dialog box.

If so, then you won't have the problem of trying to close it afterwards.

Just my 2 cents...

Here is the macro: Find_on_click_TS2

Action Arguments
RunCommand Find
GoTOControl TS #
FindRecord =[Forms]![Main Details]![TS]
OnError Fail,

If you can find a better way to pull the TS # (Primary Key) and related Info from "[Forms]![Main Details]![TS]", You'd be my hero

Hi,

Unfortunately, you still haven't told me exactly what you are trying to accomplish so that I could formulate a possible alternate approach for you.

Are you trying to open the form to a specific TS#? Or, what?

My apologizes, yes I am trying to open the form to a specific TS#, and if that TS # does not exist then I need a message box to pop up (which I have already written).

Please let me know if you need any other information that I have left out. I work for a law firm so giving out to much info can cost me my job...but if you have questions I will be more then happy to answer them to the best of my abilities

Thanks again,

David92595

This post has been edited by David92595: Jul 15 2011, 05:23 PM

Hi,


Okay, thanks for the clarification. If I was going to do something like this, I might try the following approach:

If DCount("*", "TableName", "TS='" & Forms![Main Details].TS & "'") > 0 Then
DoCmd.OpenForm "AZ Fees", , , "TS='" & Forms![Main Details].TS & "'"
Else
MsgBox "Sorry, that TS does not exists."
End If

Note: The above assumes that TS is a text field.

Hope that helps...

Thanks I'll give this a try.

And Yes, the TS Field is a Text field

One question as you seam to be so responsive/awesome. I've never used or heard of the "Dcount" Command. What is it? what are the positives and negitives of using this command??? (If you know of a good website that will explain it, that would be great). If not, no worries, I know you MVP's don't really like to explain things we can look up. I was more interested in a site you recommend for a good explanation...Thanks again

This post has been edited by David92595: Jul 15 2011, 06:21 PM

DCount() is a built-in function in Access, one of the domain aggregate functions. you should be able to find it in Access Help - unless the topic has been removed from A2007 Help.

hth
tina

Hi,

Tina is correct! But if you prefer a web link, here's one from MSDN:

DCount Function

Hope that helps...

Thanks!

Hi,

(IMG:style_emoticons/default/yw.gif)

Let us know how it goes...