Form Updating Combo Boxes

[CO711]

Hi,

I have encountered a problem developing a few applications. I have a form with 2 cascading combo boxes that filter the results into a subform.

My problem is trying to enter data into it. So far I have been entering it into the table, and it shows up in the Subform when there is no criteria, but When I try to find the specific record in the combo boxes their not updated as an opition to pick from. Am I going about this in the wrong way or what can I do?

Thanks for your time.

 

[KenHigg]

Sounds like you need to update (requery) the combo boxes each time you make an update to the subform data. ?

 

[CO711]

Thanks

Thanks, How do I go about doing that. I ahve tried a few differnt things but can't get it working.

 

[rainman89]

you can put the requery in the afterupdate of the subform.

me.parent!yourcomboboxname.requery

 

[CO711]

you can put the requery in the afterupdate of the subform.

me.parent!yourcomboboxname.requery

Can you be more specific about "parent" what's in that field?

and does it matter if I already have a list of commonds in the Afterupdate, where i put the new command. Thanks any help is greatly appreciated.

 

[rainman89]

take a look here

parent refers to the main form that your subform is a "child" of

the list i gave you shows you how to reference controls on forms, subforms and others.

and no. you can put more in the afterupdate of the form

 

[CO711]

hmm.

I've tried refrencing your comments here and it hasen't worked.

My Combo Boxes are entitled cboRowField and cboColumnField, the second one being depandent on the first.

The Code on the Subform's AfterUpdate is as follows:

Private Sub Form_AfterUpdate()
Me.Parent!productgroup.Requery
End Sub

should it be

Private Sub Form_AfterUpdate()
Me.Parent!productgroup.Requery
Me.Parent!cboRowField.Requery
Me.Parent!cboColoumnField.Requery
End Sub

Thanks for the help!

 

[rainman89]

can you post up a sample so we can see exactly what you are trying to do?

 

[CO711]

My Two combo boxes are cboRowField and cboColumField is dependent on the frist.

This is the Forms "On Open" Event Procedure:

Option Compare Database
Option Explicit
Private Sub cboColumnField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboNumericField = Null

strSQL = " SELECT DISTINCT tblDemo.NumericField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "' And "
strSQL = strSQL & " tblDemo.ColumnField = '" & cboColumnField & "'"
strSQL = strSQL & " ORDER BY tblDemo.NumericField;"

cboNumericField.RowSource = strSQL

strSQLSF = " SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "' And "
strSQLSF = strSQLSF & " tblDemo2.ColumnField = '" & cboColumnField & "'"


Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""

Me!sfrmForm.LinkChildFields = "RowField;ColumnField"
Me!sfrmForm.LinkMasterFields = "RowField;ColumnField"
Me.RecordSource = strSQLSF
Me.Requery
End Sub
Private Sub cboNumericField_AfterUpdate()
Dim strSQLSF As String

strSQLSF = " SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "' And "
strSQLSF = strSQLSF & " tblDemo2.ColumnField = '" & cboColumnField & "' And "
strSQLSF = strSQLSF & " tblDemo2.NumericField = " & cboNumericField

Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""

Me!sfrmForm.LinkChildFields = "RowField;ColumnField;NumericField"
Me!sfrmForm.LinkMasterFields = "RowField;ColumnField;NumericField"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
Private Sub cboRowField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboColumnField = Null
cboNumericField = Null

strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "'"
strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"

cboColumnField.RowSource = strSQL

strSQLSF = "SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "'"

Me!sfrmForm.LinkChildFields = "RowField"
Me!sfrmForm.LinkMasterFields = "RowField"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
Private Sub cmdShowAll_Click()
On Error GoTo err_cmdShowAll_Click
cboRowField = Null
cboColumnField = Null
cboNumericField = Null
Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""
Me.RecordSource = "tblDemo"
Me.Requery

exit_cmdShowAll_Click:
Exit Sub

err_cmdShowAll_Click:
MsgBox Err.Description
Resume exit_cmdShowAll_Click

End Sub
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = " SELECT DISTINCT tblDemo.RowField FROM tblDemo ORDER BY tblDemo.RowField;"
cboRowField.RowSource = strSQL

End Sub
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
Private Sub cmdPrintsfrm_Click()
On Error GoTo Err_cmdPrintsfrm_Click
Dim stDocName As String
Dim MyForm As Form
stDocName = "sfrmForm"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False
Exit_cmdPrintsfrm_Click:
Exit Sub
Err_cmdPrintsfrm_Click:
MsgBox Err.Description
Resume Exit_cmdPrintsfrm_Click

End Sub

cboRowField After update Event Procedure is
Private Sub cboRowField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboColumnField = Null
cboNumericField = Null

strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "'"
strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"

cboColumnField.RowSource = strSQL

strSQLSF = "SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "'"

Me!sfrmForm.LinkChildFields = "RowField"
Me!sfrmForm.LinkMasterFields = "RowField"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

cboColumnFields After Update is

Private Sub cboColumnField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

cboNumericField = Null

strSQL = " SELECT DISTINCT tblDemo.NumericField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "' And "
strSQL = strSQL & " tblDemo.ColumnField = '" & cboColumnField & "'"
strSQL = strSQL & " ORDER BY tblDemo.NumericField;"

cboNumericField.RowSource = strSQL

strSQLSF = " SELECT * FROM tblDemo2 "
strSQLSF = strSQLSF & " WHERE tblDemo2.RowField = '" & cboRowField & "' And "
strSQLSF = strSQLSF & " tblDemo2.ColumnField = '" & cboColumnField & "'"


Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""

Me!sfrmForm.LinkChildFields = "RowField;ColumnField"
Me!sfrmForm.LinkMasterFields = "RowField;ColumnField"
Me.RecordSource = strSQLSF
Me.Requery
End Sub

and the Subforms After Update Event Procedure is

Option Compare Database
Private Sub Form_AfterUpdate()
Me.Parent!productgroup.Requery
Me.Parent!cboRowField.Requery
Me.Parent!cboColoumnField.Requery
End Sub


The subform is based on on two tables tblDemo, tbl Demo2

is there any other information you need?

Thanks again

 

[rainman89]

is there any other information you need?

Thanks again

How about a sample of your database, not the code

 

[CO711]

I can't attach an example of the stuff I am working with but it's very similar to the one the this website http://www.candace-tripp.com/utterangel.aspx Titeled "Cascading Combo Boxes Database",
I designed mine based alot from this. I hope this can help for the code is the same, except I do not have a third combo box.

 

[rainman89]

I dont need any data, just so i can see your queries and forms

 

[CO711]

I appreciaite your persistance in helping me out, and want to give you all the information I can to fix this problem, but my computer litearly will not let me post anything or attach anything due to administrative restricitions.
My whole project is very very similar to the "cascading combo boxes" example I posted above.

There are no queries and my Forms are virtually the same. Thanks again for all your help and time!