Fill DA 2062 from Access DB

[wiscotech]

thread702-1614610

I'm trying to do the same thing as MrBilko in the forum above. However, the link provided by majp is broken. I need to fill in a DA 2062 (Hand Receipt) (EXCEL Version can be found at this site. Word versions can also be found with a web search. Does anyone have an Access version already created or know how to pull records from the Access DB into EXCEL or Word?

 

[SkipVought]

hi,

Not sure exactly what the ultimate objective is, but it seems that you have data in an Access DB and need to populate a form in order to PRINT. Correct?

There exists an Excel form, so how can you EITHER 1) get the Access data into the Excel form or 2) design an Access form and get the data into it? Correct?

Assuming "Yes" and "Yes," the Excel form may be the way to go IF the only data you wish to populate is the TABLE area (a. thru g.)

I'd do the entire thing in Excel. You can query the MS Access db directly, maybe even in the form itself. Need more insight into what the QUANTITY (g.) columns are AND what your query/data structure looks like.

If you choose this approach, I'd suggest reporting in forum707 for better results. The other option is really an Access REPORT that looks like the form.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

http://www.4shared.com/zip/rvoLjc0m/MajP_2062.html

If you go to the Army site and get the form, it is a word document. I saved that document as a Word Template. The original document has 454 form fields. Although not visible they are numbered 1 to 454. So my code prompts the user for the location of the database and reads the data from the table and then puts that data into the correct form fields. The demo works, but it is only a demo. I do not know the structure of the database or what type of information goes in each field. I provided a dummy database to show that it can pull information from the tables and input into the correct fields. I hard coded some other information because did not know where it would come from. If you provide a real database with notional data I could refine the code. If you can follow what I did then you should be able to fix it easily.
Since it is a template:
1)You open the form
2)Prompts the user for a database location
3)Presents a file browser
4)Populates the form
5)Then prompts the user to save as a new ".doc". You can not save over the template.

The same approach could be used on the Excel file as well.

I would need to know more about the database structure to know where to pull the real data.

 

[wiscotech]

I ended up modifying a database I found on

http://platoonleader.army.mil/.

If you need to fill out DA 2062's from an Access DB, go to that site and search for "arms room" or "database."

The solution utilized there was to create the form in Access and use VB to fine tune the formatting.

 

[whjwilson]

MajP - I am working on a similar issue with the DA Form 2062 and came accross your thread. I see your post and sample database you provided but could not download it.

I tried going about the issue in a different manner:

I ended up cutting up the DA Form 2062 and breaking it up into several parts that I input into a report. I broke the report into several sections that contains a Report header, Page header, Details, Page Footer and Report Footer.

The original form has 16 lines for data on the first page and each page after that has 21 lines for data (37 lines total for the form). The data for the report is being pulled from a table populated by the user. The size of the report will depend on the size of the unit, the more equipment they have the more items they will need. I need to come up with a way in which the report will print properly utilizing the amount of items input. For instance if they only have 7 items....I need it to fill in the 7 items and then input 30 blank lines in the report.

I think your approach would be a much more simpler approach, unless there is some coding that could be done to get the 2062 to work properly the way I attempted. Any suggestions or guidance would be much appreciated.

Will Wilson

 

[SkipVought]

whjwilson,

Please post your questions in a separate thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

You can print X empty rows in a report with some code. However, not certain how you set this up used for the different pages since the first page has only 16 records and the second 21.
But I would think the logic would be if
the number of records is less than 16 records add N blank records to make 16. Not sure why you would need 31 because that would be an all blank second page.
16 < X < 37 add N blank records to equal 37
37 < X < 58 add N blank records to equal 58
58 < X < 79 add N blank records to equal 79
....

Public Function getnumberofBlanks() As Integer
  Dim recordCount As Integer
  Dim N As Integer
  recordCount = DCount("*", Me.RecordSource)
  If recordCount <= 16 Then
    getnumberofBlanks = 16 - recordCount
  Else
    N = 1
    Do while N * 21 + 16 <= recordCount
      N = N + 1
    Loop
    getnumberofBlanks = (N * 21 + 16) - recordCount
  End If
End Function

Public Sub printBlankRecords(numberBlanks As Integer)
  Dim recordCount As Integer
  recordCount = DCount("*", Me.RecordSource)
  TotalCount = TotalCount + 1
  
  If TotalCount = recordCount Then
    Me.NextRecord = False
    'once you get to the last record, stay on last record
  ElseIf TotalCount > recordCount And TotalCount < (recordCount + numberBlanks) Then
    Me.NextRecord = False
    'make the font and backcolor the same appearing to be empty record
    Me.fldOne.ForeColor = Me.fldOne.BackColor
    Me.fldTwo.ForeColor = Me.fldTwo.BackColor
  End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  printBlankRecords getnumberofBlanks
End Sub

I did not fully test the logic for getting the number of blanks. I think it is correct. I like doing very formatting complex forms in word because it makes formatting easier, but requires much more code.

 

[whjwilson]

MajP - Thanks for the quick reply. I put your code into my database, but now get an error:

Run-time error '3163':

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

When I Debug the error shows at this line of code:
recordCount = DCount("*", Me.RecordSource)

Not sure what I am doing wrong. The field size are all set to the max of 255.

 

[MajP]

This was a guess. Your recordsource would have to be the name of a stored table or query. If it is a SQL string then it will not work. So store the forms record source as a query.

 

[MajP]

Here is a zip file that fills a 2062 and leaves the required rows blank. Also I provided a word Template. Open the Word file and it will prompt you to browse to the database. Once you select the db, It will fill the word form.

http://www.4shared.com/zip/oC8LQFEu/Form_2062_1_.html

The Word templated needs some formatting to make it look correct. I screwed up some of the formatting.

Here is the code. I have made it general so that it can be used to make blank rows on any report.

Code in the rpt

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  printBlankRecords Me
End Sub
[/code[

code in a standard module

[code]
Public TotalCount As Integer

Public Function getnumberofBlanks(rpt As Access.Report) As Integer
  Dim recordCount As Integer
  Dim N As Integer
  recordCount = getVisibleRecords(rpt)
  If recordCount <= 16 Then
    getnumberofBlanks = 16 - recordCount
  Else
    N = 1
    Do While N * 21 + 16 <= recordCount
      N = N + 1
    Loop
    getnumberofBlanks = (N * 21 + 16) - recordCount
  End If
  Debug.Print getnumberofBlanks
End Function

Public Sub printBlankRecords(rpt As Access.Report)
  Dim visibleRecords As Integer
  Dim numberBlanks As Integer
  
  TotalCount = TotalCount + 1
  visibleRecords = getVisibleRecords(rpt)
  numberBlanks = getnumberofBlanks(rpt)
  If TotalCount = visibleRecords Then
    rpt.NextRecord = False
    'once you get to the last record, stay on last record
  ElseIf TotalCount > visibleRecords And TotalCount < (visibleRecords + numberBlanks) Then
    rpt.NextRecord = False
    'make the font and backcolor the same appearing to be empty record
    MakeDetailBlank rpt
  End If
End Sub
Public Sub MakeDetailBlank(rpt As Access.Report)
  Dim ctrl As Access.Control
  For Each ctrl In rpt.Detail.Controls
    If ctrl.ControlType = acTextBox Then
     ctrl.ForeColor = ctrl.BackColor
    End If
  Next ctrl
End Sub
Public Sub MakeDetailVisible(rpt As Access.Report)
   Dim ctrl As Access.Control
  For Each ctrl In rpt.Detail.Controls
    If ctrl.ControlType = acTextBox Then
      ctrl.ForeColor = vbBlack
    End If
  Next ctrl
End Sub
Public Function getVisibleRecords(rpt As Access.Report)
  getVisibleRecords = DCount("*", rpt.RecordSource)
End Function

Another easier solution would be to create a table of blank records. You would need 20 blank records because that is the most you could have. Then you would also need a sort field so that all blanks are sorted to the end.

Then the solution is very simple See rpt2062WithBlanks

Private Sub Report_Open(Cancel As Integer)
  Dim numberRecords As Integer
  Dim numberBlanks As Integer
  Dim recordSource As String
  numberRecords = DCount("*", "qry2062")
  numberBlanks = getnumberofBlanks(numberRecords)
  recordSource = "Select * from qry2062 UNION Select Top " & numberBlanks & " * from tblBlanks order by 6, 2"
  Me.recordSource = recordSource
End Sub
Private Function getnumberofBlanks(numberRecords As Integer) As Integer
  Dim N As Integer
  If numberRecords <= 16 Then
    getnumberofBlanks = 16 - numberRecords
  Else
    N = 1
    Do While N * 21 + 16 <= numberRecords
      N = N + 1
    Loop
    getnumberofBlanks = (N * 21 + 16) - numberRecords
  End If
End Function

All 3 solutions work without problem.

 

[heiny013]

I think I used this same method more or less, and it worked out for the most part. Did the same for a DD 1750 and a couple of other forms. I came across this thread looking for a way to use XFDL forms with access (still haven't found away), so thought I'd leave a link to a solution that worked for me:

[URL unfurl="true"]http://www.tactical-tech.net/database.php[/url]

(you may have to hold down the shift button when you open the access file to get to the forms and developer stuff)