Cascading Combobox

Question

Copy from: https://softwareengineering.stackexchange.com/questions/158330/cascading-comboboxes

ok so i have a form, in Access 2010, with 1 Textbox and 3 ComboBoxes (1 Enabled & 2 Disabled).

the first ComboBox is not tied to the datasource but is subjective to the other 2 comboboxes. So i handled the Click event for the first Combobox to then make the other 2 enabled, and preload the 2nd ComboBox with a custom RowSource SQL Script dynamically built based on the 1st ComboBox Value.

This all works great for New information but when i goto review the information, via Form, its back to the New mode on the controls.

Question: What event do i need to handle to check if the current Form Data contains data for the Control Source of the Controls?

As i would express it in Logic (its a mix between C & VB, i know but should get the pt acrossed):

DataSet ds = Form.RowSet
if (ds = Null) then 
  cbo2.enabled = false
  cbo3.enabled = false
else
  cbo2.rowsource = "select id, nm from table"
  cbo2.value = ds(3)
  cbo3.value = ds(4)
end if
... do some other logic ...

Updated Logic - Still problem, cant catch for RecordStatus for some reason (gives 3251 Run-Time Error)

Private Sub Form_Current()
    Dim boolnm As Boolean: boolnm = (IsNull(txtName.Value) Or IsEmpty(txtName.Value))
    Dim booltype As Boolean: booltype = IsNull(cboType.Value)
    Dim boolfamily As Boolean: boolfamily = IsNull(cboType.Value)
    Dim boolsize As Boolean: boolsize = IsNull(cboType.Value)

    Dim rs As DAO.Recordset: Set rs = Me.Recordset
    MsgBox rs.AbsolutePosition

'    If rs.RecordStatus = dbRecordNew Then
'        MsgBox "New Record being inserted, but not committed yet!", vbOKOnly
'    Else
'        MsgBox rs(0).Name & " - " & rs(0).Value & vbCrLf & _
'            rs(1).Name & " - " & rs(1).Value & vbCrLf & _
'            rs(2).Name & " - " & rs(2).Value & vbCrLf & _
'            rs(3).Name & " - " & rs(3).Value
'    End If
    'MsgBox "Name: " & CStr(boolnm) & vbCrLf & _
            "Type: " & CStr(booltype) & vbCrLf & _
            "Family: " & CStr(boolfamily) & vbCrLf & _
            "Size: " & CStr(boolsize), vbOKOnly

End Sub

`If Me.NewRecord Then`, no need for a recordset. – Fionnuala Jul 26 '12 at 15:44 — Fionnuala, Jul 26 '12 at 15:44

score 0 · Answer 1 · answered Jul 26 '12 at 16:10

0

Here is the final result, with Remou's assistance, and this is only a precursor to the end result (which is out of the context of the question).

Private Sub Form_Current()

    If Me.NewRecord Then   <=======================
        cboType.Value = 0
        cboType.Enabled = True
        cboFamily.Enabled = False
        cboSize.Enabled = False
    Else
        Dim rs As DAO.Recordset: Set rs = Me.Recordset
        'get Family ID
        Dim fid As String: fid = rs(2).Value
        'Build SQL Query to obtain Type ID
        Dim sql As String
        sql = "select tid from tblFamily where id = " & fid
        'Create Recordset
        Dim frs As DAO.Recordset
        'Load SQL Script and Execute to obtain Type ID
        Set frs = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
        'Set Type ComboBox Value to Type ID
        cboType.Value = frs(0)
        cboType_Click 'Simulate Click Event since the Value has changed

        'Make sure all 3 Comboboxes are enabled and useable
        cboType.Enabled = True
    End If

End Sub

answered Jul 26 '12 at 16:10

GoldBishop

2,820
4
47
82

You are making life difficult for yourself. #1 You do not need a recordset to get a value from the current recordset. #2 You can refer to a field or control in the rowsource of a combo. #3 You can set the rowsource of a combo to an sql string. #4 I have never had to simulate click. – Fionnuala Jul 26 '12 at 16:14
my Click Simulation, performs other processes that i really dont want to copy/paste into this method. Alot of the design is due to the fact that i may not be the one supporting this document (hint, hint ;)) and i want to make it so the next person knows what is going on, per say. I try not to have too many implied references and try to be as explicit as i possibly can when its not something i will be supporting actively. – GoldBishop Jul 26 '12 at 17:55
I am not sure that working against the grain of MS Access will make it easier for the next person. – Fionnuala Jul 26 '12 at 18:37
I have sense moved away from alot of the recordset references anyways. As i have dealt with .net for awhile i forgot RecordSets in VB6 framework sucks and is overly segregated on functionality when it comes to the difference between ADO and DOA. – GoldBishop Jul 27 '12 at 21:07

Cascading Combobox

1 Answers1