Cascade combo box Not In List Handler

[zapzip]

hello-
I have 2 cascaded combo boxes on a form that I would like to add a new record to using Not In List event.

I have searched & found several Not in LIst Event Handlers but none that seem to handle cascaded combo boxes.

Would greatly appreciate anyone would knows of a source or could stear me in the right direction.

Thanks


Change. Remember. Enjoy. Do it often. Dotcom

 

[Remou]

Can You explain a little more how you see this working?

I can only see two separate events, one for each combo, even if the combos cascade.

 

[zapzip]

Thanks for your reply- let me see if I can explain it better:
The 1st level combo box has an event handler that allows a new value to be entered without problem. However, the 2nd level combo box gets an error if try to input a new value.

Run-time error '3201':

You cannot add or change a record becauyse a related record is required in table 'L_RR'. 
 
L_RR is top level table.

This I believe is because the 2nd cbo is trying to "write" a record without a corresponding value in top level table.

 

[zapzip]

I am using faq 702-5205 as GLOBAL Not In List Event handler.

http://www.tek-tips.com/faqs.cfm?fid=5205

 

[MajP]

Yes you are correct. You will have to show us your sql for the combo, and describe your table structure in table L_RR. Then show how you handle the not in list event. You need to add a foreign key.

cmbo2 gets it records from some table, lets call it tbl2. tbl2 is related to L_RR in a one to many relationship. There are many records in tbl2 that relate back to an L_RR record. Each record in tbl2 needs a foreign key that links to a primary key in L_RR. You have referential integrity. You can not create an orphan. So in order to create a new record in tbl2 you need to ensure you add a foreign key to the tbl2.

 

[zapzip]

Thanks for your ideas MajP & Remou.

Table structure:

Table L_RR
RR_ID	    PK  AutoNumber
RRName         Text

Table L_RRSub	PK AutoNumber
RRSub_ID		Text
RRSubName		Text
RRSubRRs_IDs	FK Number

The form (whose Record Source is a table) I am using has 2 combo boxes. They both have separate Not in List events.
The 1st cbo is associated with the primary (1st Level/ top level) table- and works fine. Code is:

Private Sub cboRentRollMonth_NotInList(NewData As String, Response As Integer)
    If AddToList(Me, "L_RR", "RRName") Then
        Response = acDataErrAdded
    Else
     Response = acDataErrContinue
       Me!cboRentRollMonth.Undo                            
        'Optional. Restores previous text.
    End If

End Sub

The 2nd cbo is associated with the 2nd level & generates error (Run-time error ‘3201’ described previously)..

Private Sub cboDepositNumber_NotInList(NewData As String, Response As Integer)    
    If AddToList(Me, "L_RRSub", "RRSubName") Then
        Response = acDataErrAdded
    Else
     Response = acDataErrContinue
       Me!cboDepositNumber.Undo 
         'Optional. Restores previous text.
    End If
End Sub

Additinal code &
AddToList routine can be found at
faq702-5205 tek- tips

http://www.tek-tips.com/faqs.cfm?fid=5205

Dose this help? I can post the code if you need it.

 

[MajP]

As I said, you try to add a record to the table:
If AddToList(Me, "L_RRSub", "RRSubName")
but you do not add a fk to this table. In order to make this work you need to add an "RRSubName" and the "RRSubRRs_IDs". You can not add a record to the table without the "RRSubRRs_IDs".

 

[zapzip]

Thanks for your reply MajP. I have include the AddToList function’s code. I understand the source of the error and what’s needed to correct it. But I don’t have a clue where to add the foreign key. The value is on the same form F_DepositSlip as cboRentRollMonth.

Your suggestions greatly appreciated.

Public Function AddToList(curForm As Form, tblName As String, _
                          fldName As String) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim frmNames As Collection, flg As Boolean, Cbx As ComboBox
   Dim frmMainName As String, curFrmName As String, CurCbxName As String
   Dim frmMain As Form, sfrm1 As Form, sfrm2 As Form, sfrm3 As Form
   Dim n As Integer, lvl As Integer, SQL As String
   
   Set frmNames = New Collection
   
   SQL = "SELECT TOP 1 * FROM " & tblName & ";"
   
   frmMainName = Screen.ActiveForm.Name
   curFrmName = curForm.Name
   CurCbxName = Screen.ActiveControl.Name
   
   'Acquire all form/subform names from Main Form to
   'subform in the chain, that holds the calling combobox.
   Do
      If curFrmName = frmMainName Then
         frmNames.ADD frmMainName
         flg = True
      Else
         frmNames.ADD curForm.Name
         Set curForm = curForm.Parent
         curFrmName = curForm.Name
      End If
   Loop Until flg
   
   'Setup Object Reference to each form/subform.
   'User can now reference any form in the chain.
   'frmMain - the main form.
   'sfrm1 - 1st subform level.
   'sfrm2 - 2nd subform level.
   'sfrm3 - 3rd subform level.
   'Note: subforms only go as deep as user has desgined.
   'Remaining sfrms will be empty.
   For n = frmNames.Count To 1 Step -1
      lvl = n - frmNames.Count - 1
      
      If lvl = -1 Then
         Set frmMain = Forms(frmNames.Item(n))
      ElseIf lvl = -2 Then
         Set sfrm1 = frmMain(frmNames.Item(n)).Form
      ElseIf lvl = -3 Then
         Set sfrm2 = sfrm1(frmNames.Item(n)).Form
      Else
         Set sfrm3 = sfrm2(frmNames.Item(n)).Form
      End If
   Next
   
   'Setup Object Reference to the Combobox.
   'User can reference the Combobox for other data.
   If frmNames.Count = 1 Then
      Set Cbx = frmMain(CurCbxName)
   ElseIf frmNames.Count = 2 Then
      Set Cbx = sfrm1(CurCbxName)
   ElseIf frmNames.Count = 3 Then
      Set Cbx = sfrm2(CurCbxName)
   Else
      Set Cbx = sfrm3(CurCbxName)
   End If

   'The Global NotInList Event
   Msg = "'" & Cbx.Text & "' is not in the ComboBox List!" & _
         "@Click 'Yes' to add it." & _
         "@Click 'No' to abort."
   Style = vbInformation + vbYesNo
   Title = "Not In List Warning!"
   
   If uMsg() = vbYes Then
      Set db = CurrentDb()
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      rst.AddNew
         If IsNumeric(rst(fldName)) Then
            rst(fldName) = Val(Cbx.Text)
         Else
            rst(fldName) = Cbx.Text
         End If
      rst.Update
      
      AddToList = True
   End If
   
End Function

 

[MajP]

This code only adds one field to a new record as seen here:

If IsNumeric(rst(fldName)) Then
rst(fldName) = Val(Cbx.Text)
Else
rst(fldName) = Cbx.Text
End If

You are going to have to add two fields. One for the value and one for the fk.

So you need to pass to your function the fk value and the name of the fk field.

Public Function AddToList(curForm As Form, tblName As String, fldName As String,[b] fldFKname as string, varFKval as variant[/b]) As Boolean

Since your combos are cascading I assume that you can push in the foriegn key value because it should probably be the primary key value in combo 1.

now add both your combo 2 text and the fk value to a new record

 rst.AddNew
  [b]rst(fldFKname) = varFKval[/b] 
  If IsNumeric(rst(fldName)) Then
      rst(fldName) = Val(Cbx.Text)
   Else
      rst(fldName) = Cbx.Text
   End If
  rst.Update

 

[MajP]

Now if you would still like to use this same code for other tables where you do not need to add a foriegn key, you can use the "Optional" key word on the parameters and "is missing function" to see if they pass in a parameter. This way the user can choose to pass in a foriegn key or use as originally planned.

ublic Function AddToList(curForm As Form, tblName As String, fldName As String, Optional fldFKname as string, Optional varFKval as variant) As Boolean

if not ismissing(varFKval) and not fldFKName = "" then
  rst(fldFKname) = varFKval
end if

 

[zapzip]

I have temporarily been pulled off this effort... I do GREATLY appreciate your suggestions and will be back> Soon I hope

Thanks

 

[zapzip]

Well at last getting back to working on this problem. Hope someone is still looking?

I modified the Event routine & code as noted below. Am getting an error “ERROR 3421 Data Type conversion error” at point noted in code (red). I have tried using different data types but can't get it to work. Any suggestions appreciated.


I modified the Not In list event as follows:

Private Sub cboDepositNumber_NotInList(NewData As String, Response As Integer)    
If AddToList(Me, "L_RRSub", "RRSubName", "RRSubRRs_IDs", _ "Me!cboRentRollMonth.Value") Then
        Response = acDataErrAdded
        MsgBox "AddtoList if is true"
    Else
     Response = acDataErrContinue
       Me!cboDepositNumber.Undo 'Optional. Restores previous text.
       MsgBox "AddToList if is false"
    End If
End Sub

I modified the code for function:

Option Compare Database

Public Function AddToList(curForm As Form, tblName As String, _
                          fldName As String, Optional fldFKname As String, _
                          Optional varFKval As Variant) As Boolean
.
.
.
.

rst.AddNew
    	If Not IsMissing(varFKval) And Not fldFKname = "" Then
            [COLOR=red] rst(fldFKname) = varFKval
 'get ERROR 3421 Data Type conversion error [/color red]
      End If
                  
      If IsNumeric(rst(fldName)) Then
            rst(fldName) = Val(Cbx.Text)
      Else
            rst(fldName) = Cbx.Text
      End If
         
   rst.Update
      
   AddToList = True

The table

L_RRSub table
	RRSub_ID		AutoNumber	Long Integer
	RRSubName		Text
	RRSubAbbrev		Text
	RRSubRRs_IDs	Number	Long Integer
	RRSubNameAbbrev	Text

 

[MajP]

1. In this line of code you are assigning a long to a variant.
rst(fldFKname) = varFKval
should not be a problem unless the value being passed in is not a variant which may give a data type mismatch. Example

Public Sub test()
Dim x As Variant
Dim y As Long
x = 1000
y = x
'no problem y = 1000
x = A
y = x
' no error but y = 0
x = ""
y = x
'gives data type mismatch
x = Null
y = x
'gives invalide use of null error

You might want to put in some error checking.
debug.print varFKval
rst(fldFKname) = varFKval

Also you may want to ensure that varFKval is not null or empty.

 

[zapzip]

Thanks for your reply. I think I am close to getting this handled. Here are my latest symptoms.

I am not passing the cbo value to the funciton. If I do a debug.print cboRentRollMonth or debug.print Me!cboRentRollMonth.value at the NotInList event it returns 90 in both cases. However, if I debug.print cboRentRollMonth I get a blank and if I debug.print Me!cboRentRollMonth.value I get Compile Error: Invalid use of Me Keyword.

Changing ME!cboRentRollMonth.value to cboRentRollMonth doesn't help. My form F_DepositSlip is bound to a table M_DepositSlip and the cboRentRollMonth is bound to a field (DepsoitSlipRRs_IDs) in that table. The cbo's Row Source is a Query (SELECT L_RR.RR_ID, L_RR.RRName FROM L_RR ORDER BY L_RR.RRName DESC; ) Bound Col: 1 Col count: 2 & Col Width 0;1

Thanks again for your assistance.

 

[zapzip]

I found the error! It was the extra quotes around "Me!cboRentRollMonth.value" replaced with simply cboRentRollMonth & no quotes.

Thanks again MajP. I have been working this a long time & really appreciated your help. Please post a "hello" so I can give you a well deserved star.

 

[zapzip]

Opps found you can go back & thank for a valuable post so no need for the "hello