By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,097 Members | 887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,097 IT Pros & Developers. It's quick & easy.

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

P: 41
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.
Jan 21 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Project Details", , ,"[ProjectID]=" & Forms![Company Projects List]![Projects Subform].Form![ProjectID]
  2.  
Mary
Jan 21 '07 #2

P: 41
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)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_To_Details_Click()
  2.  
  3.     Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.  
  6.     stDocName = "Project Details"
  7.  
  8.     stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'"
  9.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  10.  
  11. End Sub
(Btw, what the 'Me!' refers to in the code ?)

But anyway thank you very much for your time.

Best regards,

G.
Jan 22 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_To_Details_Click()
  2.  
  3.     Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.  
  6.     stDocName = "Project Details"
  7.  
  8.     stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'"
  9.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  10.  
  11. 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
Jan 22 '07 #4

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

Best regards,

G.
Jan 22 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.