Selecting records from msgBox

[Tupacmoche]

Hi Form Masters:

I have an issue with a subform (Grid layout). The first column is a ComboBox it has the following query for its data soruce:

SELECT dbo_dm_proposal.Proposal_id, dbo_dm_proposal.Proposal_Title, Format([Target_Amt],"Currency") AS Ask_Amt, dbo_dm_proposal.Start_Date, dbo_dm_proposal.Staff_Report_Name
FROM dbo_dm_proposal
WHERE (((dbo_dm_proposal.Id_Number)=[Forms]![Donor_Intake_Form]![DonorAdvanceID]) AND ((dbo_dm_proposal.Proposal_Is_Active)="Y"))
ORDER BY dbo_dm_proposal.Proposal_id;

It works fine the user can select any of the record that are returned when it is selected. Now to the issue, I added an event procdure on Dbl Click as follows:

Private Sub ProposalNum_DblClick(Cancel As Integer)
Dim MsgStr1 As String
Dim MsgStr2 As String
Dim MsgStr3 As String
Dim MsgStr4 As String
Dim MsgStr5 As String
MsgStr1 = Nz(DLookup("Proposal_id", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "'"))
MsgStr2 = Nz(DLookup("Proposal_Title", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "'"))
MsgStr3 = Nz(DLookup("Target_Amt", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "'"))
MsgStr4 = Nz(DLookup("Start_Date", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "'"))
MsgStr5 = Nz(DLookup("Staff_Report_Name", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "'"))
MsgBox ("Proposal Number: " & MsgStr1 & vbCrLf & "Proposal Title: " & MsgStr2 & vbCrLf & "Target_Amt: " & Format(MsgStr3, "Currency") _
& vbCrLf & "Start Date " & MsgStr4 & vbCrLf & "Staff Responsible: " & MsgStr5)
End Sub

The requirement is that a window pops open showing the record matches in it which can be selected from the popup window. Can this be accomplished in an Access MsgBox ? If not what is the best approach?

 

[June7]

Not with MsgBox. An InputBox can display a numbered list (a concatenated string). User responds with number. Otherwise, use a popup form.

 

[Tupacmoche]

Hi Data Masters,

Still working on the first issue but can anyone see what is wrong with my two condition DLookup I get the error message : Syntax error (missing operator) in query expression. But, I don't see what is wrong. Here is the code.

MsgStr1 = Nz(DLookup("Proposal_id", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"))
MsgStr2 = Nz(DLookup("Proposal_Title", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"))
MsgStr3 = Nz(DLookup("Target_Amt", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"))
MsgStr4 = Nz(DLookup("Start_Date", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"))
MsgStr5 = Nz(DLookup("Staff_Report_Name", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"))

Any other suggestions on how to address the first issue is appreciated.

 

[MajP]

Need to learn how to debug your own code

strWhere = "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"
debug.print strWhere
MsgStr1 = Nz(DLookup("Proposal_id", "dbo_dm_proposal", strWhere))
strWhere = "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & " and [Proposal_Is_Active]= '" & "Y" & " '"
debug.print strwhere

Please post your debug code.

 

[MajP]

are your IDs numeric and is your is active a boolean by chance.

 

[Tupacmoche]

Hi MajP,

Id's is varchar(10) and Active is char(1) so strings.

 

[MajP]

and your debug.print of the criteria? Need to see how it resolves.

 

[Tupacmoche]

Hi MajP,

I have not used Debug.Print but did find the issue. I left out a single quote '.

MsgStr1 = Nz(DLookup("Proposal_id", "dbo_dm_proposal", "id_Number= '" & [Forms]![Donor_Intake_Form]![DonorAdvanceID] & "' and Proposal_Is_Active = '" & "Y" & " '"))

 

[Minty]

You've gone into quote meltdown overload at the end - simply use

And Proposal_Is_Active = 'Y'"))