Access Code Confirmation

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhempleAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.