Jurgen Welz
jwelz at hotmail.com
Tue May 17 14:56:09 CDT 2011
I've devised a method to allow users to select a person's record by an alias first name in a lookup combo and there appear to be some issues. For example, I have a fellow known as Willie whose payroll name is Guillermo and I need my users to be able to look up the user in a search combo. Choosing either Willie xxx or Guillermo xxx navigates the form to display the Guillermo record. Aliases include common misspellings such as Jurgen and Juergen for Jürgen. Alias names appear in a continuous sub form on the main form record. Assume a master record in tblEmployee with an autonumber primary key, EmployeeID and text FirstName and LastName fields. Assume an alias table that contains a foreign key pointing to the Employee record and a text FirstNameAlias field. The alias names are unioned to the employee names in the lookup combo. The combo displays full names being the FirstName & ' ' & LastName concatenation and there are options to load LastName & ' ' & FirstName and to include only active, only inactive or both records as well as regional filters. The query that I wish to union appears: SELECT tblEmployee.EmployeeID, tblEmployeeAlias.AliasFirstName & ' ' & tblEmployee.LastName AS Employee FROM tblEmployeeAlias INNER JOIN tblEmployee ON tblEmployeeAlias.EmployeeID = tblEmployee.EmployeeID I have 31 alias names in the table. 28 of them work. 3 don't in that the concatenation of the LastName doesn't happen. If I add a column to the select statement: [LastName] & ' ' & [tblEmployeeAlias].[FirstName] AS Expr1, the LastNames appear. If the select statement includes a column for LastName alone, it appears in the query so there is no problem with missing or null data and the join functions. We know it functions as the 3 alias names appear and a join failure would exclude those records. AliasFirstNames that don't show a concatenated last name are Rob, Rick and Mitch. The last name will not appear in the concatenation of the Alias table first name with primary record last name in the 3 records. I don't commonly do display concatenations of fields from different tables but am baffled why some work and some don't. There are no names with apostrophes. Common names like Mke and Ron work as do unusual name like mine which is commonly misspelled. It seems to make no difference if the first or last name is unique. If I reverse the order of the names in the concatenation, the Last Names show. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com > From: cjlabs at att.net > To: accessd at databaseadvisors.com > Date: Tue, 17 May 2011 11:01:26 -0500 > Subject: Re: [AccessD] another tab control issue > > I've used Form_Resize on several on the forms, including the problem form and another form with a tab control, and they look great (ie, normal) in 2010. Have to do a little tweaking, but that's nothing compared to having a tab control mangled beyond use by 2010. > > Now to change all the rest of them. . . . > > > Thanks, > Carolyn Johnson > > > > ----- Original Message ----- > From: A.D. Tejpal > To: Access Developers discussion and problem solving > Sent: Monday, May 16, 2011 11:29 PM > Subject: Re: [AccessD] another tab control issue > > > Carolyn, > > Amongst various alternatives, my sample db named Form_Resize (in access 2003 file format, reference: DAO 3.6), might be of interest to you It is available at Rogers Access Library. Link: > http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45 > > Apart from automatic resizing to suit current screen resolution, the sample offers custom resizing (up or down in steps of 5 percent) of any given form, if so desired by the user, with the option to freeze the custom size so that thereafter the form opens to custom size (over-riding the size dictated by actual resolution). At any stage the behavior can be reset to normal (i.e. automatic resizing to suit current resolution). > > Resizing is effected through a self contained class. Six styles of demo forms are included in the sample as follows: > (a) Simple controls - all free to float and resize. > (b) Simple controls - with certain controls having tag property settings for locking their position and / or size. > (c) Combo box and list box. > (d) Nested subforms - Continuous. > (e) Nested subforms - Datasheet. > (f) Tab control having (i) Nested subforms and (ii) Option group. Also - an option group directly on the form. > > Note: > ------ > (a) The sample db has also been tested successfully on Access 2010. Resize behavior of tab controls on access 2010 is found to be different from that in access 2003 and the resize class in sample db duly caters for this peculiarity. > (b) It is observed that use of Times New Roman font provides most pleasing results. > > Best wishes, > A.D. Tejpal > ------------ > > ----- Original Message ----- > From: cjlabs > To: Access Developers > Sent: Tuesday, May 17, 2011 02:01 > Subject: [AccessD] another tab control issue > > > I have a form with a tab control that has worked normally for years in Access 2000, 2002, 2003, and 2007. It has text boxes, list boxes, labels and buttons on each of the 7 pages on the tab control. > > I use ADH scaling code (been using it since Access97) and it has always worked fine. On this particular form in Access2010, the scaling is screwed up -- the tab control is much wider than it should be, so that the rest of the controls end up in the wrong location and some are off screen. I have other forms with similar tab controls in this database that scale correctly. > > If I do not use the scaling code, the form appears normal (just small). > > I've tried creating a new form and pasting the controls and code into it. I've tried decompilng. > > Has anyone seen this behavior? Any suggestions? > > TIA > Carolyn Johnson > St Louis MO > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com