Setting the controlsource of an object

[f9073341]

Hi guys,

I'd like to set the controlsource of a subform's textbox. The subform is continuous. I declare an sql query on form load and wish to dynamically bind the fields from that query to the various objects on the form. Here is my code so far.

    Dim alpha As String
    Dim strSQL As String
    Dim rst As DAO.Recordset
    alpha = "user1"
 
    strSQL = "SELECT tblUserReqAssignment.Requirement, tblRequirement.Acceptance_Event, tblRequirement.Verification_Status " _
                & "FROM tblUserReqAssignment, tblRequirement " _
                & "WHERE (((tblUserReqAssignment.Requirement)=[tblRequirement].[SR_ID]) AND " _
                & "((tblUserReqAssignment.User)= '" & alpha & "'));"
 
    Me.RecordSource = strSQL
 
    Set rst = CurrentDb.openRecordSet(strSQL, dbOpenDynaset)
 
    txtSR.ControlSource = rst.Fields("Requirement")
    txtAcceptanceEvent.ControlSource = rst.Fields("Acceptance_Event")
    txtStatus.ControlSource = rst.Fields("Verification_Status")

It doesn't work right now which is where I hope you guys can help me!

Many Thanks,

 

[boblarson]

No DAO Recordset needed. It should be:

    Dim alpha As String
    Dim strSQL As String
    alpha = "user1"
 
    strSQL = "SELECT tblUserReqAssignment.Requirement, tblRequirement.Acceptance_Event, tblRequirement.Verification_Status " _
                & "FROM tblUserReqAssignment, tblRequirement " _
                & "WHERE (((tblUserReqAssignment.Requirement)=[tblRequirement].[SR_ID]) AND " _
                & "((tblUserReqAssignment.User)= '" & alpha & "'));"
     
   Me.RecordSource = strSQL
 
     
    Me.txtSR.ControlSource = "Requirement"
    Me.txtAcceptanceEvent.ControlSource = "Acceptance_Event"
    Me.txtStatus.ControlSource = "Verification_Status"

 

[f9073341]

You're a star! That'll definitely work... right after I fix a problem I totally overlooked. I'll try and explain it as best I can.

My SQL statement below retrieves the result based on matching the value assigned to "alpha" (which will eventually be a global variable which has the users network ID assigned to it). However, what it retrieves is what that users surname is, what I need is for it to retrieve those results based on the net ID.

The Net ID is stored in the 'tblUsers' table along with the Surname. The Surname (with accompanying link to requirement) is in the tblUserReqAssignment table.

What SQL should I use to be able to reach into the tblUsers table and grab the NetID name based on the Surname in the tblUserReqAssignment?

    Dim alpha As String
    Dim strSQL As String
    alpha = "user1"
 
    strSQL = "SELECT tblUserReqAssignment.Requirement, tblRequirement.Acceptance_Event, tblRequirement.Verification_Status " _
                & "FROM tblUserReqAssignment, tblRequirement " _
                & "WHERE (((tblUserReqAssignment.Requirement)=[tblRequirement].[SR_ID]) AND " _
                & "((tblUserReqAssignment.User)= '" & alpha & "'));"
     
   Me.RecordSource = strSQL
 
     
    Me.txtSR.ControlSource = "Requirement"
    Me.txtAcceptanceEvent.ControlSource = "Acceptance_Event"
    Me.txtStatus.ControlSource = "Verification_Status"

If you are able to answer this, you are truely genius... I struggle to follow this problem and it's in my own code!!

Maybe a subquery?

Many Thanks

 

[boblarson]

What SQL should I use to be able to reach into the tblUsers table and grab the NetID name based on the Surname in the tblUserReqAssignment?

I would create a function using a DLookup and pass it the Surname in query to return the ID.