Hi David!
1. Even if I don’t understand what the use of this procedure is, you have to move the line DoCmd.SetWarnings False before the statement Select Case to cover all the cases. Take care to reset the SetWarnings to True before the exit of procedure (E.g. in case of Error). I never use the command RunSQL. In code I use the method Execute of a database object. […]Normally, you shouldn’t store all this info and the table AccountLedgerTbl should be a query on table TransactionTbl for report purposes.
2. This field came from the CatagorySubTbl where shouldn’t be there. I didn’t use that query but if you want to keep it, take the [CatagoryID] from the table SubCatagoryTbl and remove that field from the CatagorySubTbl.
3. Because in this subform, the CatagoryID, except of the normal values, could be Null or -1 (ALL), you have to give to the user the chance to choose the category for the new subcategory. In case of Null or -1 category, you can pass the control in a simple pop up form as dialog to save the new subcategory, checking the max stored SubCatagoryID before and after this “dialog box”. If those max values differs, then the new subcategory has added. So, you just have to say: Response = acDataErrAdded and the new subcategory will be the current in the list. In case of an actually CatagoryID (>0), you just step to an INSERT INTO statement in “stealth mode” and set again the Response to acDataErrAdded. If the user doesn’t want to add the new subcategory, just set the Response to acDataErrContinue. Because the cboSubCat.Column(1) returns Null in the NotInList event, have to keep this info (Category) in a private variable in the module of the subform or in a hidden textbox. I prefer the private variable way.
Code:
Private Sub cboSubCat_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim lngID As Long
Dim lngCat As Long
strMsg = "Sub category '" & NewData & "' is not Not in Current List, Would you Like to Add?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "New sub category") = vbYes Then
'Get the last SubCatagoryID
lngID = Nz(DMax("SubCatagoryID", "SubCatagoryTbl"), 0)
'Get the CatagoryID
lngCat = Nz(Me.Category, 0)
If lngCat > 0 Then
'We have an existing CatagoryID
On Error Resume Next
CurrentDb.Execute "INSERT INTO SubCatagoryTbl (CatagoryID, SubCatagoryName) " _
& "VALUES (" & lngCat & ", '" & NewData & "')", dbFailOnError
If Err Then
MsgBox Err.Description, vbExclamation, "New Sub Category"
Err.Clear
End If
Else
'CatagoryID could be -1 (ALL) or Null.
'User have to add the new subcategory via the specific form.
DoCmd.OpenForm "frmNewSubCat", , , , , acDialog, NewData
End If
'Comparison of "old" and "new" SubCatagoryID.
If lngID = Nz(DMax("SubCatagoryID", "SubCatagoryTbl"), 0) Then
'Nothing added
Response = acDataErrContinue
Else
'We have a new sub category
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If
End Sub
You can pass the new subcategory name to the pop up form through the OpenArgs property:
Code:
'Code of the frmNewSubCat
Private Sub Form_Load()
Me.txtSubCatName = Me.OpenArgs
End Sub
But, after a deeper look in your project, I think that is the right time to rebuild it from scratch. You have written much more code than it needs, and, you have used more controls and risky commands in forms than required. For example, while you already have the name of the TransType in the second column of the CboTransType, you put it in TxtTransType, or, you try to replace the navigation buttons of the forms and the ribbon with your own.
I recommend routing your energy to the creativity, not to the repetition.
Good luck with your project!
John