Newbie - Help to review and comment my Access project

[Babycat]

Hi Everyone

@Edit: Attached the db file

I am actually an electronic hardware engineer. I dont have much experience in IT field. I have been learning Access since 2020,
Anyway, my fisrt access program was sent to my friend last year (at that time, I did not even know to compile and split db).
Now, it is a bit better I think, I am developing a second access program of my life , it is about inventory management system.
I already know to encrypt user password, split database, making consistent-connection to BE, export data to excel...
This is designed for multi-user but I have not tested it in real- LAN condition yet.

I here by attached my project to our forum, everyone please feel free to take a look and give comment so that i can improve it.

This is still non-commercial project, I do it for learning as my hobby, but I also have intention to add-in more features and find customer to sell once I am confident with my access level (anyway, that is future story...)
(However, if someone find a good feature in my project, feel free to use it, but dont just identically copy it for your commercial project)

It looks nice and smoothly if number of transaction (oder) is small, when I use form "Frm_DataGenerator" to generate let say 1,000,000 records of transaction (order), it becomes extremely slow.
On those continuous forms, I really want to make pages to avoild too long list, I have searched but did not found an appropriate solution.

Thank everyone for your comments and advices

 

[CJ_London]

@Babycat - nothing attached? compact your db and then zip it

a million records will impact performance if tables are not properly indexed - and quite frankly, why would you want a form to display that number of records?

Principle should be form opens with an empty recordset or is passed criteria to limit the return to a few records rather than populating with a whole table and applying filters. Subsequent 'filtering' would then be applied as criteria rather than a filter

 

[isladogs]

Looks like you forgot to attach the database!

 

[Babycat]

Hi,
The first file was exceeded file size limit, I have compress and attached it again

 

[Babycat]

@Babycat - nothing attached? compact your db and then zip it

a million records will impact performance if tables are not properly indexed - and quite frankly, why would you want a form to display that number of records?

Principle should be form opens with an empty recordset or is passed criteria to limit the return to a few records rather than populating with a whole table and applying filters. Subsequent 'filtering' would then be applied as criteria rather than a filter

Dear CJ_London

I have filter elements so that en-users can seek exactly what they want, however they can also reset the filter, in that case, all record should be shown, likely you remove all filter excel sheet.

 

[CJ_London]

there are missing objects so file won't compile - looks like something to do with encryption and there are no relationships defined so difficult to work out what the app is supposed to do. Also all your log in functionality is a PIA if you want us to spend time trying to make valid comments.

My comments in my first post stands - you should not be using filters. Use criteria instead

 

[Babycat]

Dear CJ_London,

Sorry for inconvenience. I have fixed compiling error by removing undeclared function/variable, I also replaced the attachment in 1st post
As mentioned, I am newbie so it is poor design....sorry about that
In fact, I dont really know what the role of database relationship is...

I only make relationship in query when I need...

About the filter...I might have used wrong term. I have form for customer input the parameters, then query use value of these textbox, combo-box in WHERE clause (please click button Import/Export Stat" to open that form.

 

[MajP]

Read

Exercices to practice relationship

i dont have no relationship inside. ok have some query with relationship but this is not what i see on other databases. That may be fine, not to have any relationships established. Having relationships established in your relationship window is not always required. It is also not the same as...

www.access-programmers.co.uk

 

[CJ_London]

About the filter...I might have used wrong term. I have form for customer input the parameters, then query use value of these textbox, combo-box in WHERE clause (please click button Import/Export Stat" to open that form.

OK, but that only applies if a certain value for another control on another form is 'Lotus'. Regret don't know what that means or how frequently it would be applied. That control can also have a value of the product code or 0, so you are mixing datatypes in one control which is not a good idea - unable to determine if productcode is text or numeric

You do not appear to be using that principle for frmMain, but perhaps that only has a small recordset.

Since your tables are linked, I am unable to see whether they are properly constructed and have appropriate indexing.

Your design principles seem OK (no spaces, use of meaningful names, code indented, etc) but personally I would not use attachment fields Store the attachments in a suitable folder and store the filename and perhaps path in a table instead. If you have 1m records with an access BE then space may become an issue.

 

[Babycat]

OK, but that only applies if a certain value for another control on another form is 'Lotus'. Regret don't know what that means or how frequently it would be applied. That control can also have a value of the product code or 0, so you are mixing datatypes in one control which is not a good idea - unable to determine if productcode is text or numeric

Dear CJ_London

Thank you for spending your valuable time reviewing my code.
The working principle is:
There are 3 parts in main form:
+ Filter (on middle top) to allocate exact product that user want to find (I think about list of 1000 products, it hard to manually allocate..)
+ Product_QuickInfo (continuous subform at left-side)
+ Controls of Mainform to show Product detail (right-side)
Both QuickInfo (QInfo) and Mainform are bound to TBLPRODUCT, clicking any line on QInfo will redirect Mainform to that product detail, accordingly.

There are 2 ways of behaviour that customer might want to open statistical transactions form: He intends to view a certain product and he does not intent any product in his mind.
If he wanna see the statistical transactions of a product, he will locate that product name on QInfo window, then dbl_click on field "Qty" of that product at this time I assign text string "Lotus" to a textbox subform QInfo". In other case, he just click on button "Import/Export Stat", the textbox value now is ZLS "". Both cases the form "FrmTransDetail" will be opened, Load event will base on textbox value to determine the recordsource is all product or only one product. In the other hand, textbox plays role of global variable.

    If Forms!MainFrm.SubFrmTable.Form!Txt_ActiveRecordID_Dummy = "Lotus" Then
    'True when user click button on FrmQInfo
            Cbx_F_MaVT = Nz(Forms!MainFrm.SubFrmTable.Form!Txt_Qinfo_ProductID, 0) ' 
            MAVT_Criteria_Str = "P.ProductID = " & Cbx_F_MaVT
    Else
            MAVT_Criteria_Str = "True "
    End If

The field P.ProductID is numeric, thus I think it OK to concatenate "P.ProductID = " & Cbx_F_MaVT

Since your tables are linked, I am unable to see whether they are properly constructed and have appropriate indexing.

The latest attachment on #1 post, I already used local tables (removed linked tables), i am not sure if they are indexing and dont know how to index them (i read somewhere that indexing is costly...)

Your design principles seem OK (no spaces, use of meaningful names, code indented, etc) but personally I would not use attachment fields Store the attachments in a suitable folder and store the filename and perhaps path in a table instead. If you have 1m records with an access BE then space may become an issue.

Yes, I want to change it, but I think i need to learn about system_file_object or something similar to copy user selected photo to the pre-defined folder and rename it... I have never studied such before.

btw: I got unpredicted issue when design the subform in this style. Mainform sets the subform's recordsource on load event, they are:
1. Mainform's load event might trigger before subform load, so below systax will introduce an error such: object is not set or not exist...

Forms!MainFrm.Form!SubFrm.Form.RecordSource = SQLSub

2. If causes flickering, expecially when SQLSub returns many records.

May you have any suggest to deal with it?

Many thanks!

 

[CJ_London]

The field P.ProductID is numeric, thus I think it OK to concatenate "P.ProductID = " & Cbx_F_MaVT

it is in this instance, just not good practice

(i read somewhere that indexing is costly...)

see this link https://www.access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/

i need to learn about system_file_object

it is not difficult, plenty of examples on this and other forums - google/bing something like 'access vba store path to document'

Mainform sets the subform's recordsource on load event,

subforms open and load before mainform. And I don't see that in your code. The subform recordsource is preopulated with

'SELECT P.*, P.ProductCode AS MAVT FROM TBLPRODUCT AS P ORDER BY P.ProductName; '

which brings through the whole table. And I can't find

'Forms!MainFrm.Form!SubFrm.Form.RecordSource = SQLSub'

anywhere in your code

2. If causes flickering, expecially when SQLSub returns many records.

without knowing which form/subform can't comment

 

[Babycat]

it is in this instance, just not good practice

Cbx_F_MaVT = Nz(Forms!MainFrm.SubFrmTable.Form!Txt_Qinfo_ProductID, 0) '
            MAVT_Criteria_Str = "P.ProductID = " & Cbx_F_MaVT

My concern is: whenever Txt_Qinfo_ProductID is null, it reassigns to productID 0, what if my db has productID 0? Then I talk to myself: productID is autonumber, it can never be 0...

For indexing and store attachment in window folder, I will study and apply to my project.

The third point, somehow I can't reproduce the issue anymore...

Anyway, thank everyone alot for helping me.