Updates to an existing Access Database

iTAG100 DATABASE Project

Background

We have an Access database with an SQL Server backend which we would like to extend.

Scope

The develop the current iTAG database to include the following

• New Table for Version with the following fields

o ID (INT)

o Version Number (INT)

o Version Specifics ( MEMO)

o Default (BIT) (Only One item will be default at a time)

• The new Table will be linked to the TagSerial Table and the Tag Service table such that we have one version per Tag Serial and One Version Per Tag Service record.

• There will be a new form to edit the version numbers

• The Current “New Tag” and “Service forms” will have an additional combo box menu to select the version number (Compulsory). It will always default to the default version from The revision Table.

• When the new tags / services are added the version number will be included into the relevant table (possible new fields required)

• A Reprint option will be added to New Tags / Service forms which repeats the movement of the file See Appendix 1, Also ask the question if there are more than 30 MACS to print.

• Add a report(s) to show details about a MAC address showing its manufacturing detail and any services which have taken place. Filters by MAC address Customer, Version, Partnumber, Date

• Add a report to show the COUNT specific versions in the field. Add filters by customer and Part number Considering the current version (i.e the latest Service always takes president)

• Add a form “Version Query” , which has a single mac address field which when it looses focus displays the Version number on the form. Then resets back to waiting for a new MAC address

• Add all above options as menu item to access the forms and reports

Appendix 1

Code for moving files to print labels

' Check to see if if the print check box is ticked and if it is how many records we have to prompt if they want to print so many

If [login to view URL] = True Then

' Now check how many records as we just print it if ts less than 30, more than 30 prompt and ask

If NumberOfRecords > 30 Then

If MsgBox("There are " + Str(NumberOfRecords) + " records. Do you wish to Print these labels?", vbYesNo, "Print Label Check") = vbYes Then

PrintCheck = True

Else

PrintCheck = False

End If

End If

If PrintCheck = True Then

' Print the labels (this just copied a pre-formatted file to the destination folder)

Dim PathForLabels As String

Dim FileForLabels As String

PathForLabels = DLookup("PathForPrintLabels", "dbo_tblGlobals", "GlobalsPK = 1")

' Check if the Label path has a "/" at the end

'If Right(PathForLabels, 1) <> "/" Then

' PathForLabels = PathForLabels + "/"

'End If

' Updated to have source and destination

FileForLabels = DLookup("FileForPrintLabels", "dbo_tblGlobals", "GlobalsPK = 1")

FileCopy FileForLabels, PathForLabels

End If

Programim Bazash të Dhënash Microsoft Access Server Microsoft SQL SQL

ID Projekti: #17532089

Rreth projektit