SubForm be updated automatically by VBA , But ...

I have
tblmain(#ID1,#ID2,#ID3,info1,info2,info3.....)
Subform.RowSource="Select ID1,info1,info2,info3 from tblmain"
Combo1,Combo2,Combo3 allow users choose ID1,ID2,ID3
Click_Event of each Combo will give a Criteria call strwhere
---> and SubForm.RowSource=="Select ID1,info1,info2,info3 from tblmain where " & strwhere I create EditForm ( EditForm.RowSource="Select * from tblmain where ...") and will be active when users click "Edit" button (data is where user selected on Subform)

Problem is: after "where" what can i write ?
1/ tblmain.ID1=Form![SubForm]![ID1] --> it show alot of data of ID1
2/ tblmain.ID1=Form![MainForm]![Combo1] and tblmain.ID2=Form![MainForm]![Combo2] and tblmain.ID3=Form![MainForm]![Combo3] but user can let some of Combo null and choose directly in the list which be shown in SubForm

All of idea will help me alot to solve this ! thanks for your help

Use the subform Filter and FilterOn properties to requery the subform after a combobox update as follows

private sub combo1_AfterUpdate()
call SetReq '
end sub

private sub combo2_AfterUpdate()
call SetReq '
end sub

private sub combo3_AfterUpdate()
call SetReq '
end sub

private sub SetReq()
'concantenate Non-Null filteres, one by one
dim sFilter as string
sFilter = ""
if me!combo1 & "" <> "" then sFilter = " ID1 =" & me!combo1
if me!combo2 & "" <> "" then
'combo1 might be null
if sFilter & "" = "" then
sFilter = " ID2 =" & me!combo2
else
Sfilter = sFilter & " AND ID2 =" & me!combo2
end if
end if
'combo1 & 2 might be null
if sFilter & "" = "" then
sFilter = " ID3 =" & me!combo3
else
sFilter = sFilter & " AND ID3 =" & me!combo3
end if
end if
'all filters may be Null
if sFilter & "" = "" then
'display all records
me.filterOn = False
else
'display filters records
me.filter = sFilter
me.filterOn = True
end if
'a filter has been updated, therefore requery
me.requery
exit sub

I hope that this helps.

thanks for your help !But ... i ve already used 2 of Functions Checknull and Getstrwhere
in my program and it s work ! that near to your advise !

Public Function Checknull(fname As String, x As ComboBox, strWhere As String)

If IsNull(x) = True Then
strWhere = strWhere 'don't change
Else
If strWhere = "" Then
strWhere = strWhere & "((tblmain." & x.Name & ")= Forms![" & fname & "]![" & x.Name & "])" 'first time
Else
strWhere = strWhere & " AND ((tblmain." & x.Name & ")= Forms![" & fname & "]![" & x.Name & "])" 'next time
End If
End If


End Function

Public Function Getstrwhere(fname As String, a As ComboBox, b As ComboBox, c As ComboBox, strWhere As String)

Call Checknull(fname, a, strWhere)
Call Checknull(fname, b, strWhere)
Call Checknull(fname, c, strWhere)

End Function

Combo event_afterupdate :

strSQL = ""
Call Getstrwhere("F_main", Me.Combo1, Me.Combo2, Me.Combo3, strSQL) 'get SQL
Me.strWhere = strSQL
If Me.strWhere = "" Then
Me.strWhere = "((tblmain.info1) Like " & "'*'" & ");" 'ComboBox all NULL

End If

Me.Subform.Form.RecordSource = strSelect & strwhere
Me.Subform.Form.Requery


My problem is that :
I want to create Edit Form ( another form ) and be active by clicking a button call "Edit" . After clicking , Edit form will be shown with the data where users select on Subform .

But data of SubForm doesn't have enough key ( just only #ID1) Just like what i said in the 1st posting.
Please help me more friend ! Thanks alot because it take time to help me alot!

Attached is a zipped Access 2000 mdb illustrating what I understand that you want - open a form using a key from another form's subform.

Open Form frmCompany and select a company.

Not that this example assumes that the form opened by the Edit button has one or more existing records. If a record needs to be created, more coding is required.

Note also that the Edit button does not check for a non-null ID1.

Thanks alot ! there re something i got from you but cuz i'm studying at Japan.. So may be i didnt write English correctly (my problem is quite different with your thinking).However...
I had changed my program already ! I added #ID2, #ID3 to Subform ! That is the best way to solve it Just what i'm thinking but my teacher !

Combo event_afterupdate :

strSQL = ""
Call Getstrwhere("F_main", Me.Combo1, Me.Combo2, Me.Combo3, strSQL) 'get SQL
Me.strWhere = strSQL
If Me.strWhere = "" Then
Me.strWhere = "((tblmain.info1) Like " & "'*'" & ");" 'ComboBox all NULL

End If

Me.Subform.Form.Filter = strwhere 'i got this from you (thanks)
Me.Subform.Form.FilterOn = True


I'll trans my program sample to English as soon as i can and post it ! And Could you please check it for me ? thanks for anyway

THe zipped file I sent was incorrect, it used the ID1 off the main form as opposed to the subform for filtering the new form.

It 1:30AM here. I realized the mistake while trying to go to sleep. I apologize. I will correct it early this morning, just not this early.

I willl get glad to help you.

Me.strWhere = strSQL
If Me.strWhere = "" Then
Me.strWhere = "((tblmain.info1) Like " & "'*'" & ");" 'ComboBox all NULL

Click to expand...

should be

Me.strWhere = strSQL

If Me.strWhere & "" = "" Then

Click to expand...

Me.strWhere = "((tblmain.info1) Like " & "'*'" & ");" 'ComboBox all NULL

Otherwise, your code looks fine.

Attached is a revised mdb, picking ID1 off the subform and using it as criteria to open another form.

Do you code Access in Janapese? If so, wow!

Good luck.

i ve posted my program already ...here is link :
http://www.access-programmers.co.uk/forums/showthread.php?t=103326

Cuz I want to ask about another problem ...so i posted a new thread !
Please help me friend !