How to let my combo list selection appear in full in my table

[Emmanuel]

I have a table with the name : INCOME_TABLE
with fields: FULL NAME, DATE, TYPE OF INCOME/EXPENSE,REVENUE.



I have another table with the name :MEMBERS with fields: FIRST_NAME, LAST_NAME, GENDERS....

I managed to create a combo on a new form which picks the first and last name displayed in the members table and the results to be displayed together in the FULL NAME field of INCOME_TABLE.

But my issue is, when I select the drop down, both first name and last name appears in the drop down as expected but when you select one of the names, only the First name displays in the combo text box and a such, only the first name appears in the FULL NAME section of the INCOME_TABLE instead of both FIRST and LAST NAME.

Any help on this will be greatly appreciated.

[Minty]

Your Members Table should have a MemberID Primary key field, and then you should simply store that in your income table instead of the Full_Name field.

You can simply look up the name and display it correctly in a combo on the form used for the Income records.

Make the combo row source =

SELECT MemberID, FirstName & " " & LastName as DisplayName From Members

Then hide the MemberID by setting the column width for the first column to 0

[Emmanuel]

Am able to let is display correctly when you drop down the list. But when you select one of the names, it only displays the first name only.
And a such, only the First name too appears in the full name section of the income table

[Welshgasman]

PMFJI, but you should be binding the ID of the member to the income table not the actual name, whether Forename, Surname or both?

[Emmanuel]

And how do I do the binding?

[Welshgasman]

Set the bound column to the correct index.? It is on the data tab. You will need to change you income table though, to hold the foreign key and NOT the name.?

I always have the ID field first (so 1), and always hide that as Minty suggested. You show what you like, but the ID field is stored, and then you get the other info from that when needed.

HTH

Edit: I can see if you follow my advice though, you will need to change your table Member as well as you do not have an autonumber ID field in there.?

Best to get your tables setup correctly first, before playing with forms?

[Emmanuel]

Am still lost with this
Am not so much an expert with this
Would be glad if you could break it down to my understanding

[Emmanuel]

I have an auto number ID in the member table

[Minty]

Can you post up a stripped down version of your database? (Compact and repair it then zipp it once you have a smaller version)
Change the names to fictitious ones, we would only need a handful of records.

As Gasman has explained you need to get your tables set up correctly first, before messing around with forms that try and cover up a poor underlying design.

[Welshgasman]

OK, that is good.

As Minty showed (and I do it this way as I mentioned) that sql code for the combo will display First and Last name, but if you bind Column1 in the properties and that control source is IncomeTable.MemberIDFK (to indicate a foreign key from member table and not to have two names the same) then ACcess will automatically populate that with the MemberID.

Then when you need First & Last names you would look them up from the Member table. Then if Ms Smith becomes Mrs Jones, the relationships are still intact.

Search here and elsewhere for 'normalization'

People post links all the time about this and it is the key to a decent functional database.

[Emmanuel]

This is a sample of the database. Just an extract

[Minty]

Have a look at this and see what has been changed and what is being displayed and stored.

[Emmanuel]

Alright
Let me check and get back to you

[Emmanuel]

Your work didn’t actually give me what I needed. But it gave me an idea which I tried and it produced the results I needed
Thank you Sir

[Minty]

As long as you aren't actually storing the full name in the Income table, doing that will lead you down a difficult path in the long run.

Good luck with your project.