Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    favorite way to add new record (a2k)

    Does anyone have a favorite way of adding a new record on a form?

    I've been using the NotInList event an unbound Find Record combo box to open a quick data entry form. Closing the form returns you to the requeried main form and the record you just entered. However, I started getting a Jet error --"The Microsoft Jet database engine stopped the process b/c you and another user are attempting to change the same data at the same time." -- that I could eliminate by not using the data entry form but it's left me needing a new way to add a record. using the NIL of the FindRecord cboBox.

    I was thinking of using the NIL of the unbound combo box to set the DataEntry property of the form and then the BeforeUpdate to change it back. But rather than recreate the wheel thought I'd ask around for favorite methods.

    Any words of advise?

    E

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: favorite way to add new record (a2k)

    Post your Not in list event, it may be something to do with that.
    The record may need saving in a specific way before requerying.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: favorite way to add new record (a2k)

    Here is both the NIL for the main form and the OnClose for the quick data entry form:
    Here's the NIL code

    Private Sub cboFindConsumer_NotInList(pstrNewData As String, pintResponse As Integer)

    Dim strTitle As String
    Dim intMsgDialog As Integer
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strMsg As String
    Dim ctl As Control
    Dim strEntry As String
    Dim strFormName As String
    Dim frm As Form
    Dim intReturn As Integer
    Dim intCS As Integer
    Dim intC As Integer

    strFormName = "frmConsumerEnter"
    strEntry = "Consumer"
    Set ctl = Me![cboFindConsumer]
    intCS = InStr(pstrNewData, ", ")
    intC = InStr(pstrNewData, ",")

    'Display a message box asking if the user wants to add
    'a new entry
    strTitle = strEntry & " Not in List"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + pstrNewData + strMsg2
    intReturn = MsgBox(strMsg, intMsgDialog, strTitle)

    Select Case intReturn
    Case vbNo
    pintResponse = acDataErrContinue
    ctl.Undo
    Case vbYes
    'Open form for adding new assistance
    ctl.Undo
    pintResponse = acDataErrContinue
    DoCmd.OpenForm strFormName
    'OpenHide (strFormName)
    With Forms(strFormName)
    Select Case True
    Case intCS > 0
    ![txtLastName] = left(pstrNewData, intCS - 1)
    ![txtFirstName] = Mid(pstrNewData, intCS + 2)
    Case intC > 0
    ![txtLastName] = left(pstrNewData, intC - 1)
    ![txtFirstName] = Mid(pstrNewData, intC + 1)
    Case Else
    ![txtLastName] = pstrNewData
    End Select
    End With
    End Select

    End Sub

    Here's the OnClose

    Private Sub cmdClose_Click()

    Dim frm As Form
    Dim strFormName As String
    Dim lngConsumerID As Long

    strFormName = "frmConsumer" 'set variable to name of original form

    If IsLoaded(strFormName) Then
    Set frm = Forms(strFormName) 'make frm refer to original form
    'set this variable to this Add form control

    lngConsumerID = Me![txtConsumerID]

    DoCmd.Close
    frm.Requery 'requery original form
    frm![cboFindConsumer].Requery 'requery the original form combo box
    frm.[cboFindConsumer] = lngConsumerID 'set the combo box value
    frm.RecordsetClone.FindFirst "[lngConsumerID] = " & frm![cboFindConsumer]
    frm.Bookmark = frm.RecordsetClone.Bookmark
    Else
    DoCmd.Close acForm, Me.Name, acSaveNo
    End If


    End Sub

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: favorite way to add new record (a2k)

    Bfore your Dim declarations, add the line:

    <pre>On Error GoTo errHandler"</pre>


    After the VbCase Yes, comment out the line:
    <pre><font color=448800>'pintResponse = acDataErrContinue</font color=448800></pre>




    At the end of the code after the last Exit Sub, add:

    <pre>errHandler:
    MsgBox Err.Description & " - " & Err.Number</pre>


    Re-run the code.
    See if an error occurs and what does it return. ??

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: favorite way to add new record (a2k)

    Not In List is generally used to add a record to a different table, not the one your form is based on. If you wish to do the latter, make sure that your main form isn't dirty before you start adding a record. Optimistic locking will also trigger this kind of error.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: favorite way to add new record (a2k)

    Do changes to an unbound control dirty the form? Is another kind of locking better for avoiding this error?

    E

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: favorite way to add new record (a2k)

    Changes to an unbound control don't dirty the recordset for the form. However, if you have a record open in edit mode and you add a new record to the same table, you're going to step on your own feet the minute. One way to handle it is to undo anything in the current record on the form and requery to get the record you just added.
    Charlotte

Posting Permissions

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