FIND/REPLACE on Access subforms

The MS Access FIND and REPLACE function works well on the active form (or page of a tabbed form) but I need it to search the other tabs (pages) which contain subforms with their respective tables.  For example, Tab 1 (Companies) has Form 1, Table 1.  Tab 2 (Contacts)has a subform and table 2.  If I do a "FIND" on a Tab 1 field, it only searches Table 1.  I want to search Table 2 also.
wduffusAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
When the problem occurs "sporadic", it's my guess you have a text field with an embedded quote causing the trouble.
Just remove the single quotes and make the concatenation like:
"[textfield]" & chr(34) & me.textfield & chr(34)
Thus inserting the quote as part of the string.

Nic;o)
0
 
devthaCommented:
you need a custom function
0
 
wduffusAuthor Commented:
devtha-
I'm sure that Access, by default, does not allow searching tables other than the control source for the active form and that I will need to write some code to accomplish this.  But what is the code?  Any ideas?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
devthaCommented:
seems complicated...can you send me the mdb (97 only) devthaa@yahoo.com
0
 
wduffusAuthor Commented:
devthaa,
Access 2000 does not allow a Save As or Export to a previous version.  I hear there are global find/replace utilities for searching in design mode (ie: Rick Fisher's products) but I can't find (pardon the pun) anything to globally search data.
0
 
devthaCommented:
A custom function would help. All you need to do is get all the recordsources for the screen.active form and its subform and go to that table and do a column search inside the function and log it into a separate table. Now that I just dumped y thought I am going to work on it irregardless of this Question.
0
 
wduffusAuthor Commented:
Please note that the main (Tab 1) form (frmMain)has its table (tblCustomers)and that the subform (frmContactsSub)which is reflected on tab 1 in datasheet style has a diifferent table (tblContacts).  Thanks!!
0
 
devthaCommented:
is it possible to get that mdb I can work on it at home. My email is in previous posts.
0
 
wduffusAuthor Commented:
The database is Access 2000.  Microsoft has designed this version not to be backward compatible and will not allow an export as an older version.  I don't know how to get you a workable copy.
0
 
devthaCommented:
I meant 2000 version. I have 2000 at home. If it is an issue then never mind...
0
 
nico5038Commented:
for wduffus

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - To be deleted
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
wduffusAuthor Commented:
I still have not received an answer to the global FIND/REPLACE "custom" function.  If anyone has a solution I would love to hear it as the need still exists.
0
 
nico5038Commented:
I'm a bit puzzled, as the find/replace pop-up does has three radiobuttons to select current procedure/module/project.
Looks like selecting "project" solves your problem.

But it will only work on the code, not on the values assigned to properties...

Nic;o)
0
 
wduffusAuthor Commented:
Thanks for the reply.  I am looking for a Global FIND/REPLACE for the end user to use to find contact data (a particular person who works at a certain company for instance).  I believe you are referencing the FindReplace while programming in the IDE.

My original question states how I have my tables set up.
0
 
nico5038Commented:
Oops, you're right.
I hope devtha did create already his function as he stated in his comment.

Nic;o)
0
 
wduffusAuthor Commented:
I did not get a solution from devtha as I cannot release my database due to liability issues.

Do you have any ideas on this custom function he talks of?
0
 
nico5038Commented:
The function effectively will have to "know" what fields are present and which are possible candidates for "update".

The function will have to build an update query based on that data.

Problem with generalizing such a function will be that "normally" an update is performed on a table or query just referencing one table, as otherwise you could end up with an attempt to update a read-only recordset :-(

Personally I would probably design a separate form with a datasheet subform where the user can use the right-click pop-up to select the rows to be updated and add a textfield where he can type the new value that's supposed to be set. Thus the building of the UPDATE query is rather straight forward.
To expand the possibilities you could add a combobox with the fields from the subform that can be updated.

When it's always one distinct value that has to be changed, you could also add a combobox with a SELECT DISTINCT and combined with a textfield use that for the generation of the UPDATE query.

Clear sofar ?

Nic;o)
0
 
wduffusAuthor Commented:
Nic;o),

This may be above my head.  When you speak of "update" queries and values it doesn't make sense.  I want to FIND, not UPDATE, if I am reading you correctly.

Here's the scenario from the user's viewpoint:
The user wants to phone Dave Smith but cannot remember his phone number and, oh no!, he can't remember his company name either.  But he can only do a FIND on the company name because the contact names (Dave Smith, for instance)are on a subform tied to a second table (tblContacts).  Access won't allow a search on anything but the main table (tblCustomers).

I want to allow the user to search tblContacts and return the company record (on tblCustomers) with all relative information (ie: phone number).  Is this possible?

0
 
nico5038Commented:
This is solved "easily" by the creation of a query with all needed information to select on.
Personally I always present my users with a datasheet selection subform where they can use the right-click popup menu to place as many filters as they need to find a person.
Besides this datasheet subform there are the action buttons like "Update", "Print", etc. to manipulate the selected row (or even rows).
Drop me a line at my nico5038 mailbox "at" yahoo.com and I'll mail a sample.

In your case a query that selects both name and company would suffide. When there's also an additional table with person related information, you could use a union query to pile up all information needed.

Nic;o)
0
 
nico5038Commented:
wduffus,

How far are we on thisone?

Nic;o)
0
 
wduffusAuthor Commented:
Sorry Nic;o)
I have been on multiple out-of-town trips and trying to get a new company off the ground.  I should be able to focus on this Find/Replace function in the next week or so.  Thanks for your contined help.
0
 
nico5038Commented:
wduffus,

How far are we on thisone?

Nic;o)
0
 
wduffusAuthor Commented:
Ni;o),

Thanks for the followup.  I do have an operating Find/Replace for my contacts.  It is a combobox supported by a query for the two tables.  It loads and gives me a drop-down list of all contacts and selection is made by last name.  The only item that remains to be fixed is that if I change my mind, delete the entry in the combobox and then move forward or backward a record I get an error "Runtime error 3077.  Syntax error (missing operator) in expression."  I can get beyond this by selecting "End" but it is a nuisance.
0
 
nico5038Commented:
Can you drop that problem in a zipped .mdb in my nico5038 mailbox "at" yahoo.com and I'll have a look.

(To make it easy please add a link to this Q ;-)

Nic;o)
0
 
wduffusAuthor Commented:
Nic;o)

Because this scenario does not happen often and due to the time required of me in this new business venture, I am not going to pursue a solution at this time.  Thank you very much for your offer and time.  I may post this question at a future date when things have died down.

Thanks again.

Bill
0
 
wduffusAuthor Commented:
Thanks Nic;o!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.