Results 1 to 14 of 14

Thread: query help

  1. #1
    Join Date
    Feb 2010
    Posts
    8

    Unanswered: query help

    morning all:

    i am working on a tracking system for customer complaints my problem is that i am taking the data from a combo box as i exit the combo box i need to run a query on a linked table to fill a text box. the text box will contain the total amount of parts for that order so that we know how many parts we need to contain. this is what i have tried it this way but my field orqty from the linked field is blank.

    Dim myordno As String

    myordno = Me.MoNum
    If Me.MoQty = 0 Then
    DoCmd.OpenQuery "MasterQty"
    Me.MoQty = orqty
    End If

    if i run the query MasterQty i get the correct value. i have also tried it this way but i get a error when i try to run it. the error is runtime error 3061 to few parameter. expected 1. the following is code is for that. where it errors out is RED.
    Dim myordno As String
    Dim DBMasterqty As dao.Database
    Dim RstMo As dao.Recordset
    Dim meqty As dao.Field

    Set DBMasterqty = CurrentDb
    Set RstMo = DBMasterqty.OpenRecordset("MasterQty")
    ' Set meqty = .createfield(amflib_momast.orqty)
    myordno = Me.MoNum
    If Me.MoQty = 0 Then
    DoCmd.OpenQuery "MasterQty", , acReadOnly
    Me.MoQty = orqty
    End If
    rst.Close
    MyDB.Close
    Set rst = Nothing
    Set MyDB = Nothing

    Thanks in advance

  2. #2
    Join Date
    May 2009
    Posts
    258
    This means that the JET query engine is not able to resolve something in your query. The query will work if you run it manually, but not in the code.

    If you are referencing a form field in your query, this could be the problem. If that's the case, you may need to add the SQL to the logic and concatenate the form field value into it. Would you mind posting the query for us to see?

    Ax

  3. #3
    Join Date
    Feb 2010
    Posts
    8
    i am not understanding what you are saying about the JET. here is the SQL view of the query. SELECT AMFLIB_MOMAST.ORDNO, AMFLIB_MOMAST.ORQTY
    FROM AMFLIB_MOMAST
    WHERE (((AMFLIB_MOMAST.ORDNO)=[Forms]![Dar Entry Screen]![MoNum]));

    where i have just the orqty i tried adding the entire part AMFLIB_MOMAST.ORQTY but then i get the runtime error 424 object required.

    i am missing something but don't have a clue as to what.

    Thanks

  4. #4
    Join Date
    May 2009
    Posts
    258
    From Everything About Using Parameters from Code:
    The answer is that you're invoking the Jet engine in a different context here, and that makes all the difference. When you get data from
    a parameter query that uses a form to supply the parameter via the Access user interface, as in the earlier example, Access can evalute
    the expression involved and supply a value to Jet. When you get data from a parameter query that uses a form to supply the parameter via
    VBA, instead of through a form, the bits of Access that manage user interface matters aren't involved. Consequently, Jet is passed the
    string "[Forms]![frmSelectCountry]![cboCountry]" instead of the value in cboCountry. Because Jet doesn't know how to evaluate the
    expression, it can't open the recordset.
    In summary, JET cannot understand "[Forms]![Dar Entry Screen]![MoNum]" because you are executing it from code. You'll need to take the text of the SQL and put it into the actual code as a string. You can then concatenate the "[Forms]![Dar Entry Screen]![MoNum]" value into the SQL and run it from there.

    So your logic would be something like as follows:
    Code:
    sql="SELECT AMFLIB_MOMAST.ORDNO, AMFLIB_MOMAST.ORQTY " _
    & "FROM AMFLIB_MOMAST " _
    & "WHERE (((AMFLIB_MOMAST.ORDNO)=" & [Forms]![Dar Entry Screen]![MoNum] & "));"
    Set RstMo = DBMasterqty.OpenRecordset(sql)
    Regards,

    Ax

  5. #5
    Join Date
    Feb 2010
    Posts
    8
    i think i am understand this but still getting the error 3061 too few parameters. expected 1 on the set RstMo line. this is what i have currently in my code.
    Dim myordno As String
    Dim sql As String
    Dim DBMasterqty As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RstMo As DAO.Recordset
    Dim meqty As DAO.Field

    Set DBMasterqty = CurrentDb
    sql = "SELECT AMFLIB_MOMAST.ORDNO, AMFLIB_MOMAST.ORQTY " _
    & "FROM AMFLIB_MOMAST " _
    & "WHERE (((AMFLIB_MOMAST.ORDNO)=" & [Forms]![Dar Entry Screen]![MoNum] & "));"
    Set RstMo = DBMasterqty.OpenRecordset(sql)

    myordno = Me.MoNum
    If Me.MoQty = 0 Then
    DoCmd.OpenQuery "MasterQty", , acReadOnly
    Me.MoQty = orqty
    End If
    rst.Close
    MyDB.Close
    Set rst = Nothing
    Set MyDB = Nothing

    i haven't the error condition as of yet.
    thanks for all your help Ax

  6. #6
    Join Date
    May 2009
    Posts
    258
    If you step through the logic up to the point where the sql variable is set, what is its value? As well, what's the value of [Forms]![Dar Entry Screen]![MoNum]?

  7. #7
    Join Date
    Feb 2010
    Posts
    8
    the values at that point of time are correct they are M617710 which is what i expected them to be. but as soon as i go to step out of the set RstMo i get the error 3061 too few parameters expected 1.

  8. #8
    Join Date
    May 2009
    Posts
    258
    So MoNum is actually text, then you'll need to add single quotes around it in the SQL:
    Code:
    sql="SELECT AMFLIB_MOMAST.ORDNO, AMFLIB_MOMAST.ORQTY " _
    & "FROM AMFLIB_MOMAST " _
    & "WHERE (((AMFLIB_MOMAST.ORDNO)='" & [Forms]![Dar Entry Screen]![MoNum] & "'));"
    Alternatively, you can check this out:
    ACC2000: "Too Few Parameters. Expected 1" Error Message

    Regards,

    Ax

  9. #9
    Join Date
    Feb 2010
    Posts
    8
    Ax:
    does it make a difference if i am using 2007 version on what i am trying to do. my orqty variable is still blank. that should have like 1100 pieces in it. i didn't think this would be that hard to do. but i am not a vb programmer, i usally program in RPG and a little java.

  10. #10
    Join Date
    May 2009
    Posts
    258
    OK, so can we confirm that you are no longer receiving an error when executing the SQL statement?

    In VB, when you query for fields in a file, they are not available for access by name, as they are in RPG. They are part of a recordset and have to be accessed as you would an item in a collection, so:
    Code:
    Me.MoQty = RstMo("ORQTY")
    would be the correct syntax.

    It looks like you are running the query and then running it again with the DoCmd OpenQuery. I may be missing something, but this is all unnecessary. You should be able to accomplish what you need with the following logic:
    Code:
    Dim myordno As String
    Dim sql As String
    Dim DBMasterqty As DAO.Database
    Dim RstMo As DAO.Recordset
    
    myordno = Me.MoNum
    Set DBMasterqty = CurrentDb
    sql = "SELECT AMFLIB_MOMAST.ORDNO, AMFLIB_MOMAST.ORQTY " _
    	& "FROM AMFLIB_MOMAST " _
    	& "WHERE AMFLIB_MOMAST.ORDNO='" & myordno & "';"
    Set RstMo = DBMasterqty.OpenRecordset(sql)
    
    If Not RstMo.EOF Then
    	Me.MoQty = RstMo("ORQTY")
    End If
    RstMo.Close
    DBMasterqty.Close
    Set RstMo = Nothing
    Set DBMasterqty = Nothing
    Regards,

    Ax

  11. #11
    Join Date
    Feb 2010
    Posts
    8
    Ax

    u have more than helped on this problem. the code you just gave me ran like a charm.. thanks so much...
    if i can ever help on here i will but i think i am a ways from helping on vba problems maybe some simple form ??

    Thanks again for all your help

  12. #12
    Join Date
    May 2009
    Posts
    258
    No problem at all, glad it worked for you. I'm a bit the opposite of you... I'm a Java programmer turned VB turned RPG, or something like that, so RPG III is my nightmare at times.

  13. #13
    Join Date
    Feb 2010
    Posts
    8
    are you using the free form at all in the rpg stuff... if you aren't you should really look at it. so much easier to write and read and no stickin indicators.....

  14. #14
    Join Date
    May 2009
    Posts
    258
    We do for some things, I enjoy writing in RPGIV much more than III. Unfortunately, we are using an ERP package that we haven't yet upgraded to the most recent version (which I believe is all RPGIV), mostly because we'd have to port all the custom mods we've made for the past decade or so. It's gonna get hairy when we do. I'm just glad there are commands like MRGSRC and CVTRPGSRC, they'll be my friends when we do the upgrade.

Posting Permissions

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