The save event does not recognise the updated record set (1 Viewer)

[CraigOram]

I am in need of a little help please,,

When you add a new record using the below, txtID will be set to 0 on the main form, and when it saves the record set it will save to the next free record, however, on the table "Commentary", the CustomerID keeps saving as 0, "for a new record" (it is not seeing that this has been change) can you think of a way I can get the TxtID on the main form to update, with the correct ID, before the event triggers to save to the other table, which is tblcustomers1 that takes most of the data.....

Happy to attach my project if this will help?

KIndest Regards

Craig

Private Sub cmdSave_Click()
Dim Msg As String
Dim Ans As Variant
Dim sRefForm As String, sSQL As String
Dim iID As Integer
Msg = "Would you like to update the lead details?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
'--- only process Save if there is data in these fields
If IsNull(TxtClientName) Then
MsgBox "Need a Client Name"
TxtClientName.SetFocus
Exit Sub
End If
If IsNull(TxtRecieved) Then
MsgBox "Need a Recieved Date"
TxtRecieved.SetFocus
Exit Sub
End If
If IsNull(TxtCBTime) Then
MsgBox "Need a call back time"
TxtCBTime.SetFocus
Exit Sub
End If
If IsNull(TxtCBDate) Then
MsgBox "Need a call back time"
TxtCBTime.SetFocus
Exit Sub
End If
If IsNull(TxtReferralType) Then
MsgBox "Need a referral type"
TxtReferralType.SetFocus
Exit Sub
End If
If IsNull(TxtAdviser) Then
MsgBox "Need an adviser name"
TxtAdviser.SetFocus
Exit Sub
End If
If IsNull(TxtCallBackStatus) Then
MsgBox "Need an adviser name"
TxtAdviser.SetFocus
Exit Sub
End If
Dim rst As Recordset
'--- use the primary key (which is always an autonumber field) to find the record
'--- if it is a new record, this will find no records, as txtID will be 0
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers1 where ID=" & TxtID1)
If chkNew = True Then '--- do we add a new record and save an existing one
rst.AddNew
Else
rst.Edit
End If
'--- transfer data from text boxes to table fields
rst![Client Name] = TxtClientName
rst!Received = TxtRecieved
rst![CB Date] = TxtCBDate
rst![CB Time] = TxtCBTime
rst![Referral Type] = TxtReferralType
rst!Adviser = TxtAdviser
rst![PB Name] = TxtPBName
rst![PB SRN] = TxtPBSRN
rst![PB Area Manager] = TxtPBAreaManager
rst![PB Regional Manager] = TxtPBRegionalManager
rst![Contact 1] = TxtContact1
rst![Contact 2] = TxtContact2
rst![Contact 3] = TxtContact3
rst![Lead Progress] = TxtLeadProgress
rst![Call back status] = TxtCallBackStatus
rst![Future Call back date] = TxtFutureCallBackDate
rst![Lead Status] = TxtLeadStatus
rst![Closed Date] = TxtClosedDate
rst![Pre Cover] = TxtPreCover
rst.Update '--- save the record
rst.Close '--- close the recordset
'save referral form - TC
sRefForm = Forms("OpenLeads").Controls("Text7").Value
iRefLength = Len(sRefForm)
iSeq = 0
iStr = 1
sTeam = DLookup("Team", "tblAdvisor", "Advisor = '" & Me.TxtAdviser & "'")
sSQL = "UPDATE tblCustomers1 SET Team = '" & sTeam & "' WHERE (tblCustomers1.ID=" & Me.TxtID1 & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True
Do While iStr <= iRefLength
sComm = Mid(sRefForm, iStr, 255)
sSQL = "INSERT INTO commentary (CustomerID, Sequence, Comment) " & _
"VALUES (" & Me.TxtID1 & "," & iSeq & ",'" & sComm & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True
iSeq = iSeq + 1
iStr = iStr + 255
Loop
Set rst = Nothing '--- reclaim the memory the recordset was using
chkNew = False '--- reset the new flag
'--- enable the list box and the Add New button and the Close button
'--- must be done before moving focus to the list box
lstData1.Enabled = True
cmdAddNew.Enabled = True
cmdExit.Enabled = True
Calendar.Enabled = False
UpdatePB.Enabled = False
UodatePBDets.Enabled = False
'--- make sure the newest data is in the list box
lstData1.Requery
'--- set the focus to the list box
lstData1.SetFocus
lstData1 = lstData1.ItemData(0)
Call lstData1_AfterUpdate
'--- disable the text boxes and the Save button, and make Edit button enabled
TxtRecieved.Enabled = False
TxtCBDate.Enabled = False
TxtCBTime.Enabled = False
TxtReferralType.Enabled = False
TxtAdviser.Enabled = False
TxtClientName.Enabled = False
TxtPBName.Enabled = False
TxtPBSRN.Enabled = False
TxtPBAreaManager.Enabled = False
TxtPBRegionalManager.Enabled = False
TxtContact1.Enabled = False
TxtContact2.Enabled = False
TxtContact3.Enabled = False
TxtLeadProgress.Enabled = False
TxtCallBackStatus.Enabled = False
TxtFutureCallBackDate.Enabled = False
TxtLeadStatus.Enabled = False
TxtClosedDate.Enabled = False
TxtPreCover.Enabled = False
cmdSave.Enabled = False
cmdEdit.Enabled = True
Click1.Enabled = True
Click2.Enabled = True
Advisor.Enabled = True
LeadStatus.Enabled = True
LeadCount.Enabled = True
PBMSearch.Enabled = True
EmailClient.Enabled = False
CmdBookCall.Enabled = False
Command57.Enabled = False
Text7.Enabled = False
Case vbNo
GoTo Quit:
End Select
Quit:
End Sub

 

[thechazm]

Please attach your project and I'll have a look.