show a value from another table in a form (1 Viewer)

P

Programming

Registered User.

Local time

Today, 07:03

Joined

Jul 5, 2018

Messages

20

• Sep 14, 2018

• #1

I have two tables
Students table where it has StudentName and StudentID
Course table where it has Student ID - CourseName -CourseID
The field pf StudentID in the course table, I made it lookup field to show the StudentName instead of the StudentID.

In the form of courses however, it shows the StudentName if the field of type combobox, If I change it to text box it shows the StudentsID.

How can I still show the student name in the form of courses ,even though ,I did not store studentName in the course table.

I did it before , but I forgot how.
Thank you.

 

June7

AWF VIP

Local time

Today, 06:03

Joined

Mar 9, 2014

Messages

5,470

• Sep 14, 2018

• #2

Why do you change to textbox? Use combobox. Otherwise options are to include Students table in form RecordSource joined to Course table or use DLookup function.

 

P

Programming

Registered User.

Local time

Today, 07:03

Joined

Jul 5, 2018

Messages

20

• Sep 14, 2018

• #3

Because I want data to be shown in textbox. I don't want the user to select or choose anything

 

isladogs

MVP / VIP

Local time

Today, 15:03

Joined

Jan 14, 2017

Messages

18,216

• Sep 14, 2018

• #4

Get rid of the lookup field in the Courses table. It's not needed and can be confusing

Create a query joining both tables using StudentID.
Use that as the record source for your form
Your combo should have three columns - StudentID (bound /hidden), LastName, FirstName

If instead you want a textbox, use e.g =LastName & ", " & FirstName as it's control source

 

P

Programming

Registered User.

Local time

Today, 07:03

Joined

Jul 5, 2018

Messages

20

• Sep 14, 2018

• #5

but I do not want to show it in combo , I want to show it in text box

 

isladogs

MVP / VIP

Local time

Today, 15:03

Joined

Jan 14, 2017

Messages

18,216

• Sep 14, 2018

• #6

See my previous reply

 

missinglinq

AWF VIP

Local time

Today, 10:03

Joined

Jun 20, 2003

Messages

6,423

• Sep 14, 2018

• #7

Programming said:

Because I want data to be shown in textbox. I don't want the user to select or choose anything

Click to expand...

Then how do you expect the student name to get there...if you "don't want the user to select or choose anything?"

Linq ;0)>

 

jdraw

Super Moderator

Staff member

Local time

Today, 10:03

Joined

Jan 23, 2006

Messages

15,379

• Sep 14, 2018

• #8

@Programming,

I think you are confusing WHAT and HOW. You and readers need a clear description of the requirement in simple English. This is the WHAT (I'm trying to accomplish). With that clear requirement, readers will offer alternatives/options as to HOW that might be achieved with MS Access.
As always, clarity in communication is key.

--Avoid lookups at table field level as Ridders advised.