Database Upload Error Message

[gweller]

Hello,

I am very new to VB and am using VB to upload data from multiple Excel files to a single database. The Excel files are generated from a form questionnaire that will be filled out by multiple organizations and emailed back to me. Each form has certain fields which require an answer and must not be left blank.

So far I am able to upload multiple excel files from a single source directory.

I am now trying to create error messages to alert the user to missing information that has not been filled out on the forms that are being sent in.

Any Suggestions?

Any help is greatly appreciated.

Thanks!

Greg

[Hack]

Welcome to DevX

Are the error messages supposed to be generated before, or after, they get into the database?

If it is before, then you could modify your upload code to check the contents of specific cells prior to actually doing the import.

If after, then you could pop up a VB screen which asks for the information, and then updates the database directly (without the need for excel at all)

[gweller]

Thanks for replying!

I would like to modify my upload code to check for missing information and alert and alert the user prior to it being uploaded into the database.

The purpose is for the user to be flagged and the upload prevented so that the user can send the form back to the sender to complete.

If you could provide some detail as to how to modify the code that would be great.

Thanks again,

Greg

[Hack]

What code do you have for your uploading?

It shouldn't be difficult to modify that to check specific cells on the spreadsheet.

These cells that must be filled out are ALWAYS in the same location on the spreadsheet, right? If the answer to this is No, then give a range name to the cell location so your code can find it no matter where it winds up.

[gweller]

Yes, the fields that must be filled out will always be located in the same place.

Here is the code I am using for the upload. The code is used to upload three separate surveys that will be coming in from multiple businesses.

Private Sub LoadAdultCare_Click()
On Error GoTo Err_LoadAdultCare_Click
Dim dbsTemp As Database

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File

Dim Path As String
Dim NewPath As String

Path = "C:\OHEP\AdultCareBeforeLoad\"
NewPath = "C:\OHEP\AdultCareAfterLoad\"
ChDir (Path)
Set dbsTemp = CurrentDb
If oFs.FolderExists(Path) Then
    Set oFolder = oFs.GetFolder(Path)
 
    For Each oFile In oFolder.Files
           
        If oFs.GetExtensionName(oFile) = "xls" Then
            DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=5, _
            TableName:="AdultCare", FileName:=oFile.Name, HasFieldNames:=False, _
            Range:="Data!A2:N127"
           
            oFs.MoveFile oFile.Name, NewPath + oFile.Name
           
        End If
       
    Next
   
End If
      
Exit_LoadAdultCare_Click:
    Exit Sub
Err_LoadAdultCare_Click:
    MsgBox Err.Description
    Resume Exit_LoadAdultCare_Click
End Sub

Private Sub LoadHospital_Click()
On Error GoTo Err_LoadHospital_Click
Dim dbsTemp As Database

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File

Dim Path As String
Dim NewPath As String

Path = "C:\OHEP\HospitalBeforeLoad\"
NewPath = "C:\OHEP\HospitalAfterLoad\"
ChDir (Path)
Set dbsTemp = CurrentDb
If oFs.FolderExists(Path) Then
    Set oFolder = oFs.GetFolder(Path)
 
    For Each oFile In oFolder.Files
           
        If oFs.GetExtensionName(oFile) = "xls" Then
            DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=5, _
            TableName:="Hospital", FileName:=oFile.Name, HasFieldNames:=False, _
            Range:="Data!A2:N211"
           
            oFs.MoveFile oFile.Name, NewPath + oFile.Name
           
        End If
       
    Next
   
End If
      
Exit_LoadHospital_Click:
    Exit Sub
Err_LoadHospital_Click:
    MsgBox Err.Description
    Resume Exit_LoadHospital_Click
   
End Sub

Private Sub LoadNursingHome_Click()
On Error GoTo Err_LoadNursingHome_Click
Dim dbsTemp As Database

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File

Dim Path As String
Dim NewPath As String

Path = "C:\OHEP\NursingHomeBeforeLoad\"
NewPath = "C:\OHEP\NursingHomeAfterLoad\"
ChDir (Path)
Set dbsTemp = CurrentDb
If oFs.FolderExists(Path) Then
    Set oFolder = oFs.GetFolder(Path)
 
    For Each oFile In oFolder.Files
           
        If oFs.GetExtensionName(oFile) = "xls" Then
            DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=5, _
            TableName:="NursingHome", FileName:=oFile.Name, HasFieldNames:=False, _
            Range:="Data!A2:N173"
           
            oFs.MoveFile oFile.Name, NewPath + oFile.Name
           
        End If
       
    Next
   
End If
      
Exit_LoadNursingHome_Click:
    Exit Sub
Err_LoadNursingHome_Click:
    MsgBox Err.Description
    Resume Exit_LoadNursingHome_Click
   
End Sub

[gweller]

I have been able to generate error messages for missing information and am now trying to figure out how to flag surveys that have already been uploaded.

If a survey has already been uploaded I would like to flag the user to be able to either continue and overwrite or abort upload.

Any suggestions?

Thanks!

[Hack]

What determines if the survey has been uploaded?

Will there be an entry in db table, or a specific file name in a certain folder?

[gweller]

There will be a specific file name in a certain folder. I am uploading files from a "pre-uploaded folder" to a "post-upload folder" and would like a message to alert the user if he/she is trying to upload a file that already exists in the "post-uploaded" folder and allow them to either abort upload or overwrite the existing file.

[Hack]

Try something like this

If Dir$("C:\OHEP\AdultCareBeforeLoad\gweller.txt") <> vbNullString Then
   MsgBox "file exists"
Else
   MsgBox "file doesn't exist"
End If