Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    32

    Unanswered: Forms that communicate info well

    Ok. So what im looking at doing is haveing from A that has a "look up" button

    Once clicked it opens another form that you can search through a listing of data.

    I need to select the data and then "submit" it to the previous form.

    I feel that this needs some VB coding and I have VB experience but Im just starting with Access VB. What kind of command should i be looking for to do this?

    Also... I may need to select send multiple times and send them along as well... but one step at a time eh?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure I understand what you're trying to do. If you're looking for a search type form, there are several, several examples in the code bank of search forms to search for data and then return the found record to another form.

    Here's one of several: http://www.dbforums.com/6289596-post39.html
    or
    http://www.dbforums.com/6325742-post63.html

    or
    http://www.dbforums.com/6342970-post69.html

    or
    http://www.dbforums.com/6250864-post11.html

    or
    http://www.dbforums.com/6274726-post18.html

    or
    http://www.dbforums.com/6274809-post27.html
    Last edited by pkstormy; 11-23-09 at 23:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2009
    Posts
    32
    Thanks pkstormy!

    Those are helping me alot!

    But the real nuts of the problem is after i have searched and found what i want... how do i send that info back to the form that opened the search form?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Your 'data' form typically has a recordset (which has an autonumber or primary key field). In your search forms, the key field you have that same autonumber or primary key (which is the 1st column in the listbox depending on what search form you're looking at.) When you click (or double-click) on the record in the listbox, you first store that autonumber into a variable (ie. CID), then close the search form and you then utilize that autonumber field (ie CID or whatever you call your variable) to open your data form using the WHERE clause (ex: docmd.openform "MyDataForm",,"[RecordID] = " & CID & ""). This then opens the data form showing only the record you selected.

    If your data form is already open, you have 2 options, you can close and reopen it with the criteria (as the above example), or you can utilize the docmd.FindRecord command (ex:
    Forms!MyDataForm!RecordID.setfocus
    docmd.findrecord CID

    (Note: for example purposes RecordID above would be your autonumber field for your table - ie. in the recordset in your data form - make sure this field is on your data form.)
    Last edited by pkstormy; 11-24-09 at 13:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My preferred method of doing this is to open the lookup form (or search form) with the DoCmd.OpenForm method using the acDialog parameter. The acDialog parameter does two things:

    1. It suspends execution of the code from the form that called it
    2. It prevents the user from doing anything else

    Here is some code that takes advantage of acDialog:

    Code:
        DoCmd.OpenForm "frmProjectEditorProjectChangeDelete", , , "ChangeID=" & Me.sfrChangeDetail.Form.ChangeID, , acDialog
        
        'Check to see what the user did on the custom delete form
        intResponse = Forms("frmProjectEditorProjectChangeDelete").txtResponse
        DoCmd.Close acForm, "frmProjectEditorProjectChangeDelete"
        If intResponse = vbNo Then Exit Sub
    When you open the lookup form control stays with the lookup form until it is closed OR hidden. The user sees the lookup form, chooses what he wants and then presses the Close button. The Close button only hides the form, then the code starts executing again after the form has been hidden. Since the form is still open, I can check the txtResonpse textbox to see what the user chose to do and then decide what to do. In this case, I close the form (since the user only hid it) and then move on to other code.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I can't say that I agree with DCKunkle on this method (I don't like doing this unless I absolutely need to as a last resort). But I do respect his response as another viable option and I know others like to use this method as well.

    DCKunkle - it would be great to have something like this posted in the code bank along with the other search examples. Currently there's no example showing this method.
    Last edited by pkstormy; 11-24-09 at 21:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    PKStormy, in no way am I offended by your comments/criticism of my method of choice. Anywhere I have used it I have not had any problems with it. I am very curious why you consider it a last resort. I respect your opinion (I have read many of your responses) and would like to know if this is personal preference or whether you have had any issues with this method.

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

    Thank you for not being offended! The same here. This may or may not be the same as the way you use the acDialog, but I used the acDialog in my forms to suspend the code in my functions to return a value back to the function (from the form) to continue the execution of the function with that value. The only reason I didn't like to do this is because it tied up the system resources with the mdb and caused problems for other users. I try to drive all my events around functions without a 'pause' so to speak in the function by waiting for the user to supply a value for that function to continue. Sometimes users would open the form and then 'sit', leaving for lunch, smoke-break, drink-break, or whatever (we both know how user's don't care about what state they leave the mdb when they want their break - only that it works when they get back.)
    Last edited by pkstormy; 11-25-09 at 22:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Thank you for your explanation, PKStormy. I have never run across that particular problem. The databases that I havae created and use don't have many users. Your explanation makes alot of sense.

  10. #10
    Join Date
    Nov 2009
    Posts
    32
    Ive been trying to Do both ways you guys have suggested and Im still haveing problems.

    Basicaly I have a from that is makeing new records and one of the fields is "Leads". I want to click on a button, open a form, Search through the "leads", and when i find the right one, select it, Hit a "add" Button, which closes the search form and adds the "lead" to a list box on the first form.

    Basicaly I need to add the selected "Lead ID" to a list box.

    Then I can work on getting that list box to write to the ManytoMany table for the call to lead id tbl but thats step 160 and im on step 2.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok BottledFurry,

    Let's see what we can do to help you. You have a form that is making new records. You want to click a button, open a form, search through the form "leads", and when you find the right one, select it, otherwises hit an add button which closes the search form and adds the 'lead' to a list box on the first form.

    when you select a leadID that is not in the listbox, I'm assuming you want to add that leadID to a new record.

    Your first goal is to add a new record to a new leadID that isn't already in the table. You'll need to test to see if the new leadID already exists. If it does, then open the recordset on the form based on the lead that already exists. This is a docmd.openform "MyForm", using the WHERE clause where there LeadID exists. If it doesn't, you'll need to open the form with a new record. If it does exist, you'll want to open the form based upon the existing record.

    You can do this in different ways....

    when you open the recordset and find that no records exist based upon the ID you selected, you open the form with a new recordset with the new LeadID. This is a select statement where you find no records and add a new recordset to the form. If it does exist, you open the recordset on that record and return records based on that recordset. you'll need to do some coding to see if that record exists and if it doesn't, add the record to your form.

    Sorry but I'm having problems thinking right now. I'll try to help you the best I can but I'm a little bit swamped with other responses.
    Last edited by pkstormy; 11-29-09 at 01:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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