data base connection runtime error 3211

[jnewl]

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

 

[jnewl]

this helps, but i get errors when i try to do combo2.

i have an option group where i can select 1 of 3 regions. based on the
option code, i store a value in a text box on the form.
based on that value, i do a query and make table 1 that has the region name
and the system name.

based on the system value, i need to find the specialty of the provider in
combo 2.

so i built table 2 with region, system and specialty.

when i tried doing what was suggested, i get the error "record source =
"select * from [provider table] where region = " & system specified on this
form does not exist.

table 1 is called system table. table 2 is called provider table and has
region, system and specialty as fields. combo1 is called system and combo2
is called type of provider


tried doing the select as select specialty from [provider table] where
system = [system table].system but that did not work,

so still have probs.

thanks much

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

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