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
> Combobox Needs To Populate Form Based Upon Two Table Fieldss, Access 2010    
 
   
parishpete
post Feb 17 2017, 10:46 AM
Post#1



Posts: 38
Joined: 12-October 08



I think I must have my stupid head on. I know I've done this before but it eludes me this time.

My form has a table as it's data source and allows the user to create a new record or find and edit an existing record.

The form's data source property is:

SELECT [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS, [current_tickets].PERMNO
FROM [current_tickets]
ORDER BY [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS;

PERMNO is the PK

My Combo box has multi fields and the row source is:

SELECT [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS, [current_tickets].PERMNO
FROM [current_tickets]
ORDER BY [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS;

By virtue of the combo box wizard the combox AfterUpdate property is:

First record where condition ="[NAME2] = " & "'" & [Screen].[ActiveControl] & "'"

It works almost fine. The user can flick through all the table records via next/last buttons or can select a record by surname (NAME2). they can also type the first few letters of the surname into the combo box and it will find a name (which is what they primarily want to do.

Where it falls down is of course with duplicate surnames as quite rightly it's only finding the first record with that surname no matter what record with that surname, is actually selected.

The obvious solution is to have the PK as the bound column but then they can't type a name into the combo box because it expects the PK.

i.e. if the user types in "brow", all the Browns show up as required. when they click on the right Brown, that entry needs to be on the form, not the first Brown.

I'd be grateful if someone can tell me what should be staring me in the face!
many thanks
Pete
Go to the top of the page
 
theDBguy
post Feb 17 2017, 11:03 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,228
Joined: 19-June 07
From: SunnySandyEggo


Hi Pete,

I think you answered your own question with this: "The obvious solution is to have the PK as the bound column but then they can't type a name into the combo box because it expects the PK."

If you used the Wizard to create this combobox, then chances are it is "unbound." If so, then go back to design view and set the Bound Column property to 4.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2017, 11:35 AM
Post#3


UA Admin
Posts: 31,215
Joined: 20-June 02
From: Newcastle, WA


Perhaps it would help to step back and think about what your data is like, and how Access works, and how best to work with them.

First, as you have found, last name of people is a very unreliable way to differentiate between two or more people. Even in combination with first name, there are going to be duplicates at some point. That's why we usually use a surrogate Primary Key, i.e. an AutoNumber which should be unique across all records in a table. I say should be because there is always the chance of corruption--although that's beyond the scope of our current focus. Your Primary Key, PERMNO, may be an AutoNumber, or it may be a unique value you generate, or it may be a real world unique value. The point is that it must be unique to serve as a PK, and your code should work with that PK whenever you are trying to identify one specific record.

The combo box has a bound column and can have multiple additional columns for supporting values. By default, the bound column is the first one on the left. Because the columns in a combo box are indexed from left to right, starting with 0, the normal situation is to have Column(0) be the bound column. That can be changed as theDBguy pointed out.

If it were me, this is how I would create the row source for this combo box.

SQL
SELECT [current_tickets].PERMNO, [current_tickets].NAME2 & " " & [current_tickets].NAME1 & " ( " & [current_tickets].INITIALS & ")"As PersonName
FROM [current_tickets] ORDER BY [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS;


In other words, to ensure that both names and the initials always appear in the combo box, I would concatenate them into a single column for display. It'll probably be easier to distinguish one name from another that way. You don't have to do that, but I find it helps to see what was selected after the combo box loses focus.

Assuming that PERMNO is an AutoNumber, the column Widths for this combo box should be "0, 2", where the bound column, column(0) is set to 0 inches width. It is there and it contains the PK value, but you can't see it. Instead you see the second column, column(1) and can match it as you describe.

On the other hand, if PERMNO is a unique, real-world value, and you want users to see it, then you would do this differently.

SQL
SELECT [current_tickets].PERMNO, [current_tickets].NAME2 & " " & [current_tickets].NAME1 & " ( " & [current_tickets].INITIALS & ")"As PersonName , [current_tickets].PERMNO
FROM [current_tickets] ORDER BY [current_tickets].NAME2, [current_tickets].NAME1, [current_tickets].INITIALS;


And in this case, the column widths could be 0, 2,1, so that the bound column is hidden, and the same field is displayed in the second VISIBLE column within the combo box.

Remember, when working with records in tables, it is usually much better to work with Primary and Foreign Keys than with text value (or attribute) fields in the same record.

.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2017, 11:47 AM
Post#4


UA Admin
Posts: 31,215
Joined: 20-June 02
From: Newcastle, WA


Coincidentally, Today's Tip Of The Day discusses this in more detail, with screenshots.

I have it set up on a rotating schedule, so this one will be available today only, and then reappear sometime next month, if you're interested.

--------------------
Go to the top of the page
 
parishpete
post Feb 17 2017, 12:33 PM
Post#5



Posts: 38
Joined: 12-October 08



Thank you both for the speedy replies. I appreciate your help very much.

you'll be pleased to know that PERMNO is an autonumber and I did have concantenated name set up and hiding in the wings.

I've altered my combobox row source SQL as per your first example (for autonumber) so that PERMNO is the value which the combobox stores and the bound field is set to 1

Only the concantenated name appears nand by use of an adhoc text box I know PERMNO is correctly stored.

When I made the change, after CB selection, the form remained at the first record. I assumed this was because of the form AfterUpdate proerty still set at:

First record where condition ="[NAME2] = " & "'" & [Screen].[ActiveControl] & "'"

I changed this to:

First record where condition ="[PERMNO] = " & "'" & [Screen].[ActiveControl] & "'"

but it made no difference. Have I missed something further?

The combo box has no OnClick property set

Regards
and again, thanks
Pete
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2017, 12:42 PM
Post#6


UA Admin
Posts: 31,215
Joined: 20-June 02
From: Newcastle, WA


Please post the complete code from the After Update event of the combo box.

--------------------
Go to the top of the page
 
RAZMaddaz
post Feb 17 2017, 01:09 PM
Post#7


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


George, et. al.,

What about a Me.Requery of the Form and a Me.SecondComboBoxName.Requery, with an "On Change" of the First Combo Box?

Pete,

Here is an example of what sounds like what you are trying to accomplish.

RAZMaddaz
Attached File(s)
Attached File  AlphaFilter82007.zip ( 40.61K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2017, 01:11 PM
Post#8


UA Admin
Posts: 31,215
Joined: 20-June 02
From: Newcastle, WA


That's probably the end game, but so far we have less than a complete picture of the VBA, so there's that lingering question.

--------------------
Go to the top of the page
 
parishpete
post Feb 17 2017, 01:12 PM
Post#9



Posts: 38
Joined: 12-October 08



Sorry,

I put FORM AfterUpdate in my last post.

It was of course the the combo box's AU property

It's an embedded macro with no object type or name shown

It originally came from the combo box wizard and just says:

Record: First

Where Condition = ="[PERMNO] = " & "'" & [Screen].[ActiveControl] & "'"

Originally it was set to NAME2 not PERMNO

kind regards
Pete

POST SCRIPT

I realised my error on the after update and changed the condition to:

="[PERMNO] = " & Str(Nz([Screen].[ActiveControl],0)

I forgot I had changed from text to number

I did say I had my silly head on!!

Thank you all for you help,
it now works perfectly

best wishes
Pete
Go to the top of the page
 
parishpete
post Feb 17 2017, 02:00 PM
Post#10



Posts: 38
Joined: 12-October 08



Thank you for your help and example.
The other suggestions were right too
I was just my stupidity not seeing what I was staring at!!
drop down box completely sorted now
memory of how I did it years ago is starting to drift back

many thanks
Go to the top of the page
 
theDBguy
post Feb 17 2017, 02:22 PM
Post#11


Access Wiki and Forums Moderator
Posts: 71,228
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! 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
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 07:49 PM