subform combobox 1

[fule12]

On main form i have two combobox , subform is in datasheat mode and one of filed is combobox, so this combo in subform is filtered by two combo from main form. problem is when go to another main form record or close and open form subform combo is empty until i dont click on any field in subform.
This is code for filtering subform combo :

rivate Sub subSpReqDt_Enter()
On Error GoTo err_Subform

Dim strSQL As String
strSQL = "SELECT tblSpProduct.fkMaker_ID, tblSpProduct.fkPart_ID, tblSpareItm.SpareItm_ID," _
& " tblSpareItm.SpItemName, tblSpareItm.SpItemPart, tblSpareItm.IssaCode, tblSpareItm.Units" _
& " FROM tblSpProduct INNER JOIN tblSpareItm ON tblSpProduct.SpProduct_ID = tblSpareItm.fkSpProduct_ID" _
& " WHERE [fkMaker_ID]=" & Me.cboMaker & " AND [fkPart_ID]=" & Me.cboPart

Forms!frmSpReq!subSpReqDt![cboItemName].RowSource = strSQL
Forms!frmSpReq!subSpReqDt![cboItemName].Requery

exit_Subform:
Exit Sub
err_Subform:
MsgBox Err.Description, vbOKOnly, " Error : " & Err.Number
Resume exit_Subform

End Sub

I know this happened because RowSource of subform combo is populating on Enter event for subform.
maybe someone have better solution how to archive this filtering.
p.s - I tryd on Current Event of Parent form, this way will work for record what was enter bifore , but when i create new Parent record , combobox on subform is empty.

Thanks

Fule

 

[TheAceMan1]

How are ya fule12 . . .
[ol][li]On a form with nested subforms, the forms open from the inner most subform out to the mainform.[/li]
[li]The above is true when changing records on a parent form. the forms change record from the inner most subform out to the mainform.[/li]
[li]When you change the RowSource of a combo, it automatically requeries.[/li][/ol]
So your choice of the [blue]On Current[/blue] event of the Parent form is the best way to go.

As for a new parent record and depending on wether the forms are linked, use the [blue]NewRecord[/blue] property of the parent to determine when selections have been made in the combo's and requery the subform combo.

See Ya! . . . . . .

 

[fule12]

Hi AceMan ,

Thanks for your reply ,
Can you give me example for NewRecord property ,
or if is maybe easy can i send you example of my'Copperfield' form ?

Thanks

Fule

 

[TheAceMan1]

Roger That fule12 . . .

Too make things easy I need to know what the data type of the two combo's on mainform are . . . numeric/text ?

It appears [blue]cboMaker[/blue] & [blue]cboPart[/blue] are text . . . is this correct!

See Ya! . . . . . .

 

[fule12]

Hi , AceMan
Sorry for delay to reply to you , different time zone.

cboMaker & cboPart are Numeric.
Thay are fKey from another two tables

Thanks


Fule

 

[TheAceMan1]

Roger That fule12 . . .

The following code calls a common routine that [blue]validates both combo's and updates the RecordSource of cboItemName if selections have been made[/blue] (NewRecord property not required!).
[ol][li]Disable or remove the code you have in the [blue]OnEnter[/blue] event.[/li]
[li]In the code module for the [blue]parent[/blue] form, copy/paste the following:[/li]

[blue]Public Sub SetSpReqDt()
   Dim SQL As String
   
   If Trim(Me!cboMaker & "") <> "" And _
      Trim(Me!cboPart & "") <> "" Then
      SQL = "SELECT tblSpProduct.fkMaker_ID, tblSpProduct.fkPart_ID, " & _
                   "tblSpareItm.SpareItm_ID, tblSpareItm.SpItemName, " & _
                   "tblSpareItm.SpItemPart, tblSpareItm.IssaCode, " & _
                   "tblSpareItm.Units " & _
            "FROM tblSpProduct " & _
            "INNER JOIN tblSpareItm " & _
            "ON tblSpProduct.SpProduct_ID = tblSpareItm.fkSpProduct_ID " & _
            "WHERE [fkMaker_ID]=" & Me.cboMaker & " AND " & _
                  "[fkPart_ID]=" & Me.cboPart
      Forms!frmSpReq!subSpReqDt.Form![cboItemName].RowSource = SQL
   End If

End Sub[/blue]

[li]Next . . . in the [blue]AfterUpdate[/blue] event of both combo's (cboMaker, cboPart) and the [blue]OnCurrent[/blue] event of the parent form, copy/paste the following:

[blue]   Call SetSpReqDt[/blue]

[/li][/ol]
Thats it! . . .

See Ya! . . . . . .

 

[fule12]

Hi AceMan !!!!
Thanks , is working now like chream !!!!
only On Current event of the parent form i add

Me.subSpReqDt.Requery

p.s - I like your way of writing the code

Man Thanks 1more time, i was afraid i will become young & bald

Fule