Creating Two Records Instead Of Only One Whenever Form Unloads 1

[iuianj07]

Hello guys.

I have frmNewTransaction, wherein we enter data in whenever we receive a new deal (work) from our Client. On the form, I have 2 combo box fields for ASR (control source ASRStChID) and FileSource (control source FilSourceID).

The VBA on the Unload Event is:

Private Sub Form_Unload(Cancel As Integer)
Dim SitusID As Long
Dim ASRStID As Long 
Dim strSql As String
Dim strSql2 As String
Dim FileSourceID As Long
Dim strWhere As String
Dim cnt As Integer

  strWhere = "Situs_ID = " & Me.Situs_ID & " AND ASRStChID = 2"
Debug.Print strWhere
cnt = DCount("Situs_ID", "ASR_Loan_Status", strWhere)
If cnt = 0 Then
  

SitusID = Nz(Me.Situs_ID, 0)
ASRStID = Nz(Me.Status, 0)
FileSourceID = Nz(Me.File_Source, 0)

Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRStID

If SitusID <> 0 And FileSourceID <> 0 Then
  strSql2 = "Insert INTO File_Source_x_Loan ( SitusID, FileSourceID) Values "
  strSql2 = strSql2 & "(" & SitusID & "," & FileSourceID & ")"
  CurrentDb.Execute strSql2
  

If SitusID <> 0 And ASRStID = 2 Then
  strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
  strSql = strSql & "(" & SitusID & "," & ASRStID & ")"
  CurrentDb.Execute strSql
  'comment out after testing
  'MsgBox strSql
  'comment the following in if the sql looks good

CurrentDb.Execute strSql

  
End If
End If
End If


End Sub

And what this does is, when we receive a new deal, and we need to change the combo box for ASR to "2" (the Primary Key for "ASR Request Received") then it should create a new record on the ASR_Loan_Status table. The same for File Source, that whenever we update the combo box for File Source (actually any value) that it should create a new record on the File_Source_x_Loan table.

It correctly creates a record for the File Source combo box, but for some reason, whenever I a record should have the ASR combo box = 2, it auto creates 2 same records in the ASR_Loan_Status table instead of only one...

Can someone help me debug this code?

Any help is greatly appreciated.

Thank you,

 

[PHV]

CurrentDb.Execute strSql
is executed TWO times ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

wow... I didn't notice that I put that twice while I was trying to change the code earlier...

one day I'll hang myself because of these kinds of embarrassing mistakes...

thanks PHV...

 

[iuianj07]

hello,

I tried to modify the code again, to try and insert the current date whenever I create a new record in ASR_Loan_Status, so I changed the code to:

If SitusID <> 0 And ASRStID = 2 Then
  strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID, StatusDate ) Values "
  strSql = strSql & "(" & SitusID & "," & ASRStID & "," & Date() & ")"
  CurrentDb.Execute strSql

and when I tried to test it, it doesn't give the current date, instead it's giving a random time...

how could I fix this?

Thanks again...

 

[PHV]

Replace this:
& "," & Date() & ")"
with this:
& ",#" & Format(Date(), "yyyy-mm-dd") & "#)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886