Hi all, A client of ours has just moved to fat clients from thin clients. They have a small customised Access DB that was running on Access 2003 on a. Summary Experienced developers use a variety of techniques to simplify their coding and maintenance efforts. Some of the tricks are general programming styles and. Microsoft Visual Basic 1990. Visual Basic Functions Tutorial. Welcome to our VBA Functions for Microsoft Access home page. This section of our website contains all of the predefined functions. We have a. net application in our project that is giving me problems on my machine. Part of the app calls various. Fixing Auto. Numbers when Access assigns negatives or duplicates. Provided by Allen Browne. Created August 2. Microsoft Access 2007 In Vb6 Msgbox' title='Microsoft Access 2007 In Vb6 Msgbox' />Last Updated. January 2. In Access 2. Auto. Number field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table. Several issues can trigger this problem Cause. Solution. An old bug. If you are using the MDB file format, apply JET 4 Service Pack 7 or later to prevent the problem recurring. Use the code below to fix your Auto. Numbers. Appending data to a linked table. Microsofts workaround is discussed in knowledge base article 8. Note that this issue is more generic than the kb article suggests it occurs any time you specify the value for the Auto. Number when appending to a linked table, not just when appending from one linked table to another. A badly designed form, discussed below. Redesign the form. Use the code below to fix the Auto. Number. Auto. Number defined as Random or Replication ID. Open your table in design view, select the Auto. Number field, and ensure Field Size is Long Integer, and New Values is Increment. Not possible in replicated databases. Resetting the Seed. You may be able to solve the problem with a compactrepair In Access 2. Compact and Repair Database on the Database Tools ribbon. In Access 2. 00. 7  Office Button Manage. In earlier versions  Tools Database Utilities. Note that compactrepair may not solve the problem in Access 2. DAOData Access Object Access2007DAO. I am trying to read a text file in Access using VBA. The code is as follows Open CTesttest. For Input As 1 Dim MyString as String Dim x as integer x 0 For. Hi, I hope somebody is able to help me urgently, I have a booking form which has been created through a query of three tables, customer services and. In the particular case where we first saw this, the Auto. Number was not the. Alternatively, the code below checks all tables in your database, and offers to fix any where the Seed of the Auto. Number is less than zero or below the existing values. The function returns the number of tables that were changed zero if none had the problem. It does not alter the values of any duplicate values in the column. The code does not work on attached tables if your database is split, run it in the back end data file. It works only with Access 2. Instructions. Back up your database. Create a new module. In Access 2. 00. 7 and later, click Module rightmost icon on the Create ribbon. In older versions, go to the Database Window, choose the Modules tab, and click. New. Access opens a new code window. Copy the function below, and paste into the code window. Choose References from the Tools menu, and check the box beside Microsoft ADO Ext. DDL and Security. From the Debug menu, choose Compile to check there are no problems. Press CtrlG to open the Immediate Window. Enter     Auto. Num. FixHow it works. Version 4 of JET supports altering the Seed for an Auto. Number which is how they got it wrong. The DAO library was not updated to handle the new feature, so we use ADOX. The code performs these steps Visits each Table in the current project Skips the queries which are tables to ADOX and the system and temporary tables names beginning with MSys or Loops through the columns of each table searching for an Auto. Number, i. e. one with the Auto. Increment property true Looks up the maximum value assigned to this column of the table Checks if the columns Seed property is negative, or is below the maximum Offers to set the Seed to one more than the maximum already assigned On confirmation, alters the Seed and prints the information to the Immediate Window Continues to the next table, unless you chose Cancel in the dialog. Function Auto. Num. Fix As Long. Dim cat As New ADOX. Catalog. Dim tbl As ADOX. Table. Dim col As ADOX. Column. Dim var. Max. ID As Variant. Dim lng. Old. Seed As Long. Dim lng. New. Seed As Long. Dim str. Table As String. Dim str. Msg As String. Dim lng. Answer As Long. Dim lng. Kt As Long. Set cat. Active. Connection Current. Project. Connection. For Each tbl In cat. Tables. lng. Answer 0amp. If tbl. Type TABLE Then. Table tbl. Name. If Leftstr. Table, 4 lt Msys And Leftstr. Table, 1 lt Then. For Each col In tbl. Columns. If col. PropertiesAutoincrement Then. If col. Type ad. Integer Then. Old. Seed col. PropertiesSeed. Max. ID DMax col. Name, str. Table. If lng. Old. Seed lt 0 Or lng. Old. Seed lt var. Max. ID Then. lng. New. Seed Nzvar. Max. ID, 0 1amp. If lng. New. Seed lt 1 Then. New. Seed 1amp. End If. Msg Table vb. Tab str. Table vb. Cr. Lf. Field vb. Tab col. Name vb. Cr. Lf. Max vb. Tab var. Max. ID vb. Cr. Lf. Seed vb. Tab col. PropertiesSeed. Cr. Lf vb. Cr. Lf Reset seed to lng. New. Seed. Answer Msg. Boxstr. Msg, vb. Yes. No. Cancel vb. Question,. Alter the Auto. Number for this table. If lng. Answer vb. Yes Then. col. PropertiesSeed lng. New. Seed. lng. Kt lng. Kt 1amp. Debug. Print str. Table, col. Name, lng. Old. Seed, lng. New. Seed. End If. End If. End If. Exit For. End If. Next. End If. If lng. Answer vb. Cancel Then. Exit For. Set col Nothing. Set tbl Nothing. Set cat Nothing. Auto. Num. Fix lng. Kt. End Function. There are other cases where Access can be fooled into setting the Seed of the Auto. Number incorrectly. These steps will reproduce the problem in Access 2. Create a table with an Auto. Number and one or more other fields. Save. Enter a half a dozen records. Create a form bound to this table. Create a subform bound to the same table, but without a text box for the Auto. Number field. Put the name of the Auto. Number field into the Link. Master. Fields and Link. Child. Fields properties of the subform. Set the Data. Entry property of the subform to Yes, so it goes to a new record. Open the main form to the first record, and try enter a record in the subform. The attempt will fail with a Duplicate error message. You are now unable to add any more records, even if you try to add them directly to the table. Explanation. Subforms are designed to enter related records. The subforms foreign key field named in Link. Child. Fields therefore inherits the value of the main fields primary key named in Link. Master. Fields. In the case above, this means that Access is trying to assign the existing primary key value from the first record to the new record being entered in the subform. Naturally that fails. Unfortunately, the Seed of the Auto. Number also gets set to one more than the failed write. Since the Seed is below existing values, further attempt to add new records also fails, as Access tries to reassign existing values. Note that the Seed is reset only if the Link. Child. Fields contains the name of an object of type Access. Field. The write still fails, but the Seed is not reset if Link. Child. Fields names an object of type Control, e. Solution. There is no valid reason for designing a main form and subform bound to the same table and linked on the Auto. Number field. Get rid of this bad design. Then use the code above to fix the problem with your Auto. Number so you can add new records to the table again. Other scenarios There may also be other scenarios where JET is tricked into resetting the Auto. Number to an unusable value. Related information. For code to examine a table and find out what the Seed of the Auto. Number is, see Get. Seed. Adox. For code to reset the Seed for one table only, see Reset. Error Handling and Debugging Tips for Access 2. VB, and VBAProfessional applications need to include error handling to trap unexpected errors. By using a consistent error handler, you can make sure that when crashes occur, the user is properly informed and your program exits gracefully. Basic error handling just hides the default behavior and exits the program. Advanced error handling can include all sorts of features such as saving information about the cause of the error and the environment at the time, attempts to address the problem, and information for the user on what they need to do next. Verify Error Handling Setting. Before you can use error handling, you need to understand the Error Trapping setting. Visual Basic 6. 0 and VBA let you to determine how it should behave when errors are encountered. From the IDE, look under the Tools Options setting. Figure 2. Setting error trappinghandling options for Visual Basic and VBAMake sure that error trapping is not set to Break On All Errors. That setting will cause your code to stop on every error, even errors you are properly handling with On Error Resume Next. Break on Unhandled Errors works in most cases but is problematic while debugging class modules. During development, if Error Trapping is set to Break on Unhandled Errors and an error occurs in a class module, the debugger stops on the line calling the class rather than the offending line in the class. This makes finding and fixing the problem difficult. I recommend using Break in Class Modules, which stops on the actual crashing line. However, be aware that this does not work if you use raise errors in your classes via the Err. Raise command. This command actually causes an error and makes your program stop if Error Trapping is set to Break in Class Modules. Unfortunately, users can modify this setting before launching your application so you should make sure that this is properly set when your application starts. Programmatically, the option settings can be viewed and modified by using the Application. Get. Option and Application. Set. Option methods. Function Get. Error. Trapping. Option As. String. Dim str. Setting As. String. Select. Case Application. Get. OptionError Trapping. Setting Break on All ErrorsCase 1. Setting Break in Class ModulesCase 2. Setting Break on Unhandled ErrorsEnd. Select. Get. Error. Trapping. Option str. Setting. End. Function. Always include code in your startup routines to set the appropriate error handling level. Sub Safe. Start. Application. Set. Option Error Trapping, 1. End. Sub. Make Sure that Every Procedure Has Error Handling. Once the Error Trapping issue is resolved, you need to add error handling to your application. Unfortunately, Visual Basic 6. VBA do not support a global error handler to manage any errors that arise. You actually have to set error handling in every procedure. Without you explicitly adding error handling, Visual Basic and VBA show the default error message and then allow the user to debug your code, or just crash. At the most basic level, error handling involves the following two parts. Error Enabler. The following section invokes the error handler. If an error occurs in the procedure, the code jumps to the line where the label PROCERR is defined. For consistency, use the same label name in every procedure. Error Handler. The following section is where the code goes if an error occurs in the procedure. Msg. Box Error Err. Number Err. Description, vb. Critical. Here you can manage the error and determine what to do next. Examine the error object Err to see what occurred. For example, Err. Number is the error number, Err. Description is the error description, and so on. Disabling Error Handling. In some situations, you need to turn off error handling. For example, you might want to see if a file exists. By looking for it and managing the error if it cant be found, you can determine whether it exists or not. Disable error handling with the following code. Turn Off Error Handling During Development and Testing. Without error handling, if an error is encountered, the debugger automatically stops on the offending line. This is great for debugging and correcting mistakes. However, if error handling exists in the procedure, when an error occurs, rather than stopping on the offending line, the code in the Error Handling section is invoked. This makes debugging much more difficult. An easy way to avoid this problem is to add a global constant or variable that controls when error handling is active. So instead of using the following codeuse this codeIf gcf. Handle. Errors Then. On. Error. Go. To PROCERR. Public. Const gcf. Handle. Errors As. Boolean False. Set this constant to False during development, and then to True when you deliver your application. That way, your users get the benefit of the error handling and you can get your work done without it. Getting Information from the Error Object. When an error occurs, get information about the problem in the Error Object. This object is named Err and contains several properties. The following are the properties that you should check Number  The error number, which is useful for testing. A value of zero means no error. Description  The built in description of the error. Sometimes this doesnt exist and this text Application defined or object defined error is given. The error object lets you easily inform the user of the problem. For example, rather than display a simple message that an error occurred, you can specify the exact error number and message to display. Msg. Box Error Err. Number Err. Description, vb. Critical. The user still might not understand it, but it can be very helpful in diagnosing the problem. For a complete list, see Microsoft Access Error Numbers and Descriptions Reference. Clearing the Error Object. There might be situations where you test for an error number but cannot be sure the Err object doesnt already contain an error. In such cases, use the Clear method to clear the object. Alternatively, you can set the error number to zero Err. Number 0, but is not as effective as the Clear method since it does not clear the description property. Crack The Case Game Scenarios Synonyms. Using Error Handling for Testing. Error handling can also be used to test a condition. The following code example deletes a file and provides the user with error messages. Sub Delete. Filestr. File. Name As. String. Dim lng. Save. Err As. Long. Dim str. Save. Err As. String. Const clng. Err. No. File As. Long 5. 3. Const clng. Err. File. In. Use As. Long 7. 5. On. Error. Resume. Next. Kill str. File. Name. lng. Save. Err Err. Number. Save. Err Err. Description. On. Error. Go. To PROCERR. Select. Case lng. Save. Err. No error. Case clng. Err. No. File. Msg. Box The file str. File. Name does not exist. Case clng. Err. File. In. Use. Msg. Box The file str. File. Name is in use. Case. Else. Msg. Box Unknown error str. Save. Err. Msg. Box Error Err. Number Err. Description. Resume PROCEXIT. End. Sub. Notice how the following syntax allows the program to continue that is, go to the next line even when a command cannot be executed. The Kill command triggers an error if the file being deleted doesnt exist or is locked. We dont care whether the object exists or not. We just want to delete it if it does. Therefore, the command to ignore the error Resume Next is appropriate.