Access Code Confirmation

Posted on 2014-08-21
Last Modified: 2014-08-21
Good morning Experts.

I'm working with some code provided by another expert, and I want to make sure that the methods are correct, as I had other code that was not correct and a few experts here helped to fix.

My knowledge of code is VERY limited and this is why I'm here asking for your assistance.

The below code imports the data only after I convert the Excel file to CSV.  It does not allow me to import and evaluate the Excel file that contains the same information.  Although the CSV import occurs, those fields containing null values are not correctly evaluated.  Null values should be acceptable values and TRUE placed in the chk_Field for the corresponding field.

Assistance needed:
Review code methods and make code modification recommends where needed.
Modify code to allow import from Excel directly
Modify code to allow Null value acceptance during import check process

Thank you in advance for your assistance.  Any code explanations you can provide would be greatly appreciated.  I would like to try and understand the flow of the code.  Thanks again.

Project Background:
The purpose of this database is to upload files manipulated by customers and verify that the data submitted is correctly formatted.

This is what should happen:
1) Navigate to Excel file provided by customer and import data into TEMP table containing all text fields.
2) Evaluate each field to ensure that numbers, text, dates, and other field types are what they are supposed to be.  Customer's sometimes change the field and/or do not provide the right formatted data and this is our way of confirming the data provided before pushing forward.  Within the TEMP table is a chk field for each imported field (Date has chk_Date). If the data provided is formatted correctly, the chk_field is updated to True, else False.
3) Once the checks on each field and record is completed, the user opens a form that utilizes the chk_fields and conditional formatting to quickly identify data issue and allows the user to modify the data into the correct format.  If Date is anything but null or an actual date, chk_Date = False and conditional formatting will highlight Date of the record/field containing the issue. Changes made on the form calls the same functions used when checking the whole table and chk_field is updated if correct.
4) Once all data format issues are resolved, the result will be pushed into a CSV file for a corporate system process.

Code needing assistance with:
Public Function GetFile() As Variant
Dim dialog As Object
Dim pickedfile As Boolean
Set dialog = Application.FileDialog(3)
GetFile = Null
With dialog
    .InitialFileName = "%USERPROFILE%\Desktop\*.xlsx"
    .AllowMultiSelect = False
    .Title = "Please select file for import"
    .Filters.Add "Excel Files", "*.xlsx"
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
        GetFile = .SelectedItems.Item(1)
    End If
End With
End Function

Public Sub readdata(FileName As Variant)
'Dim i As Integer, j As Integer, S As Boolean, ErrCnt
Dim ErrCnt As Integer
Dim FN As Integer
Dim Str As String
Dim A() As String
Dim rs As Recordset
FN = FreeFile
CurrentDb.Execute "Delete * FROM tbl_TEMP_RFI"
Set rs = CurrentDb.OpenRecordset("tbl_TEMP_RFI")
Open FileName For Input As #FN
    Line Input #1, Str ' Read line with headings and ignore
ErrCnt = 0
Do Until EOF(FN)
    Line Input #1, Str
'    Str = Replace(Str, "'", "") 'Remove extra apostrophe
    A = Split(Str, ",")
        ErrCnt = ErrCnt + 3 + chk0(A(0)) + chk1(A(1)) + chk2(A(2)) ' + chk3(A(3))
        rs("ProductInfo_NDC") = A(0)
        rs("ProductInfo_ExpectedLaunchDate") = A(1)
        rs("ProductInfo_PkgSize") = A(2)
        'rs("PkgQty") = A(3)
        rs("chk_ProductInfo_NDC") = chk0(A(0))
        rs("chk_ProductInfo_ExpectedLaunchDate") = chk1(A(1))
        rs("chk_ProductInfo_PkgSize") = chk2(A(2))
        'rs("cPkgQty") = chk3(A(3))
Close #FN
'If ErrCnt = 0 Then
    'Here you can directy import data from tblProducts
'    DoCmd.OpenForm "CheckImport"
'End If
DoCmd.OpenForm "CheckImport"
End Sub

Open in new window

This is the code that Experts here assisted with and works as it is supposed to.  I'm providing as a reference because the above code uses this code in the data validation process.
Function chk0(ByVal varValue As Variant) As Boolean
    ' Check if varValue is Null or contains exactly 11 digits.
    chk0 = IsNull(varValue) Or (Format(Val(Nz(varValue)), String(11, "0")) = Trim(varValue))
End Function

Function chk1(ByVal varValue As Variant) As Boolean
    'Check if varValue is Null or a valid date expression.
    chk1 = IsNull(varValue) Or IsDate(varValue)
End Function

Function chk2(ByVal varValue As Variant) As Boolean
    'Check if varValue is Null or numeric
    chk2 = IsNull(varValue) Or IsNumeric(varValue)
End Function

Open in new window

Question by:dhemple
    LVL 84

    Accepted Solution

    Your question reads like a project assignment, and many Experts simply will not get dragged into questions like this. As you're aware, we're not really here to do your work for you, and implement your Assistance Needed listing. We'll be glad to help, but we expect you to do the work and let us help - not the other way around.

    That said: The code you posted seems to do what you imply it should. It prompts the user to locate an Excel file, and imports it into a temporary table. From there, it seems that it opens a form, and I'm assuming that form is where the user would validate the data.

    FWIW, you can import an Excel file directly using standard Access methods:

    Dim sFile As String
    sFile = GetFile()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempTableName", sFile, True

    This would put all the data from the Excel file into the table identified. From there, you'd continue the process as before.

    I'd also suggest that you name your "chk" functions to reflect what they actually do. For example, the one that checks for a valid Date should be named "CheckDate"

    Author Comment

    Scott, Thank you so much for taking the time to respond to my post.  I'm very sorry for the appearance of my post.  I never meant for it to look like a project assignment.  I was looking to include as much information about the project to ensure the reader fully understood the project and my struggles.  I clearly missed the mark on this.  Very sorry and thank you for pointing this out to me.

    Your import solution is much simpler and your recommendations for "chk" functions are perfect.

    I've implemented your suggestions and everything is working perfect!!

    Thanks so much.  I truly appreciate your time and assistance.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now