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

    Unanswered: Contact Insertion error...

    Ok, I'm having an issue trying to insert a contacts' last name, first name and phone number into the parent form.

    If you open the db, double-click on "frmTask", at the bottom there is a Listbox called "Pick-Up Contact" / List87 respectively. When you double-click on that list box, it'll open the form, "frmSearchContact". Here a user can search for a contact name and phone number to call when the task is completed and ready for pick-up by the originating organization. THIS WORKS FINE, even returns the results and all.

    What I'm trying to do is when the user searches for someone, like if you type in "T", it'll pull up a contact name, when the user finds the person needed, they then will double-click on it in the list box on that form, "frmSearchContact", this will then input the LastName, FirstName, Phone of that particular contact into the listbox on the parent form, "frmTask".

    That's what I can't figure out.

    Thanks for the help,

    B
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I don't open projects here ... Is the target listbox bound? You can easily populate any listbox by adding the value(s) by using a query or directly adding them to it's value list ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look at the "Key Up" events - this will allow you to make "JustType" searches.
    I uploaded a project for you a while back called "Search.mdb" take a look in there - it has pretty much all you need except you'll simply need to move the click event to the key up event of the textbox.
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    Look at the "Key Up" events - this will allow you to make "JustType" searches.
    I uploaded a project for you a while back called "Search.mdb" take a look in there - it has pretty much all you need except you'll simply need to move the click event to the key up event of the textbox.

    George,

    I have you're example, however, I don't thik it does what I needed mine to do. I'll take another look at it.

    Mine works fine, clicking in the box, opens the form, the search ability of the form works. I'm just trying to pass the data that is found from the search back to the parent form into that list box but save just the contactID number into the table.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Example

    Bryan,

    I thought I'd send you a quick example. There are a lot of different ways you can do this. The easiest is just set the rowsource of List7 to a query name (which automatically queries when you do this) after the Find button is clicked.

    You can really get fancy with searches like these (see my SampleSearchForm in the code bank! Please take a look at it as it's pretty nice.) I wanted to keep this simple though.

    Whoops....Disregard this attachment!!! I need to make a quick fix.
    Attached Files Attached Files
    Last edited by pkstormy; 05-28-07 at 20:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    New example

    Sorry about that - I forgot to put the code which writes back to frmTask. I changed the listbox (List85 for Contact_ID) on frmTask to a combobox (works a little better.) But you can change it back to a listbox if you like (if you change it back to a listbox, on the rowsource, set the criteria for the Contact_ID = forms!frmTask!Contact_ID. The Contact_ID field NEEDS to be on the frmTask (which I added and made invisible - see note below Contact_ID field on frmTask.) Otherwise you'll have trouble storing the value in the tblTask!

    The way it works is this:
    The combobox (or listbox) i.e. list85 on frmTask ControlSource has criteria = Forms!frmTask!Contact_ID.
    In the FrmSearchContact, you simply pass the value in List7 (Contact_ID) to Contact_ID on frmTask and then requery List85 (which again, can be either a combobox or listbox. As a combobox, I set the ControlSource = Forms!frmTask!Contact_ID. If you change it back to a ListBox, in the ROWSOURCE for List85 set the criteria for the Contact_ID column = Forms!frmTask!Contact_ID. The reason a combobox works better is you don't need to requery the listbox when you go to a different task since the ControlSource on the combobox is DIRECTLY tied to Contact_ID (i.e. via the ControlSource). Otherwise, you may have to issue a: Forms!frmTask!List85.requery if you were to go to a different Task which may or may not be a problem.

    I also added a First Name search combobox on frmSearchContact to just show you how that works. You can delete it if you like. A "Show All" button is also always good to have on search forms as well.
    Attached Files Attached Files
    Last edited by pkstormy; 05-28-07 at 21:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I'm going to take a look at it at work tomorrow and see if I can figure it out.

    I know I put the "ContactID" box on the search contact form, I was going to see if what you did could be done using that method rather than putting in on the frmTask and hiding it. I don't know, I'll give it a whirl at 6am tomorrow!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Attachement didn't attach

    Quote Originally Posted by georgev
    I uploaded a project for you a while back called "Search.mdb" take a look in there - it has pretty much all you need except you'll simply need to move the click event to the key up event of the textbox.
    Here it is again - double click a name and you'll open the edit form for that person.
    The code for what you want is there and is really easy to implement.
    Attached Files Attached Files
    George
    Home | Blog

  9. #9
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Sorry about that - I forgot to put the code which writes back to frmTask. I changed the listbox (List85 for Contact_ID) on frmTask to a combobox (works a little better.) But you can change it back to a listbox if you like (if you change it back to a listbox, on the rowsource, set the criteria for the Contact_ID = forms!frmTask!Contact_ID. The Contact_ID field NEEDS to be on the frmTask (which I added and made invisible - see note below Contact_ID field on frmTask.) Otherwise you'll have trouble storing the value in the tblTask!

    The way it works is this:
    The combobox (or listbox) i.e. list85 on frmTask ControlSource has criteria = Forms!frmTask!Contact_ID.
    In the FrmSearchContact, you simply pass the value in List7 (Contact_ID) to Contact_ID on frmTask and then requery List85 (which again, can be either a combobox or listbox. As a combobox, I set the ControlSource = Forms!frmTask!Contact_ID. If you change it back to a ListBox, in the ROWSOURCE for List85 set the criteria for the Contact_ID column = Forms!frmTask!Contact_ID. The reason a combobox works better is you don't need to requery the listbox when you go to a different task since the ControlSource on the combobox is DIRECTLY tied to Contact_ID (i.e. via the ControlSource). Otherwise, you may have to issue a: Forms!frmTask!List85.requery if you were to go to a different Task which may or may not be a problem.

    I also added a First Name search combobox on frmSearchContact to just show you how that works. You can delete it if you like. A "Show All" button is also always good to have on search forms as well.

    Paul,

    I took a look at the attachment. It works but the only thing that I'm having an issue with is that it won't display the phone number of the individual.

    I did set up 4 columns, 0";1";1";1" but the only thing that shows is a contact name, no phone number.

    Although i do like the list box better, I just can't figure out how to do it just yet. I'm going to work on that.


    *** Ok, switched it back to a list box. It works some what the same way, just displays the first and last name inside ONE of the fields of the listbox. It doesn't display the way I'd like it to: LastName, FirstName, Phone

    I think it's some thing with the following line:

    SELECT tblContact.Contact_ID, [FirstName] & " " & [LastName] AS Name FROM tblContact WHERE (((tblContact.Contact_ID)=Forms!frmTask!Contact_ID ));
    Last edited by Grafixx01; 05-29-07 at 12:41.

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    The only other thing that I'm finding difficult is making the list box on the frmSearchContact show up blank when it is opened and only return results when the user types in criteria and searches for it.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT tblContact.Contact_ID, [FirstName] & " " & [LastName] AS Name FROM tblContact ...
    It's showing in one field because you've told it to do that!
    Code:
    SELECT Contact_ID, FirstName, LastName FROM tblContact...
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're populating the listbox via VBA then you can apply a recordsource that will return no values in the form load event.
    Code:
    SELECT Contact_ID, FirstName, LastName
    FROM tblContact
    WHERE 1 = 2
    See how the above returns no results?
    George
    Home | Blog

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    Code:
    SELECT tblContact.Contact_ID, [FirstName] & " " & [LastName] AS Name FROM tblContact ...
    It's showing in one field because you've told it to do that!
    Code:
    SELECT Contact_ID, FirstName, LastName FROM tblContact...

    georgev,

    That worked! A little time to resize the columns, but it worked! Thanks~!

    Now I gotta figure out how to make the listboxes in both forms show up blank until data is 'searched for' and 'double-clicked' into them.

    BUT ...

    THANKS AGAIN!

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Grafixx01
    Now I gotta figure out how to make the listboxes in both forms show up blank until data is 'searched for' and 'double-clicked' into them.
    See post #12 + Form_Load event.
    George
    Home | Blog

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by georgev
    If you're populating the listbox via VBA then you can apply a recordsource that will return no values in the form load event.
    Code:
    SELECT Contact_ID, FirstName, LastName
    FROM tblContact
    WHERE 1 = 2
    See how the above returns no results?

    Ok, since I'm trying to learn, can you explain what exactly this does? I put it at the "on load" event for both forms? and I guess mine would change to be:

    Code:
    SELECT ContactID, LastName, FirstName, Phone
    From tblContact
    Where 1 = 2

Posting Permissions

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