Avatar of bobrossi56
bobrossi56
 asked on

Access table not showing correct column

First let me say I am at best an Access "Hacker" and I already know this database does not use all the correct syntax and naming conventions. Over the years, thanks to you folks, I have gotten better, but I do not want to do this database over again.

I am trying to modify an existing Access database used to assign horses to riding students. An expert helped me a short while ago to make a few changes. I applied them to the database and they work great, but in the process I am getting incorrect data in my tblHorseAssign.

If you open the attached database and open tblHorseAssign and look at the top row (ID 61) you will see that in the ReportedBy column the
ID (7) is now appearing rather than the instructor name, as it did before my mods to this tblInstructorName, where I just added an ID field.
Not sure how to have the name appear in the table vs. the ID.

Same goes for LessonType column. Again, I added an ID field to the tblLessonType and now the ID is showing (10) instead of the LessonTypeDesc.

Thanks Experts...
-BobR
Horse-Scheduling.mdb
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
bobrossi56

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bobrossi56

ASKER
I assume you are referring to the combobox on the form. I did that and now when I try to fill out the form it errors.
Rey Obrero (Capricorn1)

copy this  and replace your codes for CalcCharge
Sub CalcCharge()
    
    If Not IsNull(Me.ReportedBy) And Not IsNull(Me.LessonType) Then
        Me.Text119 = DLookup("LessonRate", "tblInstructorRates", "LessonTypeID = " & Me.LessonType & " AND InstructorID = " & Me.ReportedBy.Column(0))
    End If
    
    If Not IsNull(Me.ReportedBy) And Not IsNull(Me.LessonType) Then
        Me.Paid = DLookup("StudentOwes", "tblInstructorRates", "LessonTypeID = " & Me.LessonType & " AND InstructorID = " & Me.ReportedBy.Column(0))
    End If
    
End Sub

Open in new window

bobrossi56

ASKER
OK, that worked but now my LessonType is still showing the ID not he name. So I changed that to bound column 2 and I get an error in the code you sent me above.
thx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bobrossi56

ASKER
Works GREAT!!!! Thanks Rey....much appreciated.