record source

[ShostyFan]

I have a form and I want the record source of a dropdown box to change based on a criteria.



as an OnOpen, I have:
If Forms![frmMainMenu]![RepairAccess] = "Yes" Then
Me.TransactionID.RecordSource = "Qry_Restricted_TransactionType"
Else
Me.TransactionID.RecordSource = "Qry_All_TransactionTypes"
End If

I have also tried as a BeforeUpdate event on the dropdown box field itelf:
If Forms![frmMainMenu]![RepairAccess] = "Yes" Then
Me.RecordSource = "Qry_Restricted_TransactionType"
Else
Me.RecordSource = "Qry_All_TransactionTypes"
End If


Each version creates errors and I have resorted to guess and check. Any help will be apreciated.

[June7]

A dropdown box (combobox ?) has a RowSource not RecordSource.

Forms and Reports have RecordSource.

[Gicu]

If the RepairAccess is a Yes\No field then you need to check for True or -1, not "Yes".
Now, about the form, is it a continuous or datasheet one or a single form? If the later then you'll be fine but with continuous form all your records will change with the current one (the code should be in the Current event of the form). To achieve what you want in a continuous form is a bit more complicated, you need code in both the Enter and Exit events (or the GotFocus\LostFocus pair) to change the source from all to restricted on enter and back to all on exit so all records display properly.

Cheers,

[ShostyFan]

Perfect.
The form is a Single form so putting the code into the OnCurrent event worked perfectly.
Thank you.