Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2015
    Posts
    4

    Answered: Does someone know about a book or webpage that explain programming code in access?

    Hi to all:

    First of all, thank you for giving me a moment of you time.

    I'm new in this world of programming and i would like to know how to read and understand correctly a programming code line x line, because I'd like to learn how to programming in access 2007-2013, but at my town is difficult to find a teacher so if anyone decide to help me, I could advance in this matter.

    I prepared a code in colors in order to better explains myself, for Example:

    BLUE = I'm not fully aware how it works, but I'd like to be sure.

    RED = This comments have helped me a little bit, but because of my level, I cant understand the code fully.

    GREEN = Have no idea

    BLACK = Well, is the beginning and the end... right?

    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Option Compare Database
    Option Explicit


    Private Sub cboKingdomID_AfterUpdate()

    ' Set the Phylum combo box to be limited by the selected Kingdom
    Me.cboPhylumID.RowSource = "SELECT tblPhylum.PhylumID, tblPhylum.PhylumName FROM tblPhylum " & _
    " WHERE KingdomID = " & Nz(Me.cboKingdomID) & _
    " ORDER BY PhylumName"

    Me.cboPhylumID = Null

    EnableControls
    FilterSpeciesList

    End Sub

    Private Sub cboPhylumID_AfterUpdate()
    ' Set the Class combo box to be limited by the selected Phylum
    Me.cboClassID.RowSource = "SELECT tblClass.ClassID, tblClass.ClassName FROM tblClass " & _
    " WHERE PhylumID = " & Nz(Me.cboPhylumID) & _
    " ORDER BY ClassName"
    Me.cboClassID = Null


    EnableControls
    FilterSpeciesList

    End Sub

    Private Sub cboClassID_AfterUpdate()
    ' Set the Order combo box to be limited by the selected Class
    Me.cboOrderID.RowSource = "SELECT tblOrder.OrderID, tblOrder.OrderName FROM tblOrder " & _
    " WHERE ClassID = " & Nz(Me.cboClassID) & _
    " ORDER BY OrderName"
    Me.cboOrderID = Null

    EnableControls
    FilterSpeciesList

    End Sub

    Private Sub cboOrderID_AfterUpdate()
    ' Set the Family combo box to be limited by the selected Order
    Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _
    " WHERE OrderID = " & Nz(Me.cboOrderID) & _
    " ORDER BY FamilyName"
    Me.cboFamilyID = Null

    EnableControls

    FilterSpeciesList

    End Sub

    Private Sub cboFamilyID_AfterUpdate()
    ' Set the Genus combo box to be limited by the selected Family
    Me.cboGenusID.RowSource = "SELECT tblGenus.GenusID, tblGenus.GenusName FROM tblGenus " & _
    " WHERE FamilyID = " & Nz(Me.cboFamilyID) & _
    " ORDER BY GenusName"
    Me.cboGenusID = Null

    EnableControls

    FilterSpeciesList

    End Sub

    Private Sub cboGenusID_AfterUpdate()
    ' Filter the list of species based on the selected genus.
    FilterSpeciesList
    End Sub


    Private Sub FilterSpeciesList()

    Dim strRS As String

    ' Filter the list box appropriately based on the combo box selection(s)
    strRS = "SELECT qryTaxonomy.SpeciesName, qryTaxonomy.Description FROM qryTaxonomy"

    If Not IsNull(Me.cboGenusID) Then
    strRS = strRS & " WHERE GenusID = " & Me.cboGenusID

    ElseIf Not IsNull(Me.cboFamilyID) Then
    strRS = strRS & " WHERE FamilyID = " & Me.cboFamilyID
    ElseIf Not IsNull(Me.cboOrderID) Then
    strRS = strRS & " WHERE OrderID = " & Me.cboOrderID
    ElseIf Not IsNull(Me.cboClassID) Then
    strRS = strRS & " WHERE ClassID = " & Me.cboClassID
    ElseIf Not IsNull(Me.cboPhylumID) Then
    strRS = strRS & " WHERE PhylumID = " & Me.cboPhylumID
    ElseIf Not IsNull(Me.cboKingdomID) Then
    strRS = strRS & " WHERE KingdomID = " & Me.cboKingdomID
    End If


    strRS = strRS & " ORDER BY qryTaxonomy.Description;"

    Me.lstSpeciesID.RowSource = strRS

    Me.lstSpeciesID.Requery


    End Sub

    Private Sub EnableControls()

    ' Clear the combo boxes
    If IsNull(Me.cboKingdomID) Then
    Me.cboPhylumID = Null
    End If

    If IsNull(Me.cboPhylumID) Then
    Me.cboClassID = Null
    End If

    If IsNull(Me.cboClassID) Then
    Me.cboOrderID = Null
    End If

    If IsNull(Me.cboOrderID) Then
    Me.cboFamilyID = Null
    End If

    If IsNull(Me.cboFamilyID) Then
    Me.cboGenusID = Null
    End If

    ' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
    Me.cboPhylumID.Enabled = (Not IsNull(Me.cboKingdomID))
    Me.cboClassID.Enabled = (Not IsNull(Me.cboPhylumID))
    Me.cboOrderID.Enabled = (Not IsNull(Me.cboClassID))
    Me.cboFamilyID.Enabled = (Not IsNull(Me.cboOrderID))
    Me.cboGenusID.Enabled = (Not IsNull(Me.cboFamilyID))


    End Sub

    Private Sub Form_Load()
    ' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
    EnableControls
    ' Show all species in the list until filters are selected from the combo boxes.
    FilterSpeciesList
    End Sub


    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx

    Also something that could explain macros would be great, because someone told me that it would be easier if I start with it, and also I'd like to have some suggestions about some books or webpages where i could find examples of code correctly explained in order to progress with my learning.

    Any help will be greatly appreciated.

    This is the database i'm using as a learning start: https://www.dropbox.com/s/8oy71yqff6...ses.accdb?dl=0

    God bless you.

  2. Best Answer
    Posted by myle

    "mmmmmmmmmmmmmmmmmmmmmmmmm
    this could be a long one LOL

    VBA work on Sub and function

    SUB are just go and do that bit of code and come back here
    Function are go do that code BUT return something (yes i know SUB can do that lets not get into that )

    and can have Private sub (only the current object can use it ) or Public SUB (the HOLE file can use it)

    Sub MY_Message(txtvalue) ' <= start the sub
    msgbox(txtvalue) ' <=msgbox the text i pass in
    End Sub '<= im finish this sub

    so somewhere in your code

    call MY_Message("Hello there") '<= this will pass the "Hello There" to the MY_Message SUB and that will show a mesage box

    some user dont add the Call ........ you code does not use the call

    I do only to make it easyer when reading code i can see it jumping around

    Your Code says
    .....
    .....
    EnableControls '<= go do that sub and come back here
    FilterSpeciesList '<= go do that sub and come back here
    ....
    ....
    ....


    If IsNull(Me.cboKingdomID) Then '<= is checking if the feild cboKingdomID is null [[ Me stand for current form ]]
    Me.cboPhylumID = Null '<= so if its null we make the Me.cboPhylumID null also
    End If '<= now end the if


    strRS = strRS & " ORDER BY qryTaxonomy.Description;" '<= add the some text together and make it longer

    Me.lstSpeciesID.RowSource = strRS '<= put strRS string value into the Me.lstSpeciesID.RowSource

    Me.lstSpeciesID.Requery '<= I want the program to Requery this Object (update it NOW)


    Me.cboGenusID.Enabled = (Not IsNull(Me.cboFamilyID)) '<= YOU DID NOT WRITE LOL THIS IS A GOOD TRICK THE PERSON WHO WROTE THIS KNOW HOW TO CODE VERY VERY WELL

    Me.cboGenusID.Enabled = '<= can have true or false ture turn it on | false turn it off

    IsNull(Me.cboFamilyID) = will return a true if its null or false if something in it

    (Not IsNull(Me.cboFamilyID)) '<= but as we want the opppstit we put in the NOT in SO true comes false and false comes true

    this is a real Basic to VB

    I DONT USE MACROS for anything I always write code I find it easyer for me.



    I'll been useing msaccess for a long long time I start in access 2.0

    just keep asking google he help you heaps."


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    mmmmmmmmmmmmmmmmmmmmmmmmm
    this could be a long one LOL

    VBA work on Sub and function

    SUB are just go and do that bit of code and come back here
    Function are go do that code BUT return something (yes i know SUB can do that lets not get into that )

    and can have Private sub (only the current object can use it ) or Public SUB (the HOLE file can use it)

    Sub MY_Message(txtvalue) ' <= start the sub
    msgbox(txtvalue) ' <=msgbox the text i pass in
    End Sub '<= im finish this sub

    so somewhere in your code

    call MY_Message("Hello there") '<= this will pass the "Hello There" to the MY_Message SUB and that will show a mesage box

    some user dont add the Call ........ you code does not use the call

    I do only to make it easyer when reading code i can see it jumping around

    Your Code says
    .....
    .....
    EnableControls '<= go do that sub and come back here
    FilterSpeciesList '<= go do that sub and come back here
    ....
    ....
    ....


    If IsNull(Me.cboKingdomID) Then '<= is checking if the feild cboKingdomID is null [[ Me stand for current form ]]
    Me.cboPhylumID = Null '<= so if its null we make the Me.cboPhylumID null also
    End If '<= now end the if


    strRS = strRS & " ORDER BY qryTaxonomy.Description;" '<= add the some text together and make it longer

    Me.lstSpeciesID.RowSource = strRS '<= put strRS string value into the Me.lstSpeciesID.RowSource

    Me.lstSpeciesID.Requery '<= I want the program to Requery this Object (update it NOW)


    Me.cboGenusID.Enabled = (Not IsNull(Me.cboFamilyID)) '<= YOU DID NOT WRITE LOL THIS IS A GOOD TRICK THE PERSON WHO WROTE THIS KNOW HOW TO CODE VERY VERY WELL

    Me.cboGenusID.Enabled = '<= can have true or false ture turn it on | false turn it off

    IsNull(Me.cboFamilyID) = will return a true if its null or false if something in it

    (Not IsNull(Me.cboFamilyID)) '<= but as we want the opppstit we put in the NOT in SO true comes false and false comes true

    this is a real Basic to VB

    I DONT USE MACROS for anything I always write code I find it easyer for me.



    I'll been useing msaccess for a long long time I start in access 2.0

    just keep asking google he help you heaps.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    All LOT of the blue SQL you coded in vb and did not have to.
    That's what queries are for.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to expand on earlier replies
    Code:
    .RowSource
    .rowsource is the underlying query for a list or combo box
    so assigning a value to the rowsource property means select the following data for this combo box
    Code:
    SELECT tblPhylum.PhylumID, tblPhylum.PhylumName FROM tblPhylum " & _
    " WHERE KingdomID = " & Nz(Me.cboKingdomID) & _
    " ORDER BY PhylumName"
    is a SQL statment which extract the specified columns (PhylumID and PhylumName) from a table called tblPhylum
    WHERE the kingdom ID is the same as the selected value in the combobox called cboKingdomID, and display any data extracted in ascending PhylumName order
    the cocde then sets the current value of the combo box
    Code:
    Me.cboPhylumID = Null
    to nothing so that the user is forced to select soemthing
    ...enablecontrols calls a function/sub routine whuich has no parameters and returns nothing, so it Access VBA speak its a sub routine

    in essence you have six top down linked combo boxes for Genus.Familiy, Order, Class, phylum and kingdom for a biological classification schema

    the code block starting with
    Code:
    If Not IsNull(Me.cboGenusID) Then 'is there a value in cboGenusID, if so
      strRS = strRS & " WHERE GenusID = " & Me.cboGenusID 'set the filter for GenusID = cboGenusID
    ElseIf Not IsNull(Me.cboFamilyID) Then
      strRS = strRS & " WHERE FamilyID = " & Me.cboFamilyID
    ....
    is a series of exclusive if statements, processed sequentially, and if there is a match it sets a WHERE filter for that itme only, if there is no match then see if the next combo box has a value selected
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I DONT USE MACROS for anything I always write code I find it easyer for me.
    amen to that


    I'll been useing msaccess for a long long time I start in access 2.0
    amen to that, also

    ....
    All LOT of the blue SQL you coded in vb and did not have to.
    That's what queries are for.
    not too certain I'd agree with that to be honest.....
    the code creates a where clause to extract data from qryTaxonomy and and it woudl be tricky to make this flexible enough using a query, granted you coudl do this if say you have a predefiend query for each of the 6 options and selecgted which query to use at runtime but it woudl still require some code behind the scenes....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Apr 2015
    Posts
    4

    Thanks to all...

    Thanks to all the guys trying to help me, this means a lot to me.

    myle(Making Your Life Easy) & healdem (Jaded Developer), I have a question guys, is there a way to unchain the 6 cbox, I mean, I'd like to understand how a cbox works and how it will work if they where independent and filtering one subform (the interacion of 1, 2, or 6 cbox in no particular order), is there a way to do this? or several ways? or easier ways? also, your explanation was very useful, could you indicate me if there were a page or book that explain this way?


    Thanks...

  8. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could begin with: http://www.functionx.com/vbaccess/
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •