Page 1 of 3 123 LastLast
Results 1 to 40 of 100

Thread: Ok, DB connection.. What gives?

  1. #1

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Ok, DB connection.. What gives?

    The module I found is as follows.. I'm not sure on the error I'm getting.. but i want it to connect to my DB.. Am I not including something in the components section or something?

    Code:
    Option Explicit
    
    Public cnn As ADODB.Connection
    Public rs As ADODB.Recordset
    Public cmd As ADODB.Command
    
    
    Public Own As String
    Public Year As String
    Public CoinSet As String
    Public Name As String
    Public Mint As String
    Public Circulation As String
    Public Grading As String
    Public Value As String
    
    Public Sub dbconnection()
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "User ID=Admin;password=;" & " Data Source=" & App.Path & "\db1.mdb;"
            .CursorLocation = adUseClient
            .Open
        End With
    End Sub
    Attached Images Attached Images  
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  2. #2
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    315

    Re: Ok, DB connection.. What gives?

    I you're missing a reference to scope a variable as ADODB.Connection.

    https://learn.microsoft.com/en-us/sq...l-server-ver16

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Can't read your error, but more than likely you are simply missing a REFERENCE (found under Project, References).

    I use this one for all my db apps....:

    Microsoft ActiveX Data Objects 2.8 Library
    Sam I am (as well as Confused at times).

  4. #4

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    That fixed it, thanks. Now just gotta actually read from the DB to fill the grid, but i'll look into that later.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  5. #5

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Ok, so i'm trying the following and I thought I was doing it right.. However, it's not calling any info from the DB. The form load calls "ResetGrid". Thinking maybe if I had a number instead of trying to call by "NAME", simply because the "NAMES" are not in alphetical order. Rather quarters - (or any coin for that matter), is released by date.. I don't if calling by "released date" is the way to go.. Maybe it is? I'm just trying to figure this out. If it is by "released date" then I will have to add that into table1 OR call it from table2. Because table2, has the info per coin such as released date.



    Code:
    Public Sub ResetGrid()
        Grid1.Clear
        setUpGrid
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "Microsoft.Jet.OLEDB.4.0" 'connecting to 2002/2003 Access
    '        .Provider = "Microsoft.ACE.OLEDB.12.0"  'connecting to an MS ACCESS 2007 database
            .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\quarters.mdb;"
            .CursorLocation = adUseClient
            .Open
        End With
        
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = cnn
      cmd.CommandText = "select * from Table1 where coinset = '" & CoinSet & "' order by Name*1"
        Set rs = cmd.Execute
        Dim i As Integer
        Do While Not rs.EOF
            i = i + 1
            Grid1.Rows = i + 1
            Grid1.RowHeight(i) = 500
            Grid1.TextMatrix(i, 0) = rs!Own
            Grid1.TextMatrix(i, 1) = rs!Year
            Grid1.TextMatrix(i, 2) = rs!CoinSet
            Grid1.TextMatrix(i, 3) = rs!Name
            Grid1.TextMatrix(i, 4) = rs![type 1]
            
            If Not IsNull(rs![Type 2]) Then ' THIS NEEDS ALL FIELDS IN THE DB TO BE FILLED NOT BLANK.. HENCE THE EOF
            Grid1.TextMatrix(i, 5) = rs!Mint
            End If
    
            Grid1.TextMatrix(i, 6) = rs!other
            Grid1.TextMatrix(i, 7) = rs!Circulation
            Grid1.TextMatrix(i, 8) = rs!Grade
            Grid1.TextMatrix(i, 9) = Format$("" & rs!Value, "$#,##0.00")
    
            rs.MoveNext
        Loop
        lblTotalCoins.Caption = CStr(rs.RecordCount)
        'I don't how to get how many OWNED count from the set for:
        'lblCoinsOwned
    'I don't know how to call the total dollar value of the ones owned for
    'lblTotalSetValue
        End Sub
    Last edited by Mongoose_MHS; Jun 5th, 2024 at 03:37 PM. Reason: spelling
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,621

    Re: Ok, DB connection.. What gives?

    "Name*1" isn't a valid column identifier... at least not as it currently written. Based on the rest of your code, I'm guessing it should be just "Name"

    Getting the owned count is a bit simpler/complicated depending on how it's stored.

    If there is a field in your table, you can check for it in your loop and increment a counter based on that.

    If it's part of another table, then you'll want to either left join to the existing table and count where a field is not null...

    Orrrr... you do a second query that does the join and count() for you...

    Without knowing how you're storing ownership, it's difficult to say how to solve that problem.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    table1:

    Code:
    ID	Own	Year	CoinSet	Name	Mint	Circulation	Grading	Value
    1	Yes	2022	American Women	Maya Angelou	D	Circulated	Unknown	$0.25
    2	Yes	2022	American Women	Maya Angelou	P	Circulated	Unknown	$0.25
    3	Yes	2022	American Women	Dr. Sally Ride	D	Circulated	Unknown	$0.25
    4	Yes	2022	American Women	Dr. Sally Ride	P	Circulated	Unknown	$0.25
    5	Yes	2022	American Women	Wilma Mankiller	D	Circulated	Unknown	$0.25
    6	Yes	2022	American Women	Wilma Mankiller	P	Circulated	Unknown	$0.25
    7	Yes	2022	American Women	Nina Otero-Warren	D	Circulated	Unknown	$0.25
    8	Yes	2022	American Women	Nina Otero-Warren	P	Circulated	Unknown	$0.25
    9	Yes	2022	American Women	Anna May Wong	D	Circulated	Unknown	$0.25
    10	Yes	2022	American Women	Anna May Wong	P	Circulated	Unknown	$0.25
    11	Yes	2023	American Women	Bessie Coleman	D	Circulated	Unknown	$0.25
    12	Yes	2023	American Women	Bessie Coleman	P	Circulated	Unknown	$0.25
    13	Yes	2023	American Women	Edith Kanaka?ole	D	Circulated	Unknown	$0.25
    14	Yes	2023	American Women	Edith Kanaka?ole	P	Circulated	Unknown	$0.25
    15	No	2023	American Women	Eleanor Roosevelt	D	Circulated	Unknown	$0.25
    16	No	2023	American Women	Eleanor Roosevelt	P	Circulated	Unknown	$0.25
    17	No	2023	American Women	Jovita Idar	D	Circulated	Unknown	$0.25
    18	Yes	2023	American Women	Jovita Idar	P	Circulated	Unknown	$0.25
    19	No	2023	American Women	Maria Tallchief	D	Circulated	Unknown	$0.25
    20	No	2023	American Women	Maria Tallchief	P	Circulated	Unknown	$0.25
    21	No	2024	American Women	Reverend Dr. Pauli Murray	D	Circulated	Unknown	$0.25
    22	No	2024	American Women	Reverend Dr. Pauli Murray	P	Circulated	Unknown	$0.25
    Table2:
    Code:
    ID	CoinSet	                Name	                        Year	                        Mints	                Info
    1	American Women	Maya Angelou	2022	   DP  	Celebrated writer, performer, and social activist
    2	American Women	Dr. Sally Ride	2022    DP	        Physicist, astronaut, educator, and first American woman in space
    3	American Women	Wilma Mankiller	2022	   DP	        First woman elected principal chief of the Cherokee Nation
    4	American Women	Nina Otero-Warren	2022	DP	Suffrage leader and the first woman superintendent of Santa Fe public schools
    5	American Women	Anna May Wong	2022	DP	First Chinese American film star in Hollywood
    6	American Women	Bessie Coleman	2023	DP	First African American and first Native American woman licensed pilot
    7	American Women	Edith Kanaka?ole	2023	DP	Indigenous Hawaiian composer, custodian of native culture and traditions
    8	American Women	Eleanor Roosevelt	2023	DP	Leader, reformer, first lady, and author
    9	American Women	Jovita Idar	2023	DP	Mexican-American journalist, activist, teacher, and suffragist
    10	American Women	Maria Tallchief	2023	DP	America’s first prima ballerina
    11	American Women	Rev. Dr. Pauli Murray	2024	DP	Poet, writer, activist, lawyer, and Episcopal priest
    12	American Women	Patsy Takemoto Mink	2024	DP	First woman of color to serve in Congress
    13	American Women	Dr. Mary Edwards Walker	2024	DP	Civil War era surgeon, women’s rights and dress reform advocate
    14	American Women	Celia Cruz	2024	DP	Cuban-American singer, cultural icon, and one of the most popular Latin artists of the 20th century
    15	American Women	Zitkala-Ša	2024	DP	Writer, composer, educator, and political activist
    16	American Women	Ida B. Wells	2025	TBD	Investigative journalist, suffragist, and civil rights activist
    17	American Women	Juliette Gordon Low	2025	TBD	Founder of Girl Scouts of the United States of America
    18	American Women	Dr. Vera Rubin	2025	TBD	Astronomer who pioneered work on galaxy rotation
    19	American Women	Stacey Park Milbern	2025	TBD	Activist for people with disabilities
    20	American Women	Althea Gibson	2025	TBD	Multi-sport athlete and first Black athlete to break the color barrier at the highest level in tennis
    I know it came out jumbled copy / paste.. However you get the point - (I hope).. just realized I didn't copy/paste the "release dates" in table2.. but yes there is another column for them. Sorry
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Yeah...agree with tech....

    Let's try this....rewrite your select statement like this (just temporarily)

    Select * from Table1 where coinset = '" & coinset & "'

    Comment out all of the lines below that statement.

    Then add Debug.Print cmd.CommandText after the Select statement.
    What is printed out in the Immediate Window?

    This is just to see if THAT part of the statement is what you expect.

    Let us know what that printout is, and we'll go from there step by step
    Sam I am (as well as Confused at times).

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    JUST saw your last post...it would be oh so helpful if you would attached the database (seeing as how it doesn't look proprietary).
    Sam I am (as well as Confused at times).

  10. #10

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Also, I just relalized.. We can't call by "release date" BECAUSE.. Some Quarters of "American Women" for example have not been released yet.. So, I think maybe add a number per coin? Like coin 1, coin 2, etc.. ???
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  11. #11

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Ok, give me a little bit.. I need a small break.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    just have a placeholder in your DB for Release Date for those not yet released...say, something like 1/1/3000. Then when you do your query and that is returned, you can put in your grid text like "NOT YET RELEASED".
    Sam I am (as well as Confused at times).

  13. #13

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    it's not letting me upload the DB.. Says "INVALID FILE"..
    Attached Images Attached Images   
    Last edited by Mongoose_MHS; Jun 5th, 2024 at 03:13 PM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  14. #14
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    zip your entire project, attach that zip file....it'll help us all
    Sam I am (as well as Confused at times).

  15. #15

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Ok..
    Attached Files Attached Files
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  16. #16

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    I also just changed "label5" on form 2 to "lblTotalSetValue"
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  17. #17
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    OKAY...I see lots of issues, but we won't get into them right now...got a couple questions...see this attachment.

    You have a column for TYPE, but there is not TYPE in the database. Also, the same for OTHER.

    I changed two field names (name to coinName, and year to coinYear)...those you used can be reserved names...best to avoid them.

    So when I added my code, and those DB changes, I get this grid loaded.
    Obviously you have column widths a bit off, and also I see you have centering of some columns in the first row, but not in the others. I can fix all that but would need to know how you want the data to appear.

    But not today...getting ready to run a Game of Jeopardy! I created for a group this evening.
    Attached Images Attached Images  
    Sam I am (as well as Confused at times).

  18. #18

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Ok, ty.. same, I'm noticing the centering in certain columns that I want. I'm not sure what I did wrong, I thought I specifically coded that stuff in correctly.. I guess not.

    "Type" & "Other" are for other coin values which I haven't really decided what to do with them atm, However they do have a place for info as I am just getting into this for my younger sister, but I am looking into them. If, they don't hold any real value I can remove them.

    Code:
    'So this would center the columns 0 and 1 because they are adjacent.. Hower 1 is not and 3 - 8 are but not 9
    'Align CONTENT in column 0 & 1 CENTERED
    Dim b As Integer, j As Integer
        With Grid1
            For j = 0 To 1
            .Col = j
                For b = 1 To .Rows - 1
                .Row = b
                .CellAlignment = flexAlignCenterCenter
                Next b
            Next j
        End With
    
    'What about number 2, Do I have to do anything or will it LEFT automatically?
    
    'Align CONTENT in columns 3 - 8 CENTERED
    Dim x As Integer, y As Integer
        With Grid1
            For y = 3 To 8
            .Col = y
                For x = 1 To .Rows - 1
                .Row = x
                .CellAlignment = flexAlignCenterCenter
                Next x
            Next y
        End With
    
    'What about number 9, Do I have to do anything or will it be LEFT automatically?
    
    End Sub
    All I know is the NAMES are to be LEFT and DOLLAR VALUE is to be LEFT.. Everything else CENTERED.. So, I dunno what I did wrong OR what I didn't do..
    Last edited by Mongoose_MHS; Jun 5th, 2024 at 06:56 PM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  19. #19

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Updated DB.. the 2 fields (type & other), are blank atm because I don't know what to put in them or if I am going to use them.. I just wanted some flexibility incase I do need to use them. I'm still researching coin collecting in general.. I have an assumption they will be used.. Just a lot of info out there to determine what I would use them for. The names mean nothing because they can be changed. They're just place holders atm.
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 07:21 AM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  20. #20

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    and I just relalized a mistake on my part.. - Referring back to #18

    I noticed that I put 3-8 centered.. when it should state "4 - 8" centerd.. NOT 3 to 8

    so.. Column CONTENT aligned should be as follows.. I don't know what I did wrong other than a mis-number as stated above.

    0 = centered
    1 = centered
    2 = left
    3 = left
    4 = centered
    5 = centered
    6 = centered
    7 = centered
    8 = centered
    9 = left

    Other than that, I don't see why your pic example is mis-aligning them.. I'm confused
    Last edited by Mongoose_MHS; Jun 5th, 2024 at 09:06 PM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  21. #21

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    After doing some more research.. Those 2 - (type & other), can be removed from the DB as they are not needed. New DB included.

    0 = centered
    1 = centered
    2 = left
    3 = left
    4 = centered
    5 = centered
    6 = centered
    7 = left

    Code:
    'Align CONTENT in columns 0 - 1 CENTERED
    Dim b As Integer, j As Integer
        With Grid1
            For j = 0 To 1
            .Col = j
                For b = 1 To .Rows - 1
                .Row = b
                .CellAlignment = flexAlignCenterCenter
                Next b
            Next j
        End With
    
    'What about numbers 2 & 3, Do I have to do anything or will it LEFT automatically?
    
    'Align CONTENT in columns 4 - 6 CENTERED
    Dim x As Integer, y As Integer
        With Grid1
            For y = 4 To 6
            .Col = y
                For x = 1 To .Rows - 1
                .Row = x
                .CellAlignment = flexAlignCenterCenter
                Next x
            Next y
        End With
    
    'What about number 7, Do I have to do anything or will it be LEFT automatically?
    Attached Files Attached Files
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 07:37 AM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  22. #22
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Here is what it looks like now....

    The reason your rows weren't centered or left-aligned was that you were doing that before you added all the rows to the grid...hence, you only had two rows at that time.

    Name:  coinset.jpg
Views: 152
Size:  45.4 KB

    quartercollector.zip
    Sam I am (as well as Confused at times).

  23. #23
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    10,323

    Re: Ok, DB connection.. What gives?

    I'll just make one comment, and then bow out.

    For my two-cents, I'd use the DAO to do this project. When you know you'll be using only a local MS-Access database, IMHO, the DAO is just much more intuitive to use, and you really don't lose much of anything.

    The only significant difference in the syntax is that you have to do an "Update" anytime you're doing an "AddNew" or "Edit" of a recordset. Beyond that, and the vastly simpler code for opening databases, the syntax is nearly identical.

    I also typically instantiate a Workspace before opening databases, but that's not essential.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  24. #24

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    I tried running it but nothing loads into the grid..

    *Nvm, it does when you click the women quarters on form 2.. I was trying to do it on form1.. So, I'll have to copy that code to form1. Atm, BOTH women and state quarters are calling the same set, but I see why.. I'll try and work on it. Thanks Sam.

    *Also, noticing the scrollbar isn't working like I thought it would, the content in the grid should scroll as I move the scrollbar.. Also, why is there an extra row at the bottom? Shouldn't the last row be the last record? Just trying to figure out the tweaks I need to make is all.
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 10:51 AM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  25. #25

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Ok, so no matter what I do it keeps calling the same set.. "American Women". I also got the scrollbar to work as I wanted.. It was the track setting. Each SubSubMenu has options, and each option should bring up it's own set. Right now, it's calling the same set. Updated DB attached

    Code:
    'GET AMEICAN WOMEN SET
    Private Sub mnuSubSubMenu_Click(Index As Integer)
    
            sCoinSet = Mid(mnuSubSubMenu(Index).Caption, 2)
        Dim c As Long
        
        For c = 0 To mnuSubSubMenu.UBound
            mnuSubSubMenu(c).Checked = (Index = c)
        Next
        CheckItemByIndex 0
        UncheckItemByIndex 1
        UncheckItemByIndex 2
        For c = 0 To mnuSubSubMenu1.UBound
            mnuSubSubMenu1(c).Checked = False
        Next
        
         If Index = 0 Then
            Form2.Label1.Caption = "American Women Quarters"
        ElseIf Index = 1 Then
            Form2.Label1.Caption = "State Quarters" 'IF so, call "STATE QUARTERS" set
            End If
    
        For c = 0 To mnuSubSubMenu2.UBound
            mnuSubSubMenu2(c).Checked = False
        Next
        
            'Call selected collection from the DB and show in the Flexgrid
            Dim i As Integer
            Set cmd = New ADODB.Command
            Set cmd.ActiveConnection = cnn
            cmd.CommandText = "select * from Table1 where coinset = '" & sCoinSet & "' order by coinName"
            Set rs = cmd.Execute
            Do While Not rs.EOF
                    i = i + 1
                    Grid1.Rows = Grid1.Rows + 1
                    Grid1.TextMatrix(i, 0) = rs!Own
                    Grid1.TextMatrix(i, 1) = rs!mintyear
                    Grid1.TextMatrix(i, 2) = rs!CoinSet
                    Grid1.TextMatrix(i, 3) = rs!coinName
                    Grid1.TextMatrix(i, 4) = rs!Mint
                    Grid1.TextMatrix(i, 5) = rs!Circulation
                    Grid1.TextMatrix(i, 6) = rs!Grading
                    Grid1.TextMatrix(i, 7) = Format$("" & rs!Value, "$#,##0.00") 'I tweaked this to fit my needs
                    rs.MoveNext
            Loop
    
    'Align the CONTENT properly
            Dim b As Integer, j As Integer
                With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 0 To 7
                            .Col = b
                            .CellAlignment = flexAlignCenterCenter
                        Next b
                    Next j
                End With
                
            With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 2 To 3
                            .Col = b
                            .CellAlignment = flexAlignLeftCenter
                        Next b
                    Next j
            End With
            
              With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 7 To 7
                            .Col = b
                            .CellAlignment = flexAlignLeftCenter
                        Next b
                    Next j
            End With
    End Sub
    Attached Files Attached Files
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 11:28 AM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  26. #26

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    I was thinking something more simple like this, However, I do realize I am dealing with the checkmark menu and the coding for them must stay intact.. So, this is NOT accurate.. But just to show an idea? I'm just getting really confused atm on how to show different sets based on menu option selection.

    Code:
    Private Sub mnuSubSubMenu_Click(Index As Integer)
    'Get "American Women" coin set
      CardSet = "American Woment"
    Form2.Label1.Caption = "American Women"
      getReleaseDate coinSet
      Unload Me
      frmRecord.Show vbModal
      
    End Sub
    'Get "State Quarters" coin set
    Private Sub smnuSubSubMenu1_Click(Index As Integer)
      CardSet = "State Quarters"
      Form2.Label1.Caption = "State Quarters"
      getReleaseDate coinSet
      Unload Me
      frmRecord.Show vbModal
    
    End Sub
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 11:20 AM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  27. #27
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    OK....so, as I said in a post long ago reference your project...i see no need for multiple forms....
    SO, I now start your program from Form2.
    The reason for that blank line is that you had the rows set to 2 in the IDE for the Grid. I changed it to 1.
    Also, the new mdb you sent, you had coinYear instead of what I had, mintYear. I fixed my code to reflect properly.

    Look at this and see what else you need help on.

    (If you want to have the startup form (which is now form2) look like your Form1, I'd suggest putting a frame/image on form2, and have it disappear upon some click action.)
    Attached Files Attached Files
    Sam I am (as well as Confused at times).

  28. #28

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Thanks Sam. I see there is a bit more coding for me to do as I build up the sets with research on them, But you gave me a great starting point to where I can continue. The only Other things at this time I would like help on is getting the top 3 pieces above the grid.. The 2 for COLLECTION and the Value of each set.:

    lblOwnCount
    lblTotalCoins
    lblSetValue - which you have atm as Label5


    There will be multiple forms because I want to left click on an entry to give specs on that coin clicked AND if I right click on an entry, I can edit that entry. Which I will need help with. So these things I do need help with.

    I know the right click can be done with something like this: However, i'm not sure if it would be similar to left clicking.. but each button will open another form for specific reasons. This will not change as this is how I want it to work.
    Code:
    Private Sub Grid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
       Dim Row As Integer
        Row = Grid1.MouseRow
        If Button = vbRightButton Then  'right mouse
        
        
       Own = Grid1.TextMatrix(Row, 0)
       Year = Grid1.TextMatrix(Row, 1)
       Coin Set = Grid1.TextMatrix(Row, 2)
       Name = Grid1.TextMatrix(Row, 3)
       Mint = Grid1.TextMatrix(Row, 4)
       Circulation = Grid1.TextMatrix(Row, 5)
       Grade = Grid1.TextMatrix(Row, 6)
       Value = Grid1.TextMatrix(Row, 7)
    
        Edit.Show vbModal
       
    End Sub

    Also, a ADD ENTRY form later on, but this one can wait.. I'm in no rush for this one, but the other 2 go with form2
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 03:15 PM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  29. #29

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Also, Just tested by taking the alignment portion and put it ABOVE calling the set from the DB I don't see it loading and then shifting alignment.. It's more instant

    Thinking, I may add that portion to a module and call it instead of having large chunks of code for every SubSubMenu option click.. Trying to make the hard coding smaller and neater.. Just thinking out loud.
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 04:00 PM.
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  30. #30
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    for the labels, add this code:

    Code:
            
    'get count of number of coins owned and total value of all coins        Dim numberOwned As Integer
            Dim myValue As Double
            For i = 1 To Grid1.Rows - 1
                    If UCase(Grid1.TextMatrix(i, 0)) = "YES" Then
                            numberOwned = numberOwned + 1
                    End If
                    myValue = myValue + CDec(Grid1.TextMatrix(i, 7))
            Next i
            lblOwnCount.Caption = CStr(numberOwned)
            lblTotalCoins.Caption = CStr(Grid1.Rows - 1)
            Label5.Caption = Format(myValue, "$##0.00")
    ...just before "End Sub" in the mnuSubSubMenu routine.
    Sam I am (as well as Confused at times).

  31. #31
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Quote Originally Posted by Mongoose_MHS View Post
    Also, Just tested by taking the alignment portion and put it ABOVE calling the set from the DB I don't see it loading and then shifting alignment.. It's more instant
    Yes....I just wanted to show you HOW it worked...set everything up as Centered, then Left-Shifted those you wanted left. Of course what you say may be the correct way to do it. BUT, remember, to get ALL rows you have to have that number of rows to go through to set the alignment.
    Sam I am (as well as Confused at times).

  32. #32

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Actually, putting the alignment ABOVE doesn't align things properly as I thought.. Just makes it instant. I just don't like the load and shift.. I gotta find a way around that to be aligned right and instant not load and then shift.

    However, Thanks Sam for the the counts.. Works well
    Last edited by Mongoose_MHS; Jun 6th, 2024 at 04:19 PM. Reason: spelling
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  33. #33
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    What does the Edit form look like? Have you designed it yet?
    No real sense in using both left and right mouse clicks. Just use left click, bring up your EDIT form with all the information you want displayed in textboxes. Then have an EDIT button (as well as a CANCEL) in which you can make changes to the DB based upon how a user changes the textbox information.
    As far as an ADD functionality, that could be on the EDIT form as well.
    Sam I am (as well as Confused at times).

  34. #34
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    I'll show you in sec how to change the 'shifting' you see.
    Sam I am (as well as Confused at times).

  35. #35
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    put this code in place of that which began with 'Align CONTENT in col 0 & 1 CENTERED

    Code:
    'Align CONTENT in columns 0, 1, 4, 5 and 6 CENTERED        
          Dim b As Integer, j As Integer
                With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 0 To 1
                            .Col = b
                            .CellAlignment = flexAlignCenterCenter
                        Next b
                    Next j
                End With
    
    
            With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 2 To 3
                            .Col = b
                            .CellAlignment = flexAlignLeftCenter
                        Next b
                    Next j
            End With
    
    
              With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 4 To 6
                            .Col = b
                            .CellAlignment = flexAlignCenterCenter
                        Next b
                    Next j
            End With
            
            With Grid1
                    For j = 1 To .Rows - 1
                    .Row = j
                        For b = 7 To 7
                            .Col = b
                            .CellAlignment = flexAlignLeftCenter
                        Next b
                    Next j
            End With
    Sam I am (as well as Confused at times).

  36. #36

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Also, it's not listing correctly.. Can I ask why? Shouldn't it load as the DB has it entered?
    Attached Images Attached Images  
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  37. #37

    Thread Starter
    Addicted Member Mongoose_MHS's Avatar
    Join Date
    May 2024
    Posts
    187

    Re: Ok, DB connection.. What gives?

    Quote Originally Posted by SamOscarBrown View Post
    What does the Edit form look like? Have you designed it yet?
    No real sense in using both left and right mouse clicks. Just use left click, bring up your EDIT form with all the information you want displayed in textboxes. Then have an EDIT button (as well as a CANCEL) in which you can make changes to the DB based upon how a user changes the textbox information.
    As far as an ADD functionality, that could be on the EDIT form as well.
    because is just INFO of the coin so one can just read the details of selected quarter.. like a description thing.. nothing more and the other form is the actual form you can edit the entry. Trust me I have my own reasons.. you may not like or agree with them but it's how I want it to look like and run. Just roll with me please..
    Monroe High School.. GO HORNETS!

    If I've help you, please add to my reputation level! Each reply has a "STAR" icon followed by: "Rate this post".. click that

  38. #38
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Your original query had it ordered by "Name", which, as I said, is normally a reserved name, so I changed it in the DB to coinName, and sorted on it.

    If you want it exactly as it appears in the DB, you would used the ID field...however, once you start adding and deleting quarters, you will not like the result....I think you should probably order by either coinName or Year...probably Year (WHICH I CHANGED THE FIELD NAME TO "coinYear" for the same reason I changed "NAME".


    EDIT, if you opt to sort by year (coinYear), you probably should sort by that AND coinName. Mine looks like this when I sort that way:

    Name:  coinset.jpg
Views: 131
Size:  46.2 KB
    Last edited by SamOscarBrown; Jun 6th, 2024 at 04:43 PM.

  39. #39
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    Then you code your EDIT form and your INFO forms. It is very simple to do both, but I don't know what you want them to look like.
    Sam I am (as well as Confused at times).

  40. #40
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,318

    Re: Ok, DB connection.. What gives?

    And you might consider FRAMES instead of FORMS for those 'info/edit' stuff.
    Show/Hide the frames instead of opening a new form and closing it. Does the same thing but is much simpler for your project. Just offering advice. Been doing this for decadess---I was a tester, evaluator and trainer for major military projects...I kinda have ideas of what clients (users) like to see.
    Anyway, code on!
    Sam I am (as well as Confused at times).

Page 1 of 3 123 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width