Solved

Access Subform Linked to Field using Wildcard

Posted on 2013-12-12
10
576 Views
Last Modified: 2013-12-13
We have Office 2010.  In Access 2010 I have a form named frmDocket with a field named Initials.  On this form I'd like to add a SubForm to an existing form named frmDktAtty that has a field named RouteTo.  I'd like to define which fields link my main form (frmDocket) to the subform (frmDktAtty).  I'd be linking frmDocket.Initials to frmDktAtty.RoutedTo

Form field    Subform Field
Initials          RoutedTo

My problem in the Initials field only contains one initial per record:
Initials
ABW
GAP
CLT
LDM
FRG

My RouteTo field contains multiple initials like the following:
JDP, GAP, AXT, LDM
MEN, LDM, CLT, FRG
PXT, JDP, JJK
LDM, JJK

Therefore I need to link the field with the equivalent of the following, however, I can't see a way to do this with a subform.

Initials = (Like "* [RoutedTo]*")

Can anyone assist me with this.

Thanks in advance!
0
Comment
Question by:Senniger1
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39714318
The route to field should be a table since it is implementing a 1-many relationship.  This may or may not be a time consuming change depending on where you are in the development cycle and how many forms/queries/reports are impacted.

1. Create a new table.
2. Write some code that reads the existing mushed field and writes a separate record for each entry.  If you have only test data, you can eliminate this step.
3. Change the form to use a subform for the route to data.
4. Create a function that recreates the mushed list that you can use in queries where you want to see the list but not update it.  This will prevent you from having to make changes to all your reports to add subreports for the many-side data.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39714358
Specify record source of the frmDktAtty as

SELECT RouteTo.routeto FROM RouteTo
WHERE InStr([routeto],forms!frmdocket!initials)>0


Leave parent and child fields properties of the subform empty.

Adjust table names in the query as necessary.
0
 

Author Comment

by:Senniger1
ID: 39714380
Sorry but I don't think this will work for me.   Basically my users can type any number of attorney initials in the RoutedTo field.  Sometimes they put question marks in there as well if they don't know the attorney.

I have three tables with different information, but all have a RoutedTo field.  I want to create a form where a user LDM (in this example) can open and see subforms for each of the three tables showing where his initials are listed as one of the entries in the RoutedTo field.

In other words, LDM will see the records for the following:
JDP, GAP, AXT, LDM
MEN, LDM, CLT, FRG
LDM, JJK

Thanks!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39714386
Rather than looking for ways to do it wrong, why not just expend the effort to do it right?
0
 

Author Comment

by:Senniger1
ID: 39714888
vadimrapp1:  I tried your code, but results are only Initials=LDM and RoutedTo=LDM.  It does not pull up anything where Initials=LDM and RoutedTo contains LDM within the field (for example RoutedTo=(MEN, LDM, CLT, FRG).

I don't see where you code allows for this and I don't think you can use a wildard with InStr

PatHartman:  I am still early in development so I could adopt your scenario if it works for my needs. I cannot see how following your steps will allow my users to enter into the RoutedTo field (from other forms) entries like "JDP, GAP, AXT, LDM" and "LDM, JJK" (this is not negotiable), but I'll try to follow your steps and see.  My users need to be able to type into the RoutedTo field Initials comma Initials comma Initials comma.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:PatHartman
ID: 39715035
My users need to be able to type into the RoutedTo field Initials comma Initials comma Initials comma
I don't think so.  What they need to do is to specify one or more people something must be routed to.  You have fixated on a data entry method rather than the requirement.  Step back from the interface and think about what the data is used for and how you might need to store it to facilitate that.  You might also want to take a breather and do a little reading on normalization.  First Normal form says that every field should be atomic.  That means it should contain one and only one value.  When you have more than one of something (children, pets, sweaters, destinations, etc), you have many and to properly implement "many", you need a separate table.  The visual representation of the many-side data is usually a subform.  You can make the subform unobtrusive but it will always be a vertical list rather than a horizontal list separated by commas.

If your users are fixated on the horizontal list separated by commas, you should STILL store the data in a normalized fashion.  You'll just need code in several form events to go from the table to the mushed field and then from the mushed field back to the table so sharpen your pencil.  For processing purposes such as selection and email and printing, etc.  you will be oh so happy the data is normalized.  Don't let a visual, interface preference, overrule common sense and proper schema normalization.
0
 

Author Comment

by:Senniger1
ID: 39715078
I totally get what your saying and appreciate the detail of this information.

In my particular circumstance, however, the RoutedTo field isn't any different for us than a Memo Field in which I want to query all the data in that field for a particular word.

I'm going to try to mock up a database with what I'm doing and attach it.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39715169
It is different.  You wouldn't sync a subform to some random word in a Memo field and that is what you are asking to do.  "Routing" means something in the real world and in the real world people want to keep track of what was routed, to whom, and when.  Then they might want to track results.  If your app is going in any of those directions, it is best to make the change now to normalize the tables.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39715260
> I tried your code, but results are only Initials=LDM and RoutedTo=LDM

here's attached database that seems to work as you want.
db.mdb
0
 

Author Closing Comment

by:Senniger1
ID: 39716960
That is exactly what I was asking for on this particular issue.  Once I saw your work I was able to alter my forms, etc. to get them to work the same way.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

20 Experts available now in Live!

Get 1:1 Help Now