Populate Text box on report based on value on another form

Question

Very novice question but I have a report that has a couple of columns including usernames. I also have a form where a user can select a user name in a combo box and the report will open up only showing records that pertain to that user. Is there a way that I can put a text box on the report that is populated based on what was selected in the combox box of the form which is used to open the report? Note I'm closing the form immediately after opening the report.

Answer 1

Since your Access version is 2007 or later, you can use a TempVar to pass the combo box value to the Control Source of your report's text box.

For example, with this as the Control Source for Username_txt, the following click event procedure will ensure the correct value is displayed in that text box:

=[TempVars]![user_name]

Private Sub reportbyuser_bt_Click()
    TempVars("user_name") = Me.Filteruser_cb.Value
    DoCmd.OpenReport "Report by user", acViewReport
    DoCmd.Close acForm, Me.Name
End Sub

You can adapt that to use your Filteruser_cb.Column(n) instead of Filteruser_cb.Value

Answer 2

After some playing around, I was able to figure it out by creating a variable called temp that stored the value of the combo box and fills in the text box on the report with the stored value.

Private Sub reportbyuser_bt_Click()
Dim temp As String

temp = Filteruser_cb.Value

DoCmd.OpenReport "Report by user", acViewReport
[Report_Report by user].Username_txt.Value = temp
DoCmd.Close acForm, "Restructuring Report"
End Sub

In fact, I don't even need to use the temp and can simply do :

[Report_Report by user].Username_txt.Value = Filteruser_cb.Value

• Also, just realized that my solution is not very good if the combo box has several different columns in it. I used this instead :

  [Report_Report by user].Username_txt.Value = Filteruser_cb.Column(n) 
  

  (With n being the number of the column)