Not-in-List Add Record

[JohnHarkins]

When I set up an event to handle the not-in-list for a combo box, I first invoke a subform which lets me add the data to the lookup table. So far, so good. Since I wanted to pick up the value that the user entered and stuff it into the subform for the lookup table, I do something like this in the on-open event for the subform:
Me.txtPgmName.Value = [Forms].[sfrmLoookupTable].cboPgmName
...in order to stuff the new value that the user put in the main form that was not found in the list and then...
me.cboPgmType = null
me.cboPgmStyle = null
... to empty PgmType and PgmStyle and be ready for the user to put a value (or select it from a combo box).

First, there's probably a better way to handle this, but I'm not sure just how. Moreover, I'd like to be able to do a requery for the main form so that when I close out the subform, the main form will reflect the newly created values. Any suggestions?

Finally and most importantly, I want to be able to share the subform between adding a new record (as above) and changing a record. Why would I want to do that? Well, I thought I could save maintaining two separate forms that are the same except for the circumstance under which it is called. So, the problem becomes, how do I know that I am entering the subform to add a new record or modify an existing one? Do I interrogate end-of-file? Can I check some other property that would indicate that I'm sitting on an existing or new record?

Thanks!




John Harkins

 

[GenieTR]

Here is a NotinList event procedure

Private Sub CboCategory_NotInList(NewData As String, Response As Integer)
Dim strMsg, CR As String
Dim result
CR = Chr$(13)
strMsg = "'" & NewData & "' is not in the list." & CR & CR
strMsg = strMsg & "Do you wish to add the category to the list?"

If NewData = " " Then Exit Sub
NewData = Proper(NewData)
If MsgBox(strMsg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmCategoryNotinList", , , , acFormAdd, acDialog, NewData
End If
result = DLookup("[CategoryName]", "tblCategory", "[CategoryName]=" & QUOTE & adhHandleQuotes(NewData, QUOTE) & QUOTE)
If isnull(result) Then
Response = acDataErrContinue
MsgBox "Please try again"
Else
Response = acDataErrAdded
End If

End Sub

Const QUOTE = """"

add this to the module section

Function adhHandleQuotes(ByVal varvalue As Variant, ByVal strDelimiter As String) As Variant

' From Access 97 Developer's Handbook
' by Litwin, Getz, and Gilbert (Sybex)
' Copyright 1997. All rights reserved.

' Replace all instances of strdelimiter with varValue with TWO instances,
' thereby handling the darned quote issue once and for all.

' Returns Null if varValue was Null, otherwise
' returns varValue with all instances of strDelimiter
' replaced with two of each.
' adhHandleQuotes("This 'is' a test", "'") returns
' "This ''is'' a test"

adhHandleQuotes = adhReplace(varvalue, strDelimiter, strDelimiter & strDelimiter)
End Function