Solved

Access Subform Linked to Field using Wildcard

Posted on 2013-12-12
10
585 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

867 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

15 Experts available now in Live!

Get 1:1 Help Now