Good morning Marie!
Her´s my christmas present to you!
First of all in my example below you need to replase all tames of your
objects (tables, queries and commandbuttons to the names you have in your
db.
Then you need to create a table where the order data shall be imported to.
This si to make it easyer to perform validation on the imported data. This
table is ONLY used to save the imported data and when next order is imported
all existing records will automaticly be deleted and the new order will be
imported.
In my example I have also used fixed names and fixed path for the excell
spreadsheet from which I perform the import. It is possible, without verry
much work to make this dynaicly but it´s christmas today so I´ll settle with
this so far.
In my example I have also asumed that you allready know the names and
departm,ent´s of your employee. Just because this seams logical to me. I
have also asuemed that the employee know their employee number also because
that seams logical to me. If this causes you any problem pleas get back with
some example data and I´ll try to help you out the best I can.
Her´s my 5 cent´s of knowledge to your problem:
To import the order data from a spreadsheet named
"C:\YourFolderName\EmployeeItemOrder.xls" I use this code (copy it into a
code module of a command button´s click_event I have places some comments
for you to read so you know what´s happening in the code):
Don´t forget to replace "cmdImportItemOrder" to name of your command button
and also remove the last "End Sub" since it´s automaticly created when you
open up code module for a commandbuttons click event (well actually every
event´s).
' Code starts here ======================================
Private Sub cmdImportItemOrder_Click()
Dim db As DAO.Database
Dim recImport As DAO.Recordset
Dim recOrder As DAO.Recordset
' Open the current db
Set db = CurrentDb()
'Open recordset of tblImportTabel
Set recImport = db.OpenRecordset("tblImportTabel", dbOpenTable)
'================================================
' Delete existing records in tblImportTabel
'================================================
'Check if any records exist in table if so delete all records
' since this table is only used for importing orders to your db
If recImport.RecordCount <> 0 Then
Do Until recImport.EOF
With recImport
.Delete
End With
recImport.MoveNext
Loop
End If
' Turn Hourglas ON
DoCmd.Hourglass True
'====================
' Import spreadsheet
'====================
' Import spreadsheet to table tblImportTabel which needs to exist in
your db
' the import is also done in this case from path
"C:\YourFolderName\EmployeeItemOrder.xls"
' where the name of the spreadsheet is "EmployeeItemOrder.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblImportTabel", "C:\YourFolderName\EmployeeItemOrder.xls", True
'===================================================
' Copy the imported records into your order table
' in this case "tblYourOrderTable"
'===================================================
' Here we check if field realy have any value if so we copy it
' Open new recordset of table tblYourOrderTable
Set recOrder = db.OpenRecordset("tblYourOrderTable", dbOpenTable)
'Check if any records exist in tblImportTabel then we loop thoriug all
' records and copy them into tblYourOrderTable
If recImport.RecordCount <> 0 Then
' Move to the first record
recImport.MoveFirst
Do Until recImport.EOF
With recOrder
' Check if records in tblImportTable have data
' if so create a new record in tblYourOrderTable and copy
' records from tblImportTable to tblYourOrderTable
If Not Trim(recImport!EmployeeID) & "" = "" And Not
Trim(recImport!NoOfItem) & "" = "" And Not Trim(recImport!ItemID) & "" = ""
Then
.AddNew
!fkEmployeeID = recImport!EmployeeID
!fkItemID = recImport!ItemID
!NoOfItem = recImport!NoOfItem
.Update
End If
End With
recImport.MoveNext
Loop
End If
' Now we are finished so now we turn Hourglas OFF
DoCmd.Hourglass False
' Display a msg to inform that import is finished.
MsgBox "The import are finished and was successfull.", vbInformation +
vbOKOnly, "Import finished"
'Clean up
recImport.Close
Set recImport = Nothing
recOrder.Close
Set recOrder = Nothing
db.Close
Set db = Nothing
End Sub
' Code Ends here ======================================
In this example I have used a spreadsheet with filednames same as the
fieldnames in the table which data is imported to. In this example the
fieldnames are:
EmployeeID (Employee´s employee number)
ItemID (Item number of item stored in your db)
ItemName (Name/description of item so the employee know what
item it is that they order)
NoOfItem (Number of items the employee order)
If you want to export item list of all your items to excell and use this as
a template for your form to e-mail that is possible to do with this code
(also this code could go in click event of an command button):
' Code starts here ==============================
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblYourItemTables", "C:\YourFolderName\OurItemList.xls", True
' Code Ends here =============================
I asume your tablename of all your items are "tblYourItemTables". If you use
this to export your items don´t forget to add fieldnames in the preadsheet
which also needs to be imported into tblImportTabel. Also this could be doen
from Access to allways make sure you have a form which is up to date. But
like I said it´s christmas so I have other things to do as well. Pleas get
back to me if you need help with this to!
And I also create this Item list spreadsheet in
"C:\YourFolderName\OurItemList.xls" where "YourFolderName" is a name of a
forlder and "OurItemList.xls" is the name of the newly created spreadsheet.
Good Luck and Merry Christmas!
// Niklas
