Button linking a continuous sub-form to a form (both built around several tables)

[Gari]

Dear All,

I’ve been using this forum since three weeks now and I always had the answer to my questions. So this encourages me to use it again but I hope I am not annoying anybody.

I am trying to develop databases for my company but I have little knowledge of Access, which means that I am learning while building them. I don’t post here my questions before having turned around the problem and done some research on my side. So I greatly appreciate your advices, which have so far helped me a lot. I hope you don’t bother helping me further.

So here is my question:

My tables are as follows:
[Company] – Primary Key: [Account Number]
[Currency] – Primary Key: [Currency ID]
[Group] – Primary Key: [Group ID]
[Owner] – Primary Key: [Owner ID]
[Project Lines – Existing] – Primary Key: [ProjectID]
[Project Lines – Oustanding] – Primary Key: [ProjectID]
[Project Lines – Proposed] – Primary Key: [ProjectID]
[Projects] – Primary Key: [ProjectID]
[RM] – Primary Key: [RMID]

The relationships of the tables are as follows:
[Group] One-to-many [Company] through [Group ID]
[RM] One-to-many [Company] through [RMID]
[Company] One-to-many [Projects] through [Account Number]
[Owner] One-to-many [Projects] through [Owner ID]
[Currency] One-to-many [Projects] through [CurrencyID]
[Project Lines – Existing] One-to-one [Projects] through [ProjectID]
[Project Lines – Oustanding] One-to-one [Projects] through [ProjectID]
[Project Lines – Proposed] One-to-one [Projects] through [ProjectID]

I have a main form called ‘Company Projects List’, which regroup data from tables [Company], [Group], [Owner].

I have then a continuous sub-form to this main form called ‘Projects Subform’, which regroups data from tables [Projects], [Currency], [Owner]

I have finally a form called ‘Project Details’, which regroups data from all the tables cited above ([Company], [Currency], [Group], [Owner], [Project Lines – Existing], [Project Lines – Oustanding], [Project Lines – Proposed], [Projects], [RM])

Here is what I am trying to do:
I want to have a button on ‘Projects Subform’. When I click on this button, the ‘Project Details’ form will pop up and give me all the details pertaining to the related project (stored in the various tables cited above).

But this seems more complicated than what I have thought primarily…

I hope I have been clear enough on my request. Otherwise, please let me know so I can give more precisions. Also, if you feel that it would be easier to look directly into the code, I can post my database in the thread.

Thank you very much for your feedback and your help.

Best regards,

G.

[MMcCarthy]

My tables are as follows:
[Company] – Primary Key: [Account Number]
[Currency] – Primary Key: [Currency ID]
[Group] – Primary Key: [Group ID]
[Owner] – Primary Key: [Owner ID]
[Project Lines – Existing] – Primary Key: [ProjectID]
[Project Lines – Oustanding] – Primary Key: [ProjectID]
[Project Lines – Proposed] – Primary Key: [ProjectID]
[Projects] – Primary Key: [ProjectID]
[RM] – Primary Key: [RMID]

The relationships of the tables are as follows:
[Group] One-to-many [Company] through [Group ID]
[RM] One-to-many [Company] through [RMID]
[Company] One-to-many [Projects] through [Account Number]
[Owner] One-to-many [Projects] through [Owner ID]
[Currency] One-to-many [Projects] through [CurrencyID]
[Project Lines – Existing] One-to-one [Projects] through [ProjectID]
[Project Lines – Oustanding] One-to-one [Projects] through [ProjectID]
[Project Lines – Proposed] One-to-one [Projects] through [ProjectID]

I have a main form called ‘Company Projects List’, which regroup data from tables [Company], [Group], [Owner].

I have then a continuous sub-form to this main form called ‘Projects Subform’, which regroups data from tables [Projects], [Currency], [Owner]

I have finally a form called ‘Project Details’, which regroups data from all the tables cited above ([Company], [Currency], [Group], [Owner], [Project Lines – Existing], [Project Lines – Oustanding], [Project Lines – Proposed], [Projects], [RM])

Here is what I am trying to do:
I want to have a button on ‘Projects Subform’. When I click on this button, the ‘Project Details’ form will pop up and give me all the details pertaining to the related project (stored in the various tables cited above).

Gari

If you put the code behind the button click

DoCmd.OpenForm "Project Details", , ,"[ProjectID]=" & Forms![Company Projects List]![Projects Subform].Form![ProjectID]
 

Mary

[Gari]

Dear Mary,

Thank you very much for your reply. I am a bit confused but I have to tell that when testing your code and answering to you, I turned around the database once again and I figured out that actually the problem did not come from the code but from the 'Project Details' form where one text box was not properly bound (the one containing the ProjectID). I corrected this and used the button access wizard and it worked perfectly.

The code I got is as follows (fyi only)

Private Sub Go_To_Details_Click()
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Project Details"
 
    stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End Sub

(Btw, what the 'Me!' refers to in the code ?)

But anyway thank you very much for your time.

Best regards,

G.

[MMcCarthy]

Dear Mary,

Thank you very much for your reply. I am a bit confused but I have to tell that when testing your code and answering to you, I turned around the database once again and I figured out that actually the problem did not come from the code but from the 'Project Details' form where one text box was not properly bound (the one containing the ProjectID). I corrected this and used the button access wizard and it worked perfectly.

The code I got is as follows (fyi only)

Private Sub Go_To_Details_Click()
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Project Details"
 
    stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End Sub

(Btw, what the 'Me!' refers to in the code ?)

But anyway thank you very much for your time.

Best regards,

G.

The Me! (or Me.) is just the codes way of referring to the Current form or subform that the event (in this case the button click) is taking place on.

Mary

[Gari]

Thank you Mary for your answer. I'll start using it from now.

Best regards,

G.