Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16

    Run time error 3061

    Frist let’s explain what I’m looking for,
    I want vba to choose an option base on a variable extract form a SQL string.
    This is want I have so far:

    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Set db = CurrentDb()
    'Retrieve drawing_type for the specified signal_id
    strSQL = "SELECT[FILEA MAPPS].drawing_type"
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = " & [Forms]![form1]![Text4] & ""
    Set rs = db.OpenRecordset(strSQL)


    'Assign to global varialbe
    m_intDrawingType = IIf(IsNull([filea mapps].Drawing_Type), 0, ([filea mapps].Drawing_Type))
    'If drawing_type = 1
    If m_intDrawingType = 1 Then
    'Close Connection
    rs.Close
    db.Close
    'Draw
    Call specialdatainfo
    Exit Sub
    End If
    rs.Close
    db.Close
    'Retrieve initial Database information
    Call getdatabaseinfo

    I’ve try several way but this code the better, but I still receive an error message (run-time error 3061 Too few parameter. Expected 1.) on this line

    Set rs = db.OpenRecordset(strSQL)

    Can somebody put me on a good road.
    Thanks

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Which line did it break?

    This might be your problem.

    Is [FILEA MAPPS].Signal_ID a text or number data type? If it is text then you need quote around "[Forms]![form1]![Text4]".

  3. #3
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16
    It break at this line

    Set rs = db.OpenRecordset(strSQL)

    I change this string from
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = " & [Forms]![form1]![Text4] & ""
    to
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = " & "[Forms]![form1]![Text4]" & ""

    with successs it break at the same place

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = '" & [Forms]![form1]![Text4] & "'"

    If Signal_ID is a text value

  5. #5
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Thank you rpeare.

    I guess I should have wrote down what it needs to change to instead of saying "you need quote around "[Forms]![form1]![Text4]"".

  6. #6
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16
    now i have error 2465 Microsoft access can't find the field'|1' referred to in your expression

    and it break at

    m_intDrawingType = IIf(IsNull([FILEA MAPPS].drawing_type), 0, ([FILEA MAPPS].drawing_type))

    note that the variable is

    Dim m_intDrawingType as string

    The field drawing_type is txt type and the value is only null or 1.

  7. #7
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    I don't think IIF works in VBA.

    Change to:

    IF ISNULL([FILEA MAPPS].drawing_type) THEN
    m_intDrawingType = 0
    ELSE
    m_intDrawingType = [FILEA MAPPS].drawing_type
    END IF

  8. #8
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16
    Still have the same runtime error 2465 on this line

    If IsNull([FILEA MAPPS].drawing_type) Then

  9. #9
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Ok, the problem is that you are query the database but not storing the value in a variable to do comparision.

    Change the code to use DLOOKUP and store the value in a variable and do your comparision.
    REPLACE:
    'Retrieve drawing_type for the specified signal_id
    strSQL = "SELECT[FILEA MAPPS].drawing_type"
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = " & [Forms]![form1]![Text4] & ""
    Set rs = db.OpenRecordset(strSQL)

    TO:
    Ok, the problem is that you are query the database but not storing the value in a variable to do comparision.
    Change the code to use DLOOKUP and store the value in a variable and do your comparision.
    REPLACE:
    'Retrieve drawing_type for the specified signal_id
    strSQL = "SELECT[FILEA MAPPS].drawing_type"
    strSQL = strSQL & " FROM [FILEA MAPPS] where [FILEA MAPPS].Signal_ID = " & [Forms]![form1]![Text4] & ""
    Set rs = db.OpenRecordset(strSQL)
    TO:
    m_intDrawingType = DLOOKUP("drawing_type", "[FILEA MAPPS]", "Signal_ID = '" & "[Forms]![form1]![Text4] & "'")

    Then use the IF statement to do your comparision.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    m_intDrawingType = IIf(IsNull(rs!Drawing_Type), 0, (rs!Drawing_Type))

    OR

    Code:
    if isnull(rs!drawing_type) then
        m_intdrawingtype = 0
    else
        m_intDrawingType = rs!drawing_type
    endif

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    christ I butchered the editing of that post!

  12. #12
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16
    Still doesn't work this is were I am:

    Code:
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim m_intDrawingType As Variant
    Set db = CurrentDb()
    'Retrieve drawing_type for the specified signal_id
    m_intDrawingType = Nz(DLookup("drawing_type", "[FILEA MAPPS]", "Signal_ID = '" & "[Forms]![form1]![Text4]" & "'"))
           
      Debug.Print
      
       If m_intDrawingType = 1 Then
          db.Close
         Call specialdatainfo
            Exit Sub
        End If
            
        'Close the cursor
        db.Close
        
        'Retrieve initial Database information
        Call getdatabaseinfo
        
    End Sub
    Private Sub specialdatainfo()
    DoCmd.OpenForm "form3"
    End Sub
    Private Sub getdatabaseinfo()
    DoCmd.OpenForm "form2"
    End Sub
    I used 2 different code for the query and both open form2 when one sould have open form3, I think the problem still in the m_intdrawingtype line

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why don't you debug.print m_intdrawingtype instead of debug.print .. using debug.print with no variable just prints a blank line.

    Also the proper way to close a database connection would be:

    set db = nothing

    Is the drawing type correctly showing on your field TEXT4 on the form FORM1?

    The reason your code is always opening form 2 is because it's evaluating m_intdrawingtype as something other than 1. Something else looks off about this formula too, are both SIGNAL_ID and DRAWING_TYPE in the table FILEA MAPPS number value fields? Your dlookup is treating DRAWING_TYPE as a number and SIGNAL_ID as a text value, if that's not the case you need to modify it.

    If your SIGNAL_ID field is a number value your formula would be:

    m_intdrawingtype = dlookup("drawing_type", "[FileA MAPPS]", "Signal_ID = " & forms!form1!text4)

  14. #14
    beaurou is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    16
    rpeare

    many thanks for your help and time.

    I modify the code as per your sugestion:

    Code:
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim m_intDrawingType As Variant
    Set db = CurrentDb()
    'Retrieve drawing_type for the specified signal_id
    m_intDrawingType = Nz(DLookup("drawing_type", "[FILEA MAPPS]", "Signal_ID = '" & "[Forms]![form1]![Text4]" & "'"))
           
      Debug.Print m_intDrawingType
      
       If m_intDrawingType = 1 Then
          Set db = Nothing
         Call specialdatainfo
            Exit Sub
        End If
            
        'Close the cursor
        Set db = Nothing
        
        'Retrieve initial Database information
        Call getdatabaseinfo
        
    End Sub
    
    Private Sub specialdatainfo()
    DoCmd.OpenForm "form2"
    End Sub
    Private Sub getdatabaseinfo()
    DoCmd.OpenForm "form3"
    End Sub
    This line Debug.Print m_intDrawingType return nothing in the immediate window, look like the evalution return nothing

    Both drawing_type and Signal_id are text field in the table Fillea Mapps

    Note: In the field Drawing_type only some record have "1" as value all the other one are blank.

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if drawing_type is a text field why are you trying to fill it in with a number?, which is basically what NZ does?

    Code:
    m_intDrawingType = Nz(DLookup("drawing_type", "[FILEA MAPPS]", "Signal_ID = '" & [Forms]![form1]![Text4] & "'"))
    Try this first:

    Code:
    m_intDrawingType = dlookup("Drawing_Type", "[FILEA MAPPS]", "Signal_ID = '" & forms!form1!text4 & "'")
    debug.print m_intdrawingtype
    NOTE: IN YOUR MOST RECENT CODE YOU STILL HAVE " MARKS AROUND FORMS!FORM1!TEXT4 WHICH IS NOT WHAT YOU WANT IF YOU ARE TRYING TO PULL A VALUE FROM A FORM

    if this gives you the correct 'drawing_type' response then you should be golden, though I do not know why you are mixing data types

    I would define m_intDrawingType as a string then check it as a string value in your check

    Code:
    if m_intDrawingType = '1' Then ...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. run time error-3061 too few parameters.
    By pervej.fac in forum Queries
    Replies: 1
    Last Post: 01-04-2014, 05:32 AM
  2. VBA Code Returning Error Run Time Error 3061
    By tgwacker in forum Access
    Replies: 2
    Last Post: 11-24-2013, 11:00 AM
  3. Run-Time Error 3061
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 09-23-2013, 06:46 AM
  4. Run-time error 3061
    By boywonder in forum Programming
    Replies: 1
    Last Post: 07-20-2011, 06:14 AM
  5. Run-Time error 3061
    By boywonder in forum Programming
    Replies: 12
    Last Post: 05-24-2011, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums