How to count number of ROWs in qerry resoult with variables

[sebna]

Hi All,

I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.

I tried to find a solution on the forum but nothing seems to work so far but at the same time my skills are basic at best.

Hope you don't mind helping me out:

The current code - this is executed as on-click event when clicked on List Box feed with query below.

What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.

 Private Sub search_items_Click()


Me.OBSFullFilledOrdersHolder.Enabled = True

mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName  ,  ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]" & _
        " , orders.[no of copies] , orders.[special requirements] FROM ORDERS INNER JOIN STUDENTS ON ORDERS.[Student Id] = STUDENTS.[Student Id]" & _
        " WHERE ORDERS.[NBPC Code]= '" & Forms![FULLFILL ORDERS]![search items].Column(0) & "' AND ORDERS.Format = '" & Forms![FULLFILL ORDERS]![search items].Column(3) & "' " & _
        " and orders.[order completed] = false"

Me.students.RowSource = mysql

Me.students.Requery

Me.Location = Me.search_items.Column(4)

What I did so far based on what I have found on the forum is I tried DCount("*", "OBSTesttest")

 Dim nrecords As Long

nrecords = DCount("*", "OBSTesttest")

If nrecords = 1 Then
MsgBox "zero records.", vbInformation, "System Error"
End If

where OBSTestTest is this queery above saved as standalone querry under name OBSTestTest

 SELECT ORDERS.[order id], STUDENTS.[first name] & ' ' & students.[surname] AS Name, STUDENTS.[contact name] AS ContactName, ORDERS.[Online Bookshelf order] AS OBS, STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4], ORDERS.[no of copies], ORDERS.[special requirements]
FROM ORDERS INNER JOIN STUDENTS ON ORDERS.[Student Id] = STUDENTS.[Student Id]
WHERE (((ORDERS.[NBPC Code])=[Forms]![FULLFILL ORDERS]![search items].[Column(0)]) AND ((ORDERS.Format)=[Forms]![FULLFILL ORDERS]![search items].[Column(3)]) AND ((ORDERS.[order completed])=False));

The querry itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error '[Forms]![FULLFILL ORDERS]![search items].[Column(0)]'.

Thank for all the help.

 

[pr2-eugin]

Hello sebna, Welcome to AWF..

Forget about saving the Query, using VBA there is a method called RecordCount, have you tried using it? Allen Browne explains about Working with Recordsets in simple terms..

 

[TJPoorman]

I am assuming that the SQL statement is to set the rowsource of a combobox or listbox. If this is the case, the code you are looking for is cbo*****.ListCount. Where the cbo***** is the name of your combobox or listbox.

 

[sebna]

I am assuming that the SQL statement is to set the rowsource of a combobox or listbox. If this is the case, the code you are looking for is cbo*****.ListCount. Where the cbo***** is the name of your combobox or listbox.

Thank you. That worked like a charm. Now I have a different problem but that is purely in logic of the code so hopefully I will find some nice solution for it.

Thanks

 

[pr2-eugin]

Do you want to tell us what is the problem you are facing? Or you looking for solution online?

 

[sebna]

I have to first identify and understand it to be able to ask a question and as I am short on time today I will get back to it on Monday and probably will get back to you guys then as well

Thanks for all the help so far.