HELP ! Report Header User Names (1 Viewer)

riti90 · Dec 21, 2017

Hi All,
I'm quite new on coding and I'd like to ask about an issue that I have with a report that I'm building.

I created an Database with login form. The logged in User Initials appear on the next form where I'm inputing data as Operator.
On the report that I created I have on the ReportHeader a few txtBoxes where I want to show the operators names(from the operators that filled the form of this report).
I managed to show only one Operator but when there are 2 or more I couldn't manage it. I don't know if that is possible.
I attached 2 photos of my report.
the thing is that i have a lot of similar reports and I cannot manage to get the names from me Operators table.
:banghead: :banghead:
I'd appreciate a lot if you can Help me with that,
Thanks

Pat Hartman · Dec 21, 2017

I don't see where in the report header you are trying to place the operator names. The best solution is to use a subform since there are multiple names to show.

riti90 · Dec 21, 2017

Hi, I have the txtBoxes under the "Test Performed by:" where i'd like to show the names of the Operators that have done this Test.
I did Use this code
"=Nz(DLookUp("[tblOperators]![UserName]& '
' & [tblOperators]![Position] &'
' & [tblOperators]![Company] ","tblOperators","Initials='" & [Operator].[Value] & "'"),"")"
but it is showing me only one of the operators not the other one.

Thanks

arnelgp · Dec 21, 2017

remove the Expression from your textbox on the Report.
(the =Nz(DLookup....)
Instead add code to the Report's Load Event.

Replace "TextBoxName" on the code with the
correct name of the textbox on the Report.

Code:

Private Sub Report_Load()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOperator
    strSQL = Replace(Me.RecordSource, ";", "")
    Set rs = CurrentDb.OpenRecordset( _
        "Select [Operator] From (" & strSQL & ")")
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            While Not .EOF
                strOperator = DLookup("UserName & ' ' & Position & ' ' & Company", _
                                    "tblOperators", _
                                    "Initials=" & Chr(34) & !Operator & Chr(34)) & ""
                If strOperator <> "" Then
                    Me.TextBoxName = "" & Me.TextBoxName & strOperator & vbCrLf
                End If
                .MoveNext
            Wend
            .MoveFirst
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub

riti90 · Dec 21, 2017

arnelgp said:

remove the Expression from your textbox on the Report.
(the =Nz(DLookup....)
Instead add code to the Report's Load Event.

Replace "TextBoxName" on the code with the
correct name of the textbox on the Report.

Code:

Private Sub Report_Load()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOperator
    strSQL = Replace(Me.RecordSource, ";", "")
    Set rs = CurrentDb.OpenRecordset( _
        "Select [Operator] From (" & strSQL & ")")
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            While Not .EOF
                strOperator = DLookup("UserName & ' ' & Position & ' ' & Company", _
                                    "tblOperators", _
                                    "Initials=" & Chr(34) & !Operator & Chr(34)) & ""
                If strOperator <> "" Then
                    Me.TextBoxName = "" & Me.TextBoxName & strOperator & vbCrLf
                End If
                .MoveNext
            Wend
            .MoveFirst
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub

Hi,
Thank You very much, it is working OK but is it any way that I can split the names in 2 txtBoxes.
I mean 1 name in the first txtBox and the other name in the other txtBox.

Thanks a lot,
I really appreciate this

riti90 · Dec 21, 2017

Hi, Thanks for the quick response.
I did try the code but it shows all the names in 1 txtBox.
is it any chance they can be in separate txtBoxes?
and It shows the names for all the report rows. I normally write more than 100 rows on the report. but it is only 2 different Operators. Can it be done to show each operator only once?
I attached a photo of how they look now.
they are all on the txtbox nr 1 but I'd like them to be each name on each txtbox.

Thanks again for the help.
You are really the best.
I really appreciate the help, really

pbaldy · Dec 21, 2017

Post 5 was moderated, I'm posting to trigger email notifications.

isladogs · Dec 21, 2017

I'm not totally clear what you want, so apologies if I've got this wrong.

Is this idea any use to you?
Up to 3 teacher names shown in 1 textbox using this as the control source

Code:

=[Teacher1] & IIf([TeacherID2]<>""," / " & [Teacher2],"") & IIf([TeacherID3]<>""," / " & [Teacher3],"")

Obviously the names could be split into 3 textboxes just as easily

riti90 · Dec 21, 2017

Hi,
I have at least 2 users for each report. So they are inputing data. And all the data on the report appear with their initial names in each row.
I want to have their names appeared on the report header in 2 different txtboxes.
They are inputing at least 100 rows per report so their name will appear in every row. But I'd like their names to appear only once..
I don't know if I'm explaining it ok..
Apologies for the bad language..

Thanks

isladogs · Dec 21, 2017

Sounds like my suggestion isn't what you want.
It should be easy enough to put each name in a separate text box

Pat Hartman · Dec 21, 2017

How many controls do you intend to add to the report? Say you add three which is one more than "normal". Most of the time the third box will be empty. and then there's the day where you have four users for a report. What happens then?

Just do it right and make the subreport (no code required). You can format it to be unobtrusive. Or, live with the concatenation code. You do NOT want to have to figure out how many textboxes you might ever need to have and live with the consequences of that choice.

arnelgp · Dec 21, 2017

rename your textboxes to TextBox1, TextBox2, etc.

Code:

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOperator
    Dim i As Integer
    strSQL = Replace(Me.RecordSource, ";", "")
    Set rs = CurrentDb.OpenRecordset( _
        "Select Distinct [Operator] From (" & strSQL & ")")
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            i = 1
            While Not .EOF
                strOperator = DLookup("UserName & ' ' & Position & ' ' & Company", _
                                    "tblOperators", _
                                    "Initials=" & Chr(34) & !Operator & Chr(34)) & ""
                If strOperator <> "" Then
                    Me.Controls("TextBox" & i) = strOperator
                    i = i + 1
                End If
                .MoveNext
            Wend
            .MoveFirst
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub

riti90 · Dec 21, 2017

Hi,
Thank you for your help but I'm getting the error that's attached to the photo.
i'm sorry if I didn't explain very well but I'd like in the txtboxes to have the Operator Name only once. I'm so sorry to disturb you but I really need to do this. Is it any way or anything like lookup for all the report to look for the Initials? because now I'm getting all the initials one by one when there are only 2 or 3 users. I just want only their names to show on the txtbox.

I uploaded a copy of my File maybe you can understand better what I mean Because I don't know exactly how to explain it.

Thank you so much.
:banghead: :banghead:

riti90 · Dec 21, 2017

Hi,
If this is a possibility can you please explain how can I do it? Because I find it a bit difficult..

Thank you.

Pat Hartman said:
How many controls do you intend to add to the report? Say you add three which is one more than "normal". Most of the time the third box will be empty. and then there's the day where you have four users for a report. What happens then?

Just do it right and make the subreport (no code required). You can format it to be unobtrusive. Or, live with the concatenation code. You do NOT want to have to figure out how many textboxes you might ever need to have and live with the consequences of that choice.

Pat Hartman · Dec 21, 2017

You are getting very different advice from arnel. I disagree totally with what he is currently helping you to do but the choice is yours. He is telling you how to do exactly what you asked for. He is giving you the literal answer to using some fixed number of text boxes. My suggestion is to do it in a way that won't come back to bite you next week when it turns out you need to add another textbox because you don't have enough. There is not enough time in my life to hard-code things that will cause me to have to revisit them sooner or later and fix the problem I made.

The concatenation code arnel started with is an acceptable method but you didn't like it. My suggestion requires NO CODE so you shouldn't have any trouble implementing it. Just add a subreport to the main report and bind it to the table with the list you want to show. If you have relationships defined, Access will even create the proper master/child links which will keep the subreport synchronized to the main report. If you didn't bother with RI, then, you'll have to define the master/child links yourself.

arnelgp · Dec 22, 2017

You may try this.

riti90 · Dec 22, 2017

arnelgp said:
You may try this.

Hi Arnel, you just saved my life.

Thank You very much.

On the Test File the code works perfectly as I'd like it to be but when I'm trying it on my File it won't work. Am I doing something wrong?

riti90 · Dec 22, 2017

riti90 said:
Hi Arnel, you just saved my life.

Thank You very much.

On the Test File the code works perfectly as I'd like it to be but when I'm trying it on my File it won't work. Am I doing something wrong?

Fixed it. Didn't check the Join Properties in the query.

Thanks a lot.

Marking Thread as SOLVED!!!

arnelgp · Dec 22, 2017

You're welcome.

Pat Hartman · Dec 22, 2017

You can get exactly the same effect by using a subform with no code and no modifications required if you at some time need to change to four inspectors or two approvers.

HELP ! Report Header User Names (1 Viewer)

Registered User.

Attachments

Super Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Attachments

Wino Moderator

MVP / VIP

Attachments

Registered User.

MVP / VIP

Super Moderator

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Registered User.

Super Moderator

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Super Moderator

Similar threads

Users who are viewing this thread