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,



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

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

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,



Let us know how it goes...