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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Populate Combobox With Value From Table For Editing, Access 2016    
 
   
adrianscotter
post May 14 2019, 02:25 PM
Post#1



Posts: 32
Joined: 24-February 13
From: Norwich, UK or Lehigh Acres, FL


Hi guys, I'm banging my head on my desk with this, my best buds Google and UtterAccess uarulez2.gif have been fruitless with my search results pullhair.gif What I'm trying to do it pre-populate a combobox with an existing value that may or may not exist in a table that I'm looking up the values from. This combobox already has a field list as a source.

This is exactly what I'm doing: To edit a next of kin contact for an employee, I'm looking up the employee record with their EmployeeID. Because the end users are too stupid (did I really say that!) to use correct capitalisation, when it comes to typing stuff like 'Jimmy MacTavish" The put all kinds of stuff in like 'jimmy mactavish' and 'Jimmy Mactavish' and 'jimmy macTavish' etc., etc. To normalise the data I use vbProperCase on each text box but I do not allow them to type Mac or Mc or Le or D' or O', they have to pick them from a list via said combobox. My problem arises during editing, I cannot pre-populate the combobox with the relevant last name prefix. Now I'm sure I've done this before but can I find out where or how I did it? No. Is anyone able to help prod me in the right direction for this please?
This post has been edited by adrianscotter: May 14 2019, 02:26 PM

--------------------
Jack of all trades, master of none!
Go to the top of the page
 
theDBguy
post May 14 2019, 02:35 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Hi. Sounds to me like you could avoid all these headaches by simply using the ID as the foreign key to the next of kin table rather than let the user enter names.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post May 14 2019, 03:11 PM
Post#3


UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA


Hi, one of the HUGE advantages of using combo boxes is that they allow you to restrict values to those already stored in the table to which they are bound. This is what might be called a "Closed Domain". All of the possible values are already stored in the lookup table. Adding new values to that Domain means someone in an administrative capacity must go to that table (separately) and add the new value. More common, and perhaps more easily recognized closed domains would be a lookup table of Weekday Names. There are 7, no more and no less. And they don't vary from time to time. Within a geographical context, a Lookup table of States in the United States would include 51 records, no more and no less, except if you also need to account for US Territories. The business rules for your organization would decide that.

In this case, the domain of interest is the table from which employee names is returned. Only existing employee names are in that table (or perhaps existing and former employees), and that means the combo box can only list those names and no others.

Typically, with a combo box, the properties of interest here are these:

Attached File  comboboxproperties.jpg ( 117.85K )Number of downloads: 2


Limit to List means that users can only select items that are already in the underlying Lookup table.

Auto Expand means that as users type in what they expect to be a valid value, the combo box tries to find and select that value.

What that means is if they mistype a name, as in your example, they'll get an error like this:

Attached File  notinlisterror.jpg ( 52.78K )Number of downloads: 0


Now, you can add feedback, and allow users to add new values if you want.

In this case, it wouldn't matter how they capitalize any part of any of the names, Access is not case sensitive by default. So as long as the names match alphabetically, it'll be fine.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post May 14 2019, 04:40 PM
Post#4


UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA


It also sounds like you might have both FirstName and LastName in a single column. Is that the way the data is stored? If so, we ought to stop and address that problem if we can.


And one other point, it also sounds like you might be trying EDIT names in the combo box? Or is that a misreading: "My problem arises during editing, I cannot pre-populate the combobox with the relevant last name prefix. "

Are you trying to append these "prefixes" as selected in a combo box to the value in a text box or something?

And finally, Normalization doesn't refer to proper capitalization, although I can see where it might be related, in a way. You want to make sure that names incorporate the appropriate formatting, e.g. MacIntosh, not Macintosh. Perhaps a more reliable approach is available, if that is the point of the procedure here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
adrianscotter
post May 15 2019, 02:11 PM
Post#5



Posts: 32
Joined: 24-February 13
From: Norwich, UK or Lehigh Acres, FL


Thanks for the input guys, I have cured the issue by having a 'Change' button that pops a little form for them to pick it again, that way I can display the name properly. Just for information, there are three fields associated with a next of kin name. FirstName, LNamePrefix (such as Mac or O' or De' etc.) and LastName. The combobox is populated by ListValue, not from a table and when editing the NOK details, it's all done on lookups, I never let this particular lot do anything that gives them access to table information, it's all held on the form and updated by VBA. I do it that way to ensure the data stays normalised. Of a company of 22 employees, 16 of them are dyslexic! Perhaps now you'll forgive my question and know why I drink as I do!!! guiness.gif

--------------------
Jack of all trades, master of none!
Go to the top of the page
 
theDBguy
post May 15 2019, 03:58 PM
Post#6


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post May 16 2019, 08:23 AM
Post#7


UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA


Thanks for the details. I have one word of advice. Over the years, I've faced a fair number of frustrating situations with users. Most are very tolerant of things that might drive me mad. One time I was sitting at a user's desk, watching him work with our Access database application. An error message popped up. He clicked "OK" and moved on before I could even read it. I almost panicked. He told me it happens every time on that screen and they'd all learned to click "OK" and ignore it. WHAT? No report to the developer? No complaint? Why not say something? The answer was it doesn't cause a problem, so why bother.

I've also had complaints about built-in Access features. The most recent was an upgrade from Access 2003 to 2013. In 2013, we have the little Date Picker attached to date controls on forms. That wasn't a "feature" in 2003. The user saw it during acceptance testing and complained that it would interfere! So, it works both ways.

My point is that, IMO, the best way to get on with the task is to try to establish rapport with users and communicate with them - a lot. Sometimes we learn things we could be doing better ourselves.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th May 2019 - 07:31 PM