Problems encountered when changing RecordSource

[Doraemon]

I would like to ask if anyone know the correct way for 1.changing the RecordSource in a subform
2.Refresh the subform to show the corresponding data

I have 3 subforms in a form,
Add_Company_Record_Form (Form)
Add_Company subform
(subform - add company details)
Add_Department subform
(subform - add company's department details)
Add_Assets subform
(subform - add the corresponding department's assets details for the corresponding company)


I have tried adding the following codes in the 'Add_Department subform', somehow the records shown in ' Add_Assets subform' didn't refresh to show the corresponding assets for the department chosen in the 'Add_Department subform'.

Private Sub Form_Current()
...
[Forms]![Add_Company_Record_Form]![Add_Assets subform].Form.RecordSource = query_string

[Forms]![Add_Company_Record_Form]![Add_Assets subform].Form.Refresh
...
End Sub

Notes: query_string is the SQL defined in recent codes and I think it should be alright.
I doubt whether the last line works (.Refresh)?

Can anyone help? Thx a lots in advance.

 

[Edski]

Try
[Forms]![Add_Company_Record_Form]![Add_Assets subform].Form.[red]Requery[/red]

But I bet you tried that already, right?

 

[Doraemon]

Yes, I have tried that, but it doesn't work as expected.

 

[Edski]

Are the 3 subforms separate, or are they nested within each other? In other words, is the assets form a subform within the department's form and the department's form a subform within the company's form?

Some more code to look at might be good too.

 

[Doraemon]

I have now changed the 3 subforms to:
1 main form + 2 subforms
Main Form - Add_Company_Record_Form
Subform 1 - Add_Department subform
Subform 2 - Add_Asset subform

I would like to achieve the followings:
When a particular record in "Add_Department subform" is clicked, the "Add_Asset subform" will only display records which are related to the particular Department record chosen in "Add_Department subform".

In the other words, when the cursor is placed in different records in the Subform 1, the Subform 2 will automatically refresh to show the corresponding records.

How can this be achievable???

The following is part of my codes, but somehow it doesn't work. I intend to change the recordsource for refresh data in subform 2, but the records shown can't be refreshed.

Codes built under "Add_Department subform":
Private Sub Form_Current()
...
Dim SQLstmt As String
SQLstmt = "SELECT * FROM Assets_Table WHERE Dept_Code = '" & Me.Dept_Code & "' AND Company_Code = '" & Me.Company_Code & "'"

[Forms]![Add_Company_Record_Form]![Add_Asset subform].RecordSource = SQLstmt
...
End Sub


Should I use any other methods for doing this? And how?

Thanks for your help!

 

[Edski]

Doraemon,
What is the relationship between each of the 3 tables?
Eg.
1.
Company -> Department (1 to many)
Department -> Asset (1 to many)

OR
2.
Company -> Department (1 to many)
Company -> Asset (1 to many)

If it is the first one, why not embed the assests subform in the department subform? This way you don't need to write any fancy code.

 

[Edski]

I think I see your problem now.

You can't seem to requery / update the second subforms recordsource from within the first subform (based on the records that you select). I get the same error message.

There are a few other threads that touch on this but I couldn't find one that deals with this particular problem.

 

[Doraemon]

Thanks for all who have given help!

I finally overcome my problem by refering to the following thread:
thread705-659353

 

[Edski]

That solution works ok but it violates an important rule of database: A non-key field must be dependant upon the table's key.