Solved

Filter propert on form uses form textboxes nopt table field names

Posted on 2011-03-24
2
229 Views
Last Modified: 2012-05-11
I have two database systems.

In one when the user applies a filter to a form the filter property on the form is set using the forms control names
e.g. ([frmContacts].[contactTelephoneNumber] Like "*N/A*")

In the other the filter property is set using the table names
e.g.([tblClient].[CL_Name] Like "*Pa*")

Both forms are based on a query of one table - one the contact table one  the client. table.
Remember both filters are set from the ribbon - none of my code.

I need the filters to use the table names (so I can use the me.filter string in an sql command to append to a table e.g.

DoCmd.RunSQL "Insert Into ContactsForMailMerge Select * From tblClient Where " & Screen.ActiveForm.Filter

This works filne when the filter uses the table names but obviously not when it uses the form names.


Why are the two forms using different syntax for the same property?
By the way if I copy the one using form names to the other database - it still uses form names.


Thanks
Lou
0
Comment
Question by:Louverril
2 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 35213147
Hi,

I think it depends on the RecordSource you used in the form. If the field you filters is 1:1 a table field then the filter will use the table field. If the field is a calculated field in the query (not in the form, that cannot be filtered) like "SELECT [Field1] & [Field2] AS Field3..." then there is no field of the table which can be used and so Access uses the form field instead. Of course a field like this can also not be used to insert a value with an INSERT command - because there is no "Field3" in the table to SELECT. You must use the same method to select the data from the underlying tables like used in the RecordSource. You should be able to use "SELECT [Forms]![frmContacts].[contactTelephoneNumber]..." but only if the form is loaded.
But as you can see in the filter expression, there is no "Forms!" prefix and so it will be interpreted as query parameter which must be entered.
If you want to use your INSERT command you must make sure that all fields of the form are based directly on table fields or you must write a text parser which analyzes the filter string and "repairs" it. Not an easy job as there are a lot of possible filter variants.

Cheers,

Christian
0
 

Author Comment

by:Louverril
ID: 35244244
Thanks for that - will check it out in the next couple of days - O got sidetracked with something else but tomorrow this is back as priority 1.

I think I will have to use me.recordsetclone and create a table.

thanks Lou

0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
MS Access 2010 Close Form  Event - Stop Form Closing 4 27
IIF help, YN field 7 22
DCount Type Mismatch 2 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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