Solved

FIND/REPLACE on Access subforms

Posted on 2001-08-21
26
349 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:wduffus
  • 12
  • 9
  • 5
26 Comments
 
LVL 6

Expert Comment

by:devtha
ID: 6413577
you need a custom function
0
 

Author Comment

by:wduffus
ID: 6413688
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
 
LVL 6

Expert Comment

by:devtha
ID: 6413715
seems complicated...can you send me the mdb (97 only) devthaa@yahoo.com
0
 

Author Comment

by:wduffus
ID: 6416111
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
 
LVL 6

Expert Comment

by:devtha
ID: 6417480
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
 

Author Comment

by:wduffus
ID: 6420024
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
 
LVL 6

Expert Comment

by:devtha
ID: 6421850
is it possible to get that mdb I can work on it at home. My email is in previous posts.
0
 

Author Comment

by:wduffus
ID: 6422211
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
 
LVL 6

Expert Comment

by:devtha
ID: 6422250
I meant 2000 version. I have 2000 at home. If it is an issue then never mind...
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6990373
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
 

Author Comment

by:wduffus
ID: 6993895
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6994180
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
 

Author Comment

by:wduffus
ID: 6994549
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 54

Expert Comment

by:nico5038
ID: 6994591
Oops, you're right.
I hope devtha did create already his function as he stated in his comment.

Nic;o)
0
 

Author Comment

by:wduffus
ID: 6995125
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6996047
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
 

Author Comment

by:wduffus
ID: 6996705
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6996861
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7168031
wduffus,

How far are we on thisone?

Nic;o)
0
 

Author Comment

by:wduffus
ID: 7168134
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7308072
wduffus,

How far are we on thisone?

Nic;o)
0
 

Author Comment

by:wduffus
ID: 7308131
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7308135
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
 

Author Comment

by:wduffus
ID: 7308958
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 7308962
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
 

Author Comment

by:wduffus
ID: 7309088
Thanks Nic;o!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now