UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Show Query Results In Subform When Fields Vary, Access 2013    
 
   
AshJT
post Nov 11 2019, 07:25 AM
Post#1



Posts: 42
Joined: 4-June 08



Hi

I have a form where I offer the user the ability to pick a table and then several fields from it (via a couple of list boxes).

I then display the records from the table (showing only the fields chosen) in a subform.

The only way I've found to do this is by using vba to create a new form, adding controls to it to match the fields chosen, then setting my existing subform's source object property to this new form.

I can't just use a fixed form because I need to vary what fields the user sees.

It works well enough, but it's not the best solution as creating the form each time locks the database for any other users.

Is there a better, less clunky way? - i need it to be a form rather than just opening the records as a query.

Cheers
Go to the top of the page
 
Larry Larsen
post Nov 11 2019, 07:48 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

There are "limits" to the amount of controls/object you can create and it would seem you would run out sooner than later...

It does seem odd in allowing the user to pick and choose, can you give use some idea of the amount of controls/fields we are talking about..

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
AshJT
post Nov 11 2019, 08:12 AM
Post#3



Posts: 42
Joined: 4-June 08



Hi

Thanks for your reply.

Hope this isn't too much info:

I work for a mailing company that sends out various materials to address lists supplied by clients. After each mailing the table is brought into the database.

When we get a bunch of mail returned - wrong address, or people wanting to opt-out or whatever, the user picks the right table from the list and is then presented a list of fields in that table. From this they pick a few relevant ones they want to display - eg Contact name, address line 1, address line 2, city, postcode etc - in a subform set to datasheet view.

Each table has different field names/layouts because they come from different clients. There are probably at least 100 tables in the database at a time - I delete the old ones perodically to keep things manageable.

The user works down the pile of mail searching for the record to match the mail in the subform, then they mark the record to show it as a return.

At the end of the process, the list with the returned records flagged up is sent to out client so they can remove the flagged records before they send out again.

At the moment I'm re-creating the form each time so I don't hit any sort of controls limit, but that process also causes the lock.

I'm now thinking maybe I get a subform with say 20 text fields on it, then set the control source of these to the fields the user picks. That may work? (I'd have to fix it so they dont try to pick more fields than I have controls, but they only really need address details, so should be more than 10 or so max)

This is just one of those things I can think of ways of doing, but I'm trying to find the 'best' way (I have a habit of kludging things!). I'm also starting to use QR codes on mail items so they can just be scanned in and no-one has to look up any data, but there are times when it's not possible to use this method.

Hope that clarifies things a bit?



Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 09:00 AM
Post#4


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


>> I'm now thinking maybe I get a subform with say 20 text fields on it <<

This is possible. I'll see if I can dig out an example for you.

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Nov 11 2019, 09:42 AM
Post#5


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


Having worked for a company engaged in a similar type of mass mailing operation, I can relate to some of the challenges of working with multiple clients.

That said, I think you're inventing more work for yourself than necessary.

Let's start with the most obvious. Why do you accept inconsistent data formats from clients? Why do you do not specify the format they must use to supply you the data you need in a consistent manner. I know that's possible because we did it. Each client will fudge the margins, but if you provide them a template of REQUIRED fields (first name, last name, address1, address2, and so on), and only accept mailing lists that include those REQUIRED fields, your life will be much simpler and, perhaps more importantly, much less costly. You won't have to invest hours of your user's time compensating for the chaotic nature of the data supplied. I say this partly because I simply can't imagine that the actual data being provided from MAILING LISTS would be so different across clients that you can't specify the core, required MAILING FIELDS in the format you need. I mean, every mailed piece goes to:

A person or a company
A street address
A city
A state
A postal code

Those must be core, required, fields and you can quite logically specify how that much is provided for your mailing service to succeed.

When you import a mailing list into your database, knowing that the required fields will be provided and in a pre-defined layout and format means you can import that relevant data into ONE permanent table. And voila, all of the follow up work is done via one form bound to those fields in that table. Each time a user works with it, they filter the records to the client at hand.

If your data is really radically different from that, I guess you have to resort to enormous amounts of manual work, but I can't really believe that to be the case.

In my ten years as a consultant to that mass mailing company we went through a few iterations to get to our most efficient solution, but one of the more important steps was defining the required layout of the mailing lists accepted from clients. Like your users, we needed to invest hours in data clean up, not in data preparation and set up followed by more hours of data clean up.

At one point we had over 1 million names in our master table of previously mailed clients and over 2 million mailing records. I don't know how that compares to the hundreds of mailing lists you manage.

Finally, if you have to do it the way you describe, I would try to set it up as a main form and subform. Load the relevant client table directly into the subform as a datasheet. Instead of creating new fields for fields you want to look at, simply hide those fields in the datasheet.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AshJT
post Nov 11 2019, 10:01 AM
Post#6



Posts: 42
Joined: 4-June 08



Hi guys

Thanks for the replies. I agree with the idea of not accepting inconsistent data believe me. Unfortunately it's just not possible for us to enforce it - sorting out data is seem as a selling point, making it easy for a client to choose us instead of another company.

A lot of the time I deal with marketing contacts, not IT ones, and they don't want to put any effort into data, so it's easier for me to work with what I'm given than try to change the culture. Defeatist, I know, but my own company would not support what they would see as me 'giving the client a hard time'.

I like the idea of getting the data in and then hiding the unwanted fields and any examples welcome. Ive done a bit of initial work and it's definitely looking more effective than creating a new form each time with vba.
Go to the top of the page
 
DanielPineault
post Nov 11 2019, 10:12 AM
Post#7


UtterAccess VIP
Posts: 7,004
Joined: 30-June 11



Why not have a table with Field1, Field2, ... Then you could allow them to map their data as they please and then you push the selected data to your generic table. Have a secondary table to manage the Field Names and you're done.

But far from proper! Sometimes it is better to help the client cleanup their act then go along with their mess. I've always found them very thankful after helping them clean things up, streamline the data/processes...

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
GroverParkGeorge
post Nov 11 2019, 10:28 AM
Post#8


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, what I described is a bit of a utopian stance, but I had to suggest it. My client (the mailing company) had a bit more clout with clients due to the nature of their business, but we still had to accept less than perfect data much of the time.

Reverting back to the problem at hand, and the idea that only some of the fields in any client mailing list would be relevant to your data clean up task, I think that's where you'll get the biggest return for your effort. I mean, your examples are pretty much the fields I'd expect to have included in every review of returned mail: Is the address right? Is the contact name right? and so on. I can't imagine that you'd have to account for more than a dozen such fields at the very MOST.

Another thing occurs to me. Maybe a discounted rate for clients who agree to provide data in YOUR preferred format would be feasible. In other words, we'll take anything you give us for $x per thousand, or we'll take the data in our specified layout for $x-$y per thousand. You could recoup that discount in lower processing costs on the back end. P

Another thing occurs to me, although it's possibly not so useful here. It depends in part on your business processes.

There are services that will pre-validate mailing lists and email marketing lists for a price. It can be a bit pricey, but when you are trying to consolidate multiple lists and ensure that the resulting compilation is mostly, or entirely, composed of valid mail or email values, it can save on the cost of mailing/emailing to the inevitable bogus records. In the United States, you can have the USPS do that, for example, if I recall correctly, although that may have been as part of the followup on returns. Most of the time, that's part of the return mail process for such mailings. We used to get a file from the USPS with that kind of information, although I don't recall too many details about it.

My former client was not interested in whether any given person on a mailing list did or did not respond. He was interested in getting an acceptable return rate from the mailing. And if he could eliminate as many of the bogus addresses as possible BEFORE spending money on mailing to them, he was ahead in two ways, lower mail costs by eliminating bogus records, and a higher return rate from the remaining valid addresses, which he could advertise to other potential clients.




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 11 2019, 10:35 AM
Post#9


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


Oh, another simple step that I spent a lot of time on was simply isolating and removing duplicates. As you probably know, most client mailing lists come with some duplicated records. Sometimes it's two variations on the contact name, sometimes it's spouses or partners at the same address. Sometimes it could even be a former tenant and a current tenant at the same address.

At first I was pretty cautious about dropping such "near duplicates" from mailings, but over time I came to opposite view. It's better to send one letter to one valid address than two or more letters that may or may not be duplicates to the same address, when that address may not even be valid. We watched our return rate very carefully. Getting more aggressive about duplicates and bogus records did not change that return rate that we could see. So there's not much of a cost there and a good benefit.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AshJT
post Nov 11 2019, 10:43 AM
Post#10



Posts: 42
Joined: 4-June 08



Hi guys

The impression I get from my colleagues in sales is that the market we are operating is pretty cut-throat. I think the time I'm putting into this is kind of brushed under the carpet so they don't have to charge it on, but screening data via a list cleaning service (we have something called PAF here in the UK which sounds similar to what you mention George) would mean a cost to us we would have to pass on, and unfortunately the sales guys live in constant fear of having to charge more than they absolutely have to.

This may be just sales guys being sales guys, but there's definitely a dread of giving a client any reason to look around for another quote. As someone who's been through redundancy and site closure due to low turnover, I can understand it.

That said you make some very valid points and I'll have a chat with my colleages to see if we can be a bit more proactive with how data comes in to us.


re: the form, I've got something working well enough to be going on with now I think - I have a form with 20 fields, from the user's input I choose datasource for the form, then I change the control source of the fields to match the ones they've chose. Finally I hide any surplus ones and refresh everything.

It seems to be working, so I'll do some more testing and then see where we are.

Thanks for the posts - very helpful to see how others tackle sort of thing.
Go to the top of the page
 
chrismbaylis
post Nov 11 2019, 10:46 AM
Post#11



Posts: 104
Joined: 25-March 10
From: St Albans, UK


Hi Ash,

I have a similar system that I am working on - a source to target mapping definition that allows the user to define what their end dataset looks like (there are 300+ fields in the source file - 90% of which they won't need). It also allows the user to create calculated variables and assign these to the target table by way of insert/update SQL.

As the end result will be different for each user, my approach is to allow the user to identify the data source they want to view using a combobox that lists valid tables - I then have a blank form which is used as a subform.

At runtime the subform creates each required field in requested display order - but the subform is then displayed as a dataset - this then allows run-time filtering and sorting, etc., adding levels of functionality.

I'm still in the middle of building the solution - but, based on the above description, it shouldn't be hard to do what you're looking for - just consider the structure and form of your data when adding controls (i.e. combo boxes, check boxes, etc.).

When I have something workable, I'll post an example for you.

Regards,
Go to the top of the page
 
AshJT
post Nov 11 2019, 10:48 AM
Post#12



Posts: 42
Joined: 4-June 08



Hi George

Just saw your post re duplicates - I do screen for these before we mail (just with a few 'find duplicate' query wizards on names and post code combinations).

Absolutley agree this is worthwhile. The last thing I want to do is annoy people by sending duplicate mail.

I also agree about erring on the side of removing a record if it may be a duplicate, rather then only working with certainties.

If Tom Bloggs gets his mailing and Ted Bloggs does not because Ted was listed in the database as 'T Bloggs' and I assumed he was a copy of Tom, I have no problem with that!
Go to the top of the page
 
GroverParkGeorge
post Nov 11 2019, 11:03 AM
Post#13


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


You sound like Gary, my former client. wink.gif

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 11:28 AM
Post#14


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Here's an example db.

You ought to be able to import the form frmResults into your db along with the module basUtility.

Then you use the function fBindRS() to load SQL or a table/query name.

frmMain is an example of how it works with frmResults as a subform.

hth,

d
Attached File(s)
Attached File  QueryResults.zip ( 85.62K )Number of downloads: 11
 

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Nov 11 2019, 01:42 PM
Post#15



Posts: 6,182
Joined: 11-November 10
From: SoCal, USA


<creating the form each time locks the database for any other users.>
do you have multiple users sharing a single common frontend db?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
AshJT
post Nov 12 2019, 05:28 AM
Post#16



Posts: 42
Joined: 4-June 08



Hi Tina

Up until recently I only had one user in at a time, but now there might be 2, so still not huge numbers, but as you suggest it's probably time to start thinking of splitting it to be front end/back end.
Go to the top of the page
 
AshJT
post Nov 12 2019, 05:29 AM
Post#17



Posts: 42
Joined: 4-June 08



Thanks! - I'll check this out.


EDIT - Just had a quick look, that's pretty much exactly what I've ended up with for my form!

I'll go through and see how it was done - looks more efficient than my code, so it's a useful learning experience for me to look through this.


This post has been edited by AshJT: Nov 12 2019, 05:35 AM
Go to the top of the page
 
AshJT
post Nov 12 2019, 05:30 AM
Post#18



Posts: 42
Joined: 4-June 08



We're definitely different people, just in a similar state of mind! laugh.gif
Go to the top of the page
 
AshJT
post Nov 12 2019, 09:18 AM
Post#19



Posts: 42
Joined: 4-June 08



Hi Chris

Sorry for not replying before - only just saw your message among all the replies.

Your system does sound much the same as what I need, so would be great to see how you've approached it.

Cheers
Go to the top of the page
 
cheekybuddha
post Nov 12 2019, 09:18 AM
Post#20


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


>> I'll go through and see how it was done <<

Let me know if you have any questions - I'mm do my best to answer them.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 11:33 PM