CardShark Bridge Bidder ("CSBB")
Programming Notes


Contents:

References and Components

At the start of the main code for each program, the References and Components being used are listed. In a couple of the programs, the IDE colors being used are also listed. The colors are a function of your copy of VB6, not of the source code, so your colors probably won't look like mine.

Many of the Subs have descriptions of what the Sub is for and I've tried to add
comments when the purpose of some code within a Sub may not be obvious.

The BidBaseDB.accdb Database ("DB")


 The Bids table fields are shown on the left, the BidNames table fields are shown below.

The names of the fields in the BidNames table are in the column headings of the grid above.
Both of these tables are in the database BidBase.accdb. This database was originally created in Access 1997 and in 2018 was updated to the Access 2007 format (which was an enormous pain).

The Bids table is the main database. Each record in it is used for making a bid. About 60 fields in each record are available to use to define characteristics of hands to match in order to make the entry's bid. The fields are in the same order that I want them to appear in the Bidding Editor grids in order to simplify that chore... I thought. It turns out that each of the 57 fields in the grid had to be manually entered in the SQL statement, but that's a story for another time.

For a couple of the longer-named fields, shorter names were needed for narrow grid columns, but I wanted to retain the longer, more descriptive names. For example, the RoundOfBidding only needs to be 1-character wide; likewise for SubCat_Selected. And Bid_Name would look better on a grid display without the underscore character, etc.

You would think that the field heading could be changed for display purposes in Access' Worksheet View, but if you do that, it changes the field name, not just the column heading. Instead, the shorter name must be entered on the Caption line in the Design mode.

Normally only about a half-dozen of these fields are needed to describe a hand for a bid, but different fields are often used to make the specifications for different entries, so a lot of different types of specification fields were made available.

If in the long run it turns out that some fields are rarely or never used, they can be removed, just as new ones could be added if needed. This process is simple compared to everything else in this project.

I recently read this about the 19th-century Brooklyn Bridge in New York:

    The reason it is still standing today is because the guy who designed it had no clue how strong it needed to be. He designed it to be a couple of times stronger than it needed to be to err on the safe side
Ditto for the BidBase database.

By placing bid names in their own table and linking them via their entry number to the Bids table, the size of the Bids table is greatly reduced compared to having the full name and description field in each Bid's record whether needed or not.

-----

May 2021 Update:

Changing the database is a lot of work and invites all kinds of errors. I have made changes in the programs by just not using some fields anymore, but now I need to add new fields for specifying specs for other hands.

For now, I'm not changing this file's references to the deleted fields other than to list them here: 

    ValSystem, Suit_Val, and NT_Val were hand specs upon which some entries' bids are based
      and those entries will have to be reworked. 
    AgainstID was supposed to be the name of the convention the entry's bid was being used against,
      but that was never used. It is being replace by new fields
        LHO_Specs, Pard_Specs, and RHO_Specs.
      These fields will have the ID numbers of entries in the HandData table. 
    Strength_Discl, xLen, SLen, DLen, and CLen were just disclosure fields which are not needed.

-----

Of the 4 programs which are currently part of the BidBase package, only one, the BidBase Editor, needs the bidding database in grids. Getting grids to display data the way you want them to is a lot harder than it should be, especially when data from two or more tables must be combined.

The BidBase Editor uses 5 grid controls, named Grid1(0) to Grid1(4). Each grid's data source is one of 5 data controls, Data1(0) to Data1(4). In CSBB, each data control's RecordSource is the same SQL which was created in Access.

So all you have to do is right-click a grid to bring up the menu in the bottom right of the image below, then click Retrieve fields and the grid will display all the fields specified in the query.

Image

Well, that's the theory, anyway. There's just a few, tiny little problems you might run into, starting with the SQL query.

First, it may turn out that you want the columns in a different order from which you set up the data table in Access. That's not too hard. You can just go into Access in the Design mode and move the columns around.

If this were all that was needed, your SQL query would just be

    SELECT Bids.* FROM Bids;

But in the BidBase Editor grids, we need to combine bid names (and related data) from the BidNames table with the data from the Bids table.

That's not too hard:

    SELECT BidNames.Selected,  BidNames.SubCat_Selected,
        BidNames.Bid_Name,  Bids.*
    FROM BidNames RIGHT JOIN Bids
    ON BidNames.IDnum = Bids.ConvID;

Bids.* means all the fields in the Bids table. Except that logically, the Bid Name should come after the bid and thus after all the fields which come before Bid in the Bids table. Also, the ConvID field in the Bids table is just used to look up the Bid Name in the BidNames table and we don't need those numbers displayed in the grids.

So clearly, using Bids.* is not going to work. We have to list each of the almost 60 fields in the Bids table individually. While this takes up a lot of space, it shouldn't be too difficult to list them all. We just do this:

    SELECT BidNames.Selected,
        BidNames.SubCat_Selected,
        Bids.Pct_Used,
        Bids.RoundOfBidding,
        Bids.Player, Prior_Bids,
        Bids.Bid_Order_Number,
        Bids.Bid,
        BidNames.Bid_Name
        Bids.HCP
        Bids.Dist_Pts
        ...and the rest of the Bids fields.
    FROM BidNames RIGHT JOIN Bids
    ON BidNames.IDNum = Bids.AgainstID;

The RIGHT JOIN code says to enter the specified fields from the BidNames table.Bid_Name data into the grid when the IDNum field in the BidNames table is the same as the ConvID field in the Bids table.

You don't have to put the table name, such as Bids, in front of each field which is uniquely named, but it's considered good practice. For example, RoundOfBidding had been named Round which is a reserved word in Access (for what should be obvious reasons) and caused problems since I was being lazy at the time and not putting Bids. in front of the fields.

This SQL RecordSource is okay as far as it goes, but the grids won't have the HandData specs which the numbers in the LHO_Specs, Pard_Specs and RHO_Specs fields (if any) link to in the HandData table.

The HandData would take up too much space in the grid, so that data is only loaded when an entry is displayed in the Input Boxes.

To test the SQL code (if you make changes to the databases), load Access (2007 or later), double-click on GridQuery, click on View near the upper left corner and then click SQL View.

Make your changes or replace what's there with your new SQL, then click View - DataSheet View

VB6 and Access Error Messages (or just errors) and Fixes

The truth is that while I have used database programs like, forever, I mainly know enough to be dangerous. (I was going to say "...enough to get by," but my recent experience refutes that.) Here's some of the problems I ran across:

  • Enter Parameter Value: ...

    • When I click on Datasheet View, a box pops up saying: Enter Parameter Value: UseAgainst.ID.  I've no idea why that is asked, so I just click OK. It tries me a couple of more times and then it just gives up and shows me the table.

    It shows the fields I defined in the column headings, but no data in the table. Don't know why. No error messages. (This will be explored more in a bit.)


  • Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

    • I try the Design View and it says Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

    Well, the SQL started with SELECT, so clearly the problem goes beyond the simple hint it's giving me. Somewhere in my list of over 60 fields across 2 tables (and 1 alias table) or in my FROM or JOIN statements, there is some misspelling or punctuation error or other offense, and all I have to go on is a clearly off-target error message.

    The first rule of cracking an error code is to simplify, so I take out all the field names after Against.Bid_Name but leave the FROM and JOIN sections in place. It's not buying.

    I take out Against.Bid_Name and simplify the JOIN command to BidNames RIGHT JOIN Bids... No go.

    Eventually I get back to the most basic SQL before it starts working. In the course of simplifying, I had made and fixed more typos so it is hard to know what caused the original problem(s). For example, when I took out the naming of individual fields and put in Bids.* and BidNames.*, I missed putting in the periods. (In my defense, the type size in the SQL View is super tiny, and unlike the Worksheet View, you can't enlarge the print.)

    Next I add back specs a few at a time, but still making typos --- BidNames.BidName instead of BidNames.Bid_Name, but instead of giving me a meaningful error message (or any error message at all), Access just changes BidNames.BidName to BidNames.BidName AS Expr1 (??).

    When it keeps working after I fix the typos, I mass cut-and-paste the rest of the individual field names and it works. It shows me the Design view and the Datasheet view with no error messages, no changed fields or other shenanigans. All is well!

    I should now be able to paste this into the RecordSource fields of the data controls and get it to work.

    Hopefully, you are not as naive as I am. You didn't really think it was going to work, right? In your heart of hearts, you were expecting another vague error message, right?


  • No value given for one or more required parameters.

    • While the SQL appeared to work in Access but not in VB6, it was actually one of those changes-without-warning so it was NOT working in Access, it just wasn't giving me an actual error message.

    In lieu of an error message, Access had asked me to provide data for a Date field before showing me the Datasheet, so I looked at that field name in the SQL and saw that it was the one field in the mass of fields I copied at the end which did not have "Bids." in front of it. Access just changed it from Date to Date AS Expr1 without so much as a "By your leave."

    After fixing it to read Bids.Date, I get an error message going to the Datasheet and remember that previously I had changed the field name to DateOfEntry to avoid using a reserved word. I fix that and everything seems to be working. I get no prompts or error messages when going to the Datasheet and Design views.

    The lesson here is that if you enter an SQL in Access' SQL View and switch to the Datasheet View and it prompts you to enter data for some field name, there is something wrong with the field name. (I think.  I've read enough "expert opinions" online to realize that nobody really knows for sure.)

    Now that everything has gone smoothly, I return to the SQL View, use Ctrl-C to copy the SQL, go to VB6 and paste the SQL into the data control's RecordSource, right-click on the associated grid and click Retrieve Fields. Same error as shown above.


    Second time

    Image

    Nov.10, 2019:

    Hadn't gotten any strange error messages in a long time and this one came up every time I tried to run the Editor.

    One thing I finally tried was creating a new directory and copying all the Editor files into it. It ran without error.

    I kept adding more files from the Bridge directory into the temporary directory and it kept working.

    Then it happened again. I finally narrowed the cause down to the window where you select the fields to sort the grids on.

    I don't remember the fields that were listed, but I think that when I changed them to the defaults, it worked.


  • Fields don't load into grid from data control:

    • A strange quirk is that if you try the grid again, it gives no error but does not load the fields. The same is true even if you go to a different data control and grid - no more error messages, no loading fields.

    The only time I've gotten no retrieving of fields into a grid is when the same field was in the SQL twice. I go back to Access and check the SQL and find a field from the BidNames table which was in the SQL twice. I take it out and find one that should have been in that column and put it in place and the grids retrieve the fields.

    Before leaving this litany of disasters, here's another thing to watch out for: In the course of trying to isolate some of the problems I was having, I used a BidBase.accdb data file from one of my backup directories.

    Later, having forgotten about that, I made changes to some of the field names in the database which was in my working directory and made corresponding changes to program code, but, obviously, the program was not working right because it was still using the old backup database, not the one I had changed.

    I only stumbled across this when I had right-clicked on a data control, clicked on ADODC Properties, looked at Use Connection String, and saw that it was using the backup database. That's why it's good to keep a running record of changes you are making in case you have to undo them.

    In that same box, click on the RecordSource tab. To enter an SQL string, the first box should show 1-adCmd Text and the Command Text (SQL) box is where you should put the SQL string starting with "SELECT" as above.


  • The RecordSource field in the VB6 Properties windows truncates SQL.

    • If you copy (Ctrl-C) the SQL (see previous paragraph) and try to paste it into the RecordSource line in the VB6 Properties windows, VB6 will truncate the SQL at the first carriage return, meaning you could end up with nothing but "SELECT" in the RecordSource because while the Command Text box in the ADODC Properties box will accept carriage returns, the RecordSource line in the VB6 Properties window will not. Same data control, same code, different results.  #(*$&($!

    Another problem I ran into: If you put a semicolon at the end of, say, SELECT Bids.* FROM Bids; and do not take it out when you add more to the SQL after FROM Bids, you will get some off-topic error message from Microsoft in their continuing game of Find The Real Error.

    The easiest way to get identical data controls is to get the first one working right, then on the BidBase Editor form in VB6, click on the data control, press Ctrl-C to copy it and then Ctrl-V to add it to the form. If you have made a test project like I did, you can even copy a control from one project to another and it will copy it with all its attributes and source code. If you copy a frame with other elements in it or if you Ctrl-Click on multiple controls, VB6 will copy the whole thing -- different controls, code and all.

    If you want to change the database structure such as by adding or removing fields, hopefully you have backed up not only the original database file (BidBase.accdb) but also the VB6 code for the entire BidBase project, but even so, you should make another copy just before you make the changes to insure that you have a good backup with all your latest changes.

    Also, each program in the BidBase system has its own .CFG configuration file. The BidBase Editor's file lists all the fields for the grids showing whether the field is visible (-1) or not (0) in the top grid left and right and the bottom grid left and right, the caption for the field's column, the name of the data field, and the column width.

    The configuration file has captions (lines starting with "==") describing what follows. The Editor program has a Sub LoadConfig which loads program setup data from the file. While the code checks to see if input matches the captions, it is possible that a file could get messed up bad enough that the code would not be able to handle it. In that case, you would need to single-step through that Sub or delete the .CFG file and the program will load one with "new" at the end of the file name, such as BBEditor new.cfg.

    This also means that if you make a change to the database design, you not only need to change all the programs and the programs' configuration files, but all the "new" configuration files.


  • Unrecognized database format

    • The BidBase Editor program is working as far as displaying data in the grids, but when it tries to access the bidding module (BidBaseDB.vbp), that module gives an "Unrecognized database format" error when trying to access the database via the following line in the Sub InitializeBidBaseDLL:

      Set db_BidBase = ws_BidBase.OpenDatabase("BidBase.accdb")

    This same line of code appears in the BidBase Editor program in Sub Initialize Database and works without error.

    This would seem to indicate that the problem is not in the database nor the code. That leaves the Project - References. Here they are:

      Project - ReferencesEditorBidder
      Microsoft Data Environment Instance 1.0YY
      OLE Automation-Y
      Microsoft Office 12.0 Access database engine Object LibraryY*
      Microsoft DAO 3.6 Object Library-Y
      Microsoft OLE DB Simple Provider 1.5 LibraryYY
      Microsoft Data Binding Collection VB 6.0YY
      Microsoft ActiveX Data Objects 6.1YY

      * When entering References for the Bidder, the only line added was the Microsoft Office line. When clicking Ok, VB6 gives an error message saying Name conflicts with existing module, project, or object library. Upon reopening the References window, VB6 has unchecked the Office line and added the other References shown below it in the chart above.

      Apparently, while some References can be the same in both Projects, the MS Office line cannot be and VB6 "solves" the problem by adding the other references. Remember this statement and the facts leading up to it because soon, it will all be turned on its ear.

    Since I don't know why VB6 substituted the other References, I don't know if they have anything to do with the original error message: "Unrecognized database format." I've gotten this error before and Googled the heck out of it to no avail.

    When people leave pleas for help with this error on various programming message boards, the most popular response is: "Your database is corrupted. Use Access to fix it." The most popular response to that is "I've already done that and it didn't help." Another response, which is one I've made, is that the same database works fine with another program.

    To simplify things, I load the Bidding Practice project which does not access the database itself. It relies completely on the Bididng module, so it does not have any database-related references being loaded other than BidBaseDB.vbp which is the Bidding module.

    I click on the bidder and then on Project - References and select the Microsoft Office Access database engine Object Library. This time when the project runs, it does not get an error when it gets to the line that caused the Unrecognized Database Format problem.

    It does, however, give the error caused by the bidder code using a field name which I had changed. After changing that, the Bidding Practice program works fine. However, I solved the Unrecognized Database problem by taking all the data References out of the Practice program (which didn't need them anyway) so that I could add the MS Office line to the Bidder's References.

    The BidBase Editor needs the MS Office Reference too, so it would seem that when I go back to that program and both projects (the Editor and BidBaseDB) have the MS Office line, the same error should occur as when I tried to add the MS Office line to BidBaseDB while in the BidBase Editor project group, yet it DOESN'T.

    The Editor program is also working perfectly! It makes no sense, but I'm not going to argue with MS about it. (Even if I could.)


  • Data member not found.

    • Ok. So not "perfectly". It turns out that while .MDB database software would let you specify fields using either a period or exclamation mark before the field name, .ACCDB databases insist on having only the "!".

    And of course if you have "." then MS gives you the oh-so-helpful error message of Data member not found. And just to keep things confusing, you must still use the "." period when passing data to and from the bidding module.

    I've also had a number of problems related to trying to do the right thing by changing a field named "Round" to "RoundOfBidding" and one named "Date" to "DateEntered". This was done because the original names were reserved words in Access, but changing the names has caused a lot more trouble than the original names ever did.

    Some of the programs refer to those fields by name and some other tables had to have their field names redone to match. A lot of effort was needed to get the configuration file for the Bidding Editor to work with the database changes.

    One problem that I haven't figured out yet is in the Bidding Editor, the database tables have to be accessed in a lot of different places and after the changes, accessing them doesn't work the way it did before. Previously, I intialized the tables once but now they have to be re-initialized every time I access one.


  • Row Cannot Be Located for Updating

    • Another problem is that when a change is made to a row in the grids in the Bidding Editor, VB6 and/or Windows gives the error message: Row Cannot Be Located for Updating. As usual, Google turned up about a gazillion hits on people looking for help on this "error" but even when a few people got answers that worked for them, it didn't help in this situation.

    Eventually I give up and in VB6, right-click on the grids to bring up the Properties window, and uncheck AllowUpdate. Bill Gates probably had a good laugh because doing this does not actually prevent anyone from entering new data, but when you change rows, you get the error You must enter a value in the '[column name]' field.

    Doing a Ctrl-Break to get into Debug mode does not help because it is not a VB6 message, so there is nothing you can do about the error message. So I add Cancel = -1 and Exit Sub to the Grid1_BeforeColUpdate sub and that actually prevents any changes being made to the grid.

    This is probably for the best because the Input Boxes should be used to add or change entries anyway because they have a lot of tests and controls built in to help prevent making entries with invalid data or which would otherwise screw up the database.

    BidBaseDB.DLL Bidding Module

    The following programs make up the complete CardShark BidBase Bidding package:

    • BidBase Bidding Practice
    • BidBase Editor
    • BidBase Bidding Simulator
    • BidBase Hand Evaluation System creator/editor
    • BidBase File Viewer/Creator.

    The first three use the same CSBB BidBaseDB.DLL file to make bids using the following:

      Inputs from client:

      1. Path and file name of database.
      2. A bridge hand (e.g.: AK953-K53-KQ42-T going from Spades to Clubs.)
      3. Prior calls, if any, starting with the first non-passing bid.
      4. Bid to show analysis for, if any.
      5. Vulnerability
      6. Scoring method ("MPs" or "IMPs")
      7. Test entry ID# (for adding/editing entries)
      8. Cancel search
      9. "Get Disclosures" flag
      10. Trump suit - For Ace Asking entries and any others with such a need.

      Return to client:

      1. Bid found, if any.
      2. ID code for entry found.
      3. Disclosures for bid.
      4. Error message(s), if any
      5. Error code(s), if any
      6. Hand data (for editor; see docs)

    If the bidding module needs to know the trump suit, such as for Roman Key Card Blackwood, it will ask if the trump suit is not passed to it. The trump suit for Test Hands entered for RKCB bid entries can be indicated by putting <C>, <D>, <H>, or <S> in the Source input box to the right of the Test Hand. Ideally, some day we will be able to determine the trump suit based on the prior bids and pass that along to the bidding module.

    The BidBaseDB.vbp project file is made up of the Bidder class (BidBaseDB_Bidder.cls) and the Analysis form (frmBidBaseAnalysis.frm) which is used to show the user analysis of bidding records used to select a bid if the user selects that option in the Bidding Practice program.

    The Project References and Components needed by the bidding module were listed above and discussed at length.

    In the Class source code, select "(General)" and look through the right drop down list to find Subs which say [Property Get] and [Property Let] after their names. The Get are routines for getting data from the client app and the Let subs are for passing data back to the client app.

    The Class has Private WithEvents frm As frmBidBaseAnalysis in its Declarations section. This lets the Bidder Class get data from the form, though I'm using a simpler method, so this isn't being used at present. The frmBidBaseAnalysis form has the corresponding Event NotifyClients(ByVal int_Notify As Integer) in its Declarations section.

    The form has two buttons, Cancel and Continue. When Cancel is clicked, it makes the button invisible and this lets the Bidder Class know that we're done, which is a lot simpler than the WithEvents just mentioned (but maybe I'm just not using WithEvents correctly). 

        Private Sub cmd_Cancel_Click()
    cmd_Cancel.Visible = False
End Sub


Private Sub cmd_Continue_Click()
    Open "BidAnalysis.cfg" For Output As #1
    Print #1, Me.Left, Me.Top
    Close #1
    
    int_Notify = 2
    RaiseEvent NotifyClients(int_Notify)
    DoEvents
    Me.Hide
End Sub

    The various BidBase programs all need to use the BidBaseDB Bidder project. After loading a BidBase program into VB6, click File and Add Project to also load the Bidder. The Project Explorer will show the original project name with its files indented below it, and the Bidder project name with its files below it.

    Click on the original project name, then click on Project and References. You should see BidBaseDB near the start of the list. If not, click the Browse button and look in the folder you made for BidBase and find BidBaseDB.VBP. Before closing References, make sure BidBaseDB.VBP has a check mark by it.

    If have loaded a BB program and BidBaseDB.VBP into VB6 and References won't show the BidBaseDB.VBP file and when you Browse, the window won't show any .VBP files, make sure you have clicked on the original project name in the Project Explorer and not the BidBaseDB name.

    While multiple instances of VB6 can load different programs/projects at the same time, only one instance of any one project can be loaded at the same time, including the BidBaseDB Bidder project.

    To load different BidBase programs which use the Bidder, you can load BidBaseDB.VBP into one, but you have to load the compiled version, BidBaseDB.DLL, into the others. Search for it the same way.

    BidBase Bidding Practice Program:

    Anyone interested in writing any kind of card game in Windows may want to look at the BidBase Bidding Practice Dealer.bas file. It has all the code needed to shuffle, deal, and display cards. It also has routines for displaying a full deck of cards face up so that the user can click on cards to give to each hand. Add this module to your program and you can focus on the game strategy and let this module do the card handling.

    Before reading this section, you may want to read the Practice program's documentation file.

    An old card deck DLL by the name of QCards is used for the card faces and backs. All the card shuffling, dealing, and displaying routines have been put in one class module.

    The cards for each hand are drawn into a single Picture control for each hand [p_Hands(1-4)]

    The cards for the deck which the user can click on to deal cards to specific hands are drawn onto individual Picture controls [Cards(1-52)] to make them easier to move around.

    Use of The Database(s)
    By Bridge Playing Programs:

    Any bridge program can easily add BidBase. A bridge program should first make a call to the database, passing it the current hand, any prior bids, vulnerability, etc. BidBase will pass back a bid and any disclosures for that bid. Optionally, BidBase will let the user see why any or all bids not chosen were rejected.

    The VB6 source code for a sample program is included with the BidBase files. It shows the parameters to be set in calling for a bid and the information returned by the bidding module.

    The database contains very few entries which specify Passing. When the database tells a program that there is no bid in it for the current hand's specs, the program can use its own algorithms or DDA to try to come up with a bid. However, if no bid is returned during the first round of bidding, the result should almost always be a Pass. In later rounds, not as many hand types will be covered in BidBase, so when a no-call is returned, the client program's routines for calculating a call will come more into play.

    If a user finds that in certain situations, a program is generating bids from its own algorithms or from DDA when Pass is the preferred call, then the user can add an entry to force the program to Pass in a specific situation.

    The first three columns in the Editor program are for selecting (1) the system used, (2) conventions used, and (3) activating and deactivating any particular bid entry, normally when there is more than one entry for the same bidding criteria.

    When one bidding entry for a system, such as Precision, is activated, the program activates all the other entries which have the system name in them. The same is true for Conventions. When you select a system or convention, you should deselect (by entering a zero) entries for whatever other systems or conflicting conventions were previously selected. Again, once you deselect one entry, all the other entries with the same Bid Name will also be deactivated.

    The user should Any bids or conventions not selected by the player for use are made inactive (by entering 0 in the Pct field) and ignored. If the database has multiple active entries for the same criteria. BidBase will randomly select one based on the Pct Used field for the entry.

    All the different bidding systems and conventions are stored in a single database. Most bidding systems, such as Standard American and Precision, have at least some bids in common. By storing all the systems and bids in one database, it assures that any changes to commonly used bids are effective for all systems.

    For example, say that Precision and Standard American systems each use identical methods for bidding 1N and the follow-up bids. If the two systems were in different databases, then the NT bids would have to be repeated in each database. If an improvement or correction were made to the NT bids in one system, it would have to be duplicated in all other system databases. Trying to keep different databases for a lot of different system identically updated would be a nightmare.

    Modifying BidBase's Structure

    BidBase's structure was created in Microsoft Access.97, and that is the easiest place to change the structure. I'm currently using Access 2007 which wants to update the data file (BidBase.mdb) every time it's loaded, but I haven't wanted to risk messing things up by converting the file just yet.

    The BidBase Editor uses an array of 5 dbGrids, named Grid1(0-4), to display up to four different parts of the database. The 3rd grid [dbGrid1(2)] is the Garbage Grid.

    Updating the Garbage Grid design:

    If you have changed the field structure (adding or deleting fields) in Access for the Bids table, close the Design window, click File and Export to export the new structure to the Garbage table, overwriting the old one.

    This not only copies the new data structure, but all the data too. So open the Garbage table for edit, press Ctrl-A to select all entries, press Delete to delete all the entries.

    Close the edit window and open the Garbage design window. Change the ID field from Autonumber to just plain Number. Turn off the Primary Key tag on it. Close the Design window.

    Reopen the Design window, go to the end of the list and add GarbageID as an Autonumber field. Make it the Primary Key. Close.

    The Config file:

    The Editor.cfg file contains, among other things, a list of all the grid columns with their visibility flags, column headings, and data field sources, and column widths. Add the new fields and/or delete the old ones. Closer to the front of the file is the number of columns. Update it, but remember that the columns start with 0, so make this number 1 less than the actual number.

    You will need to copy the changes to the BBEditor-Starting.cfg file, but I wait until I'm sure that the changes are working so that I can look back at the file if there are any problems.

    Updating the Grids:

    I tried not specifying any columns in the grids at design time and adding the columns, captions, and widths at run time, but when a refresh of the Data Control was done, the grids reverted back to the default column widths. So I went back to setting them up at design time, which is a bit of a pain.

    For starters, if you click the body of a grid and another grid is underneath it, the bottom grid is selected instead of the top one, so if you want to modify the grid, right-click and select Edit first.

    Load BidBase Editor into VB6 and open the editor window. Delete Grid1(1-4), just leaving Grid1(0). With Grid1(0) selected, press Ctrl-J to bring it to the top.

    Right-click on the grid and click Edit. Right-click on any fields you deleted and Cut them. If you added a new data field, go to the column before which you want the new field to appear, click on it, then right-click and click Insert.

    If you want to move any columns, click on the column, right-click on it and click Copy. Click the column before which you want to move the other column and click Paste, then go back and delete the original column.

    When you are done massaging the columns, right-click on the grid and click Properties. Click the Columns tab. Go down the list to the columns you added, if any, and select the data field to go in it. Change the caption if you wish.

    When you are done, right-click the grid, click Properties, click Columns, then go down the list of columns and compare them to the configuration file to make sure the configuration file matches the grid layout.

    When everything is okay, copy Grid1(0) four times, recreating Grid1(1-4) with all the changes you made.

    In the Properties window, change the DataSource for each grid (1-4) from Data1(0) to the same index number as the grid (1-4) and change Visible on grids 1-4 to False.

    Now we need to move the grids below the other controls so that the other controls will show up. We could do this at run-time, but then you couldn't see the controls during design-time, so let's do it now.

    Click the Heading bar of grid1(4). (Remember that if you click the body of the grid, any grid under it will be selected instead.) Then press Ctrl-K to move it to the bottom. Repeat for grids 3, 2, 1 and 0 and they will be in the correct order.

    Updating the source code:

    In the source code, add/delete fields in the Save routines as needed. See the mSave routine and the routine where an entry is copied to the Garbage table when the entry has been modified or deleted.

    If you've done everything right, then the BidBase Editor should accept the changes. If it comes up looking screwy, put a Stop in the LoadConfig routine and single-step the loading of the column specs. (Go to the Immediate window and enter Me.Show to see the form.)

    I back up my files every night and often have to go back to them when the working files get screwed up one way or another. You will certainly want to back them up before starting changes like these.

    The BidBase Editor Program

    Feel free to skip the rest of this document unless you are really interested in the details of how to Move or Resize controls.

    Moving a control with a mouse

    The Tutorial is an important part of the Editor because (1) the editor can look overwhelming and (2) people won't study the docs Image and (3) the tutorial is a better teacher because it is interactive.

    The problem with it is that if the user wants to see something hidden behind the Tutor box, he needs to be able to click-and-drag the box out of the way.

    One way to do this is to put the tutorial and other pop-up controls into individual forms. This is the simplest way to have a control which you can easily move and resize; however, it can make the code much more complex because the tutorial interacts with the other Editor controls quite a bit and also shares a number of variables with the Editor form.

    Another way to allow moving controls is to use DragDrop; however, DragDrop is not really meant for this purpose. It is designed for clicking and dragging controls or data to another control and dropping it into that control.

    So getting DragDrop to work just for moving controls to specific positions brought up a number of problems. Googling led to people asking questions about dragging controls with a mouse, but none which addressed the problems that came up here.

    Without going into all the gory details of figuring this out, here are the basic Subs which are involved in dragging the Tutorial box (and other pop-up controls). Now that it is working, the methods don't seem very complicated, but it took a lot of work to solve all the problems.

      Sub frm_Tutorial_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
        If Button = vbLeftButton Then
          If Not bln_Dragging Then
            bln_Dragging = True
            frm_Tutorial.Drag vbBeginDrag
            sng_DragOffsetX = X
            sng_DragOffsetY = Y
          End If
        End If
      End Sub

    The above MouseDown event starts the drag-drop. If the MouseDown event is used for any other purpose, you will need to figure some way to distinguish between the two purposes in the event code. One way is to put the control inside a frame control. Image

    Not only does this eliminate conflicts in frm_Tutorial's MouseDown event, but it makes the tutorial box look more like a regular window for which the title bar is used for clicking and dragging.

    In VB6's design mode, bring up the Properties for the frame, set BorderStyle to None, make the frame's BackColor the Active Title Bar color (which in this case is red), and change the MousePointer to Size N-S. Then put the MouseDown code in the frame's MouseDown event rather than in frm_Tutorial's.

    On the right side of the red "title bar", put a small label with "X" in it. Another label covers the rest of the title bar.

    The procedure just described will work with any controls, but if the control being dragged is a Frame, an alternative is to leave room at the top of the Frame in which to put the labels for the Title Bar and the X. Set their colors as shown and in Properties, set the Frame's Border to None.

    If you use another form or just a label at the top of the existing one as the control for the mouse to use to click-and-drag, a change must be made to the code above. Instead of frm_Tutorial's MouseDown event, you will be using the label's MouseDown, but frm_Tutorial.Drag vbBeginDrag remains the same because you are dragging the form, not the label.

    The drag-drop ends when the mouse button is released. At that time, whichever control (or form) the mouse pointer is over has its DragDrop event executed. The code in that event determines what happens next.

    The pop-up windows/controls which appear in the BidBase Editor can all be moved as needed to see the data behind them. Normally, the controls will be situated over a grid and moved to some other location on a grid. In that case, the grid's DragDrop event will be executed.

    Since the grids are all part of an array, the same DragDrop sub is executed for all of them with just the Index parameter changing to indicate which grid is being used.

      Sub Grid1_DragDrop(Index As Integer, Source As Control, X As Single, Y As Single)

        With Source
          .Drag vbEndDrag
          .Move Grid1(Index).Left + X - sng_DragOffsetX, _
             Grid1(Index).Top + Y - sng_DragOffsetY
          .ZOrder 0 ' You usually want the dragged control to be on top of others.
        End With
        bln_Dragging = False
        sng_DragOffsetX = 0
        sng_DragOffsetY = 0
      End Sub

    The control being moved does not actually move while it is being dragged. Instead, a "shadow frame" appears so that you can see where the control will appear when the mouse button is released. This means that the dragged control's .Left and .Top values do not change while being dragged and must be manually Moved after the dragging is completed, which is when the DragDrop code is executed:

    • Grid1(Index).Left and .Top - When the mouse button is released while the mouse pointer if over a control (even over the control being dragged), that control's DragDrop event is executed.
    • Source has the name of the control being dragged. This allows every control dragged and dropped on one of the grids to be controlled by this same code. The code gets the Source name from the control's MouseDown event
    • X and Y define the location of the mouse pointer when the mouse button is released relative to the left and top of the target control, which is Grid1 in this example
    • sng_DragOffsetX and ...Y specify the distance between the mouse pointer and the left and top of the Source control when you first clicked on the control to move it, as can be seen in the example MouseDown event code above.

    Problem: I recently replacced one of my 3 monitors which have 1920x1080 resolution with a monitor with a resolution of 2560x1440. When I drag the Find Bid For Hand frame, which is the one I drag most often, frame appears where the cursor has move to when I release the mouse button, but during the dragging the shadow frame appears in the 1920x1080 area of the monitor. I've Googled and found nothing about this issue nor about the shadow frame in general.

    I also discovered that this dragging code, which I wrote a couple of years ago or more, doesn't use the DragOver event. Again, Googling hasn't turned up any examples of the use of that event.

    If the user drags down into the Input Boxes area, then every control in that area (which is a LOT) would need to have the following type of code added to them, so it's probably best to limit the down-drag to the top of that area.

    If the user releases the mouse button when the pointer is in L_Tutorial (the control which displays the text of the tutorial), an additional adjustment must be made for the margins of L_Tutorial as shown below:

      Sub L_Tutorial_DragDrop(Source As Control, X As Single, Y As Single)

        With Source
          .Drag vbEndDrag
          .Move frm_Tutorial.Left + X - sng_DragOffsetX + L_Tutorial.Left, _
            frm_Tutorial.Top + Y - sng_DragOffsetY + L_Tutorial.Top
        End With
        bln_Dragging = False
      End Sub

    If the mouse pointer were on frm_Tutorial itself, then X and Y would be the location of the mouse pointer relative to frm_Tutorial's Left and Top, but when the mouse pointer is on L_Tutorial, then the X and Y values are relative to L_Tutorial's Left and Top which, in turn, are relative to frm_Tutorial's Left and Top.

    Enlarging the Tutor box during run-time:

    Image Another potential problem is if the size of the print is somehow different on the user's computer.

    Frankly, I'm not sure if that is possible in this case or not, but this is a relatively simple thing to assure against.

    The white bar at the bottom of the screen clip is a picture box control named pTutorDragBar. It was left white in the picture for visibility in this description.

    In actual use, its color is set to the same as the frame it is in (frm_Tutorial) so that it's not visible. In its Properties window, set the MousePointer to 7 - Size N-S so that the user can tell that this is the way to resize the control.

    Here is the code:

      Sub pTutorDragBar_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
        lng_Top = pTutorDragBar.Top
        lng_Y = Y
        bln_Dragging = True
      End Sub

      Sub pTutorDragBar_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

        Static Moving As Boolean
        If bln_Dragging Then
          Moving = Not Moving
          If Moving Then
            pTutorDragBar.Top = lng_Top + Y - lng_Y
            L_Tutorial.Height = L_Tutorial.Height + Y - lng_Y
            ResizeTutorial
          Else
            lng_Top = pTutorDragBar.Top
          End If
        End If
      End Sub

      Sub pTutorDragBar_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

        bln_Dragging = False
      End Sub

    Sub ResizeTutorial is called with each movement to show the changes to the frm_Tutorial size and layout so that the user knows when to stop. Only resizing the length is allowed since that should be sufficient and that keeps the code a little simpler.

    The same type of grab bar and code is used in the BidBase Editor to adjust the relative sizes of the first two grids and in the BidBase File Viewer program between its two windows.

    Refreshing The Grid

    In theory, a Data1(g).Refresh (maybe followed by Grid1(g).Refresh (where g is the grid number) should get a grid to be updated, but it's not currently working. I've Googled, and I've tried a lot of different things.

    I never used to have the problem and have no idea why it stopped updating.

    The only thing which (usually) works is reloading Data1(g). Since b_Show_Click does that, I just call that Sub, but even that doesn't always work.

    Sometimes I'll save a new entry from the Input Boxes and nothing shows up in the grid, so I think the save didn't take and I'll click the Save button again, only to have the entry show up twice and then I have to delete one of them. If it showed up on the 2nd click, why didn't it show up on the 1st click? Don't know.

    Creating Practice Files:

    A long-term goal for the practice program has been to let the user choose a convention to practice rather than just going through random deals. Many conventions come up only rarely which can make it hard to remember the bids when the need does arise. Practicing such conventions could be done in an hour or less a week.

    One way to do that would be to list the conventions in a menu and have a bunch of deals created for the purpose of practicing the selected convention. At least one bridge program works this way.

    The problems with this approach are (1) deals which are designed just for that purpose do not provide the variety of realism of randomly generated files and (2) only the conventions which are hard-coded into the program are supported.

    The method adopted for BidBase is to create files for conventions in which the database is searched for deals which match the auction specifications for a convention. Obviously, this does away with the disadvantages mentioned above.

    The first step was to provide a means for entering the auctions to search for. The combo list box shown above was actually added for that purpose and is used for DDA when not needed for finding deals.

    Example:

    These are all the possible starting bids for New Minor Forcing. Because it can take minutes (or longer) between deals to find the next matching deal, an alternative is offered for the user to create the specified auctions and run a search during which the deal number for each matching file is saved in a file for the convention.

    Each stored convention has two files. The first gives the convention name followed by the specified auctions, just as they appear above.

    The second file contains the deal number and dealer number (S=1) for each matching deal. The first test run came up with 120 deals out of 10,000 deals checked or 1.2%, illustrating why it is better to find the matching deals in advance of using them.

    The reason for saving the dealer number is that a specified auction could start with any player as dealer, so we need to know who to make dealer when displaying the deal.

    On the other hand, it makes more sense to always have a specified auction end with East so that the user (South) is the next to bid.

    At first only the bidding with South as declarer was used for testing deals, but the numbers between matching files were even huger. So then a somewhat complex method was used to start with South, testing all the auctions specified, and if no matches were found, move to West as declarer, then North, and then East. This quadruples the chances of finding a matching auction in each deal. (Maybe... Math isn't my strong point.)

    The reason for having two files is to allow making the deal number list a random access file to make it easier when a user wants to stop after a certain deal and next time start with that deal.

    Each record in the file is 6 bytes - 4 for the deal number stored in n long integer and 2 for the dealer number stored as an integer.

    The file record in the Deals file contains the deal number in the long integer field and the record number in the 2-byte field. The two fields should point to the same deal, but it gives us something to check against.

    Files are given names indicating the convention, such as "New Minor Forcing", "Drury", "Bergen Raises", etc.

    For more information about how the practice system works, see the BB Practice documentation.