Hi I have a slight problem in trying to set up 2 forms linked to
tables.
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
second(Clients)
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
update.
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
both
setup controls/fields on the Clients form from the Applications form ,
avoid
'blank' records going into the database and have some method for
allocating,
deallocating key values?
(The current portions of code in respect of the forms concerned are
below)
FMNT80
Apps_Form:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
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_cmd_new_Click:
Exit Sub
Err_cmd_new_Click:
MsgBox Err.Description
Resume Exit_cmd_new_Click
End Sub
<ENDS>
Clients Form:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
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
DoCmd.Close
Exit_cmd_close_Click:
Exit Sub
Err_cmd_close_Click:
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
doexit
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
Form_Current
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_exit:
Exit Sub
exit_err:
If Err.Number = 2501 Then
Debug.Print "2501"
Else
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
Else
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
Form_Current?
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
<ENDS>
