Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem in my vb code in access 2009.
    I have to checque a value of a query in a form. Herefor I use this code, For the check I use a button:

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("Countrecordsdeuren", dbOpenDynaset); 
    Set db = CurrentDb
    MsgBox rst!namefield
    When I hit the button, I get this error: "run-time error 3061. too few parameters, expected 1"
    I don't even get the messagebox. how can I check where my error is?
    But when I run the query directly, I get a record.
    What am I doing wrong?

    grts Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does the query refer to a control on your form? DAO doesn't recognize expressions of the form

    [Forms]![NameOfForm]![NameOfControl]

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By the way, you should set the db variable before opening the recordset:

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Countrecordsdeuren", dbOpenDynaset);

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783486' date='08-Jul-2009 12:12']By the way, you should set the db variable before opening the recordset:

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Countrecordsdeuren", dbOpenDynaset);[/quote]

    In my query it refers indeed to a field in the form. How can i do it otherwise?

    grts Nancy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It shouldn't be difficult, but I need some information about the query. Could you do the following:
    - Open the query Countrecordsdeuren in design view.
    - Switch to SQL view.
    - Copy the SQL string and paste it into a reply.

    Also, can you tell me the data type of the field with the condition - is it a text field, a number field, or a date/time field?

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783488' date='08-Jul-2009 12:27']It shouldn't be difficult, but I need some information about the query. Could you do the following:
    - Open the query Countrecordsdeuren in design view.
    - Switch to SQL view.
    - Copy the SQL string and paste it into a reply.

    Also, can you tell me the data type of the field with the condition - is it a text field, a number field, or a date/time field?[/quote]

    Her is my sql of the query
    Code:
    SELECT Count(tblSubProjectDeur.IDsubProjectdeur) AS aantalplus, tblSubProjectDeur.lnkIDsubProject
    FROM tblSubProjectDeur
    GROUP BY tblSubProjectDeur.lnkIDsubProject
    HAVING (((tblSubProjectDeur.lnkIDsubProject)=[Forms]![FrmProject]![subProject]));
    The field on the form is a generel number field, i also tiried it as a text field and the name of the field is "subproject", but it is unbound, I even tried it with a bound field. nothing works
    The field has an number (the idkey)

    grts Nancy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject " & _
      "FROM tblSubProjectDeur GROUP BY lnkIDsubProject " & _
      "HAVING lnkIDsubProject=" & Me.subProject
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    The line

    MsgBox rst!namefield

    will still cause an error because namefield is not a field in the recordset.

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783499' date='08-Jul-2009 13:27']Try this:

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject " & _
      "FROM tblSubProjectDeur GROUP BY lnkIDsubProject " & _
      "HAVING lnkIDsubProject=" & Me.subProject
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    The line

    MsgBox rst!namefield

    will still cause an error because namefield is not a field in the recordset.[/quote]

    That is not correct, I have "MsgBox rst!aantalplus", but that is not the problem.
    I think that it is like you said, refering to a field in a loaded form.

    grts Nancy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You originally reported the line as

    MsgBox rst!namefield

    but

    MsgBox rst!aantalplus

    should work.

  10. #10
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783503' date='08-Jul-2009 13:52']You originally reported the line as

    MsgBox rst!namefield

    but

    MsgBox rst!aantalplus

    should work.[/quote]

    It does not work.
    Always get the message "run-time error 3061. too few parameters, expected 1"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you temporarily change the code to

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject " & _
      "FROM tblSubProjectDeur GROUP BY lnkIDsubProject " & _
      "HAVING lnkIDsubProject=" & Me.subProject
    Debug.Print strSQL
    Stop
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    When the code pauses, the SQL string should be in the Immediate window ("Direct" in Dutch).
    Could you copy it and paste it into a reply?

  12. #12
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783507' date='08-Jul-2009 14:12']Could you temporarily change the code to

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject " & _
      "FROM tblSubProjectDeur GROUP BY lnkIDsubProject " & _
      "HAVING lnkIDsubProject=" & Me.subProject
    Debug.Print strSQL
    Stop
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    When the code pauses, the SQL string should be in the Immediate window ("Direct" in Dutch).
    Could you copy it and paste it into a reply?[/quote]

    Here is wat in that immediate windows hows (i have an englisch version)
    SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject FROM tblSubProjectDeur GROUP BY lnkIDsubProject HAVING lnkIDsubProject=4SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject FROM tblSubProjectDeur GROUP BY lnkIDsubProject HAVING lnkIDsubProject=4

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I assume that you pasted the SQL string twice. If

    SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject FROM tblSubProjectDeur GROUP BY lnkIDsubProject HAVING lnkIDsubProject=4

    produces error 3061, it must mean that one of the field names is not spelled correctly. Please check them very carefully.

  14. #14
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783510' date='08-Jul-2009 14:39']I assume that you pasted the SQL string twice. If

    SELECT Count(IDsubProjectdeur) AS aantalplus, lnkIDsubProject FROM tblSubProjectDeur GROUP BY lnkIDsubProject HAVING lnkIDsubProject=4

    produces error 3061, it must mean that one of the field names is not spelled correctly. Please check them very carefully.[/quote]

    I always copy paste the namefields, so I'm sure that it is correct spelled.
    I really don't understand

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm afraid we won't be able to solve this without seeing the database.

    Could you create a stripped down copy of the database in Access 2000 format, zip it and attach the zip file to a reply?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •