Cascading Combo Box

[TryingMyBest]

Hi

I really hope you guys can help me with this one.

I have a form with 2 cascading combo boxes on it. cboProgrammeName feeds cboProjectName and the following text boxes:
txtProgrammeManager
txtProgrammeID

cboProgrammeName gets it's data from QRYProgrammeData (I need to filter the data to only include current programmes.)

cboProjectName then feeds:
txtResponsible person
txtEmailAddress
txtWBSCode

cboProjectName gets it data from QRYProjectData which is a maketable query...again, I need to filter the data in this combo box to include only those records that are current and only those that relate to the choice made in cboProgrammeName

My code is as follows:

Private Sub cboProgrammeName_AfterUpdate()

'populate text boxes
Me.txtProgrammeManager.Value = Me.cboProgrammeName.Column(3)
Me.txtProgrammeID.Value = Me.cboProgrammeName.Column(0)

'if programme name is empty remove all other related information from the
'text boxes, else run query for project name list
If IsNull(Me.cboProgrammeName) Then
Me.cboProjectName = Null
Me.cboProjectName.Enabled = False
Me.cboProjectName.Locked = True
Me.txtProgrammeManager = Null
Me.txtProgrammeID = Null
Me.txtResponsiblePerson = Null
Me.txtEmailAddress = Null
Me.txtWBSCode = Null
Else
Me.cboProjectName.Enabled = True
Me.cboProjectName.Locked = False
DoCmd.OpenQuery "QRYProjectData", acNormal, acReadOnly
End If

End Sub

Private Sub cboProjectName_AfterUpdate()
Me.txtResponsiblePerson.Value = Me.cboProjectName.Column(3)
Me.txtEmailAddress.Value = Me.cboProjectName.Column(4)
Me.txtWBSCode.Value = Me.cboProjectName.Column(6)
End Sub

My problem is that I am getting runtime error 3211 after I choose from cboProgrammeName the second and any subsequent times. I have figured out that this is because the text boxes that are fed from cboProjectName are referencing the table that is created by QRYProjectData.

I am stuck as to how else to code this but need to find a solution so that I can use queries to populate my combo boxes and then feed that information to text boxes. All information also needs to be retained on the form for furture use.

Is this a good way to do things or can Access offer a better solution.

I'm relatively new to Access so am unsure of the complete capability of it....using Access 2002.

Many thanks
Jo

 

[TryingMyBest]

Tried this and it didn't work

Me again!

I've added this code directly under Private Sub cboProgrammeName_AfterUpdate() and it's still not working so clearing the screen didn't help. How can I easily unlock the table to use it again without closing the DB?

'clear any data currently on the form
Me.cboProjectName = Null
Me.txtProgrammeManager = Null
Me.txtProgrammeID = Null
Me.txtResponsiblePerson = Null
Me.txtEmailAddress = Null
Me.txtWBSCode = Null

Thanks
Jo

 

[TryingMyBest]

I've got it!

I figured this out now