Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: It's complicated

    Ok, first off, I'll attach the db so you can see what I'm working with as far as how the db has come. Just hold 'shift' to bypass the security (this will be changed).

    Here's what I'm trying to do:

    The form, frmTask , has a list box at the bottom where it says "Pick-Up Contact". When the user double-clicks on this, it opens another form, frmSearchContact, where the user types in the last name of the contact who is asked to be notified when the task is ready for pick-up.

    When the user types in the last name, then clicks "Find!" command button, it queries the table, tblContact, for the users with that last name. The results are displayed below in a list box with 3 rows: Last Name, First Name, Phone Number

    When the user sees the one they want, they can either double-click on it and the data then gets populated into the "Pick-Up Contact" text / list box on the frmTask. OR they can highlight the one they want and then click "Ok" command button which will insert the information back in the "Pick-Up Contact" text / list box on the form, frmTask.

    Anyone?
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    What do you have so far? Where are you stuck on?
    Me.Geek = True

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    nick,

    I got the 'double-click' event in the frmTask to open the frmSearchContact form. That's no big deal. I have the form designed the way that I want it.

    I just can't figure out how to write the query to get the text box (txtLastName) data to query the tblContact when the user clicks "Find!" and then display in a list box below the results with the fields of "LastName, FirstName, Phone" respectively in 3 columns.

    Then I need to have the user double-click on the name they want or highlight it and then click 'Insert Contact' back into the Pick-Up Contact field on frmTask

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The way I see it, you can scratch frmSearchContact altogether, unless you need it for a different purpose.

    In frmTask, change the list box to a 3-column combo box. The 3 columns will be LastName, FirstName, and PhoneNo. The widths would be 1";0";0". The row source type would be Table/Query, and the row source property would be a SQL statement (which you can design in the Access' query design interface), as follows:
    Code:
    SELECT fldLastName, fldFirstName, fldPhoneNo FROM YourTableName
    where all three field names and the table name should be replaced with your real names for these objects.

    You can name the combo box cmbLastName (you don't need a separate lastName text box), and all three (cmbLastName, and the FirstName, and PhoneNo text boxes) should all be blank (unbound) in their Control Source property.

    In the cmbLastName AfterUpdate property, you should have code as follows:
    Code:
    FirstName = Me.cmbLastName.Column(2)
    PhoneNo = Me.cmbLastName.Column(3)
    Does that serve your purpose?

    Sam

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by Sam Landy
    The way I see it, you can scratch frmSearchContact altogether, unless you need it for a different purpose.

    In frmTask, change the list box to a 3-column combo box. The 3 columns will be LastName, FirstName, and PhoneNo. The widths would be 1";0";0". The row source type would be Table/Query, and the row source property would be a SQL statement (which you can design in the Access' query design interface), as follows:
    Code:
    SELECT fldLastName, fldFirstName, fldPhoneNo FROM YourTableName
    where all three field names and the table name should be replaced with your real names for these objects.

    You can name the combo box cmbLastName (you don't need a separate lastName text box), and all three (cmbLastName, and the FirstName, and PhoneNo text boxes) should all be blank (unbound) in their Control Source property.

    In the cmbLastName AfterUpdate property, you should have code as follows:
    Code:
    FirstName = Me.cmbLastName.Column(2)
    PhoneNo = Me.cmbLastName.Column(3)
    Does that serve your purpose?

    Sam
    Sam,

    I had it that way before. However, I had it in a 3 column list box. I found this to be inefficient because there may be multiple contact with the same last name or other criteria. And on the 'frmSearchContact' it has a command button to open another form, frmContact, so that the user can enter a new contact if the contact does not already exist within the db.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You can do all that from the combo box.

    Adjust the widths of the two hidden columns so you can see them (e.g. 1";1";1"). The contact isn't in the database? No problem. Set the combo box On Not In List property to [Event Procedure], and put in some VBA code to open the frmContact form, do the input, close it, and capture what you inputted.

    A list box simply won't hack it for this purpose.

    Sam

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Is this what you rquire?

    Done a little demo for you in your db. all my new additions re prefixed with XX_

    hope this helps

    dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by danlindley
    Done a little demo for you in your db. all my new additions re prefixed with XX_

    hope this helps

    dan

    Dan,

    Looked at it. If you look at the form that I have, frmTask, it's got the box I need populated at the bottom called, Pick-Up Contact.

    Currently, when the user double-clicks this box, the form, frmSearchContact opens. (I've done this).

    On that form, frmSearchContact, there is a text box that the user enters the last name of the individual in and then I have to make a command button that says "Find!". This then takes the data that the user put into the text box and querires the table, tblContact.

    The results are returned below in the list box with the LastName, FirstName, Phone. The user can then double-click on the desired name and it then populates back to the main form, frmTask.

  9. #9
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    sorry bud;

    i can't see where there needs to be a join between contacts and task. Is the field for the contact also in the table for tasks? if so i can only see user name?

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  10. #10
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    cancel that i see
    sometimes simple is best.... and i'm just a simple fellow.

  11. #11
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Try this one.

    do a search and click the button below where the results appear.

    dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  12. #12
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by danlindley
    Try this one.

    do a search and click the button below where the results appear.

    dan

    Ok, I'm trying to figure out what you did.

    The "Pick-Up Contact" on the frmTask is now grey-ed out. I can't double-click to open it. The search contact form doesn't allow me to actually search for anyone.

    Ummm... Could you explain?

  13. #13
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    ok, search for someone, eg, type the first OR last in the text box and click the button by the text box.

    Then highlight the result, there won't be many, and click the button below the results window.

    That should work and you should see the contact name in the box on frm tasks

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  14. #14
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by danlindley
    ok, search for someone, eg, type the first OR last in the text box and click the button by the text box.

    Then highlight the result, there won't be many, and click the button below the results window.

    That should work and you should see the contact name in the box on frm tasks

    dan

    Gotcha. It's close to what I needed.

    However, if you look at the original db that I posted and you open the frmTask form, double-click in the box at the bottom that shows the field "Pick-Up Contact". When you do this, it'll open up the "Search for Contact" form, this is where I need it to search, display, double-click and put the selected user back into that field, "Pick-Up Contact" on the frmTask.

    * i'm going to see if i can figure out what you did on the search form and migrate it over. I'll give it a whirl!

    **Original db is attached.
    Attached Files Attached Files

  15. #15
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    a diferent way to look at it

    question,
    won't a lookup box be sufficient?

    - look at this example. You ca still type the surname of a person and when you have the one you want it populatesthe rest of the info for you, without needing another form

    dan
    Attached Files Attached Files
    Last edited by danlindley; 05-18-07 at 18:12.
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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