This article was previously published under Q210340
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
This article shows you how to create and use a procedure to display the current line or row number in a subform.
NOTE: This article explains a technique demonstrated in the sample file, FrmSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
233324 (http://support.microsoft.com/kb/233324/EN-US/) Microsoft Access 2000 Sample Reports Available in Download Center
MORE INFORMATION
The following examples demonstrate how to create and use the sample function, GetLineNumber().
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click ReferencesTools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
on the
1.
Open the sample database Northwind.mdb.
2.
Create a module and type the following line in the Declarations section:
Option Explicit
3.Type the following procedure:
Function GetLineNumber (F As Form, KeyName As String, KeyValue)
Dim RS As DAO.Recordset Dim CountLines
On Error GoTo Err_GetLineNumber
Set RS = F.RecordsetClone
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value.
Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
The GetLineNumber() function requires the following three parameters:
•
The form object on which to place line numbers.
•
The name of the unique key field in the subform's underlying table. If the record source does not have a single unique key field, add a field with an AutoNumber data type to the underlying table for this purpose.
•
The current key field value.
You can use the following sample expression as the ControlSource property setting of a text box on a subform, if the subform's underlying table has a field called ID as its unique key field:
=GetLineNumber(Form,"ID",[ID])
How to Use the GetLineNumber() Function
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
1.Open the Order Details table in Design view, add the following field to the table, and then save the table:
Field Name: ID Data Type: AutoNumber
This field will serve as the required single unique field for the table.
2.
Open the Order Details Extended query in Design view, add the ID field from the Order Details table to the query grid, and then save the query.
3.
Open the Orders Subform form in Design view and add the following text box to the form:
Name: LineNum
ControlSource: =GetLineNumber([Form], "ID", [ID])
4.
On the View menu, click Tab Order. Drag the LineNum field from the bottom of the Custom Order list to the top, and then click OK.
5.
Save and close the Orders Subform.
6.
Open the Orders form in Form view and move to a record with multiple order line items. Note that the LineNum text box displays the record number for each product in the order.
There are three ways to refer to data sources physically located outside an Access database in a SQL statement:
Use linked tables
Use the IN clause
Use direct references to the external tables
The most efficient and easiest way to reference external tables is to use linked tables. Sometimes though the linking must be done dynamically by the user because data sources are always changing. This situation arises when polling for data for summary or collection. Another situation prohibits altogether the dynamic linking or importing of tables. This arises when users are using an Access 2.0 database in an Access 95 environment. Unless the database is converted, users cannot create new objects, thus eliminating linking or importing tables.
A simple solution to this would be prompting the user for the name and location of the datasource via a common dialog or input box. Then execute dynamic SQL which would append the data to a preexisting table.
Since a dynamic SQL statement is being executed, no objects are created.
An example is listed below:
Sub GetOutsideData()
''' Function: GetOutsideData() ''' ''' Comments: This routine prompts the user for the location of the database ‘’’ containing the data to import. A dynamic SQL statement is constructed ‘’’ building the connect string into the SQL statement. The statement is ‘’’ then executed and the data appended to a preexisting table. It is ‘’’ assumed the user the table and field names of the outside table is ‘’’ constant. ''' ''' Arguments: none ''' ''' Returns: none ''' ''' Called by: ''' ''' Keywords: APPEND QUERY ''' ''' Date Developer Action ''' ------------------------------------------------------------------------- ''' 8/09/96 Marty Wasznicky created
On Error Resume Next
Dim varReturn As Variant, db As Database Dim szdatasource As String, szSql As String, szMsg as string Dim szTitle as string, szAnswer as string
szMsg = "Enter Path to database" szTitle = "Run Data Demo" szAnswer = InputBox$(szMsg, szTitle)
Const gcErrDiskorNetwork = 3043
If Len(szAnswer) < 1 Then Exit Sub
Set db = CurrentDb()
szSql = "INSERT INTO tblTest ( PMC ) SELECT DISTINCTROW LIMS_CUST.PMC FROM _ LIMS_CUST IN " & Chr$(34) & szAnswer & Chr$(34) & ";"
db.Execute Trim$(szSql)
''' This traps the network error that occurs when the data file is ''' located on a network drive instead of the local hard disk. ''' This error only occurs in Access 7. Also this only occurs using a Novell ''' network client and the extension to your data file is ".dat"!!!!!
If Err = gcErrDiskorNetwork Then MsgBox "You place data file on local hard disk" End If
I used to get data from Finance in Excel format and they wanted me to put them into an Access Database. The imported tables always end up missing with few column fields necessary in the MS Access Reporting later on. Here is how I did it.
Private Sub CMD_ImportXlsTable_Click()
On Error GoTo Err_CMD_ImportXlsTable_Click
If IsNull(Me.FileNameCombo) Then
prompt = MsgBox("Please choose the Filename to process.", vbOKOnly, "Warning!!!")
Exit Sub
Else
ImportXlsTable
If Me.FileNameCombo = 2 Then'WIRELESS BALANCE ON HAND
DoCmd.SetWarnings False
'***This corrects the entry WNPM into WNPM-W StockRoom
DoCmd.OpenQuery "Update Wireless WNPM to WNPM-W Q", acViewNormal
'***This corrects the entry in LOC_AV field by removing the blank prefixes
DoCmd.OpenQuery "Update Imp BOH LOC_AV Q", acViewNormal
DoCmd.SetWarnings True
Else
GoTo ProcessCartons
End If
ProcessCartons:
If Me.FileNameCombo = 7 Then 'Cartons Wireless issued not shipped
'***ADD Additional Column Fields called:
'***Found, Remarks
Dim SQL As String
For i = 1 To 4
Select Case i
Case 1
SQL = "ALTER TABLE [Wcartons issued not shipped] ADD COLUMN Found YESNO"
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY:
This article describes two methods that you can use to start to compact one or more databases automatically at a scheduled time. In the first method, you create a small database with a table, a form, and a macro. The table stores the names of the databases you want to compact. The form contains a procedure in the Timer event that starts to compact the databases whose names are in the table. The macro opens the form every time you open the database.
In the second method, you can use a scheduling utility, such as the NT Schedule Service, to open an Access database by using the /compact command line switch.
This article assumes that you are familiar with Visual Basic for Applications and with creating Access applications by using the programming tools that are provided with Access. For more information about Visual Basic for Applications, see your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Microsoft Access Basic in Access version 2.0. For more information about Access Basic, see the "Building Applications" manual.
SCENARIO: A certain table called API Temp Data needs to have an incremented numbering on ItemNo field. If we use autonumbering we cannot have multiple users entering at the same time in a given table. One item entered may contain number 1 by one user and the other user my have it’s entered item numbered as 2. In order to facilitate the automated numbering system starting with 1 with each user without using the autonumber of MS Acces is to create a procedure that will do the trick for you.
PROCEDURE:
The following is a portion of the procedure in the submit Record click event.
’***START Of INS Procedure
’***Programmer: Noel Jerome Q. Noel
‘***Contact: noeljltd@hotmail.com
‘1. Count the number of Records based on the ControlNo that is in the Form
function IncrementValues(i) as Long IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable end function
Note that it's necessary to pass a value to the function in order to have it count for each record. If you don't include this piece then you'll likely get a lot of 1's in every row instead of the counting result you'd like to see.
SELECT ... IncrementValues([any_field]) ...
You can futher customize this function, the following code with start over at 1 every 4 seconds, so if you run a query that numbers your table, it will start over at 1 on it's own next time you run the query (assuming it's > 4 seconds later).
global IncrementVariable as Long global lastcall as Date
Function IncrementValues(i) As Long If Now > (lastcall + 4 / 60 / 60 / 24) Then lastcall = Now IncrementVariable = 0 End If IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable End Function
And this one restarts every 4 seconds, and gives you the option of designating a starting point other than 1.
Function IncrementValues(i, Optional myBase As Long) As Integer If Now > (lastcall + 2 / 60 / 60 / 24) Then lastcall = Now If myBase Then IncrementVariable = myBase - 1 Else IncrementVariable = 0 End If End If IncrementVariable = IncrementVariable + 1 IncrementValues = IncrementVariable End Function
Finally, it's a good idea to choose data type Long instead of Integer, in case you have a lot of data... and if you're running on a P75, you might want to omit the timed restart, or do more than a 4 second delay, or it might restart at 1 in the middle of running a long slow query.
REFERENCE: http://www.codeguru.com/forum/archive/index.php/t-235929.html There is an article Microsoft Access 2000: Choosing between MSDE and Jet (http://www.microsoft.com/sql/techinfo/deployment/70/msdejet.asp) which lists all the limitations of Access database in the Word file (http://www.microsoft.com/sql/techinfo/deployment/70/Access2000MSDE.doc). Here is extract: "Appendix B: Access/Jet (.mdb) Specifications
File size (.mdb filetype) 2 GB No. of objects in a database 32,768 Modules 1,000 Number of characters in an object name 64 Number of characters in a password 14 Number of characters in a user name or group name 20 Number of characters in a table name 64 Number of characters in a field name 64 Number of fields in a table 255 Number of open tables 2048. Table size 1 gigabyte Number of characters in a Text field 255 No. of characters in a Memo field (data entered via UI) 65,535 No.of char. in a Memo field (data entered programmatically) 1 gigabyte Size of an OLE Object field 1 gigabyte Number of indexes in a table 32 Number of fields in an index 10 Number of characters in a validation message 255 Number of characters in a validation rule 2,048 Number of characters in a table or field description 255 Number of characters in a record 2,000 Number of characters in a field property setting 255"
