Results 1 to 12 of 12
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    Too Few Parameters Expected 9

    Greetings

    I'm getting the dreaded parameters error - not sure why

    I'm trying to add a new line/record to a subform and I've checked for all the usual suspects and the query appears to be clean, but...

    Here is the query
    Code:
    CurrentDb.Execute "INSERT INTO TblServiceLines (RefNum, ServiceDecision, ServiceType, PlaceOfService, ServiceCodeRange1, ServiceCodeRange2, ServiceQty, StartDate, EndDate) " & _
          "SELECT [Forms]![FrmUnited].TxtRefNum, Me.TxtServDecision, Me.TxtServType, Me.TxtPlaceOfServ, Me.TxtCodeRng1, Me.TxtCodeRng2, Me.TxtQty, Me.TxtBegDate, Me.TxtEndDate;"
    One additional piece of information... The button this query is attached to is in the subform but the RefNum value is called from a textbox in the main form..



    Any insights would be greatly appreciated - I'm at my wits end with this project

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Concatenate variables. References to form controls are variables.

    CurrentDb.Execute "INSERT INTO TblServiceLines (RefNum, ServiceDecision, ServiceType, PlaceOfService, ServiceCodeRange1, ServiceCodeRange2, ServiceQty, StartDate, EndDate) " & _
    "SELECT " & Me.Parent.TxtRefNum & "," & Me.TxtServDecision & "," & Me.TxtServType & "," & Me.TxtPlaceOfServ & "," & Me.TxtCodeRng1 & "," & Me.TxtCodeRng2 & "," & Me.TxtQty & ",#" & Me.TxtBegDate & "#,#" & Me.TxtEndDate & "#"

    Note the use of # delimiter for date field type parameters. If any fields are text type, use apostrophe delimiter for the parameters.

    Why use INSERT action? Is subform bound to table? Why not just normal data input?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    June ~ Thank You very much!!

    I went ahead and re-bound the controls to the table; and of course, everything cleared up.

    I was using an INSERT Qry because what I have and what I am trying to accomplish is this:

    I have a form/sub-form within which I need to be able to enter, search for, and edit the data

    In addition, I need to be able to enter new records into the sub-form and have it auto-increment the count of how many records there are

    I have it set to provide a total record count when I search, but I need to increment that count each time I enter a new record and thus the reason I chose to enter those new records using an Insert Qry attached to an 'Add' button.

    What I'm trying to do seems (in my feeble newbie mind) as though it should be ridiculously simple to acheive, but what I am finding from all of my googling is that it is nightmarishly difficult; if not impossible.

    And I fear I am not very good with framing my questions to get the help I need in the online communities.

    You are always there to help June and I can't thank You enough..

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you need to save the count? This can be calculated when needed. If you need to generate a sequential custom unique identifier, this can be done with VBA code. This is a common topic. Here is one thread https://www.accessforums.net/showthread.php?t=23329
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    And what happens with your record count, when user deletes a record in subform?
    Btw, where you save the count? In main form source table?

    When you really need to save the number of records in subform linked with main form current record, then it will be much easier to use proper subform events (e.g. OnLostFocus, or OnDelete and AfterUpdate) for this.

  6. #6
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey June...

    I really don't need to 'save' the count I just need to show the end user what the count total is

    E.g., When the end user searches for a specific record the main form populates with the customer record and the sub form populates with all of the orders for that customer

    The end user may need to add 5 more records to the sub form (orders) so what I want to be able to do is when the search populates both forms I have a textbox which says what the current record count is for that customer

    When the end user adds a new record I want the count to increment by 1 until all 5 new records are entered.

    Now, with that said, I have added the following code into the On Current Event which seems to provide both the record count when first populated after the search and then it increments after each new record is entered

    However, this effort is tantamount to a guess so I don't know this is the correct way to do what I'm looking for...(I've learned that not everything that works is necessarily the best way to do things)

    Code:
    Private Sub Form_Current()
      If Me.RecordsetClone.RecordCount = 0 Then
        Me.Text38 = 0
      Else
          Me.RecordsetClone.MoveLast
        Me.Text38 = Me.RecordsetClone.RecordCount
      End If
    End Sub
    If this is the correct way to do this then my next question is how can I move the cursor to the last record in the sub form (I thought .MoveLast would accomplish this, but it doesn't)

    Thanks for sticking with this June

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps I'm missing something but why not just show the navigation buttons on the subform?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Still don't understand why this is needed. Why does user care how many records there are? Just enter data.

    A textbox in form header or footer can have expression to count records: =Count(*)

    .MoveLast moves to last record of recordset, not the form. For moving to form record that corresponds to RecordsetClone record, would require use of bookmarks.

    Move to last record (but what event would you put this in?):

    DoCmd.GoToRecord acDataForm, , acLast
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Ajax... Thanks for joining the party...

    You're not missing a thing Ajax - I kinda wanted to remove that bar to eliminate the 'search' feature - but I'm up against a deadline with this project so, even though it may not be what I think I want...

    It will nevertheless get me where I need to be, soooo....

    I thank you - I'll run with that solution as it will work just fine.

    Thanks Ajax...

  10. #10
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey June...

    The user would need to know the record count as the work they do is record transference.

    Users will be asked to enter as many as 99 records in the subform so having a way to keep track of how many they entered.

    You probably saw Ajax's post and my response that I wanted to do this in absence of using the Navigation Buttons - but now, I'm just going to go ahead and use that feature.

    However, my next project will require the removal of those buttons so this information will be put to good use

    Can't thank you enough, June

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I
    kinda wanted to remove that bar to eliminate the 'search' feature
    you do know users can right click on a control and search from there anyway?

    However you can disable the feature there and in the navigation bar by setting the forms allow filter property to no

  12. #12
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Geez... Nope, Ajax -I did not know those things, but I do now... Thank You...

    This is the first time I have worked with a Form/SubForm combination - All the vids and research I've done make it seem as though it is not this difficult...

    So, I'm not sure why I am struggling with this as badly as I am - But I'm am beyond my wits end, nothing (and I do mean nothing) I have tried to do works

    Thank You, again Ajax...I'll be posting again soon for more help - This time with trying to toggle through the main form records such that the subform will also show the records associated with each of the new record.

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

Similar Threads

  1. Too Few Parameters. Expected 1
    By flamesofdoom in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 02:23 PM
  2. too few parameters; expected 2
    By slimjen in forum Forms
    Replies: 13
    Last Post: 07-26-2012, 02:42 PM
  3. Too few parameters. expected 1.
    By seth1685 in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 08:08 AM
  4. Too few parameters expected 4
    By forestgump in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 09:10 AM
  5. Too few parameters. Expected 2.
    By PPCMIS2009 in forum Access
    Replies: 0
    Last Post: 01-28-2009, 01:02 PM

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