Select Records in the Record Source for Form

  • Thread starter Thread starter Leonard Priestley
  • Start date Start date
L

Leonard Priestley

I have made a simple database of equipment and want to provide a combo box
that enables the user to see only equipment of a particular model. The
technique of using a recordset clone and a bookmark to find the model only
supplies some of the required records. I assume I need to modify the record
source of the form and I have tried to use the combo box to modify the query
the form is using, or to provide an alternative query. I can't get this to
work. Presumably you can use VBA code to define a recordset and requery the
form's record source, but I can't figure out how to do it. Can someone
point me in the right direction please?

Leonard
 
On the AfterUpdate event of the combo box add the code:
Me.RecordSource = "qry1"

Where "qry1" can either be the name of a new query or you
can tye the SQL statement directly in there:
Me.RecordSource = "SELECT Table1.Field1, Table1.Field2
FROM Table1 WHERE Table1.Field1='a'"

There is no need to requery the form, it will be done
automatically once you change the forms properties. I will
warn you that in a multiuser environment this will give
your users grief. Since you are changing the forms design
when they close the form it will want to know if its
design change should be saved and will ask the user with a
scarry message. You can force a no save on the close code
but you can't capture that if the user closes the form
with the top X of the form.

-Cameron Sutherland
 
Back
Top