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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobrossi56

ASKER

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