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.