FindRecord Macro Won't Work

[Kgwill85]

Okay, I've been fooling around with this macro for the past week and I
can't get it to work correctly.

I have a form that consists of two combo boxes and one command
button. The first combo box is populated by a list of Project ID
numbers, and the second one is populated by a list of Project names.
The information from these combo boxes are being pulled from a table
that is formed by an additional form named Project_Inventory.

So far I have set the OnClick for the command button to open up the
Project_Inventory form and to find the PARID (Project ID), however
when I try to find the Project Name the macro doesn't work. When I
leave the Project Name out it works fine and correctly searches for
the PARID.

Does anybody know how to get them to both work correctly? If the
PARID isn't known but the user knows the Project's name then he or she
should be able to just search for the Project's Name. But it's not
working for me.

 

[Steve Schapel]

Kgwill85,

What have you tried so far? Can you give the details of the macro at
the moment - all Actions, and pertinent Arguments? Thanks.

 

[ajetrumpet]

do you have anything written in the "where condition" section of the "open form" event within your macro design?? The following statement should do what you want....you might be missing a part of this statement....

WHERE ([Project_Inventory.Project ID]=Forms![Your search form]![combo1 name] Or [Project_Inventory.Project ID] Is Null)
AND
([Project_Inventory.Project Name]=Forms![Your search form]![combo2 name] Or [Project_Inventory.Project Name] Is Null])

Your "Project_Inventory" form in which you enter data to the underlying table has nothing do with this macro. That form is simply considered to be a "user friendly" version of your table. The "Project_Inventory" and "Project Name/ID" portion of the clause above assumes that these are the names of your tables and fields as well...

 

[Kgwill85]

Kgwill85,

What have you tried so far? Can you give the details of the macro at
the moment - all Actions, and pertinent Arguments? Thanks.

--
Steve Schapel, Microsoft Access MVP








- Show quoted text -

I posted this same issue in the formscoding section of this board and
it has 40+ replies and no solution. Here's what my macro looks
like...

FindRecord
Find What:=[PARID]
Match: Whole Field
Match Case: No
Search: All
Search As Formatted:No
Only Current Field:Yes
Find First:Yes

This macro works fine. It will search the PARID field. The problem
is that I need this form to be able to search the BO_Project_Name as
well. I thought that if I put the Find What like this... =
[PARID]Or[BO_Project_Name] that it would work but it doesn't. The
form comes up blank when I change the macro to this. I've tried
making a separate FindRecord macro just for the [BO_Project_Name] and
I get nothing. It seems to search PARID but not the BO_Project_Name.

 

[Kgwill85]

Here's a link to the other thread I made about this issue in the
formscoding section if that helps.

http://groups.google.com/group/micr...4626dcf6b5b/79ff385c62101e49#79ff385c62101e49

 

[ajetrumpet]

Why don't you start over with and type in what I have in the above response?
There should be no problem with that code, did you try it??

I will look at the link later today....if people can't seem to work it out though, maybe you're better off redoing the whole thing from scratch.
From what you want to do, there aren't many steps involved, shouldn't really be more than 2!

 

[Steve Schapel]

Kgwill85,

You should always precede a FindRecord action with a GoToControl action.
In this case, the active control on the Project_Inventory form just
happens to be the PARID (presumably because it is the first in the Tab
Order, but even then, I would still explicitly state it.

I still can't understand how you are relating to the Project_Inventory
form from the form that you have the criteria entered. I will assume
that it is a different form, and that it is already open.

So, I would set up the macro like this...

Action: SelectObject
Object Type: Form
Object Name: Project_Inventory

Condition: [PARID] Is Not Null
Action: GoToControl
Control Name: [Forms]![Project_Inventory]![PARID]

Condition: ...
Action: FindRecord
Find What:=[PARID]

Condition: ...
Action: StopMacro

Action: GoToControl
Control Name: [Forms]![Project_Inventory]![BO_Project_Name]

Action: FindRecord
Find What:=[BO_Project_Name]

This is making quite a few assumptions, as you haven't provided much
information.

 

[Kgwill85]

Thank you, I'm about to try this out.

I'll try and explain it even more for you also.

I have a form named SearchProjectInventory. That form has two combo
boxes and a command button. This is where the PARID and the
BO_Project_Name will be entered so Access can search for the matching
record. The Project_Inventory form is what will be populated after
the search is conducted. PARID and BO_Project_Name are 2 out of many
fields present in the Project_Inventory form.

My goal is to have the user be able to quickly find a record using the
SearchProjectInventory form. They might not know both the PARID and
BO_Project_Name however, that's why it is important for the search
function to work if they only know one or the other. (Being the PARID
or BO_Project_Name).

 

[Kgwill85]

After running the Macro I realize that it only works when search for
the PARID. If I don't enter a PARID and try to search just the
BO_Project_Name I get an error that says the FindWhat is blank and
that it cannot search without a PARID.

 

[Steve Schapel]

Kgwill85,

Ok, thanks for the further explanation. Though I think my earlier
suiggestion did relate to these requirements. I made the assumption
that the search would be based on the PARID if known, and if the PARID
on the SearchProjectInventory is left blank, then the BO_Project_Name is
used.

I still am unaware of the names of the controls on the
SearchProjectInventory form, so I have assumed they are named the same
as the fields they are searching... which is probalby not the best idea
as it becomes confusing. And I am also unaware of how the
Project_Inventory form is being accessed. You gave me the details of
the macro before, but I think you must have left something out... surely
there must be an OpenForm or SelectObject or some such in there somewhere?

May I suggest a simpler approach?... Instead of a separate form, you
could put the two unbound comboboxes in the Form Header section of the
Project_Inventory form. And instead of using a command button, you
could simply use the After Update event of each combobox. That's how I
would do it. Name the comboboxes something other than the names of the
fields, for example SearchID and SearchName. So then, on the After
Update event of the SearchID combobox, you would have a macro like this:

Action: GoToControl
Control Name: [PARID]
Action: FindRecord
Find What: =[SearchID]
Action: SetValue
Item: [SearchID]
Expression: Null

And on the After Update event of the SearchName combobox, you would have
a macro like this:

Action: GoToControl
Control Name: [BO_Project_Name]
Action: FindRecord
Find What: =[SearchName]
Action: SetValue
Item: [SearchName]
Expression: Null

In cases like this, it often helps to make the BackColor of the unbound
search comboboxes something other than the main data controls, to help
make it clear that they have a different function

 

[Steve Schapel]

Kgwill85,

I can't understand this. Did you use the Condition in yoiur macro:
Condition: [PARID] Is Not Null
.... as I suggested, and then enter three dots:
....
in the Condition for the FindRecord action?

Can you post back with the full and precise details of the macro you
have now, so I can test? Thanks.

 

[Kgwill85]

You are correct, I have an OpenForm macro set for the
Project_Inventory to open when you click the search button in the
SearchProjectInventory form.

I was actually looking into an alternative way to to this, and your
idea is similiar to what I was thinking. I'll see what happens.
Thanks for the help.

 

[Kgwill85]

Thanks for the help, it really is starting to work out for me. I
appreciate it.

 

[Steve Schapel]

Pleased to hear it, Kgwill85. Let us know if you have any further
questions, and best wishes for the rest of your project there.

 

[Guest]

Hey Steve,

I had a question about 'Conditions'. If the FindRecord returns no results,
how do you make sure to display a error message and close macro?
Thanks!

Kgwill85,

You should always precede a FindRecord action with a GoToControl action.
In this case, the active control on the Project_Inventory form just
happens to be the PARID (presumably because it is the first in the Tab
Order, but even then, I would still explicitly state it.

I still can't understand how you are relating to the Project_Inventory
form from the form that you have the criteria entered. I will assume
that it is a different form, and that it is already open.

So, I would set up the macro like this...

Action: SelectObject
Object Type: Form
Object Name: Project_Inventory

Condition: [PARID] Is Not Null
Action: GoToControl
Control Name: [Forms]![Project_Inventory]![PARID]

Condition: ...
Action: FindRecord
Find What:=[PARID]

Condition: ...
Action: StopMacro

Action: GoToControl
Control Name: [Forms]![Project_Inventory]![BO_Project_Name]

Action: FindRecord
Find What:=[BO_Project_Name]

This is making quite a few assumptions, as you haven't provided much
information.

--
Steve Schapel, Microsoft Access MVP

FindRecord
Find What:=[PARID]
Match: Whole Field
Match Case: No
Search: All
Search As Formatted:No
Only Current Field:Yes
Find First:Yes

This macro works fine. It will search the PARID field. The problem
is that I need this form to be able to search the BO_Project_Name as
well. I thought that if I put the Find What like this... =
[PARID]Or[BO_Project_Name] that it would work but it doesn't. The
form comes up blank when I change the macro to this. I've tried
making a separate FindRecord macro just for the [BO_Project_Name] and
I get nothing. It seems to search PARID but not the BO_Project_Name.

 

[Steve Schapel]

Zef,

You could use a Condition that checks the table/query that the form is
based on to see if the required record exists. For example:
DCount("*","YourQuery","[ID]=[Forms]![YourForm]![FindID]")>0