User Panel
Posted: 11/13/2011 4:10:17 PM EDT
Minor bug fix: gun-1.3.2
This fixes the Company Name showing up as "0" in the ATF reports. Please see the instructions for how to upgrade! ==========1.3========== Feature fix: gun-1.3 This release fixes the ATF printout reports to include the acquisition and disposition company names. Thanks, kingjamez, for pointing this out! Be sure to follow the upgrade instructions, and let me know if you have any issues! ==========1.2========== Feature fix release: gun-1.2 This release fixes the firearm "Action" and firearm "Type" boxes to be able to accept and list for future use new entries. Thanks, bgeer! As always, things won't work without the instructions. Rather than adding specific entries to show up by default, I chose to do it this way for make upgrading from previous 1.x versions easier. The only thing you should have to do is overwrite the old gun.odb file with the new one from the gun-1.2.zip archive. Please first backup your data using the BAK form "backup now" option, just to be safe! From now on, older stuff will be hidden in "spoiler" tags:Click To View Spoiler ==========1.1========== Bug fix release: gun-1.1 As always, things won't work without the instructions. This release fixes a bug in which changes were prevented from being saved because of a constraint placed on the ANT and CR columns in GUN_LOG. If you're using version 1.0, then it's probably just easier if you follow these simple instructions on Page 2 to achieve the same bug fix. ==========1.0========== Well, this is it! First, the instructions. gun-1.0 It's exactly the same as RC6, so if you've downloaded that, then you're good to go. ETA: If you prefer a hard copy bound book, here are a portrait (PDF) and landscape (PDF) template I made. ==========1.0 RC6========== OK. Update. RC6. I wouldn't have done this, but I started a project page for this over at Sourceforge: https://sourceforge.net/projects/gun/ The download link is at the project page, linked above. This is a minor update. Just has the "open image" button added. To restore a backup from an earlier version, extract the three files from your gun-*.tar.gz backup file, and overwrite the files by the same name in the gun folder with the files from your backup. ==========1.0 RC3========== This is it! RC3 is here! Unless someone points out or I find some major flaw, RC3 should be the first release of 1.0. gun-1.0_RC3.zip Brief rundown of stuff: 1 - See the instructions in order to make it work. 2 - This database should be ATF-compliant for use as an electronic bound book. 3 - Is currently and will remain free. 4 - Cross-platform via LibreOffice (OpenOffice, too, but you have to install the Report Builder manually). 5 - In addition to the ATF reports (use the ATF_REPORT form), I've added a report to print all your guns. 6 - Automatic, periodic backups. You'll have to change the default setting in the BAK form. I use FreeOTFE to save encrypted backups to the cloud (in my case, DropBox). Backups are only made if a change is made to data in the GUN form, so you can also make manual backups using the BAK form. ==========1.0 RC2========== RC2 is here! gun-1.0_RC2.zip One last major feature update... The BAK form can be opened to change backup settings. Backups are only made if you're making changes to data in the GUN form. You can also hit the "Backup Now" button to make a backup. Backups are saved in *.tar.gz archives, which can be opened with 7-zip. You'll definitely have to change the path to the backup folder. I think it will default to your home folder, but I'm not sure. Sort of a minor update: Guns marked as "Personal" will not be logged to GUN_LOG and will not show up in the ATF Printout or ATF Spreadsheet reports. Enable macros (see the instructions) if you want all the features to work.You'd also better take a look at the instructions if you want it to work at all. I'd also like to remind you that this database is and will remain free. IMPORTANT: If things weren't working for you earlier, this might be the reason why. The instructions have been modified to reflect the fact that you need to register the database. This does NOT mean information is sent anywhere. This just means that the LibreOffice program keeps a local list/registry of databases, and in order for all the features of this database to work properly, it needs to be on this list. Basically, go to Tools > Options > LibreOffice Base > Databases > New > Browse > Open gun.odb and save the settings. The registered name must be gun. All the bogus/testing data has been deleted. The requisite screen shot: ==========1.0 Release Candidate 1========== First release candidate! gun-1.0-RC1.zip I've greatly simplified the process for printing ATF reports per regulation. Just go to the ATF_REPORT form, enter the start date and end date of the date range you need to print, and click the button! I've left the bogus testing data in there for now, but will remove it for the next release candidate (RC2). I hope for/expect RC3 to be the first *actual* release. As always, it won't work without the instructions. Notes on ATF compliance. I've polished up the different "categories" that can be associated with each gun to include Events and Maintenance events. Please check it out and let me know what you think! Also, if you frequent any other gun forums, please let others know this is available! Gratuitous screen shot: And a screen shot of the Maintenance window: ==========0.9.9========== Update! As always, it won't work without the instructions. Notes on ATF compliance. gun-0.9.9.zip You can now dynamically add sub-categories of the "All Miscellaneous" category. When you open up the MISC form (or click on "Manage" while viewing All Miscellaneous or a sub-category of it), entering your own category is mandatory so items will show up in lists. My next project will be to automatically reduce all caps to lowercase, except for commonly recognized abbreviations. Just kidding. That'd be way too much work. Anyhow, give it a run, and let me know what you think! Enable macros per the instructions if you want all the accessory stuff to work. Otherwise, version 0.9.5 doesn't have any macros. ==========0.9.8========== Re-worked the accessories selection system and added a miscellaneous type. As always, it won't work without the instructions. Notes on ATF compliance. gun-0.9.8.zip I've added an accessory "miscellaneous" to capture perhaps less common accessories. Ideally, though, each category of accessory should have its own specialized data entry page. Suggestions are welcome! Here are some screen shots: Just a reminder, please test things out, but don't go adding large amounts of legitimate data until version 1.0! ==========0.9.7========== HUGE update! As always, it won't work without the instructions. Notes on ATF compliance. gun-0.9.7.zip From this version forward, the database will use macros. Why? Because some features that I've added (which I think are awesome) require macros. In particular, macros are needed to easily peruse and edit the accessories. Since this database is a work in progress, PLEASE test it out and let me know what you think! But you might want to wait until version 1.0 to start entering lots of data. Screenshot of the accessories section: The full main data entry page: To enable the macros for this database, click on... 1 - Tools > Options > Security 2 - Macro Security > 3 - Trusted Sources > Add > 4 - Browse to the directory that contains your gun.odb file and gun folder (in my case, this directory is db in my home folder), and click OK > OK > OK then File > Exit (also exit the quick starter in your system tray, if it's enabled): If you have no need or desire for the additional features that macros bring to the database, you don't have to enable them, or you can use gun-0.9.5.zip, which does not have any macros installed, but should still satisfy ATF requirements. ==========0.9.5========== Minor update. As always, this thing will NOT work without the instructions. gun-0.9.5.zip Pretty much just cleaned out the sample entries. Also, the latest version of HSQLDB is 2.2.6, which should also work just fine. If you're using 2.2.5, no need to upgrade the engine, but you can, if you want. ==========0.9.4========== Important update! As always, the instructions. gun-0.9.4.zip ATFquery now selects (1) all guns in inventory and (2) guns disposed of/transferred during the specified time period. You only have to change one line. Change '2011-07-01' to the start of your date range (be sure to use single quotes), and change CURRENT_DATE to the end of your date range (using the format 'YYYY-MM-DD' including the single quotes). If the end of your date range is the same day as the day you're running your report, you can leave CURRENT_DATE as is. WHERE DATE_DISP BETWEEN '2011-07-01' AND CURRENT_DATE ATFquery Printout now labels all information fields, and can be used for the periodic printouts. ATFquery Spreadsheet can be used as before, e.g., for the ASCII requirement (save as comma- or tab-separated values document). Since both of these reports draw from ATFquery, just change ATFquery, save the changes, then open up one or both of these reports, as needed. ==========0.9.3========== Update: gun-0.9.3.zip Reports are much better now! Instructions for setting the date range for the reports are in the README.txt: The reports have been improved and now can be set to included all guns acquired or disposed of during a time frame that you plug in. This makes printing the ATF-required reports much easier. The most difficult part is to set the date range. To do this, go to Queries in the main window, right-click on ATFquery and select Edit. There are two lines you need to change. For each line, change the first date to the start of your date range. Change CURRENT_DATE to the last day of your date range in the format 'YYYY-MM-DD' (be sure to use the 'single-quotes'), or if the last day of your date range is today (the day you're running the report), just leave CURRENT_DATE as is. First line you need to change: WHERE DATE_ACQ BETWEEN '2011-07-01' AND CURRENT_DATE Second Only line you need to change: WHERE DATE_DISP BETWEEN '2011-07-01' AND CURRENT_DATE Then File > Save the query and File > Close, then File > Save in the main window. Then click on Reports, and double-click the report you want to run. ATFquery Spreadsheet opens a spreadsheet with the records from your date range. You can save this as a LibreOffice spreadsheet, or as a comma- or tab-separated values document. The latter (comma- or tab-separated values document) fulfills the ASCII format requirement. If you prefer, you could use this spreadsheet to print a "mail merge" type of report, otherwise... PRINTquery opens a no-frills printable report of all records in the specified date range. ==========0.9.2========== UPDATE! Unlimited pictures, print reports, audit trail, pre-generated list of calibers, cross-platform, free. gun-0.9.2.zip As before, follow the instructions on how to set up the HSQLDB engine and then the database itself. Instructions to export data from an old version of the database and import data to a new version. Version 0.9.2 - Multiple feature updates Notably... Please install the Report Builder extension if you use OpenOffice! It seems that at least the Windows version of LibreOffice has Report Builder pre-installed.
==========0.9.1========== Update: gun-0.9.1.zip As before, follow the instructions on how to set up the HSQLDB engine and then the database itself. Added calendar drop-down menus for the dates. Also some basic format-checking (e.g., it won't let you put letters in at the phone number or ZIP code). ==========0.9.0========== UPDATE! Actually, a significant update. gun-0.9.0.zip Changed the name from "guns" to "gun" to reflect the significance. Cleaned things up substantially. Same directions for setting up the database apply to this version. Probably the biggest noticeable change is that the tab order is "normal." This was accomplished, in short, by putting *all* gun data in the main "GUN" table. Only when UPDATE is called on ATF-required data fields are entries made into the "GUN_LOG" table. You have to save the entry for the gun (initially) before you can add images to the record. ==========0.8.0========== Sooner than expected, because it was easier than I thought! Here's the original thread. guns-0.8.0.zip Please let me know if there are required bits of information that this database doesn't record. Be sure to read the "README.txt" file. The database won't work if you don't follow some of the directions in there. Specifically, you need to upgrade the database engine to HSQLDB version 2.2.5 and modify the path to the database itself. Here's the README for reference: This database requires OpenOffice or LibreOffice, and the HSQLDB 2.2.5 engine. LibreOffice http://www.libreoffice.org/download/ A recent version of Java http://www.java.com/ HSQLDB http://sourceforge.net/projects/hsqldb/ After downloading and installing LibreOffice (and Java, if necessary), download the hsqldb-2.2.5.zip archive and extract its contents. Particularly, the file "hsqldb.jar" from /hsqldb-2.2.5/hsqldb/lib/ inside the zip archive. Put "hsqldb.jar" somewhere on your computer where it won't be moved or deleted. Open LibreOffice, go to "Tools > Options > LibreOffice > Java" and make sure "Use a Java runtime environment" is checked, and a version is selected and highlighted. Then click on "Class Path > Add Archive" and select the file "hsqldb-2.2.5.jar" Click "OK > OK" and "File > Exit" LibreOffice. If the quickstarter is enabled in your tray area, exit that, too. You may now open gun.odb. HOWEVER... The first thing you have to do upon opening gun.odb is go to "Edit > Database > Properties" and change ~/databases in the "Datasource URL" box to the directory that contains your "gun.odb" file and "gun" data directory. You might have to change the forward slashes to backslashes (e.g., /gun/ to \gun\). After updating the path, save the database "File > Save" or using the shortcut keys CTRL+S. Let me know if this doesn't work for you. ========================================================================== ========================================================================== Version 0.8.0 - Initial release ========================================== This attempts to implement the basics of ATF electronic "bound book" requirements. Please reference ATF Rul. 2008-2 (PDF) and relevant portions of federal law. 1.All data entered into the relevant regulatory-required fields is logged into the table "GUNS_LOG." This table may receive INSERT of new rows, but rows within this table are protected against UPDATE and DELETE commands. Please beware that it is this table that contains the "original entry." 2.Daily memory backup. OpenOffice has the ability to "always create backup copy" (Tools > Options > Load/Save > General). This option must be enabled. The location of the backup copy is set at "Tools > Options > LibreOffice > Paths > Backups." It is up to you to ensure this is set up and working properly to "protect the data from accidental deletion or other system failure." 3.Regulatory requirements for recording pertinent information. All necessary fields should be there. If not, let me know what should be added. 4.Queries can be constructed using SQL and/or the tools included with OpenOffice. Futured versions of this database may include pre-set queries. 5.Periodically print all records. This can be done using SQL and/or the tools included with OpenOffice. Futured versions of this database may include pre-made dynamic reports. 6.The "required acquisition and disposition records" can be downloaded to portable storage devices. After exiting the OpenOffice application, you should be able to simply copy the "guns.odb" file and accompanying "guns" directory to wherever. 7.See #4 and #5. 8.Record both manufacturer *and* importer of foreign-made firearms. Implemented. 9.See #4 and #5. Antique firearms can be identified as "ANT (Antique)." 10.Name and FFL # or name and address of supplier/consigner and the purchaser/transferee. Implemented. 11.Done. 12.Up to you. 13.Providing ASCII copy and printout of records upon termination of license. See #4 and #5. I'm releasing this database under the Creative Commons "Attribution-ShareAlike" license (Link) which basically says you have to attribute the work to me (tc2k11) if you redistribute it, you have to share it under the same license if you redistribute it or a derivative work, and it's not my fault if the SHTF for you because you used this work. |
|
The advertisement on the picture webpage is exactly the kind of product I was looking for!
|
|
Quoted:
The advertisement on the picture webpage is exactly the kind of product I was looking for! I have adblock running. ETA: Hmmm... re-hosted at photobucket.com |
|
Is there anyway you can give steps for open office like you did for Libreoffice? I am computer literate and get along well with Office but databases have always kicked my ass.
|
|
Quoted:
Is there anyway you can give steps for open office like you did for Libreoffice? I am computer literate and get along well with Office but databases have always kicked my ass. Should be exactly the same directions. Just swap out LibreOffice, swap in OpenOffice, and it should work. |
|
Quoted:
Quoted:
Is there anyway you can give steps for open office like you did for Libreoffice? I am computer literate and get along well with Office but databases have always kicked my ass. Should be exactly the same directions. Just swap out LibreOffice, swap in OpenOffice, and it should work. I have open office on a Mac, and I don't speak Java so I don't know how to point to the library that has the "guns.odb" file in it The first thing you have to do upon opening guns.odb is go to "Edit > Database > Properties" and change "~/databases/" in the "Datasource URL" box to the directory that contains your "guns.odb" file and "guns" data directory.
This is baffling me right now. |
|
Quoted:
Quoted:
Quoted:
Is there anyway you can give steps for open office like you did for Libreoffice? I am computer literate and get along well with Office but databases have always kicked my ass. Should be exactly the same directions. Just swap out LibreOffice, swap in OpenOffice, and it should work. I have open office on a Mac, and I don't speak Java so I don't know how to point to the library that has the "guns.odb" file in it The first thing you have to do upon opening guns.odb is go to "Edit > Database > Properties" and change "~/databases/" in the "Datasource URL" box to the directory that contains your "guns.odb" file and "guns" data directory.
This is baffling me right now. Ah. A Mac, you say. Do Macs have home directories? If so, just make a folder called "databases" in your home directory, then put the "guns.odb" file and "guns" data directory in there. ETA: The "Edit > Database > Properties" etc. are directions for using the File menu. |
|
Progress!
Still broken though, </a> Screen shot 2011-11-14 at 6.31.22 PM by Countfrenchula, on Flickr" /> Don't bother spending anymore time trying to teach me this, I can tell you put in a lot of hard work to get this where it is. I will ask my neighbor to help me later. |
|
Quoted:
Progress! Still broken though, http://<a href=http://farm7.static.flickr.com/6103/6346114458_049f82b93d.jpg</a> Screen shot 2011-11-14 at 6.31.22 PM by Countfrenchula, on Flickr" /> Don't bother spending anymore time trying to teach me this, I can tell you put in a lot of hard work to get this where it is. I will ask my neighbor to help me later. OK. Looks like you need to set the path to the "guns" data folder. Your neighbor will probably be able to help you much more efficiently, but please let me know if I can answer any questions! Since I want to use this program for my own collection when I get my C&R, the plan is that the work I do on this thing will keep me out of jail, so feedback from perspectives other than my own is important. And just a reminder, this database and any improvements I make to it will be shared here for free. |
|
UPDATE! Actually, a significant update. gun-0.9.0.zip
Cleaned things up substantially. Same directions for setting up the database apply to this version. Probably the biggest noticeable change is that the tab order is "normal." This was accomplished, in short, by putting *all* gun data in the main "GUN" table. Only when UPDATE is called on ATF-required data fields are entries made into the "GUN_LOG" table. You have to save the entry for the gun (initially) before you can add images to the record. |
|
Update: gun-0.9.1.zip As before, follow the set up instructions from 0.8.0 at the bottom of the OP.
Added calendar drop-down menus for the dates. Also some basic format-checking (e.g., it won't let you put letters in at the phone number or ZIP code). |
|
Quoted:
looks good!!! Thanks! If you happen to give it a try, please let me know if you have any suggestions. |
|
Quoted:
Progress! Still broken though, http://<a href=http://farm7.static.flickr.com/6103/6346114458_049f82b93d.jpg</a> Screen shot 2011-11-14 at 6.31.22 PM by Countfrenchula, on Flickr" /> Don't bother spending anymore time trying to teach me this, I can tell you put in a lot of hard work to get this where it is. I will ask my neighbor to help me later. Did you get it working? |
|
Some people own .44-40. May be located elsewhere, just figured I would pipe in.
Quoted: Pic of the calibers drop-down menu. Features auto-complete. http://i1102.photobucket.com/albums/g441/tc2k11arf/screenshots/calibers.png |
|
Quoted:
Some people own .44-40. May be located elsewhere, just figured I would pipe in. Quoted:
Pic of the calibers drop-down menu. Features auto-complete. http://i1102.photobucket.com/albums/g441/tc2k11arf/screenshots/calibers.png It wasn't there, but it is now! ...or will be in the next "release." ETA: If you don't want to wait for the next version, additional calibers can be added to the FIELDS table. |
|
Quoted:
Can this be run from a thumb drive? Sure can! The path to the "gun" folder (as opposed to the file gun.odb) just needs to be set properly, as in the directions for set up. If you want to run Open/Libre Office from the thumb drive, it might take some extra tweaking. Here's LibreOffice Portable and OpenOffice Portable. ETA: All kinds of different set-ups are possible, including encryption and/or remote encrypted backup with TrueCrypt or FreeOTFE. |
|
When i go to edit/database/properties i cant change the ~database url.
|
|
Quoted:
When i go to edit/database/properties i cant change the ~database url. Hmmm. The URL starts out with "jdbc:" and that can't be edited, but the rest of it should be editable. Also, make sure to extract the files from the zip archive, otherwise it will be opened "read only." I'll put up a couple screen shots shortly. |
|
Quoted:
Quoted:
Can this be run from a thumb drive? Sure can! The path to the "gun" folder (as opposed to the file gun.odb) just needs to be set properly, as in the directions for set up. If you want to run Open/Libre Office from the thumb drive, it might take some extra tweaking. Here's LibreOffice Portable and OpenOffice Portable. ETA: All kinds of different set-ups are possible, including encryption and/or remote encrypted backup with TrueCrypt or FreeOTFE. Thanks! I'll be sure to check it out! |
|
Quoted:
Think ive done it right so far but i get this http://i299.photobucket.com/albums/mm290/walldj45/gunproblem.jpg You have Java and the HSQLDB engine 2.2.5 set up properly? If so, first extract all files from the zip archive: Then copy the location of the "gun.odb" file and the "gun" folder: Then open "gun.odb" and go to Edit > Database > Properties: ...and change ~/databases to the location of the "gun.odb" file and the "gun" folder: ...and change /gun/ to \gun\: ...and click OK, then File > Save. ETA: Oh yeah... And click on "Tables," wait for it to refresh and show the tables, then you can go back to "Forms" and open that to enter gun records. Not sure why it won't refresh automatically, but it doesn't, at least not for me: |
|
Quoted:
Quoted:
Think ive done it right so far but i get this http://i299.photobucket.com/albums/mm290/walldj45/gunproblem.jpg You have Java and the HSQLDB engine 2.2.5 set up properly? SNIP ETA: Oh yeah... And click on "Tables," wait for it to refresh and show the tables, then you can go back to "Forms" and open that to enter gun records. Not sure why it won't refresh automatically, but it doesn't, at least not for me. Thank you very much, i have to leave now but ill try it out tomorrow, looks great! ETA: Porque estas en mexico? |
|
This database requires LibreOffice (or OpenOffice), a recent version of Java, and the HSQLDB 2.2.5 engine.
LibreOffice http://www.libreoffice.org/download/ A recent version of Java http://www.java.com/ HSQLDB http://sourceforge.net/projects/hsqldb/ After downloading and installing LibreOffice (and Java, if necessary), download the hsqldb-2.2.5.zip archive and extract its contents. Particularly, the file hsqldb.jar from /hsqldb-2.2.5/hsqldb/lib/ inside the zip archive. Put hsqldb.jar somewhere on your computer where it won't be moved or deleted. Open LibreOffice, go to Tools > Options > LibreOffice > Java and make sure Use a Java runtime environment is checked, and a version is selected and highlighted. Then click on Class Path > Add Archive and select the file hsqldb.jar. It should look something like this: Click OK > OK and File > Exit LibreOffice. If the quickstarter is enabled in your tray area, exit that, too. Now you can set up the database itself. |
|
Added more explicit and detailed instructions on how to set up the HSQLDB engine and then the database itself to the OP.
|
|
I just set it up. It looks very nice, I'll start using it this weekend. Thanks!
|
|
Just started filling it out.
Is there a way to copy entries into a new record to avoid duplicate typing? |
|
Quoted:
Just started filling it out. Is there a way to copy entries into a new record to avoid duplicate typing? Hmmm, not sure, but I'll look in to it! Posted Via AR15.Com Mobile |
|
Looks like making nearly duplicate records is possible, but would require a macro, which I currently don't have the expertise to write. Maybe someday.
On a positive note... While trying to figure that one out, I figured out a way so that if you have a new caliber that's not on the list, just type it in once, and it will appear in the drop-down menu from then on! |
|
Quoted:
Quoted:
Just started filling it out. Is there a way to copy entries into a new record to avoid duplicate typing? Hmmm, not sure, but I'll look in to it! Posted Via AR15.Com Mobile It is accomplished! It uses a macro, though, so I'll probably provide the code with instructions on how to install the macro, rather than installing the macro by default. |
|
Stay tuned...
Major update this weekend! By way of preparation, you should install the Report Builder extension for OpenOffice. |
|
Quoted:
Just started filling it out. Is there a way to copy entries into a new record to avoid duplicate typing? I'll be posting the necessary macro, with installation instructions, this weekend. |
|
Now no need to re-enter all your data!
Before upgrading to 0.9.2, export your data from 0.9.1 following the directions below. You can then delete (or move into a backup folder) the gun.odb and gun folder for 0.9.1 and replace them with those for 0.9.2. Be sure to set the path to the database, as before! Then follow the direction below to import your data into 0.9.2. |
|
Make sure that you have replace the old gun.odb file with the new one, and the old gun folder with the new one. Then make sure you have set up the path to the gun.odb file and gun folder as before.
Import Open the spreadsheet created above and select all and only the rows containing your data, and all column including the last column. To do this easily, select the last column in of the last row that contains your data: Press and hold down the keyboard buttons CTRL + SHIFT + Home to select all your data to the beginning. Right-click on the selected data and select Copy: Go back to or open the main window of your new database, highlight the GUN table, right-click and select Paste: Make sure the table is GUN, click Append data, check the box Use first line as column names, and click Un-check the box for GUN ID_GUN, then highlight SERIAL and make sure SERIAL in both columns are highlighted. Make sure each label matches up (i.e., both are highlighted at the same time). Then click Create: All of your data from the spreadsheet should now appear in your database, and you can edit it as usual using the GUN form. If you did not change 0 to FALSE and 1 to TRUE, you will get an error message like this: Go back to your spreadsheet and change all 0 to FALSE and 1 to TRUE. |
|
Things are just about ready to start entering data for real!
Actually, as far as I can tell, this database as of v0.9.2 is ready to be used as an electronic bound book. If you see something that needs fixing, let me know! Remember, this database will remain free! I've included the license emphasizing this in the zip archive. |
|
Reports significantly improved!
Update: gun-0.9.3.zip Reports are much better now! Instructions for setting the date range for the reports are in the README.txt: The reports have been improved and now can be set to included all guns acquired or disposed of during a time frame that you plug in. This makes printing the ATF-required reports much easier. The most difficult part is to set the date range. To do this, go to Queries in the main window, right-click on ATFquery and select Edit. There are two lines you need to change. For each line, change the first date to the start of your date range. Change CURRENT_DATE to the last day of your date range in the format 'YYYY-MM-DD' (be sure to use the 'single-quotes'), or if the last day of your date range is today (the day you're running the report), just leave CURRENT_DATE as is. First line you need to change: WHERE DATE_ACQ BETWEEN '2011-07-01' AND CURRENT_DATE Second line you need to change: WHERE DATE_DISP BETWEEN '2011-07-01' AND CURRENT_DATE Then File > Save the query and File > Close, then File > Save in the main window. Then click on Reports, and double-click the report you want to run. ATFquery Spreadsheet opens a spreadsheet with the records from your date range. You can save this as a LibreOffice spreadsheet, or as a comma- or tab-separated values document. The latter (comma- or tab-separated values document) fulfills the ASCII format requirement. If you prefer, you could use this spreadsheet to print a "mail merge" type of report, otherwise... PRINTquery opens a no-frills printable report of all records in the specified date range. |
|
Important update!
gun-0.9.4.zip ATFquery now selects (1) all guns in inventory and (2) guns disposed of/transferred during the specified time period. You only have to change one line. Change '2011-07-01' to the start of your date range (be sure to use single quotes), and change CURRENT_DATE to the end of your date range (using the format 'YYYY-MM-DD' including the single quotes). If the end of your date range is the same day as the day you're running your report, you can leave CURRENT_DATE as is. WHERE DATE_DISP BETWEEN '2011-07-01' AND CURRENT_DATE ATFquery Printout now labels all information fields, and can be used for the periodic printouts. ATFquery Spreadsheet can be used as before, e.g., for the ASCII requirement (save as comma- or tab-separated values document). Since both of these reports draw from ATFquery, just change ATFquery, save the changes, then open up one or both of these reports, as needed. |
|
Summary of instructions and features...
I'm releasing this database under the Creative Commons "Attribution-ShareAlike" license (Link) which basically says you have to attribute the work to me (tc2k11) if you redistribute it, you have to share it under the same license if you redistribute it or a derivative work, and it's not my fault if the SHTF for you because you used this work. Rest assured, though, that I'm making every effort to make this fulfill the ATF's electronic bound book requirements, and I plan on personally using it for exactly that purpose. However, since I'm (1) not a database or programming expert and (2) not making any money off of this, I just have to add the same disclaimer everyone else seems to add to their software. 1. Getting ready... This database requires LibreOffice (or OpenOffice + the Oracle Report Builder), a recent version of Java, and the HSQLDB 2.2.6 (or later) engine. After downloading and installing LibreOffice (and Java, if necessary), download the hsqldb-2.2.x.zip archive and extract its contents. Particularly, the file hsqldb.jar from /hsqldb-2.2.x/hsqldb/lib/ inside the zip archive. Put hsqldb.jar somewhere on your computer where it won't be moved or deleted. Open LibreOffice, go to Tools > Options > LibreOffice > Java and make sure Use a Java runtime environment is checked, and a version is selected and highlighted. Then click on Class Path > Add Archive and select the file hsqldb.jar. It should look something like this: Click OK > OK and File > Exit LibreOffice. If the quickstarter is enabled in your tray area, exit that, too. Now you can set up the database itself. IMPORTANT: If you've been trying out an earlier version of the database, first export/backup your data. 2. Setting up the database... First extract all files from the zip archive: Then copy the location of the "gun.odb" file and the "gun" folder: ABOUT THE MACROS: When you open the gun.odb file, you might get a warning about macros. You don't need to enable macros at this point, but if you want all the features to work, you can enable macros later by following the instructions in #6 below. Then open gun.odb and go to Edit > Database > Properties: ...and change ~/databases to the location of the "gun.odb" file and the "gun" folder: ...and change /gun/ to \gun\: ...and click OK, then File > Save. ETA: Oh yeah... And click on "Tables," wait for it to refresh and show the tables, then you can go back to "Forms" and open that to enter gun records. Not sure why it won't refresh automatically, but it doesn't, at least not for me: You must also "register" the database. This does NOT mean information is sent anywhere. This just means that LibreOffice keeps a list/registry of databases, and in order for all the features of this database to work properly, it needs to be on the list. Basically, go to Tools > Options > LibreOffice Base > Databases > New > Browse > Open gun.odb and save the settings. The registered name must be gun. See # 6 below for enabling macros. IMPORTANT: If you were using an older version of the database and already exported your data, now is the time to import/restore your data. 3. Setting the date range... Now all you need to do is open the ATF_REPORT form!Click To View Spoiler ATFquery selects (1) all guns in inventory and (2) guns disposed of/transferred during the specified time period. You only have to change one line. Go to Queries, right-click and Edit the query ATFquery. Change '2011-07-01' to the start of your date range (be sure to use single quotes), and change CURRENT_DATE to the end of your date range (using the format 'YYYY-MM-DD' including the single quotes). If the end of your date range is the same day as the day you're running your report, you can leave CURRENT_DATE as is. WHERE DATE_DISP BETWEEN '2011-07-01' AND CURRENT_DATE 4. Printing reports... Just need to use the ATF_REPORT form. Click To View Spoiler ATFquery Printout now labels all information fields, and can be used for the periodic printouts. ATFquery Spreadsheet can be used as before, e.g., for the ASCII requirement (save as comma- or tab-separated values document). Both of these reports require the Oracle Report Builder extension. LibreOffice for Windows has this extension pre-installed, I think. Since both of these reports draw from ATFquery, just change ATFquery to select the date range you need, save the changes, then open up one or both of these reports, as needed. 5. Copy Record and Edit as New This feature is now loaded by default, if you enable macros as in #6.Click To View Spoiler If you're entering records with lots of similar/repetitive data, you can install the CopyRow macro to make things easier. If not, no need to install this macro. To install the CopyRow maco, open CopyRow.txt (in the GUN_SQL) folder, select all text, and copy.
In Open/LibreOffice, go to Tools > Macros > Organize Macros > Open/LibreOffice Basic. Go to My Macros > Standard. Click on New. Enter the name CopyRow for the module. In the window that opens, delete all text/code and paste the contents of CopyRow.txt. File > Save the macro and File > Close the window. Back in the main window, go to Forms, right-click on the GUN form, and select Edit. Double-click on the button Duplicate Record and Edit as New. In the Events tab, click on the [...] button for Mouse button pressed. Under Assign, click on Macro > My Macros > Standard > CopyRow > onBtnClickCopyRow and click OK > OK. Then File > Save the form, File > Close the form, and then File > Save in the main window. Now when you click on the Duplicate Record and Edit as New button, it will copy the current record and open it for editing. If you delete or do not enter a unique Serial #, you will get an error when trying to save the entry. If you decided you don't want to save the entry, hit the Undo Data Entry button before saving the new record: 6. Enabling macros... To enable the macros to take advantage of the "accessories" features and automatic, periodic backups for this database, click on... 1 - Tools > Options > Security 2 - Macro Security > 3 - Trusted Sources > Add > 4 - Browse to the directory that contains your gun.odb file and gun folder (in my case, this directory is db in my home folder), and click OK > OK > OK then File > Exit (also exit the quick starter your system tray, if it's enabled): If you have no need or desire for the additional features that macros bring to the database, you don't have to enable them, or you can use gun-0.9.5.zip, which does not have any macros installed, but should still satisfy ATF requirements. This post is a work in progress, and will be linked from the OP. |
|
Time to revisit the ATF requirements, ATF Ruling 2008-2 (PDF).
|
|
Minor update. Pretty much just cleaned out all the superfluous test records that I had created with the CopyRow macro.
|
|
it won't let you put letters in at the phone number That actually is something I would like to be able to do. For example, one of my phone numbers is 555-GUNS. (well, it's not 555, but you know what I mean.) |
|
HUGE update! As always, this thing will NOT work without the instructions.
gun-0.9.7.zip From this version forward, the database will use macros. Why? Because some features that I've added (which I think are awesome) require macros. In particular, macros are needed to easily peruse and edit the accessories. Since this database is a work in progress, PLEASE test it out and let me know what you think! But you might want to wait until version 1.0 to start entering lots of data. Screenshot of the accessories section: To enable the macros for this database, click on... 1 - Tools > Options > Security 2 - Macro Security > 3 - Trusted Sources > Add > 4 - Browse to the directory that contains your gun.odb file and gun folder (in my case, this directory is db in my home folder), and click OK > OK > OK then File > Exit (also exit the quick starter your system tray, if it's enabled): If you have no need or desire for the additional features that macros bring to the database, you don't have to enable them, or you can use gun-0.9.5.zip, which does not have any macros installed, but should still satisfy ATF requirements. |
|
Quoted:
it won't let you put letters in at the phone number That actually is something I would like to be able to do. For example, one of my phone numbers is 555-GUNS. (well, it's not 555, but you know what I mean.)
In the interests of data integrity for people who might use this database, I will probably leave those constraints in place. However, you can change these properties quite easily. Right-click on the GUN form, select Edit. Right-click on the phone number box that you want to change (it will show ###-###-####), and select Control. Delete everything from the Edit Mask (NNNLNNNLNNNN) and Literal Mask (###-###-####). |
|
Sign up for the ARFCOM weekly newsletter and be entered to win a free ARFCOM membership. One new winner* is announced every week!
You will receive an email every Friday morning featuring the latest chatter from the hottest topics, breaking news surrounding legislation, as well as exclusive deals only available to ARFCOM email subscribers.
AR15.COM is the world's largest firearm community and is a gathering place for firearm enthusiasts of all types.
From hunters and military members, to competition shooters and general firearm enthusiasts, we welcome anyone who values and respects the way of the firearm.
Subscribe to our monthly Newsletter to receive firearm news, product discounts from your favorite Industry Partners, and more.
Copyright © 1996-2024 AR15.COM LLC. All Rights Reserved.
Any use of this content without express written consent is prohibited.
AR15.Com reserves the right to overwrite or replace any affiliate, commercial, or monetizable links, posted by users, with our own.