By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

VBA/Access: How do I "abandon" a record when using linked forms?

P: n/a
Hi I have a slight problem in trying to set up 2 forms linked to

What I want to do is to inform a control on one form(Apps)
as to the value of a key of a 'new' record entered via a

In opening the second form I set up certian fields and controls
on it from the first. In doing so however this seemingly creates a
headache in coding for a situation where a user wants to abandon
entry of a new 'client', for example a NewClient button on the first
being selected by mistake for example.

If I set the Cancel in respect of the BeforeUpdate on the clients form
in the event of null fields, my current code will keep the cleints
form on the 'new' record, and will seemingly not allow an 'abort'
or move to another record. If I don't set the Cancel the Update will
quite happily insert 'blank' data into the database as part of the

A related problem is to do with key allocation.
If having setup a 'new' client by means of opening the clients
form, the user abandons entry of that new client, I would ideally
like to reclaim the key allocated. I had previosuly been using
AutoNumbers but in abandoning records the AutoNumber concerned seemed
quite happy to advance even when a record had been abandoned..

So how do I code the various event routines for the form so that I can
setup controls/fields on the Clients form from the Applications form ,
'blank' records going into the database and have some method for
deallocating key values?

(The current portions of code in respect of the forms concerned are


MultiUse = -1 'True
Attribute VB_Name = "Form_Apps"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database

Private Sub cmd_new_Click()
On Error GoTo Err_cmd_new_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Client"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms.Item(stDocName).donewentry Me.Form.Name, cmb_client.Name
Forms.Item(stDocName).Controls.Item("cmb_type") = 3
Exit Sub

MsgBox Err.Description
Resume Exit_cmd_new_Click

End Sub

Clients Form:
MultiUse = -1 'True
Attribute VB_Name = "Form_Client"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database
Dim newentry As Boolean
Dim cbForm As String
Dim cbControl As String
Dim alloc As Integer

Public Sub donewentry(cbF As String, cbC As String)
cbForm = cbF
cbControl = cbC
newentry = True
If Not newrec Then DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cmb_type_BeforeUpdate(Cancel As Integer)
If Not IsNull Then
If abandon = True Then abandon = False
End If
End Sub

Private Sub cmd_close_Click()
On Error GoTo Err_cmd_close_Click

Exit Sub

MsgBox Err.Description
Resume Exit_cmd_close_Click
End Sub

Private Sub Form_AfterInsert()
If newentry Then
abandon = False
Forms.Item(cbForm).Controls.Item(cbControl).Value = txt_id
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txt_name) And Not abandon Then
Cancel = True
abandon = True
alloc = alloc - 1
ElseIf abandon Then
Cancel = False
SendKeys ("{ESC}{ESC}")
If newentry Then doexit
End If
End Sub

Private Sub doexit()
On Error GoTo exit_err
DoCmd.Close acForm, Me.Form.Name

Exit Sub

If Err.Number = 2501 Then
Debug.Print "2501"
MsgBox Err.Number
End If
Resume exit_exit
End Sub

Private Sub Form_Current()
If Form.NewRecord Then
newrec = True
Me![txt_id] = alloc
alloc = alloc + 1
newrec = False
If abandon Then
abandon = False
End If
End If

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2501 Then Debug.Print "Ignored 2501"
Response = acDataErrContinue
End Sub

Private Sub Form_Load()
Rem Note alloc only set to 10 here as an example
Rem In reality the allocation of a key needs to be done in

alloc = 10 :
newentry = False
End Sub

Private Sub Form_Unload(uCancel As Integer)
If newentry And abandon Then
If MsgBox("Abandon?", vbYesNo) = vbNo Then
uCancel = True
End If
End If
End Sub

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.