Subreport cutting across pages at bad location

So, I’m not very good in Access and cannot figure out a solution to a very annoying issue… I have a report that has two levels of subreports. The problem I’m running into is that when the Main Report is opened, one of the grandchild subreports gets broken in between pages at a bad spot. Basically, the header of the subreport is split between pages (top half on current page and bottom half on next page). I’ve set the “KeepTogether” property on “Yes” for that particular header, but that doesn’t seem to work.

One work around I considered was creating a section in the parent report to contain only that problem subreport and setting that parent section “KeepTogether” property to “Yes”. However, the problem I run into there is that it pushes the subreport to a new page no matter what, leaving a good possibility of the page only having a bit of text at the top (leaving a good amount of wasted empty space for the remainder of the page).

So, after that, I attempted another idea. I created a field in the “Yes/No” field in the parent report with the intent that it will determine whether the parent “KeepTogether” property is “True” or “False”. But my VBA capabilities are limited and was unable to write a code that would work to successfully execute this concept…

Lastly, I attempted yet another attempt idea, which was similar to the last. This time, the “Yes/No” field would determine if the “KeepTogether” property of that particular GroupLevel of the section in the parent report. So, if the field was set to “Yes”, my intent was that the GroupLevel KeepTogether property would be set to ‘1’ (Keep whole group together). However, that didn’t work because the OnOpen event for a report happens before any of the data is called. As such, when I attempt to open the report, I get an error stating that it can find the specified field.

I’ve exhausted myself with all the research I’ve done on this and am just about to give up… I’m hoping somebody in this sub would be able to help me find a solution for this? Hopefully I’ve explained my issue clearly enough. Thanks in advance.

submitted by /u/demax182
[link] [comments]

Posted on

Switching from Excel to Access for data lookup (client details, rates etc) worth it?

Over the last year at work I’ve set up multiple Excel spread sheets that that use a variety of formulas (INDEX MATCH, IF, SUM etc ) to pull data through from tabs onto a ‘front sheet’.

All of these sheets need to be printable for my job (they form cover sheets for individual shipments)

Some accept weight and dimension information (adds/multiples/divides to provide volumetric weight and totals or conversions between metric/imperial) and have two drop down menus to select a list of pre entered client addresses.

Based on the client to/from addresses it then auto fills client codes for those addresses (for shipping/billing) and a unique ‘route ID’ that the sheet then uses to pull through billing charges that are also changeable based on the total weight (min/per kg/max) (the Route ID has to be set by a user, the data with the rates in it is a frankly appalling table of information my management have made me use, the route is based off origin site to dest site. But the rate sheet is based off City to City, so a human needs to decide which site = which city. Because not every city is in the rate sheet and our instructions are ‘use the closest city’ I’m wary of ‘fixing it’ as we get new ones fairly often and it’s a global file. )

Some of the others pull in airline information based on unique 3 digit numbers identifying the airline. These then return a delivery address as well as contact information and rate information for those airlines.

At first I had one, for my job – why look up rates in a spread sheet manually right? Then someone else asked me to do one for them. Then word spread!

We’re in the process of reducing paper usage and uniforming the office in terms of documents used. Another department is also working on merging the data we hold in terms of contact details for airlines/trucking companies etc as we have duplicated data all over the place after years of departments doing their own thing and letting it fall into disuse. It’s got wildly out of hand TBH and now I’m like 100% sure this would be bettered handled in a database – at the very least to keep all info in one central location.

Which is where the problems started.

I have ZERO access or ‘programming’ knowledge. This isn’t my job role at all, not even tangentially related. I export cargo, this is way outta my ball park – and I still have my full time job to actually do alongside this, plus yanno family/life XD No one else in my office can even grasp the spreadsheet – if I ever leave the entire system is fucked.

At the very base it would be nice to have a centralised place to ‘check’ information (like airline contact details) especially for our night shift given they’re often in the office alone with no one to call and ask. Even if it can’t produce printable forms like above.

Best case this should be rolled out across the office as either an internal database or a web accessible ‘app’? with users able to update their own client information/rates and access each others for cover etc. But in reality we don’t have the IT support (nor are we likely to get it)…

At this point is it even worth me trying to learn how to do this, or am I better off using the clunkier but ‘easier’ Excel sheets I already have set up? Still pretty much unsupported but I have much more faith in being able to teach someone how to use the spreadsheet than the database!

(I did manage to get one form set up that when you type in the airline code it gives you all the contact details listed on the table with it. It took me all morning :/ I’m trying to get it pull charge info from a different table based on one the changeable variables….haven’t got that yet.)

submitted by /u/likeafuckingninja
[link] [comments]

Posted on

Switching from Excel to Access for data lookup (client details, rates etc) worth it?

Over the last year at work I’ve set up multiple Excel spread sheets that that use a variety of formulas (INDEX MATCH, IF, SUM etc ) to pull data through from tabs onto a ‘front sheet’.

All of these sheets need to be printable for my job (they form cover sheets for individual shipments)

Some accept weight and dimension information (adds/multiples/divides to provide volumetric weight and totals or conversions between metric/imperial) and have two drop down menus to select a list of pre entered client addresses.

Based on the client to/from addresses it then auto fills client codes for those addresses (for shipping/billing) and a unique ‘route ID’ that the sheet then uses to pull through billing charges that are also changeable based on the total weight (min/per kg/max) (the Route ID has to be set by a user, the data with the rates in it is a frankly appalling table of information my management have made me use, the route is based off origin site to dest site. But the rate sheet is based off City to City, so a human needs to decide which site = which city. Because not every city is in the rate sheet and our instructions are ‘use the closest city’ I’m wary of ‘fixing it’ as we get new ones fairly often and it’s a global file. )

Some of the others pull in airline information based on unique 3 digit numbers identifying the airline. These then return a delivery address as well as contact information and rate information for those airlines.

At first I had one, for my job – why look up rates in a spread sheet manually right? Then someone else asked me to do one for them. Then word spread!

We’re in the process of reducing paper usage and uniforming the office in terms of documents used. Another department is also working on merging the data we hold in terms of contact details for airlines/trucking companies etc as we have duplicated data all over the place after years of departments doing their own thing and letting it fall into disuse. It’s got wildly out of hand TBH and now I’m like 100% sure this would be bettered handled in a database – at the very least to keep all info in one central location.

Which is where the problems started.

I have ZERO access or ‘programming’ knowledge. This isn’t my job role at all, not even tangentially related. I export cargo, this is way outta my ball park – and I still have my full time job to actually do alongside this, plus yanno family/life XD No one else in my office can even grasp the spreadsheet – if I ever leave the entire system is fucked.

At the very base it would be nice to have a centralised place to ‘check’ information (like airline contact details) especially for our night shift given they’re often in the office alone with no one to call and ask. Even if it can’t produce printable forms like above.

Best case this should be rolled out across the office as either an internal database or a web accessible ‘app’? with users able to update their own client information/rates and access each others for cover etc. But in reality we don’t have the IT support (nor are we likely to get it)…

At this point is it even worth me trying to learn how to do this, or am I better off using the clunkier but ‘easier’ Excel sheets I already have set up? Still pretty much unsupported but I have much more faith in being able to teach someone how to use the spreadsheet than the database!

(I did manage to get one form set up that when you type in the airline code it gives you all the contact details listed on the table with it. It took me all morning :/ I’m trying to get it pull charge info from a different table based on one the changeable variables….haven’t got that yet.)

submitted by /u/likeafuckingninja
[link] [comments]

Posted on

Running Sum in MS-Access Query

Running Sum in MS-Access Query

Introduction.

We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), published on this Website with the Post Title: Auto-Numbering in Query Column, during January 2010. Hope you have come across that Post, if not you may visit the Page by following the above link.

The RunningSum() Function is written somewhat on similar logic of QrySeq() Function.

Before going into details let us take a look at some sample Images, before and after run of the new Function in a test Query Column.

A small Table with two Fields: Table_Units and with few records.

The SELECT Query: RunningSumQ1 recordset in datasheet view, with summary values in a separate Column, with the column name RunningSum, from where the RunningSum() Function is called from.

The SQL Code of RunningSumQ1 Query.

SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

A Report Designed using RunningSumQ1:

The Query Preparation Note.

Before diving deep into the VBA Code I want you to check the above sample data, to draw your attention to an important point while preparing the data for the RunningSum() Function.

  1. A unique ID Field, like PrimaryKey required in the Query, with either Numeric or String Data and strictly no duplicates in them.
  2. If this is not readily available in the Source Data, you may join (concatenate) two or more field values together, to create unique values in a separate Column, as a Key Field in the Query.
  3. If this method is followed then create a Test Query similar to the sample one given below, using the first Query as source, to find out whether any duplicates still exists in the Source Query or not.
  4. Sample ID Field Record-Count Test Query:

    SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
    FROM RunningSumQ2
    GROUP BY RunningSumQ2.ID2;

    The CountOfID2 Column result should be like the sample Image give below, with all Count values are showing as one.

  5. When all the ID Field values are unique then the CountOfID2 Column will have the value 1 in all records.  Greater  than one in any record means that those records have duplicate key values and needs to join some other field to eliminate duplicates.
  6. Once you are sure that all records have unique ID values then you may add other required fields in the first Query for the purpose you plan to use it, like Form or Report Source Query.
  7. Once you are ready with the Query data then it is time to add the function in a new Column in the Query, like: Summary:RunningSum([ID],”ID”,”Units”,”MyQuery”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

Dim D As Object

Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
'Function: RunningSum()
'Purpose : Creates Running-Sum Value of a Field.
'The Query can be used as Source for other Processing needs.
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date : November 2019
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calculating Running Sum in String Format
'4 Query-Name in String Format
'-----------------------------------------------------------
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
'-----------------------------------------------------------
Static K As Long, X As Double, fld As String, J as Long
Dim p As Variant

On Error GoTo RunningSum_Err

If SumFldName <> fld Then
fld = SumFldName
Set D = Nothing
K = 0
X=0
End If

K = K + 1
If K = 1 Then
Dim DB As Database, rst As Recordset

Set D = CreateObject("Scripting.Dictionary")
J=DCount(“*”,QryName)
Set DB = CurrentDb
Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)

While Not rst.EOF And Not rst.BOF
X = X + rst.Fields(SumFldName).Value
p = rst.Fields(KeyFldName).Value

D.Add p, X

rst.MoveNext
Wend

rst.Close
Set rst = Nothing
Set DB = Nothing

RunningSum = D(IKey)
Else
RunningSum = D(IKey)
End If

RunningSum_Exit:
If K > J then
Set D = Nothing
End If
Exit Function

RunningSum_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

Familiarising the VBA Code.

On the Global area of the Standard Module an Object Variable is declared with the name D. 

The function RunningSum() is declared with four parameters.

  1. The Unique Key Field Value.
  2. The Key-Field Name in String format.
  3. The Summary Field Name in String format.
  4. The Query-Name in String format.

The returned value from function is Double precision number.

Four Static Variables are declared in the Function:

  1. K – is a control variable.
  2. X – to hold the Summary Values, added to it at record level.
  3. fld – A control Variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.

The Static Variables retain their old values during repeated calls of the Function.

Variable p is to hold the IDKey-value retrieved from the record.  It is declared as Variant Type to accept either Numeric or String Key Value.

The Working Logic of the Function.

The statement If SumFldName <> fld Then checks whether the Key-Field Name passed to the function is different from last call of the Function.  If it is different then it assumes that a different Query is passed to the function.

The Dictionary Object D is erased from memory and other variables are initialized.

In the next step the K Variable is incremented by one. When K=1 the function’s main task is initiated.  

The Database and Recordset Objects are declared.

The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).

By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually then you can declare and instantiate a Dictionary Object, like the Class Object of Access or Collection Object.

Note: If you are not familiar with Dictionary, Class Object or Collection Object, then we have all the information you need to learn the fundamentals about them, in this Website.  The links are given at the end of this page. You may visit them to learn with sample code and Demo databases, available to download.

Adding Dictionary Object Reference File.

To add the Dictionary Object to your Database’s Library Files List do the following:

On the VBA Window, select Tools – – >References… and look for the file: Microsoft Scripting Runtime in the displayed list and put checkmark to select it.

Once you do this you can declare and instantiate a Dictionary Object as given below.

Dim D As Dictionary
Set D = New Dictionary

If you do this you have an added advantage of displaying it’s Properties and Methods, when you type a dot (D.) after it’s Object name, by intellisense automatically.

Next, the database object DB is set with the active database and the Query is opened as recordset in rst.

Within the  While. . .Wend Loop the summary field and the unique key Field values are read from each record. The Summary field value is added to the Variable X.  The Key value of record is written as Key Value of Dictionary Object and the current Value in X is written as Dictionary Object Item, in Key, Item pair.

The Dictionary Object Items are always written in this way.  The Item can be a single value, an Array, Objects or Collection of Objects. All of them should have a Unique Key Value to retrieve the Item later.

The purpose of Key in Dictionary  Object is similar to the function of Primary Key in a Table.  We can retrieve any value Randomly or Sequentially from the Dictionary Object using the Key,  like A = D(Key) or  A = D.Item(Key).

In this way the cumulative summary value, at each record level, is added to the Dictionary Object as it’s Item, with unique Key. When all the record level processing is complete, the first record summary field value is returned to the function calling record by execuring the RunningSum = D(IKey) statement, from the first Dictionary Item.  All the above actions are taking place when the  control Variable K is equal to 1.

Subsequent Calls of the function with the Key Value parameter of each record retrieves the corresponding summary value of that record from Dictionary Item and returns it to the Query Column, that’s how it works.

Some Images of a sample Run done on the Products Table of NorthWind.accdb are given below.

Sample Query Run (Key Values are String Type) Data on Form.

SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
FROM Products;

The RunningSumQ2 Query is the Record Source of the Form.

Sample Run Data on Report.

The RunningSumQ2 Query is the Record Source of the Report.

Download Demo Database.

CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality

COLLECTION OBJECT

  1. MS-Access and Collection Object Basics
  2. MS-Access Class Module and Collection Objects
  3. Table Records in Collection Object

DICTIONARY OBJECT

  1. Dictionary Objects Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item

MS-ACCESS EVENT HANDLING

  1. Withevents MS-Access Class Module
  2. Withevents and Defining Your Own Events
  3. Withevents Combo List Textbox Tab
  4. Access Form Control Arrays And Event
  5. Access Form Control Arrays And Event-2
  6. Access Form Control Arrays And Event-3
  7. Withevents in Class Module for Sub-Form
  8. Withevents in Class Module and Data
  9. Withevents and Access Report Event Sink
  10. Withevents and Report Line Hiding
  11. Withevents and Report-line Highlighting
  12. Withevents Texbox and Command Button
  13. Withevents Textbox Command Button
  14. Withevents and All Form Control Types

Posted on

Help with duplicating value in Access Table

In my Access Table, I have two columns that are giving me difficulty. The first is the ID (this is the key column that is automatically generated). The second column in the table is “Case Number.” I need to get the number in the “ID” column to automatically populate into the “Case Number” column.

Thanks for the help!

submitted by /u/allforthebest21
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

Need Help creating Online Form Entry

Does anyone have experience with creating an online form Entry? Ideally i will be able to have the Users input the data entry on an online site. I would also like for them to have the ability to run pre-made reports from the online site too. Any help or suggestions would be very appreciated!!!

submitted by /u/sailor231323
[link] [comments]

Posted on

Data Checking VBA

So I’m using a part of a login code i got from youtube as a data checking system. The user will do an input, then click the button that will fire up this code. However, every time I try to, it shows an error saying “method or data member is not found” and then highlights the “Private Sub” line.

The expected flow is that input is checked if it can do A, B or C (and so on)

I haven’t used VBA before, so I dont know whats wrong.

Private Sub login_Click() If IsNull(Me.ID) Then MsgBox "Please Enter Student ID to Login", vbInformation, "Student ID Required" Me.ID.SetFocus Else 'Processing if key If IsNull(Me.ID.TextLength = 3) Then If (IsNull(DLookup("Room_id", "Key", "user_id='" & Me.ID.Value & "'"))) Then MsgBox "Unknown Key ID", vbInformation, "Unknown Key ID" Refresh Else DoCmd.OpenForm "Key Return" End If 'Processing if student ElseIf IsNull(Me.ID.TextLength = 10) Then If (IsNull(DLookup("user_id", "User", "user_id='" & Me.ID.Value & "'"))) Then MsgBox "Unregistered Student ID", vbInformation, "Unregistered Student ID" Refresh Else DoCmd.OpenForm "Session_Register" End If 'Processing if NRIC user ElseIf IsNull(Me.ID.TextLength = 12) Then If (IsNull(DLookup("user_id", "User", "user_id='" & Me.ID.Value & "'"))) Then MsgBox "New user detected. Request help from staff to continue.", vbInformation, "Unregistered User ID" DoCmd.OpenForm "New User" WindowMode = acDialog Else DoCmd.OpenForm "Session_Register" End If 'Processing if invalid number Else MsgBox "Invalid Input", vbInformation, "Invalid Input" Refresh End If End If End Sub 

submitted by /u/dat_Boi_Ge0rGyy
[link] [comments]

Posted on