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

    Text box value from a query (97/2K)

    I have a form (bound to a table) which consists of 4 text boxes. By typing a number into the CustomerID box, the relevant customer details appears in the subform. The subform contains a text box showing, among others, the Area in which the customer lives. On the main form is another text box which I want to display the Driver ID corresponding to the value in the subform Area text box. This can be obtained from a driver table which stores Driver ID and Area.
    I have constructed a query which displays the correct Driver ID.
    How do I get the text box(Driver ID on the main form) to display its result?
    Also, once displayed, can this be stored in the bound table?

    Many thanks

    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: Text box value from a query (97/2K)

    >>How do I get the text box(Driver ID on the main form) to display its result?
    If you have a query that will find the right Driver ID, then put this value into the text box using a dlookup.
    In the after update event for the text box where you type the customerID put something like:

    me!driverID = dlookup("[Driverid]","qryfindDriverID")

    >>also, once displayed, can this be stored in the bound table?
    Yes assuming the field is in the underlying table. Just set the control source of the driverid control to the driverid field.
    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: Text box value from a query (97/2K)

    Thanks John for the response, but may I check something?
    You said "In the after update event for the text box where you type the customerID put something like:"
    Do you actually mean the CustomerID text box or do you mean the DriverID text box?

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

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

    Re: Text box value from a query (97/2K)

    Hi John

    I have done as you suggested but I get an error message stating that MsAccess cannot find the macro
    <me!driverID = dlookup("[Driverid]","qryfindDriverID")>
    I've made sure that my names are valid.

    Help!

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

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

    Re: Text box value from a query (97/2K)

    I meant the CustomerID because I thought youy wanted the DriverID looked up when you enter a customer.
    the afterUpdate event happens after you enter or change the contents of a form control.
    Regards
    John



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

    Re: Text box value from a query (97/2K)

    Rob
    To put this code in the afterupdate event, you have to first select [event procedure] , then click on [event procedure] so that the three dots appear to the right of it. Then click these three dots to be taken off the VB editor. the code I gave you needs to written off in the VB editor.

    If you just write it into the properties box Access assumes what you wrote is the name of a macro. (I assume that is what you have done.)
    Attached Images Attached Images
    Regards
    John



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

    Re: Text box value from a query (97/2K)

    Thanks John. I had indeed typed the code into the AfterUpdate box. Following your instructions, it now works.
    However, another problem has arisen. When I select a Customer ID from a drop down list (I've changed the text box for a combo box) the Customer Details subform displays the appropriate record. This worked fine until a few moments ago when it ceased to function. Now, when I select a Customer ID, the Customer Details subform does not display a new record.
    If you have the time, I would be grateful for any suggestions.

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

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

    Re: Text box value from a query (97/2K)

    You must have changed something for it to stop working.
    Here are two things that can cause the behaviour you describe.

    Is the subform linked via a mster field - child field relationship? In design view, if you look at the properties of the subform control do you have values in the Link child Fields and Link Master fields properties ? Are they the correfct ones?

    Sometimes subforms don't use these link fields. Instead they have a data source that is a query that uses the combo box in the main form as a parameter. In this case, you need to requery the subform after entering data in the combo.
    Regards
    John



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

    Re: Text box value from a query (97/2K)

    Thanks for getting back, John. Both Link Child Fields and Link Master Fields properties show the value <Customer ID> which I presume is correct. I don't think I've changed anything, but.....
    Should I recreate the primary form?

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

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

    Re: Text box value from a query (97/2K)

    I don't know what to suggest!
    Recreating the form might the quickest thing to do.
    Have you been playing aroiund with the combo box? Are you sure that the customerID is still the bound column?
    (It is common with combos to display the name but store the ID)
    Regards
    John



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

    Re: Text box value from a query (97/2K)

    Confused. I've recreated the form and subform from scratch. When I open the main form, the previously selected record is displayed in the subform. If I select a new Customer ID the subform does not change. However, if I close the form then reopen it, the new Customer details appear in the subform.

    Can anyone help?

    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
  •