Access Subform Linked to Field using Wildcard

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!
Senniger1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vadim RappConnect With a Mentor Commented:
> 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
 
PatHartmanCommented:
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
 
Vadim RappCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Senniger1Author Commented:
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
 
PatHartmanCommented:
Rather than looking for ways to do it wrong, why not just expend the effort to do it right?
0
 
Senniger1Author Commented:
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
 
PatHartmanCommented:
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
 
Senniger1Author Commented:
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
 
PatHartmanCommented:
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
 
Senniger1Author Commented:
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
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.