> hi
> i'm sorry but i didnt understand the code sent, but i think i got the problem
> right, so try to follow this:
> if you have
>
> Table1: tblRegions with 2 fields: regionID (primary key), and RegionName
> (text)
>
> Table2: tblSystems with 3 fields: systemID (primary key), and systemName
> (text), and regionID (each system is related to one of the regions, right?)
>
> Table3: tblSpecs with 3 fields: specID (primary key), and specName (text),
> and systemID (each spec is related to one of the systems, right?)
>
> now, on the form you set:
>
> combo1.rowsource="table1"
>
> combo2.rowsource="Select * From table2 Where RegionID=" & combo1
>
> combo3.rowsource="Select * From table3 Where SystemID=" & combo2
>
> then use the "AfterUpdate" event of combo1 to:
> combo2.Requery
>
> and use the "AfterUpdate" event of combo2 to:
> combo3.Requery
>
> now, whenever the user selects a value in combo1(a region), the list of
> available systems will be in combo2, and whenever the user selects a value in
> combo2(a system), the list of available specs will be in combo3
>
> hope i made it clear
> good luck
> Erez
>
> jnewl wrote:
> >i am building an application for our help desk to improve response time for
> >answering questions regarding insurance information. have an option group
> >that allows the help desk person to choose a specific region. based on that
> >option, a combo box generates a list of system names from our legacy systems.
> >based on that selection, another combo box generates a list of provider
> >specialties that are identified for this system.
> >for example region could be cny, system could be system1, and specialty
> >could be acupuncture. the selections work for all these categories. it
> >works the first time thru for the specialty but when i try a second pass of
> >different options, i get a data base runtime error 3211, when i select
> >provider specialty.
> >
> >the record locks on the form is set to no locks.
> >
> >thanks for your help
> >
> >i have tried closing tables, queries, and then reopen the queries with no
> >success.
> >
> >i have included the code
> >Dim sysvalue, spcltyvalue
> >
> >Private Sub Form_Open(Cancel As Integer)
> >DoCmd.Maximize
> >Forms![csd calculation program]![queryvar] = Null
> >Forms![csd calculation program].[systemvar] = Null
> >Forms![csd calculation program].[spcltyvar] = Null
> >
> >Forms![csd calculation program].[region category] = Null
> >
> >Me.System = ""
> >Me.[type of provider] = ""
> >
> >End Sub
> >
> >Private Sub region_category_AfterUpdate()
> >
> >DoCmd.Close acQuery, "qry find system"
> >DoCmd.Close acTable, "system table"
> >DoCmd.Close acTable, "tfeeschedules"
> >DoCmd.SetWarnings False
> >DoCmd.OpenQuery "qry delete records from provider table 2"
> >Me.System = ""
> >Me.[type of provider] = ""
> >Forms![csd calculation program]![queryvar] = Null
> >Forms![csd calculation program].[systemvar] = Null
> >Forms![csd calculation program].[spcltyvar] = Null
> >
> >Me.System.RowSource = "SELECT [system table].System FROM [system table];"
> >
> >DoCmd.Close acTable, "system table"
> >
> >If Forms![csd calculation program]![region category] = 1 Then
> > Forms![csd calculation program]![queryvar] = "CNY"
> >End If
> >If Forms![csd calculation program]![region category] = 2 Then
> > Forms![csd calculation program]![queryvar] = "ROCH"
> >End If
> >If Forms![csd calculation program]![region category] = 3 Then
> > Forms![csd calculation program]![queryvar] = "UTICA"
> >End If
> >
> >DoCmd.OpenQuery "qry find system"
> >End Sub
> >Private Sub system_AfterUpdate()
> >
> >sysvalue = Me![System].Column(0)
> >Forms![csd calculation program].systemvar = sysvalue
> >DoCmd.Close acQuery, "qry find system"
> >DoCmd.Close acTable, "system table"
> >DoCmd.Close acTable, "provider table"
> >DoCmd.Close acQuery, "qry find provider"
> >DoCmd.Close acTable, "tfeeschedules"
> >DoCmd.OpenQuery "qry find provider"
> >
> >End Sub
> >
> >Private Sub type_of_provider_BeforeUpdate(Cancel As Integer)
> >MsgBox "in before update"
> >
> >Me.[type of provider].RowSource = "select [provider table 2].specialty from
> >[provider table 2];"
> >spcltyvalue = Me![type of provider].Column(0)
> >
> >Forms![csd calculation program].spcltyvar = spcltyvalue
> >End Sub
> >
> >Private Sub close_form_Click()
> >On Error GoTo Err_close_form_Click
> > DoCmd.Close
> > DoCmd.Quit
> >Exit_close_form_Click:
> > Exit Sub
> >
> >Err_close_form_Click:
> > MsgBox "error closing form"
> > Resume Exit_close_form_Click
> >
> >End Sub
>
> --
> May all beings be happy.
>
> Message posted via AccessMonster.com
>
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1>
>