Results 1 to 15 of 15
  1. #1
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12

    Prefill records to a subform


    Hey guys,

    I am new to coding in VBA and need a bit of help. I use the following code below in my main form to prefill a text box (Prior1Text which is also in the main form) for new records to save a bit of time for entry. What I would like to do is this exact same thing, but it would prefill a text box in my subform. My subform control is called Prior1form and the text box I am looking to fill is called Text720 which would fill from field P1S1Description. This field is within the table that is related to the subform. I have tried using the same basic code in the subform, and while it works when it is just the subform open as a normal form, when it is embedded in as a subform it no longer works. Thanks for any and all help.

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        With Me.RecordsetClone
        If .RecordCount > 0 Then
        .MoveLast
        Me.Prior1Text = !P1Text
        End If
        End With
    End Sub

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If the description is related to the link criteria you don't need to store it and should not include it as a data entry field. You can pull that information any time you want with any of a handful of different methods any time you need to see it on a form/subform/query/report/etc. From the sound of it you are duplicating data from table to table which you do not need to do if you have a properly normalized table. Additionally, if you are using a bound form, when you put a value in a text box that is bound to your table you are creating a new record, the method you're currently following will create a record where everything is blank except the PK of the table driving the subform, the FK of the table driving the main form and whatever value is in prior1text. So if you navigate away from that record you're basically going to end up with a record that (I'm guessing) has no real value and you'd have to edit that record somewhere down the road to make it useful.

    In short if you are storing data on one table, there is no reason to store it on two.


    Perhaps an example of what you're trying to do (maybe a screenshot of your form) might make your meaning more clear if I have misinterprited.

  3. #3
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Thanks for the response. I think I mis stated what I am trying to achieve. The code shown above I currently have set up in the parent form and upon creation of a new case some data is carried forward and autopopulated. It works how it is supposed to work. I showed the code to give an example of what I am trying to achieve next. The two are only related in the sense of a similair function they perform.

    Within my subform I have a completely different text box (i.e. no duplication of data). We can call this text box Text720 bound to field P1S1Description in a table linked to the subform. What I would like to happen is upon a new record being started in the subform, to pull from the last record in P1S1Description and place it in the Text720 control in the form. The code shown above completed this function in the main form. I am wondering if and how I can manipulate this programming in order to achieve my task. I tried just simply adding the above coding (control and field names changed of course) to the subform and when it was a primary form the coding worked, but the moment it was embedded as a subform it stopped working.

    I hope that makes more sense, otherwise I can put up some screens shots later tonight.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    is your 'copy' button on your subform or on your main form?

  5. #5
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Sorry for the ignorance, but I am not sure what you mean by 'copy' button. If you are referring to the code I have tried it on both the main form and subform. If you are referring to an event, such as AfterUpdate, I have tried that on both as well (Main form and Sub).

    Thanks

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok let me be more clear.

    I am guessing you do not automatically want any new record to inherit the data from the previous record by default.

    So...

    What is your trigger to copy that data, or what do you want it to be

  7. #7
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Quote Originally Posted by rpeare View Post
    Ok let me be more clear.

    I am guessing you do not automatically want any new record to inherit the data from the previous record by default.
    Actually, that is exactly what I want (I probably should have just worded it that way to start ). Just certain fields but I want it to happen every time a new record is created. However it also needs to be able to be overwritten (thus why I can't choose one default to go with). For example, in the first record a response might be "black" in the first text box and then they fill in a number of other components in the form. Cases 2-8 could also be "black" for that particular field so I want it to always automatically auto populate for every new record created. However in record 9, it could change to "orange". I need them to be able to overwrite "black" to "orange" in record 9 and then for records 10+ it will auto populate "orange" for each new record until otherwise changed. In reality it is actually strategies and priorities that we are tracking (long text fields) but the color example seemed easier.

    My above code does exactly that for any field in the main form, however I can't for the life of me figure out what I need to do in order to replicate that in a subform

    So...

    What is your trigger to copy that data, or what do you want it to be
    Ideally (as I am sure you know given above), new record. If it can't be done, a button command is also fine.

    Thanks

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The code you originally wrote will work if that's your intent. You'd just have to put it in the BEFORE INSERT statement of the subform, in essence you'll have the same statement in both your main and subform.

  9. #9
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Hmmm, that is what I did but it isn't working and it is driving me a bit crazy. I put in the code, Before Insert, in the subform and it didn't work. When I open the form as its own seperate form the code works, but when it is placed as a subform in my main form it doesn't work. I am not changing anything in the code when I do that, but I wonder if there should be something added because it is a subform?

    Is there anything else that I could have set up wrong that could be causing this issue?

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is my example.

    benks.zip

  11. #11
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Ok, so I did some further testing and figured out why it wasn't working "for me". The code was working as intended and you were correct. I just wasn't correct in my problem but now have my head wrapped around it. That said, I still have an issue that hopefully you can help me figure out.

    The subform was developed because the database essentially has more then 255 data points they want to collect (I am building this for a client. I think it is crazy overkill, but such is life). Basically I want to happen is the subform to "act" as if it is part of the main form. One record would essentially look like a single flat file. I have formated the subform so that the navigation bars, scroll bars, etc. are all removed. The only way they can select next record is on the main form. So, for each record on the main, there will only ever be one record in the subform. Do I need to switch my relationship to a 1 - 1 vs. a 1 to many?

    So what I would like to happen is that when I hit "next record" on the main form I auto populate into the subform as well using the before insert feature. The tricky part is the subform will technically always be 1 of 1 in terms of records as it relates to the main form, but I still want to pull from the last record created in the subform. Hopefully that makes sense.

    Thanks so much for your help. I feel like I made at least a bit of progress in figuring out why it wasn't working thanks to yourself.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    so in reality, you have one record, it's, say 400 fields long. You're putting 200 of those fields in table1 200 of those fields in table 2 and you want your data entry to handle this.

    The only way I can think to do this with a bound form is to set the CYCLE property of the subform to THIS RECORD, the only problem then is that if they have a mousewheel they can still go to the 'blank' record but if you are enforcing a 1 to 1 relationship on in your relationship setup they shouldn't be able to enter any new data and if they try to it'll generate an error message. There is a way to disable your mousewheel but I don't have the code handy to do it.

    I guess my real question is.... what is the nature of the table that needs more than 255 fields? The only thing I've ever done that came close was a questionnaire where the number of questions was around 100. I can't really think of a reason why a single record would need more than 255 fields and if we can address that part it may provide a better answer to your question. To me a single record with that many fields screams of a non-normalized structure and though the client may want it there may be a better way to enter/store the data and you can hide the true structure from your client.

  13. #13
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    Quote Originally Posted by rpeare View Post
    so in reality, you have one record, it's, say 400 fields long. You're putting 200 of those fields in table1 200 of those fields in table 2 and you want your data entry to handle this.
    More or less, yep.

    The only way I can think to do this with a bound form is to set the CYCLE property of the subform to THIS RECORD, the only problem then is that if they have a mousewheel they can still go to the 'blank' record but if you are enforcing a 1 to 1 relationship on in your relationship setup they shouldn't be able to enter any new data and if they try to it'll generate an error message. There is a way to disable your mousewheel but I don't have the code handy to do it.
    Will look into it

    I guess my real question is.... what is the nature of the table that needs more than 255 fields? The only thing I've ever done that came close was a questionnaire where the number of questions was around 100. I can't really think of a reason why a single record would need more than 255 fields and if we can address that part it may provide a better answer to your question. To me a single record with that many fields screams of a non-normalized structure and though the client may want it there may be a better way to enter/store the data and you can hide the true structure from your client.
    Couldn't agree more. While I can't go into the details of the project (for obvious reasons) it is government contract which I am sub contracted for and they love to overkill it with data. I have thought long and hard about a better way to normalize the structure but given there requirements I couldn't come up with one. Perhaps you can, so I will give you the basics.

    While there are a number of basic questions throughout the questionaire the real sticking point and data point hog is one particular section. Essenatially there are 5 possible priorities they could identify. Each priority has up to 5 possible strategies. Each strategy has up to 5 tasks. Within each task there are 10 points then need to report on. 5x5x5x10=1250 data points (all text). On top of that there is a reporting period variable as well, where each of the fields can only be filled in once per period. In addition there is a location parameter as well. There are other miscellanous questioins as well, so total data points is ~1,400. It also needs to be incredibly simple and easy to follow from a data entry standpoint, as people with limited computer skills will be entering it. Thus why the "flat" approach is most logical to them and cycling through records was discouraged. I am welcome to any potential ideas that could perhaps make it easier to use/develop

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Is there a required output display?

    I think based on your description I'd have the following:

    A table for the 'main' questionnaire
    A table for the 'priorities' with a FK link to the 'main' questionnaire, this would allow you to have anywhere between 0 and all the priorities (even if there are more than 5) for each questionnaire
    A table for the 'strategies' with a FK link to the 'priorities' table,
    A table for the 'tasks' with a FK link to the 'strategies' table
    And finally a table for the 'points' with a FK link to the 'tasks' table.

    Of course if there are strategies tied to specific priorities you'd have to have a table listing all the possible strategies per priority, a table for all the possible tasks for given strategies, etc.

    I would be inclined to do this with an unbound form rather than a bound form too, I've always found unbound forms easier to control in terms of data addition/deletion and handling in general though they are a bit more trouble to set up sometimes.

    This would also allow you to really minimize the sheer size of your data entry form.

  15. #15
    benks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    12
    That is pretty similair to how I orginally pitched it to them, but they required a certain format of how people would view it. I did a mock for them today of how your version could look, but it was a no go again. I have however let them know that auto filling those fields is most likely not possible.

    That said, thanks for all the help.

    Cheers

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

Similar Threads

  1. Replies: 2
    Last Post: 05-28-2013, 04:00 PM
  2. Replies: 2
    Last Post: 02-12-2013, 11:23 AM
  3. Adding Records in a Subform
    By chris.williams in forum Forms
    Replies: 3
    Last Post: 10-14-2011, 01:21 PM
  4. Replies: 12
    Last Post: 08-30-2011, 03:36 PM
  5. Subform with no records
    By Bamstick in forum Access
    Replies: 1
    Last Post: 11-18-2009, 03:59 PM

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