problem in last level of cascade combo boxes

[eugzl]

Hi All.
In my child form I create multilevel cascade combo boxes. In case when I selected value in the last level of the cascade combo boxes I'm getting warning error message:
"The current field must match the join key '?' in the table the serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table."

For cascade combo boxes I used following queries:
Level 1
SELECT tblBrands.BrandID, tblBrands.Brand
FROM tblBrands;
Level 2


SELECT tblDeviceTypes.DeviceTypeID, tblDeviceTypes.DeviceType, tblDeviceTypes.BrandID
FROM tblDeviceTypes
WHERE (((tblDeviceTypes.BrandID)=[Forms]![fRequest]![cboBrand]));
Level 3
SELECT tblDevices.DeviceID, tblDevices.BrandID, tblDevices.DeviceTypeID, tblDevices.ModelID, [tblDeviceTypes].[DeviceType] & ' ' & [tblBrands].[Brand] & ' ' & [tblModels].[Model] AS Device
FROM tblModels INNER JOIN (tblDeviceTypes INNER JOIN (tblBrands INNER JOIN tblDevices ON tblBrands.BrandID = tblDevices.BrandID) ON tblDeviceTypes.DeviceTypeID = tblDevices.DeviceTypeID) ON tblModels.ModelID = tblDevices.ModelID
WHERE (((tblDevices.BrandID)=[Forms]![fRequest]![cboBrand]) AND ((tblDevices.DeviceTypeID)=[Forms]![fRequest]![cboDeviceType]));

I will appreciate if someone will show and explain how to fix the problem.
Thanks

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[eugzl]

Hi June. Thanks for reply.
The problem in fRequest form. It has 2 cascade combo boxes. One is related to device and another related to location. Both have same error when selected value on last level. The attached file is last version.

AddNewCascadeCB.zip
Thanks

[June7]

You have included lookup tables in form RecordSource. Then you allow edit of fields from lookup tables. This should not be allowed. Change the form RecordSource to tRequest. Remove/modify comboboxes bound to lookup table fields. The first 2 cascading comboboxes need to be modified to be UNBOUND. The Device combobox is correctly bound to tRequest DeviceID field.

Similar situation with the Location selection.

[eugzl]

Hi June7. Thanks for continue to help.
I changed RecordSource of the fRequest form to tblRequest. And Unbound cboBrand, cboDeviceType, cboFacility, cboBuilding, cboWing, cboFloor combo boxes. The previous error message is gone. But when I click Save button to save the entered data I got error message:


But that field has selected value. I clear controls in order to prepare form to give user ability enter new record. When I commented the line issue the rest code executed properly the all controls clear accept cboDevice and selected value in cboDevice saved in the table.


What need to do to fix the issues?
And another problem if it is possible in this post. If fRequest child form has RecordSource tblRequest table and some unbound combo boxes. By what way will I display data on all controls of this form in case when I double click record on ListBox of the parent fInventory form?
Thanks

[June7]

cboDevice is bound to field so if you clear it you are removing data from record. You 'clear' BOUND controls by simply moving to a new record row.

Only 'clear' the UNBOUND controls.

[eugzl]

Is it will be correct if I WITH statement will changed:

With r
     Me.cboBrand = Null
     Me.cboDeviceType = Null
     Me.cboFacility = Null
     Me.cboBuilding = Null
     Me.cboWing = Null
     Me.cboFloor = Null
End With


DoCmd.GoToRecord , , acNewRec

Thanks

[June7]

The With r is not applicable, it means nothing to this block of code because it doesn't do anything with r. Could do:

With Me
     .cboBrand = Null
     .cboDeviceType = Null
     .cboFacility = Null
     .cboBuilding = Null
     .cboWing = Null
     .cboFloor = Null
End With

[eugzl]

The Save event create duplicate record. How to fix that problem?

Thanks

[June7]

This is a bound form. Why are you opening a recordset object then adding a record to recordset? This is likely why you get 2 records. Record is added via form inputs and then also with recordset code. This would happen when clicking the button but using Tab or Enter from Location combobox will move to next record, in this case a new record, and duplication does not happen.

[eugzl]

My modified Save event. Now it looks like

Private Sub btnSaveClose_Click()
   
    Dim d As DAO.Database
    Dim r As DAO.Recordset
   
    'Caption ?Save" or :Close"??
    If Me.btnSaveClose.Caption = "Close" Then
        'close form
        DoCmd.Close acForm, Me.Name
    Else
      Set d = CurrentDb
      Set r = d.OpenRecordset("tblRequests", dbOpenDynaset, dbInconsistent)
     
        r.AddNew
        With r
            If VerifyFields Then
           
                !DeviceID = Me.cboDevice
                !RequestDate = Me.txtReqDate
                !FirstName = Me.txtFirstName
                !LastName = Me.txtLastName
                !DepartmentID = Me.cboDepartment
                !TicketNo = Me.txtTicketNo
                !Amount = Me.txtAmount
                !LocationID = Me.cboLocation
               
                r.Update
                r.Close
               
                'clear controls
                With Me
                    Me.cboBrand = Null
                    Me.cboDeviceType = Null
                    Me.cboFacility = Null
                    Me.cboBuilding = Null
                    Me.cboWing = Null
                    Me.cboFloor = Null
                End With
               
                DoCmd.GoToRecord , , acNewRec
               
                Set r = Nothing
                Set d = Nothing
            Else
               MsgBox "Please fill in the highlighted fields."
            End If
        End With
    End If
   
    Form_fInventory.lstRequest.Requery
    Form_fInventory.lstRequest = Me.RequestID
 
End Sub

What do you think? Is it correct? Or the WITH statement in both cases must to be With Me.

Thanks

[June7]

Form is BOUND to table. Inputs to controls are passed to table. Then your code creates another record with recordset AddNew. This is cause of duplication. I see no need for recordset.

Look again at my example for the With Me code.

[eugzl]

I modified Save event based on your suggestion.

Private Sub btnSaveClose_Click()
 
    Dim d As DAO.Database
    Dim r As DAO.Recordset
 
    'Caption ?Save" or :Close"??
    If Me.btnSaveClose.Caption = "Close" Then
        'close form
        DoCmd.Close acForm, Me.Name
    Else
        Set d = CurrentDb
        Set r = d.OpenRecordset("tblRequests", dbOpenDynaset, dbInconsistent)
 
        With Me
            If Len(Trim(Me.cboDevice)) > 0 And Me.cboDevice <> 0 Then
                !DeviceID = Me.cboDevice
            End If
 
            If Len(Trim(Me.txtReqDate)) > 0 Then
                !RequestDate = Me.txtReqDate
            End If
 
            If Len(Trim(Me.cboDepartment)) > 0 Then
                !DepartmentID = Me.cboDepartment
            End If
 
            If Len(Trim(Me.txtFirstName)) > 0 Then
                !FirstName = Me.txtFirstName
            End If
 
            If Len(Trim(Me.txtLastName)) > 0 Then
                !LastName = Me.txtLastName
            End If
 
            If Nz(Me.txtTicketNo, 0) > 0 Then
                !TicketNo = Me.txtTicketNo
            End If
 
            If Nz(Me.txtAmount, 0) > 0 Then
                !Amount = Me.txtAmount
            End If
 
            If Len(Trim(Me.cboLocation)) > 0 And Me.cboLocation <> 0 Then
                !LocationID = Me.cboLocation
            End If
 
            If VerifyFields Then
               
                !DeviceID = Me.cboDevice
                !RequestDate = Me.txtReqDate
                !FirstName = Me.txtFirstName
                !LastName = Me.txtLastName
                !DepartmentID = Me.cboDepartment
                !TicketNo = Me.txtTicketNo
                !Amount = Me.txtAmount
                !LocationID = Me.cboLocation
 
                r.Close
 
                'clear controls
                With Me
                    Me.cboBrand = Null
                    Me.cboDeviceType = Null
                    Me.cboFacility = Null
                    Me.cboBuilding = Null
                    Me.cboWing = Null
                    Me.cboFloor = Null
                End With
 
                DoCmd.GoToRecord , , acNewRec
 
                Set r = Nothing
                Set d = Nothing
            Else
                MsgBox "Please fill in the highlighted fields."
            End If
        End With
    End If
   
    Form_fInventory.lstRequest.Requery
    Form_fInventory.lstRequest = Me.RequestID
End Sub

Is now looks correct?
Thanks

[June7]

Should remove ALL lines that concern recordset. Don't need to declare, set, open, close recordset (nor the d variable). Eliminate all.

Then don't need the additional lines that set form field values a second time.

You still have the With Me block clearing controls wrong. Don't repeat the Me qualifier for each line within the block. Look at my example again. Also, With Me is already established earlier, don't need to repeat.

BTW, for a BOUND form record entry/edit is committed to table when: 1) close form or 2) move to another record or 3) run code to save.

Because this is a BOUND form, closing the form with Close button will save data to table but no validation will occur. If you mean for the Close button to close form without saving input, then those inputs need to be canceled and the initiated record has to be canceled (explore Me.Undo). Otherwise, use UNBOUND form for data input and a bunch of code to create record (for which a recordset is one method of accomplishing).

[Micron]

BTW, for a BOUND form record entry/edit is committed to table when: 1) close form or 2) move to another record or 3) run code to save.

Perhaps better to say
2) moving off the record.

?
Moving off of a subform record and clicking on a main form control can cause a record save without intentionally choosing another record - even if the main form control is not bound (i.e. it isn't part of another record).

Semantics?