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
> Confusion About First & Last Name Fields, Need To Enter A Person In Multiple Records, But Not Have Tblname Show Mult. Entries...explanation Inside., Access 2016    
post Apr 7 2020, 08:01 PM

Posts: 553
Joined: 27-September 05
From: Boston, MA

ok, so let's say I have a database for sports cards. I have:




CardID (primary Key)
NameID (foreign Key)
BrandID (foreign Key)
SportID (foreign Key)

I then have a query

CardID (from tblCard)
NameID (from tblCard)
BrandID (from tblCard)
SportID (from tblCard)
fldFirstName (from tblName)
fldLastName (from tblName)
fldBrand (from tblBrand)
fldSport (from tblSport)

I don't know if all that's neccesary, but anyway, now to get to the question....

I have a form,

frmCards (using QryCards as a datasource). I have combo-boxes that list the values from tblBrand and tblSport, and store the IDs for whatever I select in the query. But what's vexing me is the name fields. I have text boxes linked back to the query, one for fldFirstName and one for fldLastName. For each record, when I enter a first name and then last name it creates a new record in tblName. So if I enter Hank Aaron on 6 different records it records the name Hank Aaron 6 times in tblName. What am I doing wrong here? Do I need to use a subform for this? I'm using the form in data-sheet view to speed up the data entry and I don't know how a subform would impact that....

Go to the top of the page
post Apr 7 2020, 08:28 PM

UtterAccess VIP
Posts: 19,037
Joined: 29-September 03
From: Oklahoma City, Oklahoma

In a data entry for you only use a single combo box to select a single NameID..

The row source for the combo bix will include both the first and last name.

The Combobox row source might look like this:

For: "Last Name, First Name"

Select tblName.NameID, tblName.fldLastName & "," tblName.fldFirstName as  FullNameLF & from tblName Order BY  tblName.fldLastName , tblName.fldFirstName;

other combobox properties:
bound column: 1
columns: 2
column withs: 0"; 2"

You can also use the "First Last" order i8s yu prefer.

Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
post Apr 7 2020, 08:43 PM

Posts: 553
Joined: 27-September 05
From: Boston, MA

I don't have a pre-populated list of names though, do I have to enter them manually into tblNames first? Is that the only way?
Go to the top of the page
post Apr 7 2020, 08:45 PM

Posts: 3,704
Joined: 27-February 09

You'd have to get them in there somehow... Can you import them from somewhere else?
Go to the top of the page
post Apr 7 2020, 08:48 PM

Posts: 553
Joined: 27-September 05
From: Boston, MA

I was going to enter them by using the form as I went along, thus entering them into the table underlying the query on which the form is based...maybe it's just not possible.
Go to the top of the page
post Apr 7 2020, 08:59 PM

UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV

If the query for the form reads the names table in addition to the cards table try taking the names table out.

Robert Crouser
Go to the top of the page
post Apr 8 2020, 08:30 AM

UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA

You need to have a FORM for entering the names into the names table. It will be bound to that one table. It will have a field for FirstName and a field for LastName. That's the way -- and the only viable way -- to add new names. Don't try to add names as part of a different process.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Apr 8 2020, 08:42 AM

UtterAccess VIP
Posts: 13,911
Joined: 6-June 05
From: Dunbar,Scotland

I would suggest you have a Main Form based on tblName

With a subform based on tblCard

You would only then need to use a Comboox in the Header of the Main Form to select the Persons name and then record whatever details you need about the Cards

Hope this helps?


Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
post Apr 8 2020, 09:22 AM

Posts: 553
Joined: 27-September 05
From: Boston, MA

Thank you very much!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    25th May 2020 - 09:54 PM