Results 1 to 10 of 10
  1. #1
    FmrVBAJunkie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    17

    Error 3421, Set QueryDef

    Hi All,

    Learning the Access VBA ropes here.

    Goal: Pass parameter into SQL Query. This is to pass a primary key into a premade query, but I don't know the primary key until the user chooses a specific record. So, I want the next form's subform (query) to display the many records to the one primary key.

    VBA Methodology Being Implemented: I'm using the Current.Db.QueryDefs(Query_Name) to pass the parameter.

    The error is occurring on the "Set qdF = CurrentDb..." line.

    Error Message:

    Below is the code: Run-time error '3421', Data type conversion error

    Misc Info that might help: The subform name for the query has a space in it. Not sure if this matters, I tried to put quotes and stuff around the name of the query.

    Code:
    Dim qdF As QueryDef
    Dim rsT As Recordset
    
    Application.Echo False
     
    Set qdF = CurrentDb.QueryDefs(qry_Amend_subform)
    
    qdF.Parameters("WBSIdd") = WBSIdd
    
    WBSIdd = WBSIdd
    
    Set rsT = qdF.OpenRecordset
    
    rsT.Close
    qdF.Close
    Set rsT = Nothing
    Set qdF = Nothing
    
    Application.Echo True
    Can someone provide guidance? I feel like I'm very close...but still soooo far awaaaay.



    Tanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try your query name in quotes
    "qry_Amend_subform"

  3. #3
    FmrVBAJunkie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    17
    Hi Orange,

    I tried the following enumerations and triggered error 3265, Item not found in this collection

    Code:
    Set qdF = CurrentDb.QueryDefs("qry_Amend subform")
    
    Set qdF = CurrentDb.QueryDefs("qry_Amend_subform")
    
    Set qdF = CurrentDb.QueryDefs![qry_Amend_subform]
    
    Set qdF = CurrentDb.QueryDefs!["qry_Amend subform"]
    Compile error with this syntax

    Code:
    Set qdF = CurrentDb.QueryDefs "qry_Amend subform"

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a routine to list all queryDefs and their fields in your database.
    You can look at the code and run it. You may get some ideas.
    Really need more context about what you are trying to do.

    You could also post the database.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : shamilQdfs
    ' Author    : mellon
    ' Date      : 10/02/2016
    ' Purpose   : Listing Queries and the fields within them
    '---------------------------------------------------------------------------------------
    '
    Sub shamilQdfs()
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim qdfIndex As Integer
    10  On Error GoTo shamilQdfs_Error
    
    20  Set dbs = Access.Application.CurrentDb
    30  qdfIndex = 1
    40  For Each qdf In dbs.QueryDefs
    50      Debug.Print qdfIndex & ". Query: " & qdf.name
            Dim fld As DAO.Field
    60      For Each fld In qdf.Fields
    70          Debug.Print " " & (fld.OrdinalPosition + 1) & _
                            ". " & fld.SourceTable & "." & fld.SourceField
    80      Next fld
    90      Debug.Print ""
    100     qdfIndex = qdfIndex + 1
    110 Next qdf
    
    120 On Error GoTo 0
    130 Exit Sub
    
    shamilQdfs_Error:
    
    140 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure shamilQdfs of Module AccessD_KillText"
    End Sub

  5. #5
    FmrVBAJunkie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    17
    Hi Orange,

    Thanks for the help. This macro is awesome. I'll definitely be able to use it more in the future. Unfortunately, I'm unable to share my database with you. What I can tell you is that I was able to reference the table by using the index number of the QueryDefs. But, when I was trying to call the field name using qdf.Parameters("FieldName"). I get an "Item not found" error. This is the same field name that I pulled using the Db code you gave me, so I know it's there...somewhere...over the rainbow. lol

    Some other weird stuff is happening. Eve though I was able to list out all of the parameters of the query using your code, when I used parameter.count, I get "0".

    Do I have to specifically reference DAO in my code when attempting to pass the parameters?

    Tanks!
    FmrVBAJunkie

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is an example of a Parameter Query I use.

    It prompts for BDate (Beginning Date) and EDate (Ending Date), once you supply them,
    it then selects Orders that were made after or on BDate and before or on EDate.

    I'm not sure of the details surrounding your issue.

    Code:
    PARAMETERS [Bdate] DateTime, [Edate] DateTime;
    SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipVia, Orders.ShipName
    FROM Orders
    WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));
    Here are some links with more info re parameter query.
    Stkovflw

    Blogannath Parameter query


    Good luck

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Apologies if I misread this.....

    This is to pass a primary key into a premade query, but I don't know the primary key until the user chooses a specific record. So, I want the next form's subform (query) to display the many records to the one primary key.
    I'm not really sure what you are doing.

    Do you have a form where you select a record, then open another form to see details?

    If yes, seems like you are going to an awful lot of trouble. You could open the form filtered.
    The syntax of the command is
    Docmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

    Could be as simple as a button click
    Code:
    Sub Button1_Click()
        Docmd.OpenForm("yourFormName", , , "WBSIdd" = WBSIdd)
    End Sub
    ----------
    BTW, not being a picker of nits...... but what orange posted in Post #4 is code, not a macro.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Steve's comments, I focused on the Parameter query aspect of the post. However, if you are working with tables in a 1 to many relationships, you might consider Form/subform with linking fields as a possible option.

    Perhaps you could give us a plain English description of WHAT you are trying to accomplish (no Access/database jargon for this). There may be several options for HOW to do it.

  9. #9
    FmrVBAJunkie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    17
    @Everyone

    Plain English jargon sounds good to me, I realize that I might not be using the right terminology.

    Yes, it's a one to many relationship. and I'm selecting a record in one form and when opening the next form, I want to see all of the many items associated with that one record. For now, I abandoned trying to modify the query (sorry, I resorted to something that I know).

    I've gotten it to work a little bit. The two forms are accessed (no pun intended) through a Navigation form. In the "many" form, I set the parent and child link fields to a textbox that contains the primary key. So far it works only when I move from the first record form to the second record form twice. This is what I'm seeing...

    Step One: Select Record in "Form 1"
    Step Two: Select Navigation Tab "Form 2", Table is empty (table is not a query anymore, but a subform of the actual table)
    Step Three: Select Navigation Tab "Form 1", the record that I previously selected is still selected.
    Step Four: Select Navigation Tab "Form 2", Table is now showing the many records associated to the record selected in "Form 1".

    I hope this helps. Thanks for looking into this everyone.

    @ ssanfu: BTW, thanks for tactfully correcting my access terminology It helps me not sound like a fool

  10. #10
    FmrVBAJunkie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    17
    I got it!!!!!!

    I wrote the code that sets the text box value in all of the events leading up to navigating to Form 2. It looks like this did something, where it cancelled the action out. I dunno, but I left the code in the Load event of Form 2, not the navigation event. And, it works!!!

    There's a lot of great mechanics in everyone that contributed to this thread. Sorry that I didn't use it, but I will be revisiting this thread to figure out how to utilize these other methods. They are more elegant, and are things that I will have to eventually learn. Thanks for all of the time dedicated to helping me solve this problem.

    Tanks!
    FmrVBAJunkie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create Querydef - Error 3265
    By Tim1 in forum Programming
    Replies: 3
    Last Post: 04-25-2016, 09:12 AM
  2. Updating a QueryDef
    By The Professor in forum Queries
    Replies: 3
    Last Post: 08-05-2014, 03:57 PM
  3. QueryDef
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 09-18-2012, 09:31 AM
  4. Can someone explain querydef?
    By roguex20 in forum Access
    Replies: 2
    Last Post: 07-22-2012, 11:57 AM
  5. Replies: 6
    Last Post: 05-10-2012, 10:57 AM

Tags for this Thread

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