Declaring data types

[mjdemaris]

Quick question: what is the benefit of declaring and using a type?

Private Type
with stuff here...
End Type

 

[theDBguy]

The way I look at it, the Type statement allows you to create or define your own data type. This would be useful if you need a complex data type, which could include multiple data types.

 

[The_Doc_Man]

For something that is very simple, you probably don't need them. However, there are such things as complicated data structures where you have things intimately bound to each other in some way. Let's take an extreme of this... look at the properties of a form. That is a data structure that is encapsulated into a form.

In one of my writing projects, I'm using a structure that involves the positions of four different moons with different orbital radii and other factors. (It makes sense in the context of the story.) Each moon gets a "MoonObj" structure that includes such things as phase of the moon, rise time and set time, orbital elevations, conjunctions and eclipses, and a few other issues.

 

[mjdemaris]

In essence, then, I could define a data type, such as User, and instead of using queries/SQL/lookups to retrieve that User's data, and perhaps corresponding data in a related table and use it globally, as long as the data type is defined Public?

 

[The_Doc_Man]

Note also that defining a structure in memory doesn't always translate well to writing structural records on disk. It can be done but you need to include specialty code to store or retrieve the elements. For instance, in my case with the "MoonObj" I have to use recordset operations to store values or retrieve them. I can't just bind the structure to a table.

 

[mjdemaris]

I typically use DAO a lot. I do use plenty of bound forms, plenty of unbound forms, and use recordsets to update join tables when needed.

I saw some references to custom data types in some of the posts and such and in fact, in the Resize Form example updated by isladogs uses custom data types.

As far as I can see right now, I don't do and won't do a LOT of processing on any particular data, until I attempt to add charts or meaningful reports perhaps.

 

[Cronk]

If the Type variable is going to be based on table data, I prefer to use a class object. That way the save/load operations can be made as object methods along with any other methods.

 

[mjdemaris]

Thanks, Cronk, for throwing a wrench into the mix, lol.

I must ask, how would I implement a class object into Access? Let's take Inventory Management as an example. I have a room full of parts, most can be purchased from various vendors. I have a stocking location as well as stocking levels. I have machines that use these parts, so part of this project is the ability to assign parts to machines, and thereby displaying a list of said parts when desired. I also would like to reorder the parts in the same app. I can go into more details later if needed.

Let's say I want to add a new part. Normally, I would be updating three tables: Item, Item Supplier (provides part number and join to Supplier table), and Bin Item (provides the stocking information and join to the Bin table). Currently the second and third table data are viewed/editable via a sub form.

So...thoughts on a Part/Item class?

I should add: I did create a User class for one specific application, but I believe that the object only lasts for a brief period during login. Even then, I don't use a class in any other application, didn't really see a benefit from the one time I used it. 2nd Edit: Unless I can keep the class 'alive' for the duration of use...one potential issue I see is that there are two or three computers that may have the app open continuously, unless...I set some sort of event that fires at a certain time to close the app and restart it. I'm thinking along the lines of using a User class to return one or more 'Groups' associated with it. As mentioned in my thread here:
https://www.access-programmers.co.uk/forums/threads/db-management-switchboard.320052/#post-1791785

 

[MajP]

So...thoughts on a Part/Item class?

Nothing you mentioned suggests any need or utility for building a custom class. You describe standard database record manipulation (add, edit, delete). DAO and ADODB are EXTREMELY robust classes to support data base structures and if it can be done in DAO or ADODB then unlikely you need a custom class. Everything you mentioned sounded like pure simple database record creation, manipulation, and edit.

However, there are plenty of times when I pull data out of a class and store it in a custom object, for the purpose of do things outside what a database does. More than pure record manipulation (add, edit, delete).
Example. In this problem the database holds items that have value, and weight. It also identifies N shipping containers that can carry T weight. The question was to find the optimum loading of the containers to maximize the profit. This is a complex algorithm that could not be done through pure DAO/ADODB recordsets. So I created classes to model the Bins (shipping containers), the items inside the bins.

How to find all combinations within a constraint

I am after some code/advice for Access. I have a list of Order Numbers(Unique) and what cubic metres that each are, I also have a cost benefit per Order. I would like to run some code to be able to find every combination of Orders but within a given constraint of Cubic size (65Cube) to find...

www.access-programmers.co.uk

In this example the database holds Locations and distances to other locations. The question was to find the shortest path from one location to another. So each location was modeled as a node in a graph.

Route Planning - shortest path

I'm trying to get my head around using the following data to create the shortest route between 2 places in a map using VBA in either excel or access. ID Name Connection 1 Connection 2 1 Location1 2 2 Location2 3 4 3 Location3 2 4 Location4 2 5 5 Location5 3 5 So...

www.access-programmers.co.uk

 

[mjdemaris]

@MajP I was just reading a thread from a couple of years ago on classes and you told the OP to check out some of your earlier posts. And seconds before you replied here I just hit the search button looking for your threads, LOL!

Thank you for clarification and I will have a look!

 

[MajP]

I would take @Pat Hartman advice. Do not try to re-invent the wheel if you do not need to. I too have seen some crazy complicated work arounds for things that can be done very simply using native Access bound controls and possibly augmented with DAO/ADODB.

 

[MajP]

what is the benefit of declaring and using a type?

Maybe an example would help. Assume you have something that cannot be easily modeled by a single variable. In one I did recently the poster wanted to find the distance between two lat/longs stored in a database and do other latt long manipulation. So I had a couple functions that excepted a lat long and returned a value or returned a lat long.
You could do this with a string representation, but you cannot manipulate a string easily so you have to parse it back into numbers.
(I actually did it with +/- decimal lattitudes and decimal longitudes, but for illustration assume this is my type)

"41°24'12.2"N 2°10'26.5"E"

Public Type LatLong
  DegreeLat as integer
  MinutesLat as integer
  DecimalSecondsLat as Single
  NorS as String
  DegreeLong as integer
  MinutesLong as Integer
  DecimalSecondsLong as Single
  EorW as string
end type

Now I can create a bunch of LatLongs and pass to different functions and easily manipulate them, but more importantly a function can return to me a LatLong with lots of pieces of information. It could be done with strings which would be a pain. Or it could be done with a function with 8 different arguments for the 8 properties of the latlong, bigger pain.

 

[CJ_London]

don't think its been mentioned but form modules are a form of class objects but without get, set and let,. It is initialised by opening a form and terminated by closing the form rather than using Set

 

[mjdemaris]

Pat, I'm not really looking for a new shiny object to use because it looks cool. I am exploring what options may or may not work well within the application. I'm reading through the content via your replies, links, and samples. If someone suggests a class is better for situation X, then I ask why and would that apply to/work with my application.

I really want this to be a good functioning app, and not put together as ad hoc as I did the first one. I've made improvements over the years for sure, but not quite what I want.

I truly appreciate your candidness and advice - from each of you.

 

[gemma-the-husky]

Maybe an example would help. Assume you have something that cannot be easily modeled by a single variable. In one I did recently the poster wanted to find the distance between two lat/longs stored in a database and do other latt long manipulation. So I had a couple functions that excepted a lat long and returned a value or returned a lat long.
You could do this with a string representation, but you cannot manipulate a string easily so you have to parse it back into numbers.
(I actually did it with +/- decimal lattitudes and decimal longitudes, but for illustration assume this is my type)

"41°24'12.2"N 2°10'26.5"E"

Public Type LatLong
  DegreeLat as integer
  MinutesLat as integer
  DecimalSecondsLat as Single
  NorS as String
  DegreeLong as integer
  MinutesLong as Integer
  DecimalSecondsLong as Single
  EorW as string
end type

Now I can create a bunch of LatLongs and pass to different functions and easily manipulate them, but more importantly a function can return to me a LatLong with lots of pieces of information. It could be done with strings which would be a pain. Or it could be done with a function with 8 different arguments for the 8 properties of the latlong, bigger pain.

Given @MajP type data structure above, the benefit of the structure is that you declare a variable of type latlong.

Dim myLatLong as LatLong.

All the parts of LatLong are kept together, and when using the variable, you get intellisense to help you code.

With myLatLong
'enter a dot, and you see all the members of the type
End With