Learning Access from scratch

[minn]

after deleting the link between tblPackSpec and tblVareity, it works out nicely, very cute, Mark !


Kindest Regards
Minn

 

[Lightwave]

It is cute to hide the CIDFK ; by the way, I mistakenly deleted CID and CIDFK in the form, and the relationship seemingly still exists; then, I got a chance to look into the properties of the control frame of the subform, and found that the underlying link is built between the links of parent field and the child field under data view

Yes that was slightly inaccurate of me... If the relationship had not been pre-defined in the relationships section I think deleting may have resulted in failure of the form but because that relationship is there you are allowed to have the CIDFK completely away.

Good luck you've pretty much got the basics now

 

[minn]

Mark, Thanks very much, I won't be able to get the basics quick without your help. it is really pity my workload as accountant will put me off the topic for a while, as year end closing requires tremendous input from me, hopefully I would have the basics with me once I got my chance coming back to study.

Have a good day , Mark. All the best !


Kindest Regards
Minn

Yes that was slightly inaccurate of me... If the relationship had not been pre-defined in the relationships section I think deleting may have resulted in failure of the form but because that relationship is there you are allowed to have the CIDFK completely away.

Good luck you've pretty much got the basics now

 

[minn]

Hi, Everyone, Hi, Mark

It seems long time no post, wish every one of you well.

I am trying hard to learn combo box, the drop down list looks cute. here is my work, but, with problem.

I have a subform, where I wish could have a dropdown list in datasheet view, so, the user may pick the content ( account in a chart of acc ), the problem is that I always get the same "account" as the record in the first line, whenever I update the the last line of my records, the previous lines get updated accordingly, which is not what I want, I wish to have a different account by different line in the subform.

I attached two screencatch, the first one is the sample I studied, which shows different content, while the second image is my work where you can see the account code is always same even if I intendedly changed them.

Kindest Regards
Minn

 

[minn]

Combo box only gives unique value to different records, I wish having a different value on different records.

By google search the internet, and studying sample database, I think the solution is to use SELECT DISTINCTROW statement to solve the problem.

I just got the half way; after put the SELECT statement into the row source, I noticed my form capture a value, however, when I move on to the next line, an error message comes up, saying the table needs a related record, can not add or change a record. it is a direct translation from Chinese message, I don't know if this is the correct information.

So, I am blocked again, please can you help me with this issue?

Kindest Regards
Minn

 

[Lightwave]

Hi there apologies for the delay in coming back to you... working

Right not sure what you are trying to do exactly. Most designers stay away from datasheet for application because you don't quite have the ability to use the controls that easily allow things like combo boxes.

I put together a variation on the previous sales database in my previous posts with three different ways in which to use the drop down or combo box. All implemented in a continuous form.

The first is looking up a value and storing the PK in a number field

The second is looking up a value but storing the text value in a text field

And the third shows a combo box set up with just a textual list attached to the control itself.

All of them can be useful with the first generally being the most subtle with the last being the most obvious.

I'm not sure if this is what you want but have a look at it and it might give you some ideas.

 

[minn]

Hi, Mark, no apologies please, you are always so helpful, and I don't know how to express my gratitudes to you. I feel I really pick up the thing faster than I initially expected, that is because of your help and guidance,
I am really getting into the right path, thank you !

I will take a close look at the samples you made, I am sure they will lead into a lot new ideas for me to learn, your works are eye openner to me

Kindest Regards
Minn

Hi there apologies for the delay in coming back to you... working

Right not sure what you are trying to do exactly. Most designers stay away from datasheet for application because you don't quite have the ability to use the controls that easily allow things like combo boxes.

I put together a variation on the previous sales database in my previous posts with three different ways in which to use the drop down or combo box. All implemented in a continuous form.

The first is looking up a value and storing the PK in a number field

The second is looking up a value but storing the text value in a text field

And the third shows a combo box set up with just a textual list attached to the control itself.

All of them can be useful with the first generally being the most subtle with the last being the most obvious.

I'm not sure if this is what you want but have a look at it and it might give you some ideas.

 

[minn]

Hi, Mark

the layout of your form looks so cool, I tried to set the property parameters the same as yours except what's in the data tab, however, my form is still displayed in full screen view, don't know how you managed to resize your form ?

By the way, it seems I could not find the combo box in your database, is there anything missing from me?


Kindest Regards
Minn

Hi there apologies for the delay in coming back to you... working

Right not sure what you are trying to do exactly. Most designers stay away from datasheet for application because you don't quite have the ability to use the controls that easily allow things like combo boxes.

I put together a variation on the previous sales database in my previous posts with three different ways in which to use the drop down or combo box. All implemented in a continuous form.

The first is looking up a value and storing the PK in a number field

The second is looking up a value but storing the text value in a text field

And the third shows a combo box set up with just a textual list attached to the control itself.

All of them can be useful with the first generally being the most subtle with the last being the most obvious.

I'm not sure if this is what you want but have a look at it and it might give you some ideas.

 

[Lightwave]

By the way, it seems I could not find the combo box in your database, is there anything missing from me?

That's because I attached the wrong file!

OK I'll try again this time hopefully with the right file

As before open up the form F001 after entering the database.

With regard to the form..

You can change how it is displayed in several ways experiment with the pop up and modal properties of the form also within the onload event of the form you can put in short pieces of command like

DoCmd.Maximize - which maximizes the form to the window of the database

If Pop up is set to on this will maximize the form to the screen if it is off it will maximize it to the database window.

Have a look and see how you get on.

 

[minn]

Mark, the illustration is elegant, and you don't know how excited I was, I made a simple ledger with combo box to show the chart of account for my subordinate, and she is really expressed, in the three method, I like the second way the most.

Just a few further questions, if you don't mind

1] what is the advantage using a query, instead of the original table, to facilitate the combo box ?

2] is it advisable to create form/Query/Report based on table T003Moons to retrieve T002Sales&Debtor data?

3] in explaining combo box 2, when you say "using the control box to add a combo box" , can you please be a bit more specific regarding the control box concept? I am a little lost here, sorry about this

In overrall, I really can understand the key points you put in the comments, for instance, set column width to zero for the first field, etc, as well as the SQL select statement. I am really impressed by your knowledge !

Thank you very much, Mark, this is indeed extraordinary, you are superb !


Kindest Regards
Minn

That's because I attached the wrong file!

OK I'll try again this time hopefully with the right file

As before open up the form F001 after entering the database.

With regard to the form..

You can change how it is displayed in several ways experiment with the pop up and modal properties of the form also within the onload event of the form you can put in short pieces of command like

DoCmd.Maximize - which maximizes the form to the window of the database

If Pop up is set to on this will maximize the form to the screen if it is off it will maximize it to the database window.

Have a look and see how you get on.

 

[Lightwave]

1] Quite often you will wish the combo box to referenced to a constantly changing list of items. Often in professional applications you allow the user to edit these lists through separate forms. If you bind a combo box to the table when the user selects from the list the order within that list will be according to entry in the table. Such entry is often quite random and with lots of entries the user will find it more and more difficult to select the correct reference unless it is sorted prior to it appearing in the combo box drop down. By binding the combo to a query rather than a table you are able to sort these items. The good thing about combo boxes you can have additonal fields that add more information for the user. Imagine a group of costcodes quite often they have a numerical code and a more descriptive name. In drop down's you could sort by either the number or the name in certain circumstances one may be better than an other. Without referring to a query you could have hundreds of codes that when shown in the drop down are neither in description order or code number order.

2] No you can just create reports on T002 Sales and Debtor data. The combo boxes are not separate entities - they are separate types of control but after selection the information is stored IN the T002Sales and Debtor Data. T003Moons and all tables referred to using combo box controls are there as a mechanism to aid the easy editing and alteration of lists in drop downs. You could for example create a form on T003Moons and for instance this would allow you to add more moons into this table if more were discovered or you wanted to put the rest of the moons of Jupiter in (I think it has something like 60 moons!) as and when new moons are discovered. Similarly a form based on that table would allow you to subtract moons as well if they disappear. Of course moons don't disappear but they might get downgraded to I don't know what they would be downgraded but hey planets can become downgraded so I guess moons might as well. err think I'm getting side tracked.

Now here's an important subtle point to why you might want to use the first type of combo box from my example database.. Historically astronomers used to name moons after there sponsors. Well they wouldn't they as they would have been out on the streets or down the mines probably as historically there wasn't much work for astronomers. This often meant that they were named after kings or queens and often not particularly relevant and a bit odd. As a result many eventually got changed to greek or roman mythological gods in line with the naming of the planets these being both more romantic and more memorable. Now how would such changes affect the example database I put together. If for instance Io had originally been called George and you had a whole Sales and debtor database with George's held in the Sales and Debtor table the astronomical society comes along and decides forthwith George will be renamed Io what do you do? Well if you have used options 2 or 3 your stuck you have to go into each record individually and rename all the George's to Io. If you have used option 1 then no problem you simply go into the Moons table and change George to Io and EVERY record in the Sales Debtor Record in that form will now show Io in the record. This is because the autonumber is held in the sales debtor table and the combo box control on the form on the sly shows the related name from the T003Moons table. This can be VERY useful in professional applications for keeping everything tight and reconcilled.

3]Remember when we discussed the changes between Access 2003 and Access 2007? You indicated that you had noticed certain changes to the menu. Tools menu had become the control menu. What I was referring to there was that within design view go to the area of the ribbon called controls and Menu using your mouse select the menu item "combo box". Move your mouse to the form and draw the combo box on the form. Control Box is simply my description of the area of the menu within design view where all the "controls" are held.

Think about this

Q. What are controls?

A. They are ANYTHING that you place on the form that assist in the editing and addition of information to your underlying tables. In this way even a field is a type of control. Other controls include - buttons / switches / combo boxes / images etc / list boxes etc.... You can even think about controls which might not be available in Access but are available in other software environments - eg dials / sliders / guages etc...

 

[minn]

This is tremendously insightful, Mark ! It's really silly me that I even forgot the most important lesson, yeah it is truely real the control box is actually the ribbon stuff ! now I fully understood what you were referring to.

I will get used to the professional habit, the database is supposed to be managed through code and serials, instead of narrative manner.

Access is a wonderful thing, and probably one of the most difficult subject to learn among MS applications; by talking to you, I really see the light hitting my mind, I own you too much, thousand words can not express enough of my thanks, Mark ! today, I regard myself having achieved my first step forward in learning Access, and there are more to be discovered though.

Have a good day, Mark, god bless you !


Kindest Regards
Minn

 

[minn]

It seems I am going nowhere for now, though I digged into NorthWind examples, but, really feel lost given so much events triggered by codes. I think I am getting into a wrong direction, but, really have no idea what is the next step to move forward. not sure if you can give me some exercises to do, Mark ? I attached my simple ledger(converted to access 2003) for reference. I knew there are so many to be learnt, simply don't know from where to pick up at this point in time.

Kindest Regards
Minn

 

[Lightwave]

Ok minn a slight correction its code triggered by events..

Some general principles I followed when I was starting out…
Concentrate on high value and try not to duplicate information from other systems. You want to avoid taking information out of one system and putting it into another system for instance. I'm slightly worried your taking information out of an accounting package and entering it into your own system.

Set realistic goals in what you wish to build.

Start small and build forward

Concentrate on simple things initially that you know completely initially.

Initially expect to be entering the information yourself - as a learner this will really focus you on user experience and ease of operation which is probably completely integral to good system design.

Good target systems can be where you have two overlapping systems both which are bad and you see that you can combine them and improve the design at the same time.

Don't fall in love with your own ideas - if its not working ditch it. Remember I did say you might have to ditch the project if it wasn't working out. About 3/4 of the things I build aren't actually ditched they just kind of atrophy away through neglect! You'll never do all the things you can think of so concentrate on action. The secret to getting things done is to take ACTION.

Remember bespoke databases are best fit for procedures that are repetitive and unusual.

Even the most advanced in here tend to shy away from building Stock control and Invoice systems as there are so many good off the shelf packages out there Nonetheless it can be useful to sometimes try these things as a learning experience. Modelling packages are tricky as well because they are so specific to an organisation and often require a lot of data input for relatively little on the other side. As a result users can spend all there time inputting information that may be a simple duplication of other systems.

The good thing is that you probably have a good basic understanding of queries tables and forms now.

Here's a couple of suggestions that might be considered as possible projects.
Where do you keep all of your passwords for all the differing logins you have? Isn't it bloody confusing wouldn't you like to have a list that you can sort by your login - by site - or by password. Do you need to remember the link to the site? I have over 90 passwords for things now and keep them in a database with suitable security precautions it's very useful.

The first database I built was a CRM - Customer Relational Management System something which I still use today just a professional contact system. A lot of professionals seem to loosely manage their personal and professional contacts and these often stretch into the thousands over long periods of time. This is an ideal task for your first database. As a businessman that may be a good thing to develop for yourself. Ironically professionals seem to be as bad as everyone else in storing their contacts.

Another thing that databases can be very good for is asset management particularly of property. I have an asset management application that tracks planning permissions on some 2,000 sites It is now tracking moneys as well and legal documents I've set it up as a sales ledger and works quite nicely adequately tracking millions of pounds in receipts. The important thing here there was absolutely nothing on the market place at that time to do this work.

Project management can be great as well. What projects are you involved in - what staff are involved in those projects when are tasks coming up and who will be involved in those. I've always found off the shelf project management software both complicted and inappropriate.

Large companies often have differing systems between departments each with their own referencing systems. Rosetta lists which link one key reference to another key reference remember when designing systems to include these important keys

 

[minn]

Thank you, Mark, as always ! I really like your idea of "focus you on user experience and ease of operation "

This afternoon, I learnt a lesson to open a filtered query from a form. that was really exciting, coz I stealed some code and put into the query criteria, add a botton on form with a macro to open that query. I followed the method explained step by step, it works out nicely in my simple ledger database. the more I learn, the more I like access, you can feel it from my words ?

I will pick a project from the list you give to me; reading your writings below, I feel there are tremendous knowledge I have to learn before I can really design my own sales model, which seems very stretching job, considering the skill level of modelling, concisive codes, deliver ease of use design ... ... well, you may notice I am pulling every little knowledge to respond your reply, even this is still a bit difficult for me at the moment

But, I am learning as quick as I can, wish read more of your posts, where ever they are in this forum. thanks for your generous contribution to learners like myself, and I wish you have good weekend, Mark !


Kindest Regards
Minn

Ok minn a slight correction its code triggered by events..

Some general principles I followed when I was starting out…
Concentrate on high value and try not to duplicate information from other systems. You want to avoid taking information out of one system and putting it into another system for instance. I'm slightly worried your taking information out of an accounting package and entering it into your own system.

Set realistic goals in what you wish to build.

Start small and build forward

Concentrate on simple things initially that you know completely initially.

Initially expect to be entering the information yourself - as a learner this will really focus you on user experience and ease of operation which is probably completely integral to good system design.

Good target systems can be where you have two overlapping systems both which are bad and you see that you can combine them and improve the design at the same time.

Don't fall in love with your own ideas - if its not working ditch it. Remember I did say you might have to ditch the project if it wasn't working out. About 3/4 of the things I build aren't actually ditched they just kind of atrophy away through neglect! You'll never do all the things you can think of so concentrate on action. The secret to getting things done is to take ACTION.

Remember bespoke databases are best fit for procedures that are repetitive and unusual.

Even the most advanced in here tend to shy away from building Stock control and Invoice systems as there are so many good off the shelf packages out there Nonetheless it can be useful to sometimes try these things as a learning experience. Modelling packages are tricky as well because they are so specific to an organisation and often require a lot of data input for relatively little on the other side. As a result users can spend all there time inputting information that may be a simple duplication of other systems.

The good thing is that you probably have a good basic understanding of queries tables and forms now.

Here's a couple of suggestions that might be considered as possible projects.
Where do you keep all of your passwords for all the differing logins you have? Isn't it bloody confusing wouldn't you like to have a list that you can sort by your login - by site - or by password. Do you need to remember the link to the site? I have over 90 passwords for things now and keep them in a database with suitable security precautions it's very useful.

The first database I built was a CRM - Customer Relational Management System something which I still use today just a professional contact system. A lot of professionals seem to loosely manage their personal and professional contacts and these often stretch into the thousands over long periods of time. This is an ideal task for your first database. As a businessman that may be a good thing to develop for yourself. Ironically professionals seem to be as bad as everyone else in storing their contacts.

Another thing that databases can be very good for is asset management particularly of property. I have an asset management application that tracks planning permissions on some 2,000 sites It is now tracking moneys as well and legal documents I've set it up as a sales ledger and works quite nicely adequately tracking millions of pounds in receipts. The important thing here there was absolutely nothing on the market place at that time to do this work.

Project management can be great as well. What projects are you involved in - what staff are involved in those projects when are tasks coming up and who will be involved in those. I've always found off the shelf project management software both complicted and inappropriate.

Large companies often have differing systems between departments each with their own referencing systems. Rosetta lists which link one key reference to another key reference remember when designing systems to include these important keys

 

[Lightwave]

Thought of an idea that might be useful related to your sales database.

See if you can get your system to show you sales
by month
by product
and if there is such a thing by customer...

It should get you thinking about using calculated fields in queries and you might have to think about things like a pivot table as well.

And in business you always want to see if there's a link between months and sales and keep and eye on your best customers / products.

 

[minn]

Fantastic, Mark, I will consider this immediately, though it sounds a bit intimidating to make calculated fields. I will do a research to nail it down, thank you, Mark !

Good weekends !

Kindest Regards
Minn

Thought of an idea that might be useful related to your sales database.

See if you can get your system to show you sales
by month
by product
and if there is such a thing by customer...

It should get you thinking about using calculated fields in queries and you might have to think about things like a pivot table as well.

And in business you always want to see if there's a link between months and sales and keep and eye on your best customers / products.

 

[minn]

HI, Mark and all

Wish you all well these days !

I am recently studying a sample database, which uses much less VBA codes, and the design of the forms are elegant, all the functions I am looking for seems there. however, what makes me most fascinated is as below

I attached the screenshot to illustrate my question though

In that image, the Query you may find seems to me not an action query; but, the data input from this form pass through to the table Customers
I thought only action query like Update type or Append type query can do the job. but, the select query performed the task of data input, why is so?

Maybe I am very blind to the capacity of a select query, simply didn't get the information that could convince me its capability


Kindest Regards
Mingfeng

 

[Lightwave]

Hi Mingfeng

Select queries neither add nor detete information per se but the fields that are revealed can be edited or will allow for additions.

Although Access will allow you to design forms on tables and for simple projects there is really nothing wrong with this practice if you are wanting to build applications that are going to be used by lots of users or you want to have the ability to upsize the database to for instance a SQL Server backend the best practice is to use the record source of forms as queries this has several advantages for improving the operation of databases over networks and when lots of people are using it although primarily when using an SQL backend.

The reasoning is that Select queries only give you a subset of the information so the idea is that by only pulling down a subset of the total fields in a table you are dragging less information across your local area network which is obviously quicker and more effiicient. You can get away with inefficient design when there are a small number of users but it becomes increasingly important as concurrency and network distances increase.

The database you are looking at seems quite well layed out so I suspect that the designer has spent a bit of time learning his craft and has been following best practice in its design.

If you can get into the practice of designing forms on queries if you make the leap to SQL Backends (you can then design databases used by 100s of people at the same time) it will be easier to adapt your databases (although still not totally straight forward).

Is this something you purchased? This is a great idea it gives you a step up and you can get in there and alter things as you wish.
When you start designing your own systems it gives you clarity as to what is happening but you start to see that there can be a lot of work to get things working exactly how you want them to.

Another source of databases that users can go on to "edit" is here. The prices are almost free in terms of usual business fees - I haven't tried any of them but the idea seems very good.

http://www.accesstogo.org.uk/

PS : Feel free to start a new thread you have a new question might open up your questions to others Afraid you might be stuck with my opinions otherwise.

 

[minn]

Hi, Mark

Thank you as always, and that's exactly what I was thinking of, select queries won't change anyting in a table, I will take another look to see if I had overlook by mistake, or the developer of this database wrote some code to achieve his goal?? I will dig a bit of the information from his design.

the database is called order management database, I downloaded from miscrosoft website for free. I have another version of order management, which is more advanced, as the user interface is built on vba code in a navigation style, I barely can understand the code. so, I will concentrate on the one I show you in the screencatch for a while, it is fascinating design, as you said, there are bunch of things I need to learn before I can get there.

Thanks for sharing with me the good source of access utility, I will take a look at their products to see if I can use.

Have a nice day !

Kindest Regards
Minn

Hi Mingfeng

Select queries neither add nor detete information per se but the fields that are revealed can be edited or will allow for additions.

Although Access will allow you to design forms on tables and for simple projects there is really nothing wrong with this practice if you are wanting to build applications that are going to be used by lots of users or you want to have the ability to upsize the database to for instance a SQL Server backend the best practice is to use the record source of forms as queries this has several advantages for improving the operation of databases over networks and when lots of people are using it although primarily when using an SQL backend.

The reasoning is that Select queries only give you a subset of the information so the idea is that by only pulling down a subset of the total fields in a table you are dragging less information across your local area network which is obviously quicker and more effiicient. You can get away with inefficient design when there are a small number of users but it becomes increasingly important as concurrency and network distances increase.

The database you are looking at seems quite well layed out so I suspect that the designer has spent a bit of time learning his craft and has been following best practice in its design.

If you can get into the practice of designing forms on queries if you make the leap to SQL Backends (you can then design databases used by 100s of people at the same time) it will be easier to adapt your databases (although still not totally straight forward).

Is this something you purchased? This is a great idea it gives you a step up and you can get in there and alter things as you wish.
When you start designing your own systems it gives you clarity as to what is happening but you start to see that there can be a lot of work to get things working exactly how you want them to.

Another source of databases that users can go on to "edit" is here. The prices are almost free in terms of usual business fees - I haven't tried any of them but the idea seems very good.

http://www.accesstogo.org.uk/

PS : Feel free to start a new thread you have a new question might open up your questions to others Afraid you might be stuck with my opinions otherwise.