Determining howmany/which

  • Thread starter Thread starter Evan M
  • Start date Start date
E

Evan M

Hello, I am looking to generate a PO from an order form that has a subform
that contains the companyname of who we will be issuing the PO to. In most
cases we will not have a problem, but occasionally we will need to order
from multiple vendors. How is it that, in code, I can determine if there are
multiple vendors? I think i can do the rest, but I'm stuck at this point.
Thanks for any assistance!

Evan M
evan AT radiologyonesource DOT com
 
Depending on your sort order, if the first and last record both have the
same vendor, then there is only one vendor. Probably a better option though
would be to run a query to open a recordset on the subform's recordset. The
only field you will need returned is the vendor's id field and you would
tell the query to return only unique ones. The number of records in the
recordset would be then number of vendors.

Example:
Dim rst As DAO.Recordset, intNumberOfVendors As Integer
Set rst = CurrentDb.OpenRecordset("Select Distinct [VendorID] From tblOrders
Where [OrderID]=" & Me.txtOrderID, dbOpenSnapshot)
rst.MoveLast
intNumberOfVendors = rst.RecordCount
Debug.Print "Number of Vendors = " & intNumberOfVendors
rst.Close
Set rst = Nothing
 
Thanks Wayne! I'll give it a shot!

Evan Mc
evan AT radiologyonesource DOT com

Wayne Morgan said:
Depending on your sort order, if the first and last record both have the
same vendor, then there is only one vendor. Probably a better option
though would be to run a query to open a recordset on the subform's
recordset. The only field you will need returned is the vendor's id field
and you would tell the query to return only unique ones. The number of
records in the recordset would be then number of vendors.

Example:
Dim rst As DAO.Recordset, intNumberOfVendors As Integer
Set rst = CurrentDb.OpenRecordset("Select Distinct [VendorID] From
tblOrders Where [OrderID]=" & Me.txtOrderID, dbOpenSnapshot)
rst.MoveLast
intNumberOfVendors = rst.RecordCount
Debug.Print "Number of Vendors = " & intNumberOfVendors
rst.Close
Set rst = Nothing

--
Wayne Morgan
MS Access MVP


Evan M said:
Hello, I am looking to generate a PO from an order form that has a
subform that contains the companyname of who we will be issuing the PO
to. In most cases we will not have a problem, but occasionally we will
need to order from multiple vendors. How is it that, in code, I can
determine if there are multiple vendors? I think i can do the rest, but
I'm stuck at this point. Thanks for any assistance!

Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
 
Back
Top