To llkhoutx : SubForm's ReCordSource base on 3 Combos

[clerics]

To llkhoutx :I change some form in a sample of my program to English and here it is !

MainForm have 3 Combo that re the keys of 3 Tables
Change Combobox----> DATA of Subform will be automatically updated

Click button Edit ---> Edit Form will be shown ---> data will be taken from Tblmain where User click on the Subform !

This all work fine ! But I want ask more about Edit Form ! The Question I'd written in This Program ! (Cuz it is difficult to explain about it here )
Any one can help me pls !
Please give me some advices ! Im really thank for all of your helping !

 

[llkhoutx]

I couldn't get the program to run, you're doing something with an ActiveX that was not evident to me.

However, when you click the Edit button on the main form, you are passing an argument which is the filter for the form being opened. Add the following code to FEdit on the Form_Open event:

Me.Filter = Me.OpenArgs
Me.FilterOn = True
Me.Requery

That takes the argument you called the FEdit with and filters the the form. You'll be positioned on the correct tblmain record and ID1/2/3, Name1/2/3 and Info1/2 will display correctly. However, only ID1/2/3 and Info1/2 can be updated.

If you also want to modify ID1/2/3 and display Name1/2/3 correctly,

On ID1_AfterUpdate
Me.name1 = Me.ID1.Column(1)

On ID2_AfterUpdate
Me.name2 = Me.ID2.Column(1)

OnID3_AfterUpdate
Me.name3 = Me.ID3.Column(1)

Could you show me the best way to set up data for [name1],[name2],[name3] If ID1, ID2, ID3 are the TextBox (Not the Combobox)

doesn't make sense to me. ID1/2/3 are the combo boxes.

I hope what I've posted solves you problem.

 

[clerics]

thanks for replying ! I know what you mean !

Me.Filter = Me.OpenArgs
Me.FilterOn = True
Me.Requery

It's right but sometime Me.OpenArgs is Null so that makes problem

Inspite of that i 've used this code for Edit_Click(event)

Dim strSQL As String
strSQL = "(tblmain.ID1=[Forms]![Fmain]![F_Sub]![ID1]) AND (tblmain.ID2=[Forms]![Fmain]![F_Sub]![ID2] AND (tblmain.ID3=[Forms]![Fmain]![F_Sub]![ID3]))"
DoCmd.OpenForm "FEdit", , , strSQL

couldn't get the program to run, you're doing something with an ActiveX that was not evident to me.

I dont know why ! It is fine on my PC !

Could you show me the best way to set up data for [name1],[name2],[name3] If ID1, ID2, ID3 are the TextBox (Not the Combobox)

I mean if they "were" a TextBox ... how can i set data to [name1],[name2],[name3]. Cuz users cannot change ID1,ID2,ID3 so ComboBox is not necessary(If user changed ID , It could be same with another one)

If program cannot run ! i ll re-send it ! (or may be im using different Access's version with you - Access2000 <--- mine)

 

[llkhoutx]

strSQL = "(tblmain.ID1=[Forms]![Fmain]![F_Sub]![ID1]) AND (tblmain.ID2=[Forms]![Fmain]![F_Sub]![ID2] AND (tblmain.ID3=[Forms]![Fmain]![F_Sub]![ID3]))"
DoCmd.OpenForm "FEdit", , , strSQL

Your above strSQL is not concantenated properly and does not reference subforms controls properly. It should be

strSQL = "tblmain.ID1=" & [Forms]![Fmain]![F_Sub].form![ID1] & " AND tblmain.ID2=" & [Forms]![Fmain]![F_Sub].form![ID2] & " AND tblmain.ID3=" & [Forms]![Fmain]![F_Sub].form![ID3]

because ID1, ID2, and ID3 are variable controls and they change. They must be concantemated to your SQL string, not built into it. Furthermore, you are not referencing subform controls properly.

You already build (unbound) name1, name2 & name3 on FEdit on the Form_Open event. Similarily, when form FEdit is open and you change ID1, ID2, or ID3, use the respective ID1, ID2, or ID3 After_Update event to similarily update (unbound) name1, name2 name3, i.e.,

private sub ID1_AfterUpdate()
Me.name1 = Me.ID1.Column(1)
end sub

private sub ID2_AfterUpdate()
Me.name2 = Me.ID2.Column(1)
end sub

private sub ID3_AfterUpdate()
Me.name3 = Me.ID3.Column(1)
end sub

I still cannot execute your zipped file. I think that the problem might be associated with characters in your control names that do not translate to English and appear as little boxes. I have all versions of published Access and used A2000 with your unzipped file.

None the less, hopefully the above solves your problem.

 

[clerics]

Ya... Thanks alot .. It make my program get less mistakes !
You re right !
Your code is more clearly than mine

strSQL = "tblmain.ID1=" & [Forms]![Fmain]![F_Sub].form![ID1] & " AND tblmain.ID2=" & [Forms]![Fmain]![F_Sub].form![ID2] & " AND tblmain.ID3=" & [Forms]![Fmain]![F_Sub].form![ID3]

Furthermore, you are not referencing subform controls properly.

How can i make reference between Main Form and Sub Form When Sub Form base on 3 keys which be placed in MainForm (if there were 1 key only , it would be ok but not ) ---> so i ve to use Event and Modual for each Combo !

You already build (unbound) name1, name2 & name3 on FEdit on the Form_Open event. Similarily, when form FEdit is open and you change ID1, ID2, or ID3, use the respective ID1, ID2, or ID3 After_Update event to similarily update (unbound) name1, name2 name3, i.e.,
private sub ID1_AfterUpdate()
Me.name1 = Me.ID1.Column(1)
end sub

private sub ID2_AfterUpdate()
Me.name2 = Me.ID2.Column(1)
end sub

private sub ID3_AfterUpdate()
Me.name3 = Me.ID3.Column(1)
end sub

This is what I did in my Program ! But I dont want do like that any more ! It s not necessary to make ComboBox [ID1],[ID2],[ID3] ---> I want to create Textbox [ID1],[ID2],[ID3] in Edit Form ! So Do i have to use DAO to take [name1] , [name2] ,[name3] from 3 diferent Table (Tbl1{ID1,name1},Tbl2{ID2,name2},Tbl3{ID3,name3}) Or Is there any easier way to do this ? Looking for ideal from you ! Thanks alot

 

[llkhoutx]

You asked:

How can i make reference between Main Form and Sub Form When Sub Form base on 3 keys which be placed in MainForm (if there were 1 key only , it would be ok but not ) ---> so i ve to use Event and Modual for each Combo !

A valid reference to a control on a subform is

forms!MainFormName!subformNam.form!controlname

regardless of where it is used.

A valid reference to a control on a subsubform is

forms!MainFormName!subformNam.form!subsubFormName.form!controlname

regardless of where it is used.

Me.controlname

is valid (shorthand) only when used in a form module to reference a controlname on that instant form, whether it be a form, subform, or subsubform.

Report controls are referenced analogously.

You ask:

I want to create Textbox [ID1],[ID2],[ID3] in Edit Form !

Based on the mdb you posted, those three variables should always be combo boxes, not text boxes, because that is how you get name1, name2 and name3 out of separate tables. You probalbly need to look at the combo box NotInList event. There are extensive posts on this site re same, especially adding to the list programmatically.

 

[clerics]

Clearly !
thanks alot to llkhoutx !
I got much experience from you !
So i keep creating more function for my program !
thanks for all again !

 

[clerics]

ah ! Is there any one can run my Program well ? Please tell me ! ... Cuz i also want to test it on English ! thanks first !