Access noob - problem using query in VBA to change an object's property

[RomeoJuliet]

Please be gentle, I'm new to Access...

I have an Order Processing app based on Northwind. I've modified table [order details] to include a yes/no field 'canVisit'. The Orders/Orders Subform look quite similar to the original, but I have buttons on the Orders form which I only want to be visible if there is at least one row in [order details] for the current order where canVisit = true.

I have code behind the afterUpdate event on the subform as follows:

Private Sub Form_AfterUpdate()
    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim LSQL As String
    Dim Lcnt As Integer
    'Open connection to current Access database
    Set db = CurrentDb()
    'Create SQL statement to retrieve filtered row count from [order details] table
    LSQL = "SELECT count(*) FROM Products INNER JOIN [Order Details] " & _
        "ON Products.ProductID = [Order Details].ProductID " & _
        "WHERE [Order Details].OrderID=Me!OrderID AND Products.CanVisit=True;"
    Set Lrs = db.OpenRecordset(LSQL)
    'Retrieve value if data is found
    If Lrs.EOF = False Then
        Lcnt = Lrs("order details")
    Else
        Lcnt = 0
    End If
    Lrs.Close
    Set Lrs = Nothing
    If Lcnt > 0 Then
        Set [Forms]![orders]![btnAdultVisit].Visible = True
        Set [Forms]![orders]![btnYouthVisit].Visible = True
    Else
        Set [Forms]![orders]![btnAdultVisit].Visible = False
        Set [Forms]![orders]![btnYouthVisit].Visible = False
    End If
 
End Sub

This is failing with the error "Runtime error 3061. Too few parameters. Expected 1.".

Any help would be greatly appreciated. I readily admit I really don't know what I'm doing!
Thanks, Mike.

 

[Kiwiman]

Howzit

Try, assuming that orderid is a number...

LSQL = "SELECT count(*) FROM Products INNER JOIN [Order Details] " & _
        "ON Products.ProductID = [Order Details].ProductID " & _
        "WHERE [Order Details].OrderID=[b]" & Me!OrderID & "[/b] AND Products.CanVisit=True;"

 

[jarico75@yahoo.]

I agree with Kiwiman. You have a proble, with the SQL code, but I also changed the way you are evaluating your file. See changes in the select statement and the IF statement.


Private Sub Form_AfterUpdate()
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim Lcnt As Integer
'Open connection to current Access database
Set db = CurrentDb()
'Create SQL statement to retrieve filtered row count from [order details] table
LSQL = "SELECT * FROM Products INNER JOIN [Order Details] " & _
"ON Products.ProductID = [Order Details].ProductID " & _
"WHERE [Order Details].OrderID= " & Me!OrderID & " AND Products.CanVisit=True;"
Set Lrs = db.OpenRecordset(LSQL)
'Retrieve value if data is found
If Lrs.RecordCount > 0 Then
Lcnt = Lrs.RecordCount
Else
Lcnt = 0
End If
Lrs.Close
Set Lrs = Nothing
If Lcnt > 0 Then
Set [Forms]![orders]![btnAdultVisit].Visible = True
Set [Forms]![orders]![btnYouthVisit].Visible = True
Else
Set [Forms]![orders]![btnAdultVisit].Visible = False
Set [Forms]![orders]![btnYouthVisit].Visible = False
End If

End Sub

 

[RomeoJuliet]

Fantastic - thank you Kiwiman & Jarico! Using Jarico's code, Lcnt is now being successfully set according to the rows in the order. The only problem now is that when the interpreter hits the first Set statement it errors with "Run time error '424': Object required".

Sorry to ask for yet more but... help please?

Thanks
Mike.

 

[Kiwiman]

Howzit

I don't think you need set. Try...

[Forms]![orders]![btnAdultVisit].Visible = True

 

[RomeoJuliet]

That's it - all sorted. Thanks very much again Kiwiman (and Jarico earlier).

Mike.