Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Form Entry from List Box (2007)

    I have a form which contains a subform. There is a hidden value on the parent form call txtName. When the form loads the sub form, which is set to continuous, loads and has multiple records. The last record is a blank record which is for a new entry. I want the value in txtName to be set to one of the fields in the sub form. Right now it sets txtName to the first record which overwrites the current value. I want this to be added to the new record. How is this accomplished?
    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Entry from List Box (2007)

    Sorry, I don't quite understand what you want to accomplish. Do you want to set the default value for the name field in the subform?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Entry from List Box (2007)

    The hidden value in the textbox is set from a different form when the form loads. The value will change each time the form loads. The subform loads and fills with several records. The last record is blank and ready to create a new record. I want the value in the textbox to be set to one of the fields in the subform. Right now when the form loads, it sets the value in the textbox to the field in the first record in the subform instead of the blank record.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Entry from List Box (2007)

    > ... it sets the value in the textbox to the field in the first record in the subform ...

    What exactly do you mean by that? As you wrote it, the value of the hidden text box is set, but I suspect you meant something different.
    Please try to explain unambiguously.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Entry from List Box (2007)

    I am sorry. Let me try to better explain.

    My table has fields Field1, Field2, Field3, and Field4. The table currently is populated with records 1, 2, 3, 4, 5, etc. When the parent form loads the continuous subform is populated with records 1, 2, 3, 4, 5, etc. The last record in the subform is blank for a new record. On the parent form there is a textbox that is either null or has a value set from another form. If the value is not null, I want to set the value of the textbox to Field1 in the new record. Currently I have this code:

    If Not IsNull(Me.txtSearchNum) Then
    [Forms]![frmweek]![Child808]![Field1] = Me.txtSearchNum
    End If

    The problem is that when me.txtSearchNum is not null, Field1 of record 1 is set to me.txtSEarchNum instead of the blank record. I hope this better explains.
    Thanks,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Entry from List Box (2007)

    You don't want to set the value of Field1 in the new record, because that would in fact create a new record.
    You want to set the default value instead:

    Forms!frmWeelk!Child808!Field1.DefaultValue = Me.txtSearchNum

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Entry from List Box (2007)

    But that is what I want to do, I want it to create a new record. The Subform contains entries for different vendors. I am setting it up so if the user doesn't know the vendor name, Field1, they click a search button to bring up the search form. They can then search for the vendor by location and/or description. When the find the vendor they want in the list box, they double click it and it sets that Vendor name to Field1 in the new record.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Entry from List Box (2007)

    I doubt it, but how about this?

    If Not IsNull(Me.txtSearchNum) Then
    Me!Child808.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me!Child808!Field1 = Me.txtSearchNum
    End If

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Form Entry from List Box (2007)

    That is what I needed. That works.

    Is [Forms]![frmweek]![Child808]![Field1] the same as Me!Child808!Field1?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Form Entry from List Box (2007)

    In the code behind a form or report, Me refers to that form or report. So in your situation, Me is equivalent to Forms!frmWeek. Using Me is more efficient, since VBA doesn't have to look at the Forms collection.

    In VBA, you can omit square brackets [ ] around a name if the name doesn't contain spaces or unusual characters. So [Forms]![frmWeek] is equivalent to Forms!frmWeek. But if your form had been named Week Form, you'd have had to use Forms![Week Form].

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •