Form Looks up different Query

[naungsai]

Dear Friends

I have a form with a subform. A field in the subform looks up a value from a query.

When I go to new record in the form, I want to filter the query. But when I am navigating the previous or old records, I want to leave the query unfiltered.

Can someone tell me how to do it or refer a sample file?

Thanks in advance

Sai

 

[Kafrin]

You need some code in the OnCurrent event of your main form that tests for being on a new record and filters the dropdown accordingly. Since I don't think you can filter combo boxes, you may have to reset the RowSource each time, eg:

Private Sub Form_Current()
 
    With Me![I]SubformControlName[/I].Form![I]ComboBoxName[/I]
 
        If Me.NewRecord Then
            .RowSource = "SELECT * FROM [I]QueryName[/I] WHERE [I]CriteriaField[/I] = [I]Criteria[/I];"
        Else
            .RowSource = [I]QueryName[/I]
        End If
 
    End With
 
End Sub

Italics show the bits you need to customise with your fomr/control names.

 

[naungsai]

Dear Kafrin and friends

I have tried with your codes. But it does not work.

I think I should attached my file for you review.

SSO means SubStockOut
MSI means Main Stock In
MSO means Main Stock Out

The area I want to solve is at MSOIID(Main Stock Out Item ID) in SSOutItemSubform.

Many Thanks in advance

Naung Sai