Results 1 to 15 of 15
  1. #1
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36

    Can I convert a subform into a stand alone input form?

    I have a mainform with 9 subforms.

    I want to remove a subform completely and use it as a stand alone form.



    I would have to copy the ID# off the main form, for example 24, 866 and paste it into a find combobox on the stand alone box to advance the form to that record.

    Yes, this is cubersome and pitiful. I should be able to leave the subform inside the mainform where it is already comveniently linked. But the overall performance of the mainform is bogging. By removing subforms, the response speed of the mainform improves significantly. I still need to enter data into that child table, though.

    So if if remove the subform, how do I use it as an interface to the child form separate form the mainform? Without having to recreate from scratch?

    What terminology do I search youtube with ? If I type "How to convert a subform to a form" nothing comes up on google

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you just open it as a separate form using docmd.openform. One of the parameters is the WHERE parameter - in there put something like

    "FK=" & me.PK

    where FK is the value in the subform linkchild property and PK the value in the linkmaster property. If you have code in the subform which references the main form this will need to be modified, as will code in the main form that references the subform

    With regards performance - sounds like you are not optimising your form recordsources - such as relevant fields not being indexed, use of LIKE *something* in criteria or use of full tables rather limiting the number of records by using criteria.

  3. #3
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    The problem is I am too rusty. It has been ten years since I put this code together and I only know enough to be dangerous. I am not a programmer and I am a hack.

    So I got the docmd.open form to work.

    I get you are saying:

    docmd.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

    maybe this is correct: docmd.openform (,,,wherecondition)

    and the wherecondition is "FK=" & me.PK

    but I have no idea anymore what FK or PK or Me means Me is current image or something? and & is concatenating the statement?

    I literally have:

    Private Sub Command554_Click()
    Rem bvd stand alone button
    Rem bvd ALTERNATE OPENFORM



    On Error GoTo ERR_BVDBALE


    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "BVD STAND ALONE"
    DoCmd.OpenForm stDocName, , , "FK=" & Me.PK, stLinkCriteria


    Exit_Command554_Click:
    Exit Sub


    ERR_BVDBALE:
    MsgBox Err.Description
    Resume Exit_Command554_Click

    End Sub

    But that gets me method or data member not found. (PK)




    if I click on the form's properties there is no category for linkmaster or linkchild like there is TAG or background color; but you must not mean that sort of property.

    I want to pass the current PARENTtable ID# to the subform and maintain the one-to-many relationship "linkage"

    I only understand what you are saying in generalities.

    I am sorry I am so dumb.

    Again, my object is to maintain a 1 to many relationship but not use a NESTED subform to do it. rather to use a command button or click on the child's form manually outside the main form. I am optimizing indices and not using LIKE *..... I am sure if I could hire you you could fix this stuff. . . .. we could try and get into the performance speed stuff, but first, this removal of the subform is going to help me in a quicker workaround, I think.

    Thanks for trying

  4. #4
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    i FIGURED OUT TO CHANGE string TO integer
    but that only gets the form to open. . .. the correct child is not displayed , only the 1st record is displayed, and the form is not really navigable as i have it, i would have to
    page through and guess the index relationship

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I told you where to find it - look in the subform properties on the data tab
    Click image for larger version. 

Name:	image_2022-01-10_015836.png 
Views:	22 
Size:	6.6 KB 
ID:	47006

    alternatively look at your relationships - should be specified there
    or look in table design - should see a key icon next to the field which is the primary key

    PK - primary key - every table should have one of these
    FK - foreign key, any child table should have this as well as it's own PK
    Me - refers to the form object where the code is - so Me.PK is the primary key to identify the current record on the form (change PK to whatever you have called your primary key, ditto FK)

  6. #6
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    IN THE MEANTIME I FIGURED THAT OUT


    stDocName = "BVD from Scratch"
    DoCmd.OpenForm stDocName, , , "ID_BVD=" & Me.ID, stLinkCriteria


    when i click the button, me.pk=24810 (out of 25,000 records)
    the BVD table has 998 records. There is a BVD record for me.24810 and the IID_BVD field is of course 24810

    While I get no error message, the display does not open to the 998th record
    the form opens up but there is only 1 record of 1 displayed and it is blank.

    The relationship is one to one maintable (MT) to child (BVD)
    A record in BVD is not required.
    ID_BVD is not indexed
    the child BVD table has no primary key.
    ID_BVD = MT.ID always

    It would seem, when i pass the value 24810, that BVD record should come up.

    even if I copy the table and create an autonumber primary key, the results do not change.

    The application works fine with BVD managed as a subform nested in the main display for MT-parent table.

    I don't understand what is going on.



  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Tables have no inherent order so the statement 'While I get no error message, the display does not open to the 998th record' does not make sense to me.

    the form opens up but there is only 1 record of 1 displayed and it is blank.
    this implies there is no record where "ID_BVD=24810 and/or you have set the form's data entry property to yes

    ID_BVD is not indexed - it should be

    Sounds to me like your tables are not correctly structured which is why you are having so many problems. Suggest post a screenshot of your relationships, would help to see what is actually going on.

  8. #8
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Thanks so much for your patience. The cobwebs are clearing.

    First: I typoed, ID_BVD is indexed, as it should be - -- - but there is no autonumber primary key in that table - -I will try to attach a jpg. but the one-to-one referential integrity is set to on with DELETE cascade and there is perfect 1:1 correlation between the 25000 records in MT (maintable) and the 998 records in Child (BVD)

    Second: possible oversight in the code you suggested.


    I started from scratch again
    a) I think possibly because I tried to short cut and copy fields in from the original subform, perhaps property record sources were not correct?
    I do know that data entry was set to NO; so I tried a totally blank form and selected from the fields available list
    b) I also used the forms operations wizard - that would have been th ebetter suggestion for a rusty novice non programmer like me:

    ACCESS autogenerated:
    stLinkCriteria = "[ID_BVD]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    you suggested and I literally typed:

    DoCmd.OpenForm stDocName, , , "FK=" & Me.PK, stLinkCriteria

    I don't know if the brackets and ! vs . matter, plus I had an undefined stLinkCriteria - maybe that messed things up?

    So now the form does open to the correct record
    MT=PK=24810 value is passed to the open form and CHILD=ID_BVD=24810 displays


    So next question: this BVD child has never had its own autonumber primary key. The application works fine for over 10 years. 25000 parent records, 998 child records, not to mention anothe rdozen tables. Since no other table links to CHild BVD, and there are no REPORTS or Queries run against it, would you be willing to say

    a) in this instance there is no need for an autonumber primary key in this table?
    b) I could suddenly add a new autonumber field to this table and call it BVD_PK, but why do that and increase overhead?
    On the relationships display ntohing would change: MT_PK : ID_BVD 1:1 remain the same with referential integrity still turned ON and DELETE cascade is ON,.
    I could not any longer have referential integrity between MT_PK and BVD_PK, since they did not launch from the beginning of use. If BVD_PK had been set up in the beginning, MT_PK = ID_BVD (FK) = BVD_PK any way, so it seems like BVD_PK (autonumber) is just a redundancy, in this case of one to one relationship

    the reason BVD exists is that
    a) MT is already 200 fields (I deleted about 80 fields yesterday as they were never used and empty)
    b) these fields are a unique collection and usually empty (998 instances out of 25000)
    c) this table could be deleted without changing anything about the MT, but they happen to also be the type of information that never would be delted.



    ACCESS is going to say I have to turn off the referential integrity, I think. I can't safely add an autonumber PK at this point simply for some unlikely future prospect I might need to run a query or report against a PK. The FK is sequential, even if it has an occasional gap in sequence. THE MT is missing record 14 and 2143. Child has no instance for those (remember only 998 instances in child to 25000 instances in MT). so bottom line, at this point, leave well enough alone. right?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you suggested and I literally typed:

    DoCmd.OpenForm stDocName, , , "FK=" & Me.PK, stLinkCriteria


    I actually said

    "FK=" & me.PK

    where FK is the value in the subform linkchild property and PK the value in the linkmaster property.


    So next question: this BVD child has never had its own autonumber primary key. The application works fine for over 10 years. 25000 parent records, 998 child records, not to mention anothe rdozen tables. Since no other table links to CHild BVD, and there are no REPORTS or Queries run against it, would you be willing to say

    a) in this instance there is no need for an autonumber primary key in this table?
    providing bvd is indexed no duplicates and allow nulls is set to no then it will uniquely identify the record so no need for an autonumber PK

    Click image for larger version. 

Name:	image_2022-01-10_154037.png 
Views:	14 
Size:	27.9 KB 
ID:	47017
    b) I could suddenly add a new autonumber field to this table and call it BVD_PK, but why do that and increase overhead?
    your db is tiny, the overhead is tiny. Which would you rather? save a small amount of space on your hard drive? or make it more difficult to manage?. The advantage of autonumber is you specify it and forget it. It does not need to be a PK although that is it's purpose, and you don't need to use for a PK providing whatever you do use meets the requirements of a PK - unique and no nulls.

    ACCESS is going to say I have to turn off the referential integrity, I think. I can't safely add an autonumber PK at this point simply for some unlikely future prospect I might need to run a query or report against a PK. The FK is sequential, even if it has an occasional gap in sequence. THE MT is missing record 14 and 2143. Child has no instance for those (remember only 998 instances in child to 25000 instances in MT). so bottom line, at this point, leave well enough alone. right?
    you have not shown how your db is structured but from your various comments on this and your other threads it is not structured in a way that makes maintenance and further development straightforward. I suspect your design means you are loading every scrap of data for every record, whether it is relevant or not to your current task. So I cannot advise whether 'leave well enough alone' is good or bad advice.

    You mentioned in post #3 about hiring me - happy to talk about that but suspect I will be too expensive for you. If you want to pursue this, please PM me (click on my name and select private message)

  10. #10
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Well there is another sticking point.

    The BVD data entry form opens fine for existing records in BVD-child-table

    But if I create a new record in the main form and then click the OPEN button, the display is for ID_BVD = ZERO and the record counter is 1.
    A blank record for 24862 is not displayied because there is not as of yet a new record 24, 862 in BVD-child table.

    My nested subform button will create an instance of 24862 in BVD-CHILD (can you explain why? is it because the mainform has MainTable as a "record source" and so all tables with a linked relationship are accessible for update in a nested subform
    - that is by default the FK is updated with the one-to-one autonumber from MAINTABLE-PK? did I say that awkwardly? how would you say it?)

    My new operations control button using docmd.openform {FK gets PK} does not create a record in BVD. So when the button passes the ID=24862 to the child-form, there is no match and the default, i suppose, is the first record....so I would be modifying on the form

    DATA ENTRY=YES is wrong. .. .that gets me back to messing up the ID key pass docmd.openform FK getsPK

    I tried a swipe at using requery, but I don't have the understanding or logic right:


    Private Sub button_open_bvd_Click()
    On Error GoTo Err_button_open_bvd_Click


    Rem ***this is wrong****
    Dim rstchange As Recordset
    rstchange.Requery
    Rem ********************

    Rem*********also wrong****
    Docmd.Requery (BVD) (where BVD is the child table)
    rs.requery
    Rem **********************


    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "bvd FROM SCRATCH"

    stLinkCriteria = "[ID_BVD]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria


    Exit_button_open_bvd_Click:
    Exit Sub


    Err_button_open_bvd_Click:
    MsgBox Err.Description
    Resume Exit_button_open_bvd_Click

    End Sub

    So what is happening and what would the correct way of doing it be?

    Thanks Click image for larger version. 

Name:	BVD LINKS redd.JPG 
Views:	10 
Size:	148.4 KB 
ID:	47021not sure if I did that image upload right... looks too small for you to see...but I did describe it to you inthe previous post(s)

  11. #11
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Click image for larger version. 

Name:	BVD null NO redd.JPG 
Views:	8 
Size:	74.8 KB 
ID:	47022
    thanks - did not know property existed or where to find it - I figure this is default in a relationship set as 1 to 1 and I suppose there are reasons why developers want to change this and allow children to exist with no parent.... I took it for granted this is a default. That it could be nothing except NO unless I changed it.....and as a novice, it is almost impossible I could have changed it.

    previous post however, poses a sticking point in the form we fixed

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    when you had a mainform/subform arrangement with the linkchild/master properties populated, that would have happened automatically. Now you have to do it yourself.

    Without knowing in detail how your app is designed I would not like to suggest anything beyond something like this:

    1. One of the parameters for docmd.openform is 'openargs'. Populate it with the ID/PK/whatever you are calling it on your main form.

    2. Then in the new form load event have the following code

    Code:
    if not isnull(Me.openargs) then me.ID_BVD=" & Me.openargs


    3. That should create a new blank (other than
    ID_BVD) record if one does not already exist.

    4.If you decide you don't want to populate it, you will need additional code to cancel the insert. However without knowing in detail how your app is designed, I would not like to suggest anything beyond something like this in the form beforeinsert event

    if msgbox("Create this record?",vbyesno)=vbno then cancel=true
    and this is just a suggestion. I'm not prepared to continue on a moving goal post basis which is what is happening



  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I figure this is default in a relationship set as 1 to 1 and I suppose there are reasons why developers want to change this and allow children to exist with no parent.... I took it for granted this is a default. That it could be nothing except NO unless I changed it.....and as a novice, it is almost impossible I could have changed it.
    You may think you have a 1-1 relationship, but you haven't necessarily. You have a relationship where you can have orphan child records because you are allowing nulls and any value that does not already exist in that field. Look at your relationships - have you enforced referential integrity? See this image

    Click image for larger version. 

Name:	image_2022-01-10_172633.png 
Views:	14 
Size:	28.0 KB 
ID:	47024

    table1 is the equivalent of your main table and table2 your other table. Note it says a 1-1 relationship but referential integrity is not enforced. Look at the FK field in table2 (the equivalent of your ID_BVD field) you will see there are values there which are not in table1. So you don't have a true 1-1 relationship where a record in table2 cannot exist if there is not a matching field in table1


  14. #14
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Thanks again. I can appreciate you wanted to leave it here.

    There is apparently some syntax error in what you are attempting to impart on me. I have tried it several ways to no avail yet. I did some googling. I haven't found anything
    useful yet I will keep plugging at it.

    I get either member not found or invalid value.

    also, I showed you a snip, whereby my referential is set and Delete cascade is active. I also stated that at least twice. Not sure why you asked if I activated referential integrity nor why you keep writing I may not have one to one and pasted the example snip - buyt better safe than sorry for me to check by snips. I can see that"

    attached is the syntax issue, in case it is obvious to you

    not sure why the code....seems to me the docmd was already passing the ID value correctly, the issue was whether ISNULL(ID_BVD ) on load, and if it is null, then what?
    To me, if it is NOT null, there was no issue. . . .. but those double negatives get me sometimesClick image for larger version. 

Name:	BVD load event.JPG 
Views:	8 
Size:	32.9 KB 
ID:	47027Click image for larger version. 

Name:	BVD load syntax error.JPG 
Views:	8 
Size:	57.2 KB 
ID:	47028

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    When you type me. A drop down appears so select the required field from that list

    And you have not taken note of using openargs

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Subform Data Input problems
    By Stan Denman in forum Forms
    Replies: 1
    Last Post: 09-15-2015, 02:36 PM
  2. Replies: 16
    Last Post: 01-06-2015, 09:57 AM
  3. Replies: 1
    Last Post: 03-28-2011, 02:34 PM
  4. Replies: 1
    Last Post: 03-24-2011, 07:25 PM
  5. Making a Stand Alone Form Available
    By PapaMammoo in forum Forms
    Replies: 1
    Last Post: 01-13-2011, 10:49 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