Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Adding New Records with Form

    I have a simple form for entering order information. The form is only used on an "exception" basis when I miss an order in an import from our legacy system.
    It's unbound. There's a main form for header info, and a
    subform. After the header section is filled out, the data is
    inserted into the order header. Then the OrderID is stored in
    a public variable (LngOrdID), and data entry in the subform is
    allowed.

    The subform contains two fields:
    Product
    Qty

    after the Qty is updated, this code executes:

    Code:
    Private Sub txtQty_AfterUpdate()
      Dim strSql As String, rsOrdDet As New ADODB.Recordset
      On Error GoTo ERR_SAVEDETAIL
      'see if the record exists already
      'if so,  it's an update
      strSql = "SELECT COUNT(*) AS REC FROM ORDER_DETAIL WHERE ORDER_ID=" & [Form_Order Entry].LngOrdId & " AND PRODUCT = '" & cmbProduct & "'"
      rsOrdDet.Open strSql, DBCNXN, adOpenForwardOnly, adLockReadOnly
      Select Case rsOrdDet!REC
        Case 0
          'new record
          strSql = "INSERT INTO ORDER_DETAIL(ORDER_ID, ORDER_NUMB, PRODUCT, ORIG_QTY, REV_QTY, SHIP_QTY) " _
              & "VALUES (" & [Form_Order Entry].LngOrdId & ", '" & Forms![order entry]!txtORDNUM & "', '" & cmbProduct & "', " _
              & txtQty & ", " & txtQty & ", " & txtQty & ")"
        Case Else
          'revise record
          strSql = "UPDATE ORDER_DETAIL SET ORIG_QTY=" & txtQty & ", REV_QTY=" & txtQty & ", SHIP_QTY=" & txtQty
          strSql = strSql & " WHERE ORDER_ID=" & [Form_Order Entry].LngOrdId & " AND PRODUCT ='" & cmbProduct & "'"
      End Select
      DBCNXN.Execute strSql
      DoCmd.GoToRecord , , acNewRec
      
    EXIT_SAVEDETAIL:
      If rsOrdDet.State <> adStateClosed Then
        rsOrdDet.Close
      End If
      Set rsOrdDet = Nothing
      Exit Sub
      
    ERR_SAVEDETAIL:
      If ErrorLog("frmSubOrdEnt.txtQty_AfterUpdate", Err) = True Then
        Resume
      Else
        MsgBox "An error occured adding order detail line.", vbOKOnly, "Error"
        Resume EXIT_SAVEDETAIL
      End If
    End Sub
    For some reason, after accepting one new record, the subform will not go to another new record.
    It seems that "DoCmd.GoToRecord , , acNewRec" isn't doing anything.
    I tried turning on the RecordSelectors property for the subform, and
    the New Record button is not enabled, either.

    Any ideas?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    duh! Can't add records to an unbound form!
    (Can you?)

    Back to the drawing board. Curses!
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    duh! Can't add records to an unbound form!
    (Can you?)

    Back to the drawing board. Curses!
    DUH! Sure you can... You hasto do it behind the scenes ... Then requery your subform ...

    BTW, as a matter of taste, when I test for existence, I just test for BOF on the recordset (especially since you're doing ADO also ...) If true, got records ...

    The Requery is what's missing ...

    ... End Select
    DBCNXN.Execute strSql
    MyFreakingSubForm.ReQuery
    DoCmd.GoToRecord , , acNewRec

    Also, SINCE you're doing ADO, you might want to put a little delay in for the damned ADODB control's lazy write issue ... About 5-6 secs does it ...

    BTW, have you had probs with stuff not showing up/displaying when it should?
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks Mike, but....

    The requery didn't (seem to) do anything. What would it be requery-ing on an unbound form?

    I go back and forth between count(*) and BOF. No real rhyme or reason to which one I use.

    I haven't heard of any "missing" data from my users, and they love to try to beat on me for any perceived problem.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    Thanks Mike, but....

    The requery didn't (seem to) do anything. What would it be requery-ing on an unbound form?

    I go back and forth between count(*) and BOF. No real rhyme or reason to which one I use.

    I haven't heard of any "missing" data from my users, and they love to try to beat on me for any perceived problem.
    Red, you can't on the unbound (for obvious reasons) ... I was talking about your subform ... That is where all the action is happening right?
    The subform contains two fields ...
    Count and BOF have nothing to do with each other ... Your "rsOrdDet!REC
    " and BOF do ... What is REC anyways? [Hint: Looks like a some I MIGHT use in the future ... ]
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Looks like I chose the wrong way to do this.
    The subform is unbound also, and isn't even "linked" to the
    main form.

    My thought was, put in the header record, save it, and then
    add all the detail lines, and save them as they are entered.
    This way, the user would see the order as it was being entered.

    Code:
    dim rs as recordset
    rs.open "Select count(*) as rec from mytable where somecondition"...
    If rs!rec=0 Then
    blah
    Where rec is just a column alias
    is the same basic thing as

    Code:
    rs.open "Select * from mytable where somecondition"
    If rs.BOF Then
    blah
    Inspiration Through Fermentation

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    Code:
    dim rs as recordset
    rs.open "Select count(*) as rec from mytable where somecondition"...
    If rs!rec=0 Then
    blah
    You DO realize that that won't work with ADO ... DAO, yes. ADO recordsets put you BEFORE the 1st record upon return ... You have to do a MoveFirst to get on the record ... That is why you have to test for BOF/EOF (take your pick) prior to making the move call ... RTE if no records otherwise ... As for your subform, it doesn't have to be "linked" (hopefully you mean the same way I think you mean) to the parent form ... And being completely unbound will not get you anywhere ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by M Owen
    You DO realize that that won't work with ADO ... DAO, yes. ADO recordsets put you BEFORE the 1st record upon return ... You have to do a MoveFirst to get on the record ... That is why you have to test for BOF/EOF (take your pick) prior to making the move call
    horsepucky, I say!

    I have 14 other instances of this same logic elsewhere in my app (without the movefirst) and it works just fine. And they're all ADO recordsets.

    That method always returns exactly 1 record. It's either a 0 or some number greater than 0.


    on the subform...
    I went to clearing the 2 fields after saving each entry. It works, but it's not visually pleasing.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    horsepucky, I say!

    I have 14 other instances of this same logic elsewhere in my app (without the movefirst) and it works just fine. And they're all ADO recordsets.

    That method always returns exactly 1 record. It's either a 0 or some number greater than 0.


    on the subform...
    I went to clearing the 2 fields after saving each entry. It works, but it's not visually pleasing.
    I know what it does ... It's been my experience that the ado recordset doesn't put you on the 1st record ... The docs say so, and every single one of my over 100 recordsets has not done so either ... So, I'll see your horsehocky and raise you a moohocky ...

    What version of Access hell are you stuck in?
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think it's something to do with cursor type. If I use adOpenForwardOnly, I don't use Movefirst, other wise I do.

    Here's another example that I just tested by stepping through.

    Code:
    Function FcstNextYear() As Boolean
      Dim rstFcst As New ADODB.Recordset, strSql As String
      strSql = "Select count(*) as Cnt from plan_fcst where year = " & YEAR(Now()) + 1
       rstFcst.Open strSql, DBCNXN, adOpenForwardOnly, adLockReadOnly
      If rstFcst!cnt = 0 Then
        FcstNextYear = False
      Else
        FcstNextYear = True
      End If
      rstFcst.Close
      Set rstFcst = Nothing
      Exit Function
      
    End Function
    I have no records for next year - "year(Now())+1", so rstFcst!Cnt =0.
    However, if I take the "+1" off, it returns 65334. Which, coincidentally, is the number of records in my forecast for this year.

    I believe we're on "bullfeathers"

    BTW: I'm on Access 2002 front end, Sql Server back end.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I was kinda thinking the same thing myself (cursor affecting the record position) ...

    Plucked chickenfeathers ...

    Weiner dog feathers ...

    Pootle Flump's feathers ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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