favorite way to add new record (a2k)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Bfore your Dim declarations, add the line:

On Error GoTo errHandler"

After the VbCase Yes, comment out the line:

'pintResponse = acDataErrContinue

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

errHandler:
MsgBox Err.Description & " - " & Err.Number

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

Reply | Quote

Bfore your Dim declarations, add the line:

On Error GoTo errHandler"

After the VbCase Yes, comment out the line:

'pintResponse = acDataErrContinue

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

errHandler:
MsgBox Err.Description & " - " & Err.Number

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

E

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

E

Reply | Quote

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.

Reply | Quote