Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    chriscla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    8

    populate textbox on new form with choice of combo on other form

    Hello



    I have a main form 'frmFiche' with some subforms (notes, offers, contacts) that together give an overview of a prospect.
    On the main form I have a combo box (cboZoekProspect) to search for a record of a prospect.

    On the notes subform (sfrmNotitieLijst) I have a button to open a form (sfrmNotitieNieuw) so I can add a new note.
    On sfrmNotitieNieuw I now have a combobox cboRelatie with the prospect names, cascading to a second combobox cboContact, allowing me to choose the contact person attached to the prospect.

    What I would like to achieve is that the prospect name is already filled in in a textbox on the sfrmNotitieNieuw form and that the cboContact is populated with the contacts attached to the prospect. In short: replace cboRelatie with a text field but keep the cascading result in cbo Contact.

    I managed to get the prospect name in a textbox on the sfrmNotitieNieuw form (=Forms![frm_fiche]!cboZoekProspect.Column (1)) but then I get no contacts in cboContact. Also after filling in the other fields on the form, the data is not saved in my table.

    Any help is much appreciated.

    Chris

    PS this thread was also posted on Excel Forum, but then I discovered this forum.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    one of the parameters for the openform command is 'Openargs'

    populate this with your prospect name? id? and in the form open event, assign the openarg to the relevant control

  3. #3
    chriscla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    8
    Hi Ajax

    Thanks for your reply.
    I put the following code on the button to open the add form
    Code:
    Private Sub btnAddNote_Click()
        DoCmd.OpenForm "sfrm_notitieNieuw", acNormal, , , acFormAdd, acDialog, cboZoekProspect
    End Sub
    But I don't see how to assign the openarg to the textbox where the name of the prospect should come.

    Chris

  4. #4
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    You need to add to the On_Open event procedure of the Form

    Code:
        If Me.OpenArgs <> vbNullString Then
            Me.YourLabelName = Me.OpenArgs
        End If

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    sfrmNotitieNieuw
    I interpret that the target control is on a subform in which case the solution is not so simple? Pass the value to the main form - you cannot use Me. Nor can you pass it to the subform as that form doesn't get opened directly. If it's a subform the value will have to be pulled from the first form before it closes?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Micron... good catch, I missed that.

    Hmm, well the only thing that comes to mind is a Public Variable. You could pass the vaue to it and then call it for your Form.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by GinaWhipp View Post
    Micron... good catch, I missed that.
    Hmm, well the only thing that comes to mind is a Public Variable. You could pass the vaue to it and then call it for your Form.
    If it is as I'm guessing, then from first form code you
    - open 2nd form (main form, as usual) without passing anything
    - code on subform Load gets value from 1st form field (Me.subformFiled = Forms!FirstForm.ControlWithValue)
    - after 1st form code line that opens 2nd main form, close (or not) 1st form.

    No public variable required (which IMHO is risky as its value is subject to inadvertent alteration). It might also be worth mentioning that a subform opens and loads first, thus I'm not certain of the success of this proposal IF the subform field whose value is to be set is part of the master/child link. I'm guessing that the 1st form won't close before the value can be retrieved. If that happens, a variable, TempVar or pause might be the way to go.

    None of this matters if I've incorrectly interpreted the design. OP needs to weigh in.

  8. #8
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Goo point but never had an issue using a Public Variable which I only use in moderation. Just seemed to me to be a quick and dirty to get the value. It will fire each time the event is triggered. But that iis... IMHO.

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by GinaWhipp View Post
    Goo point but never had an issue using a Public Variable which I only use in moderation. Just seemed to me to be a quick and dirty to get the value. It will fire each time the event is triggered. But that iis... IMHO.
    I prefer using public functions to get some value from a loaded forms/subforms. For example:
    Code:
    Public Function CurrentProduct() As Variant
        On Error Resume Next
        'Value of control in the loaded form.
        CurrentProduct = [Forms]![MainFormName]![txtProductID]
    End Function
    
    Public Function CurrentSupplier() As Variant
        On Error Resume Next
        'Value of control in a subform of the loaded form.
        CurrentProduct = [Forms]![MainFormName]![SubFormSuppliers]![txtSupplierID]
    End Function
    We can call them from anywhere, e.g. in a query:
    Code:
    SELECT * FROM tblProductSuppliers WHERE SupplierID = CurrentSupplier();
    similarly, in a control source:
    Code:
    =Dlookup("email";"tblSuppliers";"SuppID=" & CurrentSupplier())
    and of course in the VBA code:
    Code:
    DoCmd.OpenForm "frmOrders", , , "SuppID=" & CurrentSupplier() & " AND ProductID=" & CurrentProduct()
    This is more safer than =[forms]![frm...] etc, especially in query criteria.
    I have faced the random and unexplainable replacement of the name of the form, in querydef criteria in style [Forms]![FormName]![ControlName], with the name of the first form in navigation pane(!). I suppose that is a bug of my Access version.

  10. #10
    chriscla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    8
    Hi
    From the above postings I learn that it is important to know what kind of forms and theirstructure. I know notice that my question has not been clear enough.

    The sfrm_notitieNieuw is not a subform (it used to be, but I decided to have it as a popup form to enter new prospect data such as date of visit, visit report, … but I kept the name).
    The form on which the button to open ‘sfrm_notitieNieuw’ is, is a true sub form ‘sfrm_notitieLijst’on my main form ‘frm_fiche’. As explained before, I use a combobox cboZoekProspect on my main form frm_fiche to look for a prospect record. On frm_fiche I have several subforms on a tabbed form, one of which is sfrm_notitieLijst. From there on I open a popup form sfrm_notitieNieuw to enter new prospects.

    The ultimate goal is to have the choice made in the cboZoekProspect combobox as a text field on the popup form sfrm_notitieNieuw and then have on the popup form a combobox allowing me to choose a contact related to the prospect’s name.

    I hope this clarifies my question.

    I’ve been trying GinaWhipp’s suggestion, but this always gives a 0 in the text field on sfrm_notitieNieuw.
    Code:
    Private SubForm_Open(Cancel As Integer)
        If Me.OpenArgs <> vbNullStringThen
            Me.cmb_relatie = Me.OpenArgs
        End If
    End Sub

    Hope one of you can sort this out.

    Attached a dummy version of my db.

    Many thanks
    Chris
    Attached Files Attached Files

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't really have the time to work out exactly what is happening as your post does not match your examples db. For example you say

    On the notes subform (sfrmNotitieLijst) I have a button to open a form (sfrmNotitieNieuw) so I can add a new note.
    The only button opens a form called sfrm_notitie

    There is a label with this code

    Private Sub Bijschrift11_Click()
    DoCmd.OpenForm "sfrm_notitieNieuw", acNormal, , , acFormAdd, acDialog, cboZoekProspect
    End Sub
    However your form does not contain a control called cboZoekProspect. If this is on the parent form then you need Parent.cboZoekProspect

  12. #12
    chriscla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    8
    Sorry, not being clear enough. the 'button' to open the add form is the tekst 'Nieuwe notitie'. This has the following OnClick code:
    Code:
    Private Sub Bijschrift11_Click()
        DoCmd.OpenForm "sfrm_notitieNieuw", acNormal, , , acFormAdd, acDialog, cboZoekProspect
    End Sub
    I added the Parent, but then I get an error saying that the data expression is not OK for one of the arguments.
    Code:
    Private Sub Bijschrift11_Click()
        DoCmd.OpenForm "sfrm_notitieNieuw", acNormal, , , acFormAdd, acDialog, Parent.cboZoekProspect
    End Sub
    best regards
    Chris

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    strongly recommend you put Option Explicit below Option Compare Database and get in the habit of compiling code as you write it (Debug>Compile). This will highlight many errors before you even run the code.

    I added the Parent, but then I get an error saying that the data expression is not OK for one of the arguments.
    Parent was a guess on my part - I don't know where your control cboZoekProspect is - all I know is it is not in the form that is running the code. I guessed that since this is supposed to be a subform, it was in the parent. See this link about how to reference controls on other forms. http://access.mvps.org/access/forms/frm0031.htm

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Well, this is the code for sfrm_notitieLijst code module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Bijschrift11_Click()
        With Me.Parent
            If IsNull(.Id) Then
                MsgBox "Please select a prospect.", vbInformation, Me.Caption
                .cboZoekProspect.SetFocus
            Else
                DoCmd.OpenForm "sfrm_notitieNieuw", acNormal, , , acFormAdd, acDialog, .Id
                Me.Requery
            End If
        End With
    End Sub
    This is the code for sfrm_notitieNieuw code module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub btn_close_Click()
        DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub Form_Load()
        If IsNull(Me.OpenArgs) Then
            'There is no relatie for note.
            Else
            Me.id_klant = Me.OpenArgs
            Me.cmb_contact.Requery
        End If
    End Sub
    This is the Control Source for the the Textbox cmb_relatie in sfrm_notitieNieuw:
    Code:
    =DLookUp("r_naam";"tbl_relatie";"IdRelatie=" & [id_klant])
    This is the Row Source of cmb_contact in sfrm_notitieNieuw:
    Code:
    SELECT tbl_contact.IdContact, tbl_contact.c_rel_id, tbl_contact.c_naam & " " & tbl_contact.c_voornaam AS Expr1
    FROM tbl_contact
    WHERE (((tbl_contact.c_rel_id)=[forms]![sfrm_notitieNieuw]![id_klant]))
    ORDER BY tbl_contact.c_naam & " " & tbl_contact.c_voornaam;
    Let me know if it works as you expected.

    Cheers,
    John

  15. #15
    chriscla is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    8
    Hi John

    This is what I was looking for. Many many thanks
    .

    Cheers
    Chris

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

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2015, 02:40 PM
  2. Replies: 2
    Last Post: 10-21-2014, 07:57 AM
  3. Replies: 2
    Last Post: 04-27-2012, 06:28 PM
  4. combo box to Populate a form
    By rcappy in forum Forms
    Replies: 1
    Last Post: 01-30-2012, 03:07 AM
  5. populate boxes based on combo box choice
    By Mattm1958 in forum Forms
    Replies: 13
    Last Post: 08-30-2010, 02:09 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