Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21

    Linking Id's through a Macro in Access

    I'm trying to make a relation between two tables to show information through a Macro. To give a perspective, I have a table called 'Companies' where there are all the information needed. I've made a 'Home' form where there is a SubForm to show the companies.


    My idea is that the field 'Companies' of this subform, which shows the name of the company, could link to another subform called 'Companies Detail' through the ID.
    So far, I've done this to the macro (the id field is called Id_company).:
    The syntaxis is in spanish so sorry for that and hope this could be understable:


    Code:
    If EsNulo([Formularios]![Consulta para Detalle Empresas]![Id_company])
    then do a BIP.


    Code:
    If NoEs EsNulo([Formularios]![Consulta para Detalle Empresas]![Id_company])
    then
    Code:
    OpenForm
    and in the conditions on
    Code:
    WHERE
    I've written:
    Code:
    ="[Id]=" & [Id_company]




    The idea is that when you click on the name of the company in the Home form, this redirects to the 'Consulta para Detalle Empresas' form. I can't get it working. I'd like to hear opinions and I'll share more information and do my best to make it understandable if it isn't at all. Thank you in advance.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is the structure of your forms? Subforms do not get opened, they are always being displayed once the main form has been opened. It seems you have a main form (empty?) with a subform showing all companies? Then when you click on a company name...?

  3. #3
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Yes, the main form has some text and the sub-form with a list of companies. Then, what I want to do, is once I click on a company from this sub-form it opens another subform (Company Detail), showing the company you clicked. Where I'm being mistaken? More or less it is similar to what there is in the Northwind sample database, in the inventory:

    Click image for larger version. 

Name:	Captura.PNG 
Views:	10 
Size:	85.5 KB 
ID:	29499

    I'm struggling on linking the right company by ID though.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Like I said, subforms do not get "opened". Are you trying to open a form? If so, in the OnClick event:
    DoCmd.OpenForm "formname",,,"company_field=" & Me!company_field

  5. #5
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Finally it seems that I've been able to link both forms, but I have a question:

    when I first click to a company to get redirect to 'Company Detail' it says something like: The form isn't open or doesn't exist in the database. If I open the form, than it works. That means I need to have the form pre-opened? I guess there's something I'm missing, so how can I refer to that form without opening it before?

    Also, I get the links in the event 'OnClick' right, but I'd like them to appear as an hyperlink (just as Northwind example). It works now (except for that I've just mentioned) but I'd like the company names to appear like an hyperlink. How should I do it? Thank you so much.

    Edit: sorry because it is in spanish, but the error message I mention is like this:

    Click image for larger version. 

Name:	CAPTU2.PNG 
Views:	9 
Size:	27.9 KB 
ID:	29500

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can check to see if the form is open:
    Code:
    If CurrentProject.AllForms("FormName").IsLoaded = False
         ' Form is closed
    Else
         ' Form is open
    Endif

  7. #7
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Quote Originally Posted by aytee111 View Post
    You can check to see if the form is open:
    Code:
    If CurrentProject.AllForms("FormName").IsLoaded = False
         ' Form is closed
    Else
         ' Form is open
    Endif
    Excuse me, I'm just a beginner with Access, how should I enter this code? I've programmed in some other languages, so maybe I should make sure the forms are all open before starting to use the database (with an IF sentence maybe), if Access does require it. Where should I write the code you provided? Will this fix the issue about not being able to access the Detail Company's form?

    Thank you very much.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When you are opening the company details form.
    If company details for is open
    ...filter it
    Forms!companydetailsform.Filter="companyid=" & me!companyid
    Forms!companydetailsform.FilterOn=True
    Forms!companydetailsform.Filter.Requery

    Else
    ..if form is closed then open it using the command above

  9. #9
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Quote Originally Posted by aytee111 View Post
    When you are opening the company details form.
    If company details for is open
    ...filter it
    Forms!companydetailsform.Filter="companyid=" & me!companyid
    Forms!companydetailsform.FilterOn=True
    Forms!companydetailsform.Filter.Requery

    Else
    ..if form is closed then open it using the command above
    I'm sorry but I stil can't understand what you mean...

    I open my database, it appears the 'Home' form which has the 'SubForm' of AllCompanies where all companies appear listed. When I click on a company it appears the error message I've mentioned above, despite I think all the OnClick events is correctly done.

    The objective of this database is to be accessible, so if someone opens it and want to look for a company detail just need to click on the company on the 'Home' form (as it happens in Northwind example). Isn't there any way so I can open and look for any company's details without having to open the form previously (like in Northwind)? Sorry for the misunderstanding and I'm really pleased with your help as it is very important to me, thank you.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't speak Spanish, I don't know what that error is. Post your database, you refer to the Northwind database but it would be easier to look at yours.

    When the user clicks on the company, use the code already posted to check if the details form is open, if it is open then filter it based on the company that was clicked, if it is not open then open it with the filter in the DoCmd statement. Did you add the code above and are still getting the error?

  11. #11
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Do you mean to add it to the Macro? Or where?

    The error says something like: Microsoft Access can't find the the referenced form 'Company Detail'. Is it possible that the form you are referring to is closed or doesn't exist in the database. It is also possible that Microsoft Access had found an error in the compilation of a module of Visual Basic.

    Basically it doesn't find the form, even though I've checked that there isn't any typo. For the moment I just wanna get the structure, so the database is very simple and as I've mentioned. Companies includes all the companies with all the information, AllCompanies is the same as 'Companies' but in a form, 'Company Detail' is just some of the fields of 'AllCompanies', and 'Home' is an empty form with the sub-form I've mentioned.

    I'll keep looking into the codes but I can't see at the moment where you mean to add the code. Thank you very much again.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    So the error is occurring when you are making a reference to the form or an object on the form but the form isn't open. Why isn't the form open? Are you following my instructions? The code is in the OnClick event, post the code that you are using when you click on a company.

  13. #13
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    The code in the onClick event is a Macro, which looks like this (Si = if, AbrirFormulario = OpenForm):

    Click image for larger version. 

Name:	MCRO.PNG 
Views:	8 
Size:	10.0 KB 
ID:	29505
    And before it looks like this:

    Click image for larger version. 

Name:	MCRO2.PNG 
Views:	8 
Size:	3.9 KB 
ID:	29506

    It is on the 'OnClick' tab. Excuse it is in Spanish, I will help you if you have any language issue with this. You mean to put the code you've posted in where it is now the Macro?

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No, the code I was providing is for VBA. There is a button for macros that say Convert to VBA (or something like that). Convert this to code and then it will be a lot easier to work with. The code will look very similar to what you see here except that you can add and remove things as you wish. It will be a good introduction to VBA for you.

    I don't work with macros although I am sure there is a similar function to check if the form is open or not.

    Is the very first line checking the company details form? That may be where your problem lies as the form isn't open yet.

  15. #15
    JBros is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    21
    Oh, okay. I've converted the Macros into VBA (the button you mentioned) but then I'm struggling to find the macros converted into VBA. Once the conversion is done, I can click on 'View Code' but I can't barely see nothing:

    Click image for larger version. 

Name:	vba1.PNG 
Views:	7 
Size:	11.2 KB 
ID:	29508

    The expression on the Macro keeps being the same, where can I include the code you provided? I'll keep looking into it and I appreciate your help.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-28-2017, 07:36 PM
  2. Replies: 1
    Last Post: 01-21-2015, 02:02 PM
  3. Access macro vs. excel macro
    By twckfa16 in forum Macros
    Replies: 3
    Last Post: 01-07-2015, 03:44 PM
  4. Replies: 7
    Last Post: 03-27-2014, 11:47 AM
  5. Replies: 0
    Last Post: 03-04-2011, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums