Every object variable you use in VBA is a pointer type.
It's not particularly hard building a continuous form. - I happen to know because I'm currently working on something in VB.Net that could be considered a continuous form. - The (slightly) hard bit is that you need to keep the visual representation of the items in sync with their state in memory (and the database).
The particular weakness of VBA in this regard is that it has only a very limited set of available UI controls (the Microsoft Forms library), if you can even count those. Anything beyond that must be provided by the host application, Access in our case. The Access UI elements are pre-built for displaying data forms on screen, and they are reasonable good at that task. However, if you don't want to use the built-in forms, but build your own, Access is a very, very poor tool to achieve that goal.
I don't think it's right to say that VBA offers pointers. To use your example, any variable is a pointer, but you can't easily use the address for anything useful. Access is super, but occasionally I feel it would be useful to manage some data by manipulating pointers and record structures. It generally ends up being easier to do this sort of stuff using a temporary table.
CHATGPT ANSWER: Unfortunately, Microsoft Access does not have a code-based method for creating continuous forms. Instead, it provides a visual interface for designing and creating forms, which can be done through the steps outlined in my previous answer.
However, if you need to programmatically create a continuous form in Microsoft Access, you can use VBA (Visual Basic for Applications) code to automate the process. Here is an example of how you could create a continuous form in VBA:
Code:
vbnet
Sub CreateContinuousForm()
'Declare the variables
Dim frm As Form
Dim tbl As TableDef
'Create a new form
Set frm = CreateForm
'Set the form's record source to a table or query
Set tbl = CurrentDb.TableDefs("Table1")
frm.RecordSource = tbl.Name
'Set the form's view to Continuous Forms
frm.DefaultView = acFormViewContinuous
'Set the form's properties
frm.AllowDatasheetView = False
frm.AllowEdits = True
frm.AllowFilters = True
frm.AllowDeletions = True
'Add the fields to the form
frm.AddNewField "Field1", acText
frm.AddNewField "Field2", acNumber
'Save and open the form
frm.Save
frm.OpenForm
End Sub
In this example, the code creates a new form, sets its record source to a table named "Table1", sets its view to continuous forms, sets its properties, and adds two fields named "Field1" and "Field2". The form is then saved and opened for viewing.
Keep in mind that this is just a simple example, and you can customize the code to suit your specific requirements.
here's a more complex example (PER CHATGPT) of how you can programmatically create a continuous form in Microsoft Access using VBA code:
vbnet
Code:
Sub CreateContinuousForm()
'Declare the variables
Dim frm As Form
Dim tbl As TableDef
Dim ctl As Control
'Create a new form
Set frm = CreateForm
'Set the form's record source to a table or query
Set tbl = CurrentDb.TableDefs("Table1")
frm.RecordSource = tbl.Name
'Set the form's view to Continuous Forms
frm.DefaultView = acFormViewContinuous
'Set the form's properties
frm.AllowDatasheetView = False
frm.AllowEdits = True
frm.AllowFilters = True
frm.AllowDeletions = True
'Add the fields to the form
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Field1")
ctl.ControlSource = "Field1"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Field2")
ctl.ControlSource = "Field2"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Field3")
ctl.ControlSource = "Field3"
ctl.Width = 100
Set ctl = frm.AddNewControl("ComboBox", acComboBox, acDetail, , "Field4")
ctl.ControlSource = "Field4"
ctl.RowSourceType = "Table/Query"
ctl.RowSource = "LookupTable"
ctl.Width = 100
'Add a label to the form header
Set ctl = frm.AddNewControl("Label", acLabel, acHeader, , "lblHeader")
ctl.Caption = "Continuous Form Example"
ctl.ForeColor = vbRed
ctl.Font.Size = 14
ctl.Font.Bold = True
ctl.Width = 500
'Save and open the form
frm.Save
frm.OpenForm
End Sub
In this example, the code creates a new form, sets its record source to a table named "Table1", sets its view to continuous forms, sets its properties, and adds four fields named "Field1", "Field2", "Field3", and "Field4". The code also adds a label to the form header with a caption "Continuous Form Example".
For each field, the code adds a control, sets its control source to the field name, sets its width, and specifies its properties such as the row source type and row source for the "Field4" control.
Finally, the form is saved and opened for viewing.
This is just one example of how you can programmatically create a continuous form in Microsoft Access using VBA code. You can customize the code to suit your specific requirements, such as adding additional fields, changing the form properties, or adding other controls such as buttons or images.
Could you produce a much more complex example of how to programmatically create a continuous form in access with using visual basic for applications code?
Sure, here's a much more complex example of how you can programmatically create a continuous form in Microsoft Access using VBA code:
Code:
Sub CreateComplexContinuousForm()
'Declare the variables
Dim frm As Form
Dim tbl As TableDef
Dim ctl As Control
'Create a new form
Set frm = CreateForm
'Set the form's record source to a table or query
Set tbl = CurrentDb.TableDefs("Orders")
frm.RecordSource = tbl.Name
'Set the form's view to Continuous Forms
frm.DefaultView = acFormViewContinuous
'Set the form's properties
frm.AllowDatasheetView = False
frm.AllowEdits = True
frm.AllowFilters = True
frm.AllowDeletions = True
frm.AllowPivotTableView = True
frm.AllowPivotChartView = True
'Add the fields to the form
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "OrderID")
ctl.ControlSource = "OrderID"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "CustomerID")
ctl.ControlSource = "CustomerID"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "OrderDate")
ctl.ControlSource = "OrderDate"
ctl.Width = 100
Set ctl = frm.AddNewControl("ComboBox", acComboBox, acDetail, , "ProductID")
ctl.ControlSource = "ProductID"
ctl.RowSourceType = "Table/Query"
ctl.RowSource = "Products"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Quantity")
ctl.ControlSource = "Quantity"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Discount")
ctl.ControlSource = "Discount"
ctl.Width = 100
Set ctl = frm.AddNewControl("TextBox", acTextBox, acDetail, , "Total")
ctl.ControlSource = "Total"
ctl.Width = 100
'Add a label to the form header
Set ctl = frm.AddNewControl("Label", acLabel, acHeader, , "lblHeader")
ctl.Caption = "Orders Continuous Form Example"
ctl.ForeColor = vbRed
ctl.Font.Size = 14
ctl.Font.Bold = True
ctl.Width = 500
'Add a button to the form
Set ctl = frm.AddNewControl("CommandButton", acCommandButton, acDetail, , "cmdCalculate")
ctl.Caption = "Calculate Total"
ctl.OnClick = "CalculateTotal"
ctl.Width = 100
ctl.Height = 25
'Add a form footer
frm.HasModule = True
frm.Module.AddFromString "Option Compare Database" & vbNewLine & "Option Explicit" & vbNewLine
frm.Module.AddFromString "Public Sub
Can you provide an explanation for the code previously supplied?
Sure, here's an explanation for the code previously supplied:
Declaring Variables:
Code:
vbnet
Dim frm As Form
Dim tbl As TableDef
Dim ctl As Control
Creating a New Form:
The first step is to declare the variables that will be used in the code. In this case, the variables are frm of type Form, tbl of type TableDef, and ctl of type Control.
Code:
python
Set frm = CreateForm
This line of code creates a new form object in Microsoft Access.
Setting the Form's Record Source:
Code:
python
Set tbl = CurrentDb.TableDefs("Orders")
frm.RecordSource = tbl.Name
In this section, the tbl variable is set to the "Orders" table in the current database, and the frm object's RecordSource property is set to the name of the tbl table. This specifies the data source for the form.
Setting the Form's View:
Code:
frm.DefaultView = acFormViewContinuous
This line sets the form's default view to continuous form view, which is what creates the continuous form.
Setting the Form's Properties:
These lines set the various properties of the form, such as whether it can be viewed in a datasheet, if it allows editing, filtering, and deletions, and if it allows pivot table or chart views.
Adding Fields to the Form:
This section of the code adds the fields from the data source to the form. The first line creates a new control of type TextBox and sets it to the ctl variable. The ControlSource property is then set to the name of the field (in this case, "OrderID"), and the width of the control is set to 100. This process is repeated for each field in the data source.
Adding a Label to the Form Header:
This section of the code adds a label to the form header. A new control of type Label is created and set to the ctl variable. The Caption property is set to a string, the ForeColor property is set to red, and the font properties are set to size 14 and bold. The width of the control is also set to 500.
Adding a Button to the Form:
Code:
python
Set ctl = frm.AddNewControl("CommandButton", acCommandButton, acDetail, , "cmdCalculate")
I don't think it's right to say that VBA offers pointers. To use your example, any variable is a pointer, but you can't easily use the address for anything useful.
What would be "anything useful"?
Can you give any example of something that could easier, simpler, faster, or better by any sensible measurement, be done using the raw pointers instead of the typed variables?
The only examples I can currently think of are cases where complex types (e.g. object variables) are not supported and you must resort to raw pointers because they are simple numerical values.
Access is super, but occasionally I feel it would be useful to manage some data by manipulating pointers and record structures. It generally ends up being easier to do this sort of stuff using a temporary table.
You second sentence very much shoots down the argument you are trying to build with the first one. ;-) - And rightfully so!
You can manage data by manipulating pointers. - But I very strongly agree that is much easier to use data structures managed by Access or VBA instead.
This is not specific to VBA. In every modern programming language it is usually *much easier* to use a higher level of abstraction, instead of working with raw pointers. There sometimes are cases where the raw pointers offer a performance benefit or can work around some framework or language limitation. In every case you pay a price of harder to read, harder to maintain, and/or riskier to modify code, which depends on knowledge of internal implementations or arbitrary assumptions.
@adhoustonj, can you please explain the purpose of your replicating AI gibberish here?
The prose text contradicts itself.
In the code it invented non-existing methods from thin air. It uses methods not available in the context it claims the code is for. Some comments describe something entirely different than the code does.
In short: The code does not work, and it's not just minor tweaks required to make it work, it's utter rubbish. - It just looks "reasonable" on first sight, which is adding to the danger of using AI generated code.
So, what is your point posting 3 long posts of non working code? You could also have posted 3 pages of Hamlet instead.
@adhoustonj, can you please explain the purpose of your replicating AI gibberish here?
The prose text contradicts itself.
In the code it invented non-existing methods from thin air. It uses methods not available in the context it claims the code is for. Some comments describe something entirely different than the code does.
In short: The code does not work, and it's not just minor tweaks required to make it work, it's utter rubbish. - It just looks "reasonable" on first sight, which is adding to the danger of using AI generated code.
So, what is your point posting 3 long posts of non working code? You could also have posted 3 pages of Hamlet instead.
The real danger of AI like chatGPC et al is not that it can replace developers or other professionals. The real danger of AI is that it can lead to a false confidence when it turns out pseudo code that appears to a less experienced person to be legit. In one sense, it could lead to an overall lowering of the general skill level of the developer community as more and more new-comers learn less and less while relying more and more on the pseudo results it can return. Uncritically accepting those results means no thought occurs in the process of copying and pasting it into a project, and no learning.
What would be "anything useful"?
Can you give any example of something that could easier, simpler, faster, or better by any sensible measurement, be done using the raw pointers instead of the typed variables?
The only examples I can currently think of are cases where complex types (e.g. object variables) are not supported and you must resort to raw pointers because they are simple numerical values.
You second sentence very much shoots down the argument you are trying to build with the first one. ;-) - And rightfully so!
You can manage data by manipulating pointers. - But I very strongly agree that is much easier to use data structures managed by Access or VBA instead.
This is not specific to VBA. In every modern programming language it is usually *much easier* to use a higher level of abstraction, instead of working with raw pointers. There sometimes are cases where the raw pointers offer a performance benefit or can work around some framework or language limitation. In every case you pay a price of harder to read, harder to maintain, and/or riskier to modify code, which depends on knowledge of internal implementations or arbitrary assumptions.
Anything useful? Like a binary tree, or a balanced binary tree, or a queue, or an ordered list? I find a VBA collection unusable compared with an alternative of creating records, and manually linking them together and managing the links with a few easily written functions to manipulate pointers to other records within the data structure.
A table is probably useable as a next best thing, but it's cumbersome in comparison.
I imagine the msxml library reads an XML file into some sort of pointer chain structure.
The only time I needed "real" pointers was in trying to evaluate some issues in my genealogical database work, looking for patrilineal or matrilineal family members among all the clutter of cousins by marriage. My own stepdaughter forced me to do some difficult work in deciphering relationships since she has (1) a biological father, (2) the father who is named on her birth certificate, (3) the father who adopted her when the first two fathers bailed out, and (4) me as a step-dad. I solved that little knot by using recursion and formal arguments ByRef plus careful ordering of how I visit person-entries.
Real pointers would have made it a bit easier. I've got to say that it was at first a nightmare, but eventually I tamed that beast well enough that it no longer went crazy on loops when I ran into persons who married more than once in their lifetimes. And before anyone asks, the data returned by sites like Ancestry.COM includes all of those cousins when you download the GEDCOM file that is their data format for ancestry information, stored in person-attribute-value style with four layers of attribute-tagging. The fact that I was able to do this without a free-range address variable is proof that it IS possible to do without such luxuries for complex data traversal.
Forgive me my ignorance, I'm not familiar with binary trees.
What would be the advantage of using raw pointers to build a binary tree instead of this:
Code:
' in Class Module "BinaryTreeNode"
Public LeftChild As BinaryTreeNode
Public RightChild As BinaryTreeNode
Public ArbitraryData As Variant
Sure, raw pointers would use less memory and thus induce less memory management overhead. However, usually you're not just storing the node relations themselves but also additional data, which relativizes the memory overhead for a class instance.
The above should be good for managing a couple of thousand nodes. Beyond that you might run into stack memory issues when manipulating significant chunks of the tree.
So, fair enough, a large binary tree of relatively small nodes might indeed be a use case for using pointers instead of complex types.
We seem to have several topics going on simultaneously.
POINTERS:
1. Pointers. A named variable IS A "POINTER" to a specific place in memory where the value starts. Addressing in lower level code is always a base and displacement model. When the source code is compiled a displacement is calculated. This will be added to the address at which the code is loaded at run time.
Base = Address where code module is loaded at runtime + displacement = offset from start of code module GIVING the actual memory position where the variable starts which will be different every time the application runs.
2. A pointer as you are pining for would allow you to directly address the fourth position of a named variable. If you stand back and think about that, the concept violates first normal form which says that every attribute should be atomic - ie NOT divisible so being able to look at memory starting at the fourth position of a defined variable doesn't makes sense in a relational application.
3. Not all applications are built to process relational data so there are uses for pointers but no one has convinced me that they would be useful in Access unless you violated first normal form and mushed multiple attributes into one.
CODE GENERATED by the AI
1. in all three examples, the AI generated code that used the Access object model of a form. It set its attributes to be continuous. It defined ONE set of controls just the way a normal Access form is built. So, it is like some of our experts here. It answered the EXACT question but missed the actual intent of the question which was closer to how does the continuous form work?
@Pat Hartman
I was really just musing that one way of handling a continuous form might be to create a linked list of the visible records, and manipulating the linked list to reflect whichever records were currently visible on screen, then displaying the record with some sort of display record routine. A single form would only need to display a single record. A continuous form would call this for each displayed record.
At the same time, there must be some fancy indexing technique going on to organise the b-tree of the active index, and some sophisticated working set techniques to determine how the active data gets swapped back and forth from memory to disk and back. I just can't see how this can be achieved without using low level pointers.
I don't see that this is anything to with normalisation of the data in the database.
Having said that, I can't really conceive of the data structures and processes needed to manage a spreadsheet, or even to play chess.
I have absolutely no idea why you would want to create your own version of a continuous form but you would simply use a standard query to power it. A different type of database would not be of any advantage. Why would a linked list be better than an RDBMS? We would all be using linked lists (which predate relational databases) if they were a better file organization. The best database I ever used was IMS. IMS is still used for very large database applications because it is a very efficient way of handling data. It was displaced at IBM by DB2 because of the flexibility of the SQL language. Tools were built to make it easy to select sets of data from the database and use them to create reports for users. Eventually, the tools got slick enough that the users were able to handle them by themselves. IMS still requires program code to select data. There is no language like SQL to facilitate data retrieval and there isn't one for a linked list either that I know of. IMS is more like a linked list than like a RDBMS given its ability to support forward and backward pointers to speed up bi-directional movement through the hierarchy. An IMS database diagram looks like an Org chart. It is essentially a parent table with branching child tables. A linked list is a single table and there is no direct relationship between one linked list and another.
Then comes the problem that Access has a limit to how tall a form can be so your visible record list will be constrained by that. I don't see a way around that. If a Report supports a taller structure, you could use a report rather than a form to create your new and "improved" continuous form. Since the form isn't bound, there is no advantage to using a form over a report given that all data manipulation has to be performed with VBA.
Then you need to create controls to display each row. Access doesn't technically support control arrays so you have to handle the recurring rows with a naming convention that uses suffixes. You could create them on the fly or work with a fixed matrix. It's pretty silly to build them on the fly because that just complicates your coding effort. The controls will not be visible unless they contain data..
You have to figure out how many rows the query returned and use a loop to populate all the visible controls. If the query returns fewer rows than what the form will hold, fine. If more, you need to keep track of the id of the first and last records so that you can handle scrolling forward and backward a page at a time. Scrolling a row at a time will probably cause flickering depending on how many controls you need to manipulate to scroll.
Then you need to write code to perform validation and update the records. Delete is a different problem. Do you want to delete the row as you mark it or delete/update all the rows updated when the user pushes a save button.
All in all, a lot of pain for a dubious at best amount of gain.
Behind the scenes, the Access continuous form is probably just a big array.
@adhoustonj, can you please explain the purpose of your replicating AI gibberish here?
The prose text contradicts itself.
In the code it invented non-existing methods from thin air. It uses methods not available in the context it claims the code is for. Some comments describe something entirely different than the code does.
In short: The code does not work, and it's not just minor tweaks required to make it work, it's utter rubbish. - It just looks "reasonable" on first sight, which is adding to the danger of using AI generated code.
So, what is your point posting 3 long posts of non working code? You could also have posted 3 pages of Hamlet instead.
I thought that was what the post was asking.. And someone said they didnt have access to chatgpt to produce the response.
In no way do i endorse or think it is correct, to be honest, I'm not good enough to deduce that, but I thought that was the purpose of the thread..\
And yeah i just checked - first two responses are about chatGPT, so I posted the chatGPT responses? I'm not going to argue if it is gibberish or not. I'd appreciate the value of you explaining why you think it is gibberish then? I thought so too - you can see i included the code tags where some said vb net, python, etc, and I don't agree with it, but i posted it because it was asked and I believe the experts are in the building here.
Like any other solution, the result has to be tested. It doesn't matter if the solution is given by an AWF expert or ChatGPT we still need to test it. The better the question is formulated the better chance the answer will be correct. "Garbage in garbage out" used to be the norm, but this doesn't seem to apply to ChatGTP.
Once tested any errors should be redirected back into the existing conversation so the AI has a fair chance to evaluate the code otherwise it's just air code.
