Query or filtered recordset as Record Source

[BruceM]

I have a vendor database (Access 2000) in which some of the vendors are
categorized as Approved. A Boolean field (Approved) in the Vendor table
defines this. If I am building an Approved Vendor form, I can either base
it on a query that uses True as the criteria for the Approved field (i.e.
the query contains only Approved vendors), or I can use something like this
in the form's Load event (I don't know if it is the correct event, but it
works):
Me.RecordSource = "SELECT * FROM tblVendor WHERE Approved = True ORDER BY
VendorName"
In the second case the main table (all vendors) is the form's record source.
Perhaps if I used a different event to define the RecordSource I wouldn't
need to name the form's Record Source on the property sheet, but I don't
really know how that works. I do know that I like to see the Record Source
on the property sheet rather than hunting through the code for it.
Also, the form's property sheet has an Order By and Filter property, but I
find these somewhat troublesome. I can't figure out how to use the Filter
property, and the form seems to drop the Order By property from time to
time, especially when switching from Form view to Design view.
I can also use the SELECT statement as the form's Record Source.
Is one approach preferable to the other, or this another case of Microsoft
providing a lot of different ways to do the same thing?

 

[Jeff Boyce]

Bruce

I'll offer another possible approach...

Either way you describe, your form will be "loaded" with all the records in
your tblVendor that meet the selection criteria.

You could also create a query that selects on the [Approved]=True field, but
also selects on the value of a combo box on the form. This combo box lists
Vendors (?by name?), but only has VendorID and VendorName (plus whatever
other field/s you'd need to break ties). It is NOT bound.

Your new query looks to the form's combo box when the form opens, sees
nothing in the combo box, and loads the form without ANY records behind it.
You then select a vendor from the combo box and tab/enter. You will need to
add the following code to the AfterUpdate event of the combo box:

Me.Requery

This causes the form to re-run the query that loads records. This time,
since you selected a vendor, there's a vendorID for the query to use, so the
form loads the (single) vendor record.

Your query needs to point to this form's combo box, with something like:

Forms!YourFormName!YourComboBoxName

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Jeff,

Thanks for your reply. Sorry I didn't reply sooner. I forgot to flag the
message, and then I got busy with some other things.

I already have a search combo box as you describe. In this case I think it
would be best to load the 125 records or so, since in many cases updating is
done on a number of records, so the ability to scroll through the records is
helpful. However, there are other situations in other projects where it is
often necessary to select only a single record, or very few records, after
which there is no further need for the form, so I would like to make sure I
understand what you describe. I think you are saying that the combo box
value would be a criteria for VendorName in the query:
Forms!frmApprovedVendor!cboVendorName
By the way, vendor names are unique, although they are not used as the PK
field. If they are not naturally unique we would contrive to make them so,
although that hasn't come up. I could use cboVendorName.Column(0) or
something if needed (i.e. the numeric PK field instead of the visible
column). I suppose that if there are multiple options (i.e. non-unique
values) then all that match would show up (which could happen with a
database of names, for instance).
Thanks for pointing out this approach as an option. I have used something
like you describe to filter records after the entire recordset has been
loaded (searching for all vendors in a particular city, or whatever), but it
had not occurred to me that it would be practical in some cases to open the
form without records.

Bruce

I'll offer another possible approach...

Either way you describe, your form will be "loaded" with all the records
in your tblVendor that meet the selection criteria.

You could also create a query that selects on the [Approved]=True field,
but also selects on the value of a combo box on the form. This combo box
lists Vendors (?by name?), but only has VendorID and VendorName (plus
whatever other field/s you'd need to break ties). It is NOT bound.

Your new query looks to the form's combo box when the form opens, sees
nothing in the combo box, and loads the form without ANY records behind
it. You then select a vendor from the combo box and tab/enter. You will
need to add the following code to the AfterUpdate event of the combo box:

Me.Requery

This causes the form to re-run the query that loads records. This time,
since you selected a vendor, there's a vendorID for the query to use, so
the form loads the (single) vendor record.

Your query needs to point to this form's combo box, with something like:

Forms!YourFormName!YourComboBoxName

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a vendor database (Access 2000) in which some of the vendors are
categorized as Approved. A Boolean field (Approved) in the Vendor table
defines this. If I am building an Approved Vendor form, I can either base
it on a query that uses True as the criteria for the Approved field (i.e.
the query contains only Approved vendors), or I can use something like
this in the form's Load event (I don't know if it is the correct event,
but it works):
Me.RecordSource = "SELECT * FROM tblVendor WHERE Approved = True ORDER BY
VendorName"
In the second case the main table (all vendors) is the form's record
source. Perhaps if I used a different event to define the RecordSource I
wouldn't need to name the form's Record Source on the property sheet, but
I don't really know how that works. I do know that I like to see the
Record Source on the property sheet rather than hunting through the code
for it.
Also, the form's property sheet has an Order By and Filter property, but
I find these somewhat troublesome. I can't figure out how to use the
Filter property, and the form seems to drop the Order By property from
time to time, especially when switching from Form view to Design view.
I can also use the SELECT statement as the form's Record Source.
Is one approach preferable to the other, or this another case of
Microsoft providing a lot of different ways to do the same thing?

 

[Jeff Boyce]

Bruce

I'd probably not use a VendorName as a criterion if I had a VendorID# field
that WAS my primary key. Aside from that, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your reply. Sorry I didn't reply sooner. I forgot to flag the
message, and then I got busy with some other things.

I already have a search combo box as you describe. In this case I think
it would be best to load the 125 records or so, since in many cases
updating is done on a number of records, so the ability to scroll through
the records is helpful. However, there are other situations in other
projects where it is often necessary to select only a single record, or
very few records, after which there is no further need for the form, so I
would like to make sure I understand what you describe. I think you are
saying that the combo box value would be a criteria for VendorName in the
query:
Forms!frmApprovedVendor!cboVendorName
By the way, vendor names are unique, although they are not used as the PK
field. If they are not naturally unique we would contrive to make them
so, although that hasn't come up. I could use cboVendorName.Column(0) or
something if needed (i.e. the numeric PK field instead of the visible
column). I suppose that if there are multiple options (i.e. non-unique
values) then all that match would show up (which could happen with a
database of names, for instance).
Thanks for pointing out this approach as an option. I have used something
like you describe to filter records after the entire recordset has been
loaded (searching for all vendors in a particular city, or whatever), but
it had not occurred to me that it would be practical in some cases to open
the form without records.

Bruce

I'll offer another possible approach...

Either way you describe, your form will be "loaded" with all the records
in your tblVendor that meet the selection criteria.

You could also create a query that selects on the [Approved]=True field,
but also selects on the value of a combo box on the form. This combo box
lists Vendors (?by name?), but only has VendorID and VendorName (plus
whatever other field/s you'd need to break ties). It is NOT bound.

Your new query looks to the form's combo box when the form opens, sees
nothing in the combo box, and loads the form without ANY records behind
it. You then select a vendor from the combo box and tab/enter. You will
need to add the following code to the AfterUpdate event of the combo box:

Me.Requery

This causes the form to re-run the query that loads records. This time,
since you selected a vendor, there's a vendorID for the query to use, so
the form loads the (single) vendor record.

Your query needs to point to this form's combo box, with something like:

Forms!YourFormName!YourComboBoxName

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a vendor database (Access 2000) in which some of the vendors are
categorized as Approved. A Boolean field (Approved) in the Vendor table
defines this. If I am building an Approved Vendor form, I can either
base it on a query that uses True as the criteria for the Approved field
(i.e. the query contains only Approved vendors), or I can use something
like this in the form's Load event (I don't know if it is the correct
event, but it works):
Me.RecordSource = "SELECT * FROM tblVendor WHERE Approved = True ORDER
BY VendorName"
In the second case the main table (all vendors) is the form's record
source. Perhaps if I used a different event to define the RecordSource I
wouldn't need to name the form's Record Source on the property sheet,
but I don't really know how that works. I do know that I like to see
the Record Source on the property sheet rather than hunting through the
code for it.
Also, the form's property sheet has an Order By and Filter property, but
I find these somewhat troublesome. I can't figure out how to use the
Filter property, and the form seems to drop the Order By property from
time to time, especially when switching from Form view to Design view.
I can also use the SELECT statement as the form's Record Source.
Is one approach preferable to the other, or this another case of
Microsoft providing a lot of different ways to do the same thing?