Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2017
    Posts
    12

    Passing a record from form to form

    Hello all.

    I'm having difficulty with populating a subform with the data selected in a list box.

    [LeadID] = primary key

    The original form has a listbox in a "View" form where column 0 is the primary key. What I want to do is if the user selects an item, and clicks a button to go to the "Remove" form, the "Remove" form is populated with the data from the selected row in the listbox. The user will then verify they want to remove the item, and if so, the table will be updated in the SQL to flag "Removed" as 1 and "DateRemoved" as the current datetime.

    I've set up a bunch of MsgBox popups for troubleshooting. On clicking a row, (row 2 for example) it confirms the [LeadID] 2 = 2. If I click row 5, it confirms the [LeadID] 5 = 7 (6 does not apply to this record). So I know it's not just picking the row, it's the value of the [LeadID] (which is what I want).

    If I click "Remove Lead", another MsgBox pops up telling me that [LeadID] 2 has been selected. This is correct, and is the last thing in the VBA for this form before it's passed off to the "Remove" form.

    When the "Remove" VBA is started, another MsgBox pops up telling me that [LeadID] 2 is selected. This tells me that the value HAS been passed to the new form. HOWEVER, when the form populates, it populates with [LeadID] 1 regardless of what lead has been selected.

    The code is below:
    "View" form
    Code:
    Public SelectedLead As String
    Private Sub RemoveLead_Click()
    MsgBox ("Opening the delete form with " & SelectedLead & " being selected")
    DoCmd.OpenForm "frmSearchLead_Delete", acNormal, , , , , SelectedLead
    End Sub
    "Remove" form
    Code:
    Dim DeleteLead As Long
    Private Sub Form_Load()
    DeleteLead = Me.OpenArgs()
    MsgBox ("Hi, this is the delete form telling you that I have " & DeleteLead & " in my selected variable")
    End Sub
    SQL for the query that the "Remove" form gets its Record Source. I suspect this is where it's going wrong
    Code:
    SELECT dbo_tblSearchLeads.CustomerID, dbo_tblSearchLeads_Sources.LeadSourceName, dbo_tblSearchLeads.Phone, dbo_tblSearchLeads.Email, dbo_tblSearchLeads.LeadName, dbo_tblSearchLeads.LeadNotes, dbo_tblSearchLeads.EliminateReason, dbo_tblSearchLeads.LeadID
    FROM dbo_tblSearchLeads INNER JOIN dbo_tblSearchLeads_Sources ON dbo_tblSearchLeads.LeadSource = dbo_tblSearchLeads_Sources.LeadSourceID
    WHERE (((dbo_tblSearchLeads.LeadID)=[Forms]![frmSearchLead_View]![LeadID]));
    Does anyone have any ideas? I've Googled until my eyes went square but nothing is helping.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't get why you're passing things from one form to another (or even opening other forms) for this. Why not just run a query from the form you're already on? However, that query needs to be an Update query but what you posted is a select query. I say Update because my understanding is that you don't want to delete a record, just update one or more fields so that it doesn't appear in the original listbox. After the Update, you'd need to requery the listbox.

    One thing you could/should do is Debug.Print your sql to the immediate window and check it. If it's valid and you copy/paste to a new query and run it, you'll see that all it does is select one or more records, which is not what you need to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try to remove the record source for the second (remove) form and set it dynamically in its OnLoad or OnOpen event:

    Code:
    Dim DeleteLead As Long
    
    
    Private Sub Form_Load()
    Dim sSQL as String
    DeleteLead = Me.OpenArgs()
    
    sSQL="SELECT dbo_tblSearchLeads.CustomerID, dbo_tblSearchLeads_Sources.LeadSourceName, dbo_tblSearchLeads.Phone, " & _
            "dbo_tblSearchLeads.Email, dbo_tblSearchLeads.LeadName, dbo_tblSearchLeads.LeadNotes, " & _
            "dbo_tblSearchLeads.EliminateReason, dbo_tblSearchLeads.LeadID FROM dbo_tblSearchLeads INNER JOIN " & _
            "dbo_tblSearchLeads_Sources ON dbo_tblSearchLeads.LeadSource = dbo_tblSearchLeads_Sources.LeadSourceID " &_
            "WHERE dbo_tblSearchLeads.LeadID=" & DeleteLead & ";"
    Me.RecordSource=sSQL
    
    
    MsgBox ("Hi, this is the delete form telling you that I have " & DeleteLead & " in my selected variable")
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Mar 2017
    Posts
    12
    Thanks for your help.

    Dynamically changing the recordsource worked a treat.

    Is there an easy way to get the parent listbox to requery as soon as the child box closes? I tried requerying the list box in the parent window on "GotFocus", but the user needs to click in the box before it disappears.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure I follow you, which child box/ parent window are you talking about, I thought you had two stand alone forms.

  6. #6
    Join Date
    Mar 2017
    Posts
    12
    I'm not much of an Access programmer, my wording may be incorrect.

    There is a "View" form which has a listbox in it which allows the user to select a line and click "Remove" to update that particular record.

    When "Remove" is clicked, another form, which I'll call "Remove", is opened showing all the details of the record to be removed. If the user clicks cancel, it just does a Me.Undo and closes the box. If they confirm, the record is updated so it is no longer qualified to display in the listbox of its parent. (Eliminated is set to true, which the SQL to populate the listbox tests for). The changes are committed and the "Remove" form is closed, taking the user back to the "View" box.

    Is there something I can put in the "Remove" box to update the listbox in the "View" form before closing its own window?

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you need to add code to the unload event of the "remove" form to request the listbox:

    Forms![frmView]!lstYouListBox.Requery

    Cheers,

  8. #8
    Join Date
    Mar 2017
    Posts
    12
    Perfect!

    I appreciate your help.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    [QUOTE=Rufusdisturbed;446957]Perfect!

    I appreciate your help.[/QUOTE

    Glad to be able to help!
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-14-2017, 03:17 PM
  2. Replies: 5
    Last Post: 11-18-2016, 10:27 AM
  3. Replies: 3
    Last Post: 09-19-2016, 06:46 AM
  4. Replies: 6
    Last Post: 07-16-2014, 12:18 PM
  5. Replies: 9
    Last Post: 10-12-2011, 02:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums