combo box to look up 2 subforms

[Allilue]

hello, (sorry, this is a long one...)

i have finally got my form working the way i want it, however, i have one glitch that i can't solve. my form has 2 subforms (Actual and Budget). there is a combo box on the form that the value selected will bring up a common code for both subforms (i.e., AAA). when both the Actual and Budget subform have code AAA, the records show up fine. It's just when only one of the subforms have the code AAA, the record will by default jump to the first record on the table. i think i just need to change the code to look this up separately. (i have a common Code table that links Actual and Budget together).

right now the code reads:

Private Sub cbo_SelectAssetCode_AfterUpdate()

'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[FA_Analy] = '" & Me![cbo_SelectAssetCode] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.[frm_Actuals Subform].SetFocus
End Sub

[FA_Analy] refers to the common code. I have the Actual Code named [AssetCode_Actual] and the Budget Code named [AssetCode_Budget]. i think i need to say something like if the combo box brings up code AAA and Actuals does not have that code, then to bring up a blank record with default values of zero. Again, righ now it's jumping to the first record when the code is not matched for both subforms.

Confusing? sorry i can't explain it better...i can clarify if necessary...

Thanks!

 

[930driver]

'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[FA_Analy] = '" & Me![cbo_SelectAssetCode] & "'"
If Me.RecordsetClone.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.[frm_Actuals Subform].SetFocus
End If
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Allilue]

Thanks!

one more question...

if i have a query that has a record returning a null field, and i want to replace the null field with a value from another table, how do i do this in the criteria section? for example, i have a query with 2 fields: AssetCode and Dept. For some records, Dept is null. so I created a second query made up of the first query and a table that contains AssetCode and Dept as well (but from a different source). i joined the AssetCode fields and want to replace the null field with Dept from the added table.

 

[Allilue]

This is what I am trying, but when i put in the criteria, the records that return null fields are gone completely.

IIf([qry_Actuals Subform]![Dept] Is Null,[Budget]![Dept],[qry_Actuals Subform]![Dept])

help!