How to use the primary key as reference in VBA Ms Access behind the report

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
I would like to reference the Primary key on the continuous long summary report on my VBA code see below so that I get the correct results per total. The report was combined using domain functions, there is no ISSUE here in terms of performance all is perfect even if there are huge queries to do the actual calculations

The report has grouped sales data for each salesman coming from un related tables & queries, thanks the domain functions has helped to construct this report accurately and with quality performance.

Problems
I want now to have the final total for all salesmen, but if I reference the subtotal per each salesman, it only picks the value for the first salesman throughout, that is why I want to reference the primary which is based from the salesmen table and visible on the report. On the report I’m using this code below onload event:
VBA Code
Me.txtsalestotal = Me.finalTotal

Open in new window


I'm putting the code above behind the report , but it keeps on picking the same value for one salesman to all salesmen, that i why a primary key referencing is required here.

I think if I can be helped to know how to reference the primary key ( which is Visible on the report) on the above code, then it will sort out this problem for good.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Docmd.OpenReport "YourReport",acViewPreview, , "txtsalestotal =" & me.FinalTotal

Open in new window

It does the same thing , one figure picked for all salesmen,

How about Dlookup on the report , for example the name of the report must act as a domain why doesn't it want that? this is the only sure way of using criteria.

Me.txtsalestotal = DLookup("Me.finalTotal","rptSalesMonthtodate","[SalemanID] = "&Me.[SalemanID] )

Open in new window



Regards

Chris
John TsioumprisSoftware & Systems Engineer

Commented:
I think you mixed something
Your DLookup won't return a KEY...it will return some value...
You need to have the rptSalesMonthtodate as RecordSource of the Report and make the sum on the footer....
and open the report like this
Docmd.OpenReport "YourReport",acViewPreview, , "SalemanID=" & me.SalemanID

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi John

I know you have a very valid point here , but it is not clearly communicated, whether you want me to use openargs or not? Below is my code for opening the report.

Private Sub CmdPayslips_Click()
Dim Cancel As Integer
Const REPORTCANCELLED = 2501
On Error Resume Next
If IsNull(Me.txtSalesStartDate) Then
Beep
MsgBox "Please select the correct Start date", vbOKOnly, "Wrong Selection"
Cancel = True
Exit Sub
ElseIf IsNull(Me.txtSalesEnddate) Then
Beep
MsgBox "Please select the correct End date", vbOKOnly, "Wrong Selection"
Cancel = True
Exit Sub
ElseIf IsNull(Me.CboFirstName) Then
Beep
MsgBox "Please select the correct the start staff number", vbOKOnly, "Wrong Selection"
Cancel = True
Exit Sub
ElseIf IsNull(Me.CboLastName) Then
Beep
MsgBox "Please select the correct the Ending staff number", vbOKOnly, "Wrong Selection"
Cancel = True
Exit Sub
End If
DoCmd.OpenReport "rptSalesMonthtodate", acViewPreview
Select Case Err.Number
    Case 0
    ' no error
    Case REPORTCANCELLED
     ' anticipated error, so ignore
    Case Else
    ' unknown error, so inform user
    MsgBox Err.Description, vbExclamation, "Error"
End Select
End Sub

Open in new window



That is the way I open the report in question,

Kindly walk me through your point up to how to sum the final value
John TsioumprisSoftware & Systems Engineer

Commented:
I think it would be beneficial for you to resolve it on your own.
Picture it like this...you open a report...its based on the sales of the salesmen.... unless you provide some form of argument how you are going to filter the report to a particular salesman.
An why you select FirstName /LastName....you select Full Name...and it resolves to SalesmanID.
Last Name & first name are required because we have so many salesmen otherwise we will have a report with blanks , but the salesman ID is showing on the report .

Both first name & last are just string but binded to a primary key called salesman ID , imagine filtering just a primary keys without the salesmen names how are going to know which salesman to filter??????
Software & Systems Engineer
Commented:
Last Name & first name are required because we have so many salesmen otherwise we will have a report with blanks , but the salesman ID is showing on the report .

Both first name & last are just string but binded to a primary key called salesman ID , imagine filtering just a primary keys without the salesmen names how are going to know which salesman to filter??????

Clipboard01.jpgClipboard02.jpgClipboard03.jpg
Kindly see the code below which is done almost the same it is working I'm getting the values :

=Nz(DSum("TotalSales","QryGoupSales","[SalesmanID]=" & [SalesmanID] & " AND [TTDate]<#" & Format(([Forms]![frmsalesSummaryprint]![txtSalaryStartDate]),"yyyy\/mm\/dd") & "#"),0)

Open in new window


Here I the value for totalsales
I now getting =Sum([txtfinalsales) I get a value on the report any idea to resolve this

Its say enter the parameter value

But if I do not use sum the value comes without problems where am going wrong???????
Here is my well researched answer to this problem:

Because of the domain functions that were used to summarize the report, then the sum function is now rendered irrelevant, meaning it can never be used on a non-bound control unless a control is bound directly to the record source in this case a query. Solution

(1) Create a new control from any of participating tables with the correct data type in this numbers

(2) Include this control as part of the main record source query, the one which supplies the main data to the report in question.

(3) In the main report assign this control with the un-bound calculate value as its record source, Ms Access will treat it as a normal native control like any other queries controls.

(4) Then to have the cumulative figures just apply the in-belt report running total and hide it

(5) Finally add another control in the page footer and then reference the hidden control, problem solved!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial