Autofill using DLookup not working

[BBonexx]

Hello All,



I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. Please help me on this issue, the figures of my Access database are below.

[NTC]

in your FullList form, if the Frequency field is bound (to the field in the FullList table) then DLookup isn't the way to go. You could have an unbound field that does the look up with its record source being:
=DLookup("Frequency","Courses","Courses = " & Forms!FullList.Courses) - - this syntax is not correct in regard to the reference of the form field as I am typing off the cuff - you will want to look up the correct use of """ to wrap a form's field reference (I can't remember exactly).

But Dlookup may not really be the best way to do this as it will be doing look ups all the time. When you select the course, why not then fill the Frequency field? ...I am assuming your FullList 'course' field is a lookup field to the Courses table. If so, include the Frequency field in that query which is behind the field in the form. Then in the AfterUpdate you can push the frequency value into the frequency field on the form (which I presume is bound to the frequency field in the fullList table) with code: Me.FrequencyFieldName = Me.CourseFieldName.column(2)
(again that column value may not be correct - it may be 1)

Hope this helps a little.

[ssanfu]

It is a lot easier that you think.

In the row source for the "Course" combo box, add the "Frequency".
So the SQL would look like "SELECT ID, Course, Frequency FROM Courses"
In the after update event of the "Course" combo box, enter
Me.Frequency = Me.cboCourse.Column(2)

The columns are zero based, so the first column = 0, the second column = 1, ....
As soon as you select a course, the frequency will be pushed into the frequency text box. TaDa!!


If you have problems, post the SQL of the course combo box. Also, it would help if you would include actual table and control names .