If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query or filtered recordset as Record Source
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? |
#2
|
|||
|
|||
Query or filtered recordset as Record Source
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" wrote in message ... 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? |
#3
|
|||
|
|||
Query or filtered recordset as Record Source
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. "Jeff Boyce" wrote in message ... 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" wrote in message ... 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? |
#4
|
|||
|
|||
Query or filtered recordset as Record Source
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 "BruceM" wrote in message ... 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. "Jeff Boyce" wrote in message ... 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|