OK abbyanu . . . . here we go! . . . .
Be aware since [blue]your combobox names are duplicates[/blue] of the textboxes, the wrong values may be returned for use. [purple]
Be sure to backup the database so you can come back to square one if you have to.[/purple]
[blue]
Special Note: Check spelling and/or substitute names in [purple]purple[/purple] throughout the code. If something doesn't work this is the first thing to check![/blue]
The code requires detection of wether or not forms are open. So in a New Module in the module window, copy/paste the following funtion:
Code:
[blue]Function IsOpenFrm(frmName As String) As Boolean
Dim cp As CurrentProject, Frms As Object
Set cp = CurrentProject()
Set Frms = cp.AllForms
If Frms.Item(frmName).IsLoaded Then
If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
End If
Set Frms = Nothing
Set cp = Nothing
End Function[/blue]
Next, we take care of the command button. The code allows you to open the form [blue]Entity[/blue] if its not already, or [blue]requery the subform[/blue] (criteria is the main control) if it is. In this way, when both forms (Location & Entity) are open, you can continually make selections from the comboboxes, hit the button and see the changes. So, in the event where you have your button code (I believe its the [blue]Click Event[/blue]), substitute the following code:
Code:
[blue] Dim frm As Form, sfrm As Form
Dim frmName As String, sfrmame As String
frmName = "[purple][b]Entity[/b][/purple]"
sfrmName = "[purple][b]Entity_Evaluation[/b][/purple]"
[green]'Detect if form Entity is open. If it is then we can
'simply Requery the subform since control is via criteria.
'If its not, Open the form.[/green]
If isopenfrm(frmName) Then
Set frm = Forms(frmName)
Set sfrm = frm(sfrmName).Form
sfrm.Requery
Set sfrm = Nothing
Set frm = Nothing
Else
DoCmd.OpenForm frmName
End If[/blue]
Now . . . the heart of the secnario. A query is used for the [blue]recordSource[/blue] of subform [blue]Entity_Evaluation[/blue] which uses user defined function calls as criteria. In [blue]Query Design View[/blue], make a query that includes all the fields in the subform [blue]Entity_Evaluation[/blue].
Then [blue]on the criteria line[/blue] for [blue]location_id[/blue] copy'paste the following (location_id is the name of the combobox on subform [blue]Location_Internal_Control[/blue] . . . double check the spelling):
Code:
[blue]PingID("[purple][b]location_id[/b][/purple]")[/blue]
Do the same for [blue]period_id[/blue]:
Code:
[blue]PingID("[purple][b]period_id[/b][/purple]")[/blue]
Save, name, and close the query.
Open the subform [blue]Entity_Evaluation[/blue] in [blue]Design View[/blue] and change the R[blue]ecordSource[/blue] to the name of the [blue]query you just made[/blue]. It should now be in the [blue]dropdown list[/blue] for the RecordSource.
While in design view, add the following code to the [blue]
On Current Event[/blue] of the form. This code takes care of setting the defaults if no records are returned via the values of [blue]location_id & period_id[/blue] of subform [blue]Location_Internal_Control[/blue]:
Code:
[blue] Dim frm As Form, sfrm As Form, RecCnt As Long
Dim frmName As String, sfrmName As String
Dim cbxLocName As String, cbxPrdName As String
frmName = "[purple][b]Location[/b][/purple]"
sfrmName = "[purple][b]Location_Internal_Control[/b][/purple]"
cbxLocName = "[purple][b]location_id[/b][/purple]"
cbxPrdName = "[purple][b]period_id[/b][/purple]"
RecCnt = Me.RecordsetClone.RecordCount
[green]'Qualify if Location_Internal_Control is in control and
'no records were returned. If not set defaults[/green]
If isopenfrm(frmName) And RecCnt = 0 Then
Set frm = Forms(frmName)
Set sfrm = frm(sfrmName).Form
Me!location_id.DefaultValue = sfrm(cbxLocName)
Me!period.DefaultValue = sfrm(cbxPrdName)
Set sfrm = Nothing
Set frm = Nothing
End If[/blue]
Finally the [blue]User Defined Function[/blue] used to set criteria in the query. Add it to the same module in the modules window:
Code:
[blue]Public Function PingID(cbxName As String)
[green]'Note: cbxName is the combobox name passed from the query.
'Its used to return the proper value form the associated
'combobox in subform Location_Internal_Control for criteria.
'As such, this routine runs twice. Once for location_id then
'again for period_id.[/green]
Dim frm As Form, sfrm As Form
Dim frmName As String, sfrmName As String
Dim cbxLocName As String, cbxPrdName As String
frmName = "[purple][b]Location[/b][/purple]"
sfrmName = "[purple][b]Location_Internal_Control[/b][/purple]"
[green]'Combobox names from form Location_Internal_Control[/green]
cbxLocName = "[purple][b]location_id[/b][/purple]"
cbxPrdName = "[purple][b]period_id[/b][/purple]"
If isopenfrm(frmName) Then
Set frm = Forms(frmName)
Set sfrm = frm(sfrmName).Form
[green]'Verify if combobox data is available. If data available,
'return appropriate combobox value, otherwise nothing.[/green]
If Len(sfrm(cbxLocName) & "") > 0 And _
Len(sfrm(cbxPrdName) & "") > 0 Then
PingID = frm(cbxName) [green]'Rerurned Criteria Value[/green]
End If
Set sfrm = Nothing
Set frm = Nothing
End If[/blue]
Thats it! Give it a whirl and let me know . . .
Note: There's alot I could say about the relationships graphic you sent, but as long as its working I'll leave it alone. On question though . . .
Are you finding your having problems with data entry?
See Ya! . . . . . .
