Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another Combo Box (97/2K)

    I have a combo box on a form which displays CustomerID. When a Customer ID is selected, another form opens displaying more combos and text boxes. I want the Customer ID from the first form displayed either in a text box or combo box on the second form. Presumably, I need a DLookup.

    Any help, please?

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Another Combo Box (97/2K)

    Does the customerID on the second form need to be stored ? - ie is the control that displays it bound.
    If it is not bound then you just just its control source to the other control on the other form
    e.g. =forms!myfirstform![customerID]

    If it is bound then you could set its value in the after update event of the customerID on the first form.

    You don't need a Dlookup because the info you want is available on a form.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Combo Box (97/2K)

    Yes. The CustomerID on the second form is bound. I considered the After Update option. Would I just enter the expression as you typed it into the Event Procedure? Is there any reason why I cannot then enter another expression into the After Update event of the this control (on the second form)?

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Another Combo Box (97/2K)

    To set its value in the afterupdate event you need

    forms!mysecondform![customerID] = me![customerID]

    Me refers to the current form, so its full name does not need to be written out.

    This code assumes that the second form is already open. You have said that the second form is opened after the customerID is entered. You need to be clear about the precise sequence because you can't set the value of the control until the form is open.

    After update events are not triggered when values are placed into a control by code. So you can create an after update for customerID on the 2nd form, but it won't occur unless a user goes into the control and enters some data.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Combo Box (97/2K)

    Thanks John,
    Just one more thing (famous last words),

    How would I modify the following code so that, upon opening, the form goes to a new record?

    Private Sub NewOrder_Click()
    On Error GoTo Err_NewOrder_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Order Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms![Order Form]![Driver Surname] = DLookup("[Surname]", "qryDriverID")

    Exit_NewOrder_Click:
    Exit Sub

    Err_NewOrder_Click:
    MsgBox Err.Description
    Resume Exit_NewOrder_Click

    End Sub

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: Another Combo Box (97/2K)

    Rob,

    Although I'm not John, here is an answer. If you want to open the form with *only* a new record showing, use

    DoCmd.OpenForm FormName:=stDocName, DataMode:=acFormAdd

    The DataMode argument to OpenForm tells Access to open the form for adding a new record only. Existing records are not visible (although the user can make them visible by selecting Records/Remove Filter/Sort if that menu option is available).

    If you want all records to be visible, you can use

    DoCmd.OpenForm FormName:=stDocName
    DoCmd.GoToRecord ObjectType:=acDataForm, ObjectName:=stDocName, Record:=acNewRec

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Combo Box (97/2K)

    Hans,

    Many thanks. The first code example is exactly what I need.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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