some issues with DB form

[ghia]

Hello!

im a rookie in Access and VB, i have been trying to finnish a database in a couple of weeks and im at the end of designing it.
i tried chatgpt and Claude to solve my problems in the form, but having trouble getting a solution thru AI.

if you like to have a look at my sample database i would appreciate it a lot.

my problems in priority order:

1: when i switch tabs or use a button in the form, the subform flickers when updating i guess, this i would like to be resolved to make it more appleasing
2: i dont seem to get rid of the "window" marked in Picture as "#2"
3: the two searchboxes "objektnamn1" and "sök objektID" im trying to get a interactive search in, my goal is that while im typing part of the name, i want a dropdown list of the objects that matches with what im typing.
4: i have locked some fields from being edited until i pressed the "redigera info"-button, then it locks back again when i switch post, but this function seem to have stopped working, while designing the form, maybe i could get a "save"- button to lock them again.
5: all in all im open for suggestions in the design and coding to make it look better and work better.

 

[Gasman]

That 'long bar' is a record selector. It is that large due to the depth of your detail section. So just set record selectors to No.

 

[ghia]

That 'long bar' is a record selector. It is that large due to the depth of your detail section. So just set record selectors to No.

sorted, thanks!

 

[Gasman]

For 3, I would use @MajP's FAYT code

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

I would also not have spaces in field/control names or characters like #
It would help if you said which controls are meant to be locked/unlocked?

 

[RonPaii]

I found that when working with AI and Access is to keep reminding it that you are looking for Access specific solutions. It tends to drop into VB or VB.Net type answers.

 

[KitaYama]

I found that when working with AI and Access is to keep reminding it that you are looking for Access specific solutions. It tends to drop into VB or VB.Net type answers.

You may know it, but if you don't :

If you use ChatGPT, you can create custom versions of ChatGPT that combine instructions, extra knowledge, and any combination of skills in a specific purpose. Each of them are called GPT and each GPT is a specialized ChatGPT made for a specific course (Programming, Excel, Politic,....)
When you ask your question in a GPT, you receive much more precise, accurate and more in depth answers. And replies are limited only to the selectd branch.

For example if you add an Access GPT to your account and ask a question in that GPT, all answers will be limited to Access.
Here's a part of my GPTs.

If I click Ms Access Copilot and ask a question, I won't see any answer about VB or VB.net. All replies are specific to Access.
There are thousands of GPTs, and you have to find a suitable one for your questions.
To add a GPT, simply click Explore GPTs in your left navigationbar and search for one.

I use only ChatGPT, but I think other AIs offer similar services too.

 

[mike60smart]

Hello!

im a rookie in Access and VB, i have been trying to finnish a database in a couple of weeks and im at the end of designing it.
i tried chatgpt and Claude to solve my problems in the form, but having trouble getting a solution thru AI.

if you like to have a look at my sample database i would appreciate it a lot.

my problems in priority order:

1: when i switch tabs or use a button in the form, the subform flickers when updating i guess, this i would like to be resolved to make it more appleasing
2: i dont seem to get rid of the "window" marked in Picture as "#2"
3: the two searchboxes "objektnamn1" and "sök objektID" im trying to get a interactive search in, my goal is that while im typing part of the name, i want a dropdown list of the objects that matches with what im typing.
4: i have locked some fields from being edited until i pressed the "redigera info"-button, then it locks back again when i switch post, but this function seem to have stopped working, while designing the form, maybe i could get a "save"- button to lock them again.
5: all in all im open for suggestions in the design and coding to make it look better and work better.

Hi
I took a quick look at your Database which is in fact 1 Table which you have copied from an Excel File.

Points to note are as follows:-
1. that you should not use spaces in field names
2. you should not use any of the following characters in field names !"£$%^&*()_+-=][}{#'@:;?/.>,<

You are recording details of Multiple Landowners in the one table when their details should be in a Related Table.

 

[mike60smart]

Hello!

im a rookie in Access and VB, i have been trying to finnish a database in a couple of weeks and im at the end of designing it.
i tried chatgpt and Claude to solve my problems in the form, but having trouble getting a solution thru AI.

if you like to have a look at my sample database i would appreciate it a lot.

my problems in priority order:

1: when i switch tabs or use a button in the form, the subform flickers when updating i guess, this i would like to be resolved to make it more appleasing
2: i dont seem to get rid of the "window" marked in Picture as "#2"
3: the two searchboxes "objektnamn1" and "sök objektID" im trying to get a interactive search in, my goal is that while im typing part of the name, i want a dropdown list of the objects that matches with what im typing.
4: i have locked some fields from being edited until i pressed the "redigera info"-button, then it locks back again when i switch post, but this function seem to have stopped working, while designing the form, maybe i could get a "save"- button to lock them again.
5: all in all im open for suggestions in the design and coding to make it look better and work better.

Hi Ghia

Instead of just 1 Table your relationship diagram should have at least the following tables.

 

[LarryE]

Hello!

im a rookie in Access and VB, i have been trying to finnish a database in a couple of weeks and im at the end of designing it.
i tried chatgpt and Claude to solve my problems in the form, but having trouble getting a solution thru AI.

if you like to have a look at my sample database i would appreciate it a lot.

my problems in priority order:

1: when i switch tabs or use a button in the form, the subform flickers when updating i guess, this i would like to be resolved to make it more appleasing
2: i dont seem to get rid of the "window" marked in Picture as "#2"
3: the two searchboxes "objektnamn1" and "sök objektID" im trying to get a interactive search in, my goal is that while im typing part of the name, i want a dropdown list of the objects that matches with what im typing.
4: i have locked some fields from being edited until i pressed the "redigera info"-button, then it locks back again when i switch post, but this function seem to have stopped working, while designing the form, maybe i could get a "save"- button to lock them again.
5: all in all im open for suggestions in the design and coding to make it look better and work better.

Please describe what you attempting keep track of with this database. If you can describe, in detail, what the database is to be used for, perhaps someone can advise you on a proper design. What you have currently is not a relational database.

 

[mike60smart]

Please describe what you attempting keep track of with this database. If you can describe, in detail, what the database is to be used for, perhaps someone can advise you on a proper design. What you have currently is not a relational database.

Hi Larry
I did some translation and it would be based on Land that has a Lighthouse and who the Landlords are.

 

[ghia]

Please describe what you attempting keep track of with this database. If you can describe, in detail, what the database is to be used for, perhaps someone can advise you on a proper design. What you have currently is not a relational database.

its and object database for lighthouses, buoyes etc. so for every object i have my own unique id, for the objects i have information about what kind, position, when maintenance was done some pictures and who owns the property and information about them. the plan is to edit each object in the form, and keep it visually pleasing.
the biggest problem i have is flickering in the form when switching between objects and tabs in the form.

 

[LarryE]

its and object database for lighthouses, buoyes etc. so for every object i have my own unique id, for the objects i have information about what kind, position, when maintenance was done some pictures and who owns the property and information about them. the plan is to edit each object in the form, and keep it visually pleasing.
the biggest problem i have is flickering in the form when switching between objects and tabs in the form.

I looked at your file, and you are a long way from being able to construct any input forms. You need to study how to design a relational database first.

 

[GPGeorge]

Unfortunately, the table in your current database is not yet suitable for a relational database application.
It appears to be the result of importing an Excel worksheet into Access with no further effort to convert it into the appropriate tables. That is a very common mistake when first attempting to use Access. There are lots of resources available to help you learn how to correct this design problem.

I can recommend a good YouTube video, for example. It will give you a solid, but basic, introduction to the Rules of Normalization.

This is only one of many such videos, so invest plenty of time studying them.

In addition, there is an excellent group of Blog articles to help you. http://www.rogersaccessblog.com/category/problems-with-repeated-columns/
Again, study all of the articles on Normalizing to relational tables from spreadsheet-style tables.

Once you've studied these resources, make an attempt to revise your table accordingly. Post back the revised tables for review and comment and suggestions. We'll help you get closer to a proper relational database design.

And, of course, set any thought of form design aside for the time being. Come back to that only after your tables are properly set up.

 

[Ken Sheridan]

What you currently have is really a spreadsheet masquerading as a table. A relational database is very different from a spreadsheet however, being made up of multiple related tables, each of which models an entity type in that part of the real world being modelled by the database, with each table containing columns representing the attributes of the entity type. An important principle of the database relational model is that each datum is stored once only. This is what the process of normalization by decomposition is is intended to ensure.

I've had a look through my own files for a suitable example to give you an idea of the sort of tables and relationships into which your current single table would be decomposed. The nearest I have to hand is illustrated below. This is the model for a simple database to monitor and schedule preventative maintenance works on bridges in a certain state in the USA. As you can see, even for a restricted subject area it involves a significant number of tables, but one thing you will note is that each table has relatively few columns. Essentially the underlying reality is made up of three main entity types, the structures themselves, the works undertaken, and the equipment used for those works. The other tables model relationships between those entity types.

It would of course be possible to extend this model to include much other data similar to that which you are recording about each structure. This would involve adding further related tables modelling the additional entity types and relationships which would be involved.

The main work in the database is done in the following query which returns a list of all preventative maintenance works currently due for the structure currently selected in a dialogue form:

SELECT
    Structures.StructureNumber,
    Structures.StructureName,
    YEAR(StructurePMJobs.DateDue) AS YearDue,
    MONTH(StructurePMJobs.DateDue) AS MonthDue,
    StructurePMJobs.DateDue,
    StructurePMJobs.DateCompleted,
    PMs.PMCode,
    PMs.PMDescription
FROM
    StructurePMs
    INNER JOIN ((Structures
    INNER JOIN StructurePMJobs
        ON Structures.StructureNumber = StructurePMJobs.StructureNumber)
    INNER JOIN PMs
        ON StructurePMJobs.PMCode = PMs.PMCode)
        ON (Structures.StructureNumber = StructurePMs.StructureNumber)
        AND (StructurePMs.PMCode = StructurePMJobs.PMCode)
        AND (StructurePMs.StructureNumber = StructurePMJobs.StructureNumber)
WHERE
    Structures.StructureNumber = Forms!frmPMSchedule!cboStructure
    AND (StructurePMJobs.DateCompleted IS NULL
    Or Forms!frmPMSchedule!cboStructure = "0");

So, the task you are faced with is firstly to determine and then build the physical model of tables and relationships between them which you will need to be able to decompose your current table. It will then be possible to recast your current data into the normalized tables. This can be done by executing a set of 'append' queries in a specific order. The rule of thumb is that rows must be inserted into the referenced tables in each relationship before rows are inserted into the referencing tables. I'm attaching a simple little demo file which illustrates how this is done.

The other change to how your data is stored which you will need to make relates to the large number of Boolean (Yes/No) columns which you have in the current table. At present you are 'encoding data as column headings'. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. The correct way to model such data would be, instead of multiple columns, to represent each datum as a separate row in a related table, with the values you are currently using as column headings each being a value in a single column in the related table. I'm attaching another little demo file which illustrates how such data can be recast into a correct structure.

 

[mike60smart]

its and object database for lighthouses, buoyes etc. so for every object i have my own unique id, for the objects i have information about what kind, position, when maintenance was done some pictures and who owns the property and information about them. the plan is to edit each object in the form, and keep it visually pleasing.
the biggest problem i have is flickering in the form when switching between objects and tabs in the form.

Hi Ghia
The attached is your 1 table converted to a relational layout with all the current data retained.

It still needs more work.

Is this what you require?

 

[Gasman]

Here are a few links to view as well. Compiled by a previous member here.

Solved - Normalisation

Micron on here posted a few links on another site, the first one being for Normalisation, which Excel users to come to Access have never heard of. He also posted a good few other links. Rather than post to his original post (as it is on another site) anymore, I am posting them here and so will...

www.access-programmers.co.uk

 

[LarryE]

its and object database for lighthouses, buoyes etc. so for every object i have my own unique id, for the objects i have information about what kind, position, when maintenance was done some pictures and who owns the property and information about them. the plan is to edit each object in the form, and keep it visually pleasing.
the biggest problem i have is flickering in the form when switching between objects and tabs in the form.

If I needed to track marine objects, their locations, who owns them, what maintenance was performed, who performed it and when it was performed, I might start with a design like this:

You will need to translate it into your own language, but you can see how a relational design works.

1. Each MarineObject may have multiple ObjectOwners and ObjectLcationDates
2. Each LocationDate may have multiple ObjectLocations
3. Each ObjectLocation may have multiple MaintenanceDates
4. Each MaintenanceDate may have multiple MaintenancePersonnel workers and MaintenanceTypes
5. Each MaintenancePersonnel worker and MaintenanceType results in some result in MaintenanceDetail

I have attached the design file