How do I prevent a Macro from running, given certain conditions? (Eg. Empty text box)

[PTRACER]

Ok, I have a database with a list of customers.

The main switchboard contains a text box, with a Search button (which is controlled by a macro).

Basically, if I want to find a customer, I put the customer's name in the Text Box, press Search (aka Run Macro) and it opens up a form giving me a list of all customers with that surname.

There's a couple of problems with the above, however and as I have little experience with Access, I am not entirely sure what to do to solve them and would be grateful for any help you can give me.

My Macro is as follows:
OpenForm - Customers <------The form that displays Customer information.
GoToControl - [ContactLastName] <------The field in the table I would like to search, based on what's written in the text box.
FindRecord - =[SurnameSearch] <-------The name of the text box the Macro refers to.

The problems I would like to solve are:

1. If the text box is empty, it opens the form anyway and displays an Action Failed error message. I would like to prevent the Macro from running if the text box is empty, and produce a custom error message (E.g. "Please enter something into the Search box.")

2. If no matching records are found, it does the same as the above, but without an error message. I would like to stop the form opening and simply display an error message saying "No matching records found."

3. For some reason, it only goes to the first matching record and doesn't filter out any records that don't match. How do I get the macro to apply a filter, based on what I've written in the text box?

I hope the above is not too complicated!

Kind Regards,
PT

 

[ted.martin]

The best way to do this is to use some VB code rather than your macro. As a start, if you select the macro (do not open it in design view) and select Tools/Macro/Convert Macro to VB, then you will have a function to start adapting. You will need to change the Switchboard command to 8 and the name of the created function.

With this code, you will be able to include an IF statement for the textbox value and also handle the "no match" option.

This isn't so much complicated as new territory for you but using code will be the only way to do what you are asking.

 

[PTRACER]

I'm afraid I don't know VB at all, so could you maybe provide some help please?

This is my code:

'------------------------------------------------------------
' Customer_Name
'
'------------------------------------------------------------
Function Customer_Name()
On Error GoTo Customer_Name_Err

    With CodeContextObject
        DoCmd.OpenForm "Customers Query", acNormal, "", "", , acNormal
        DoCmd.GoToControl "[ContactLastName]"
        DoCmd.FindRecord .SurnameSearch, acEntire, False, , False, acCurrent, True
    End With


Customer_Name_Exit:
    Exit Function

Customer_Name_Err:
    MsgBox Error$
    Resume Customer_Name_Exit

End Function

 

[Mike375]

In your macro design page click View and then Condtions and that will open a column to the left of the Action column.

In the Condition column type [Forms]![YourFormName]![YourTextBoxname] Is Null

For the Action column you have StopMacro. You might also have above the StopMacro the same condition but on the Action column you have MsgBox and at the bottom of the macro screen you type in the message.

Then convert that to code and see how it looks.

If only one form is open/active you can just put the field/textbox name in Condtions such as [YourTextBoxName] Is Null.

 

[PTRACER]

Thanks a lot! Now, this works perfectly fine, however now I would like it to filter the results, so the records are filtered based on what is written in my text box.

Here's the code:

'------------------------------------------------------------
' Customer_Name
'
'------------------------------------------------------------
Function Customer_Name()
On Error GoTo Customer_Name_Err

    With CodeContextObject
        If (Eval("[Forms]![Switchboard]![SurnameSearch] Is Not Null")) Then
            DoCmd.OpenForm "Customers Query", acNormal, "", "", , acNormal
        End If
        If (Eval("[Forms]![Switchboard]![SurnameSearch] Is Null")) Then
            MsgBox "Oops!", vbOKOnly, ""
        End If
        If (Eval("[Forms]![Switchboard]![SurnameSearch] Is Null")) Then
            Exit Function
        End If
        DoCmd.GoToControl "[ContactLastName]"
        DoCmd.FindRecord .SurnameSearch, acEntire, False, , False, acCurrent, True
    End With


Customer_Name_Exit:
    Exit Function

Customer_Name_Err:
    MsgBox Error$
    Resume Customer_Name_Exit

End Function

 

[Mike375]

DoCmd.OpenForm "YourFormName", acNormal, "", "[TextBoxName]=[Forms]![YourFormName]![FieldNameToMatchText Box Entry]", acEdit, acNormal

If you were doing it in the macro you would have OpenForm in Action line, the form name down the bottom and the Where Clause

[TextBoxName]=[Forms]![YourFormName]![FieldNameToMatchText Box Entry]

 

[PTRACER]

Unfortunately that doesn't work...

If we back track a bit, rather than using "FindRecord", can I use "ApplyFilter"?

And if so, then how? (I spent half an hour searching and experimenting with various things that I found, but wasn't successful)

 

[Mike375]

"Unfortunately that doesn't work..." Correction, does not work for what you want. If you have a matching record it will work. It is 100% with a macro and 99.99% with VBA.......Sorry Super Moderator Bob Larson but I deal in reality.

So let's establish what you want.

Find Record is great when you are using a list that is an index. In other words, the person is there or the car is there or the whatever is there. You click on the LastName and it finds the record in the form that has the same ID etc. The form needs to be open.

OpenForm with Where Clause will turn up a form ready for a new record IF no record matches the Where Clause.

SOooooo....are you looking for an existing record that matches the criteria of your textbox entry.??? If so, then the entry in the textbox needs to be right.

Note: Whether VBA or macro best to have a saverecord first.

If you are looking for an existing record then a Combo box is real easy. Personally, I prefer a tabular form. But either way FindRecord will work because in either case your list is based on the records.

If you are entering a name, a car model etc in your textbox....then if no matching record FindRecord dies and OpenForm is ready for a new entry.

As to applying a filter I like the parameter query....But it can't find a record that is not there. Set the recordsource for the parameter query and the form will be blank if nothing matches your entry in the textbox

For starters make a Combo based on the records in question. If that does not find the record....then we need to get Bob Larson or others in.


Mike

 

[PTRACER]

I suppose what I want to have is this:

I load the database and the Switchboard appears. The Switchboard contains 2 text boxes and a button. The first text box is the "Search for Customer by Surname" box. The second text box is the "Search for Customer by Postal/Zip Code" box.

Say I want to search for the details of a customer named Smith. I type Smith in the "Search for Customer by Surname" box, press the "Search" button and it opens a new form containing the first instance of a customer with the surname "Smith".

A filter has filtered out any customers from the form who aren't called Smith, so when I click "Next Record", it takes me to the next customer in the table with the surname Smith.

If I have 50 customers and only one is called Smith, then I would be presented with one record. (It'd say "Record 1 of 1 (Filtered)" at the bottom of the screen).

 

[ajetrumpet]

If that does not find the record....then we need to get Bob Larson or others in.

Why do we need to get Bob Larson involved? Or others? What others? Can't you handle this, Mike? Or is it over your head?

 

[Mike375]

I suppose what I want to have is this:

I load the database and the Switchboard appears. The Switchboard contains 2 text boxes and a button. The first text box is the "Search for Customer by Surname" box. The second text box is the "Search for Customer by Postal/Zip Code" box.

So you have a form that opens and it has two unbound textboxes....is that correct? It is important to bear in mind that you have a form and you want a search to be done on your records and based on an entry in an unbound text box.

Say I want to search for the details of a customer named Smith. I type Smith in the "Search for Customer by Surname" box, press the "Search" button and it opens a new form containing the first instance of a customer with the surname "Smith".

Why type in Smith. With the combo as soon as you get to Sm.....you can only find records that are there. With Smith there always be at least one record. The combo can also be used to OpenForm as opposed to FindRecord and so have 1 of 5 filtered. All the Combo does is put Smith in the box....no different to you typing into the unbound textbox EXCEPT the combo can only use records/names that are in the data base.

If you do it as a tabular form that goes to the "S*" with a click, that form will show all your "S*" as well as other details....nothing special since the tabular form would be based on the same query or table. Click which Smith you want and that will take you to your non tabular form. If that Smith was the third down the list then you will go to the same order in the non tabular form.....assuming correct sort....Or you can it do OpenForm instead of FindRecord

The combo or tabular form means you are not searching for a record that is not there.

A filter has filtered out any customers from the form who aren't called Smith, so when I click "Next Record", it takes me to the next customer in the table with the surname Smith.

Yep...as above. Although the tabular index form lets you look at all the Smiths. Whether you want to go to the first Smith's non tabular form or the fifth Smith is upto you or who is doing the clicking If you want 1 of 6 filtered...fine....1 of 6 but without filted then fine......the 1 of 6 without "filtered" will happen if you use a parameter query.


If I have 50 customers and only one is called Smith, then I would be presented with one record. (It'd say "Record 1 of 1 (Filtered)" at the bottom of the screen).

Again, do you only want to search on records that exist or do you want a form to open if no matching record....If the former the typing into the textbox is not the way to go.

 

[Mike375]

Why do we need to get Bob Larson involved? Or others? What others? Can't you handle this, Mike? Or is it over your head?

Adam,

If the stuff does not work then what.......Bob seems to have a top knowledge on the Access "insides"....that stuff is way out of my league.....and you could not help as you are still trying to do "vertical"

 

[ajetrumpet]

and you could not help as you are still trying to do "vertical"

Shut up, Smart A**!

I can't help anyway. I have no knowledge of this subject.

 

[PTRACER]

I suppose what I want to have is this:

I load the database and the Switchboard appears. The Switchboard contains 2 text boxes and a button. The first text box is the "Search for Customer by Surname" box. The second text box is the "Search for Customer by Postal/Zip Code" box.

So you have a form that opens and it has two unbound textboxes....is that correct? It is important to bear in mind that you have a form and you want a search to be done on your records and based on an entry in an unbound text box.

Correct. I have a form called "Customers Query", which I created based on...well, a query, that would display the customer details.

Say I want to search for the details of a customer named Smith. I type Smith in the "Search for Customer by Surname" box, press the "Search" button and it opens a new form containing the first instance of a customer with the surname "Smith".

Why type in Smith. With the combo as soon as you get to Sm.....you can only find records that are there. With Smith there always be at least one record. The combo can also be used to OpenForm as opposed to FindRecord and so have 1 of 5 filtered. All the Combo does is put Smith in the box....no different to you typing into the unbound textbox EXCEPT the combo can only use records/names that are in the data base.

To be honest, I would still prefer a text box.

The combo or tabular form means you are not searching for a record that is not there.

Does it present a problem if I search for a record that isn't there?

If I have 50 customers and only one is called Smith, then I would be presented with one record. (It'd say "Record 1 of 1 (Filtered)" at the bottom of the screen).

Again, do you only want to search on records that exist or do you want a form to open if no matching record....If the former the typing into the textbox is not the way to go.

I was hoping for a "No matching records." message box, if the surname doesn't exist in the database. If it's easy to prevent the form from opening when none can be found, then that would be preferable. If the form opens and displays all the records, because a match can't be found, then I can live with that.

------------------------------------

As the form is based on a query, can the text box not somehow be linked to the "Criteria" section of the query?

 

[PTRACER]

Sorry to have been a pain, but this has in fact solved my problem:

As the form is based on a query, can the text box not somehow be linked to the "Criteria" section of the query?

I wrote [Forms]![Switchboard]![SurnameSearch] in the Query and just used an "Open Form" button on the Switchboard and that does exactly what I want. I don't know why I was looking for a more complicated soluton that does the same thing, I guess I expected it to be more complicated than it actually is.

Sorry to have troubled you guys, you've been a great help.

 

[Mike375]

was hoping for a "No matching records." message box, if the surname doesn't exist in the database. If it's easy to prevent the form from opening when none can be found, then that would be preferable. If the form opens and displays all the records, because a match can't be found, then I can live with that.

You can achieve that OK. Have a go it at yourself. Most of this sort thing is all very similar. And the number of Events on a textbox and the form itself that will trigger code or a macro will allow just about anything to be done. Being able to use "Conditions" will make the data base sit up and talk.

Mike

 

[Mike375]

I wrote [Forms]![Switchboard]![SurnameSearch] in the Query and just used an "Open Form" button on the Switchboard and that does exactly what I want. I don't know why I was looking for a more complicated soluton that does the same thing, I guess I expected it to be more complicated than it actually is.

That is what I mentioned to you earlier about using a query.

 

[ajetrumpet]

Hey Mike,

Ever heard of QUOTE TAGS!? They work great!

 

[Mike375]

I am a luddite

 

[PTRACER]

I have a similar question related to combo boxes...

I have a combo box that contains a list of "Job Statuses", i.e. Pre-Workshop, Workshop, Awaiting Customer Response and Completed.

If I select "Completed", I would like it to automatically fill in a field in a table with the current date/time. Is this done via a macro, or via a form, or via VB?