Load Recordset Values into Access Form-CodePudding

rookie here - I have a form ("6_SubmissionGrid") containing a listbox ("lst_SelectSubm") from which I am creating a recordset ("rs6"). I wish to launch a second form ("6a_ContractQuote") and populate textboxes from the recordset, but am having trouble getting the values to display. I've not set any parameters or events on the second form (except to clear rs6 upon close) and have the following code in the form containing the listbox. The recordset is intended to only hold one unique record.

Dim Db As Database
Dim rs6 As Recordset
Dim SlctSubm As String
Dim strSQL As String
Dim PrincNo As String
Dim PrincName As String
Dim txt_PrincNo As String
Dim txt_PrincName As String

Public Sub btn_LaunchContrQuote_Click()

    Set Db = CurrentDb
    Set rs6 = Db.OpenRecordset("SELECT [5_SUBMISSION].[5_SubmNo], [5_SUBMISSION].[5_SubmStatus], [5_SUBMISSION].[5_DateRecd], [5_SUBMISSION].[3_PrincNo], [3_PRINCIPAL].[3_PrincName], [5_SUBMISSION].[2_AgcyNo], [2_AGENCY].[2_AgcyName], [5_SUBMISSION].[1_ProducerNo], [1_PRODUCER].[1_Last Name], [1_PRODUCER].[1_First Name], [5_SUBMISSION].[4_ObligeeNo], [4_OBLIGEE].[4_ObligName], [5_SUBMISSION].[5_ProjectDescription], [5_SUBMISSION].[5_ProjectCity], [5_SUBMISSION].[5_ProjectState], [5_SUBMISSION].[5_Underwriter], [5_SUBMISSION].[4_AddtlObligee1], [5_SUBMISSION].[4_AddtlObligee2], [5_SUBMISSION].[4_AddtlObligee3], [5_SUBMISSION].[4_AddtlObligee4] " & _
         "FROM (((5_SUBMISSION INNER JOIN 2_AGENCY ON [5_SUBMISSION].[2_AgcyNo] = [2_AGENCY].[2_AgcyNo]) INNER JOIN 3_PRINCIPAL ON [5_SUBMISSION].[3_PrincNo] = [3_PRINCIPAL].[3_PrincNo]) INNER JOIN 4_OBLIGEE ON [5_SUBMISSION].[4_ObligeeNo] = [4_OBLIGEE].[4_ObligeeNo]) INNER JOIN 1_PRODUCER ON ([5_SUBMISSION].[1_ProducerNo] = [1_PRODUCER].[1_ProducerNo]) AND ([2_AGENCY].[2_AgcyNo] = [1_PRODUCER].[2_AgcyNo]) " & _
         "GROUP BY [5_SUBMISSION].[5_SubmNo], [5_SUBMISSION].[5_SubmStatus], [5_SUBMISSION].[5_DateRecd], [5_SUBMISSION].[3_PrincNo], [3_PRINCIPAL].[3_PrincName], [5_SUBMISSION].[2_AgcyNo], [2_AGENCY].[2_AgcyName], [5_SUBMISSION].[1_ProducerNo], [1_PRODUCER].[1_Last Name], [1_PRODUCER].[1_First Name], [5_SUBMISSION].[4_ObligeeNo], [4_OBLIGEE].[4_ObligName], [5_SUBMISSION].[5_ProjectDescription], [5_SUBMISSION].[5_ProjectCity], [5_SUBMISSION].[5_ProjectState], [5_SUBMISSION].[5_Underwriter], [5_SUBMISSION].[4_AddtlObligee1], [5_SUBMISSION].[4_AddtlObligee2], [5_SUBMISSION].[4_AddtlObligee3], [5_SUBMISSION].[4_AddtlObligee4] " & _
         "HAVING ((([5_SUBMISSION].[5_SubmNo])= '" & Me.lst_SelectSubm & "'));")


rs6.MoveLast

PrincNo = rs6.Fields(3).Value
PrincName = rs6.Fields(4).Value

DoCmd.OpenForm "6a_ContractQuote", acNormal, , [txt_PrincNo] = PrincNo And [txt_PrincName] = PrincName


End Sub

Appreciate any recommendations. I've tried various configurations on the Where Condition of the OpenForm command, but no luck. Thank you!

CodePudding user response：

You could address the form after having opened it:

Dim ContractQoute As Form
'...
PrincNo = rs6.Fields(3).Value
PrincName = rs6.Fields(4).Value

DoCmd.OpenForm "6a_ContractQuote", acNormal

Set ContractQoute = Forms("6a_ContractQuote")
ContractQoute![txt_PrincNo].Value = PrincNo
ContractQoute![txt_PrincName].Value = PrincName
Set ContractQoute = Nothing