How do i make a field on a form run a query?

[TOMMY.MYERS668]

I'm writing a simple database that will keep track of all the schools that my unit teaches. I have a field [ISR] (meaning instructor student ratio) I have a table created hith all of our courses and the ISR for each course. When the EU selects the dropdown to enter a course i would like the isr field to be auto filled with the ratio from the table.

example of isr table:

mos ratio

92y10 12
92y30 12
92g10 15
92s30 6

this is a small excerpt from the table and the ISR will remain constant over time but different per the mos .. I have the dropdown field on the form named [COURSE]



I currently have a querry wrote : select ratio from [ISR TABLE] where mos = [COURSE]; and that seems to work for the querry but i dont know how to make the field [ISR] run it.

[TOMMY.MYERS668]

I have alo tried putting this in the countrol source for ISR --SELECT [ISR TABLE].[RATIO] FROM [ISR TABLE] WHERE [ISR TABLE].[MOS]=COURSE; sorry everything in the army is in capps . this just gets me a ###### error in the isr field or #name?

[ketbdnetbp]

TOMMY.MYERS668 -

Instead of a query, you could try something like…
'AfterUpdate event code
'Check to see if course was selected
If Not IsNull(Me![Course]) then
'set up variable for ratio
Dim lngRat as Long
'place ratio value into variable
lngRat = DLookup("[Ratio]","[ISR Table]","[MOS]='" & Me![Course] & "'")
'set value of isr on form
Me![ISR] = lngRat
Else
'if course is null do something like…
Exit sub
End If

In the After Update event of the [Course] control on the form. Please note: the above assumes that [Course] is the same value as [MOS] and it is the bound column of the [Course] control on the form. Otherwise, you may need to reference a specific column() or change the criteria syntax.

Hope this gets you started and, thanks for your service,

Jim

[TOMMY.MYERS668]

Copied that into the after update and it works perfectly... thank you so much. Also I'm unfamilliar with this me! command ... Is that what allows xfer of variables from access to vba? btw thnx for the comment. Proud to Serve! Hooah!

[ketbdnetbp]

TOMMY.MYERS668 -

In VBA, Me is a property that refers to the Form or Report in which the current code is running. It is faster than using a fully qualified object reference, such as: [Forms]![NameofForm]![NameofControl]. It differs from Screen.ActiveForm which refers to the form that has the focus. If possible, you may want to download, Microsoft Office Access 2007/2010 Visual Basic Reference, as it contains a lot of useful information. It should be available from the Microsoft website. If you can't find it, send me a PM, and I'll look around for a link.

All the best,

Jim

[TOMMY.MYERS668]

Your an allstar! thanks boss!