VBA in Access Question

[Supernova9]

Ok, I want to create a function, called CountRows. (It's going to work like mysql_num_rows, but VBA has nothing to do that ) I'm new to working in VBA, so I can conceptualise things, it's just syntax/getting it into what VBA wants is the difficulty.

The function will be called with strPurchaseOrderRef, a string variable that contains an order number.

I want the function to take that variable, go to the table in my database called tblHWPurchasing, and count how many records have that order number in the field called MasterOrderRef. Then pass back an integer value equalling the number of times that row occurs.

i'm guessing it would involve an SQL count query, but I can't seem to make it work.

Can anyone help?

[Caesar2]

The SQL you need:

SELECT COUNT(*) FROM tblHWPurchasing WHERE MasterOrderRef = ordernumber

The function:

Function strPurchaseOrderRef(Ordernumber As Integer) As Integer
  Dim rs As ADODB.Recordset
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  rs.Open "SELECT COUNT(*) FROM tblHWPurchasing WHERE MasterOrderRef = " & Ordernumber, conn, adOpenForwardOnly, adLockReadOnly
  strPurchaseOrderRef = rs(0)
  rs.Close
  conn.Close
  Set rs = Nothing
  Set conn = Nothing
End Function

Don't forget to add a reference to your ADO library (assuming you want to use ADO). Tools --> References, check Microsoft ActiveX Data Objects 2.x Library

[Supernova9]

Ok, that still doesn't work, the debugger takes me to that rs.Open line, and tells me I'm missing a required parameter.

I've got the exact line you specified, so what am I missing? Can anyone else help?

[Structural Integrity]

Originally Posted by Supernova9

I've got the exact line you specified

The Ado.Connection object as second parameter of open. Or was it the first? Either way, you need to give the connection object.

[Caesar2]

You need at least a Source and an ActiveConnection as parameter.
Paste your code if it still doesn't work