Is it possible...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Everyone,

I am making a database which imports all of its information. One table,
tblFTE, has four fields, FTEID (Primary Key/Autonumber), Date, EmployeeID and
MonthlyHours. The user will import a list of about four hundred EmployeeIDs
and MonthlyHours. However, the date is not included. Is is possible to
somehow have a prompt for the user to enter the date, and then have Access
take that date and put it in the Date field for the information imported?
Just curious! Thank you for any help!!!

Tandy
 
Yes. You don't say how you're doing the import and from what type of data
file, but your solution likely will involve using a form that has a textbox,
let the user enter a date in that textbox, and then click a button on the
form to start the import. The process that does the import (whatever you're
using) can be programmed to read the date from the form's textbox and use
it.

But, tell us more details about the import process that you're using, etc.
 
Here's one way to do this:

1) Create a table (name it tblTempImport) that can hold the imported data
from the EXCEL file (we'll use this as a temporary holding place for the
data).
2) Create a form that has a textbox and a command button on it.
3) Name the textbox txtDate and the button cmdImport.
4) Set format of the textbox to Short Date.
5) Use this programming code for your command button's Click event (Code
assumes that the name of the permanent table is tblPermanent, and assumes
that you have a fixed path and file name for the EXCEL file):

' START of code
Private Sub cmdImport_CLick()
Dim dbs As DAO.Database
Dim datDate As Date
Dim strSQL As String
Const strImport As String = "tblImportData"

' change tblPermanent to real name of permanent table
Const strTable As String = "tblPermanent"

' change string to actual path and file name of EXCEL file
Const strFile As String = "C:\MyFolder\EXCELFile.xls"

On Error GoTo Err_Msg
If Len(Me.txtDate.Value & "") = 0 Then
MsgBox "Enter a date!"
Else
Set dbs = CurrentDb()
strSQL = "DELETE * FROM tblImportData;"
dbs.Execute strSQL, dbFailOnError

' change True to False if the EXCEL file's spreadsheet
' DOES NOT have headers in first row
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, _
strImport, strFile, True

datDate = Me.txtDate.Value
strSQL = "INSERT INTO [" & strTable & "] " & _
"SELECT T.*, #" & Format(datDate, "mm\/dd\/yyyy") & _
"# FROM [" & strImport & "];"
dbs.Execute strSQL, dbFailOnError
End If

Exit_Code:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Msg:
MsgBox "Error " & Err.Number & " has occurred: '" & Err.Description & "'."
Resume Exit_Code
End Sub
' END of code


Now, you can open this form, put the date in the textbox, and click the
button to import the data.
 
Sorry, slight typo in the code that I posted:

' START of code
Private Sub cmdImport_CLick()
Dim dbs As DAO.Database
Dim datDate As Date
Dim strSQL As String
Const strImport As String = "tblImportData"

' change tblPermanent to real name of permanent table
Const strTable As String = "tblPermanent"

' change string to actual path and file name of EXCEL file
Const strFile As String = "C:\MyFolder\EXCELFile.xls"

On Error GoTo Err_Msg
If Len(Me.txtDate.Value & "") = 0 Then
MsgBox "Enter a date!"
Else
Set dbs = CurrentDb()
strSQL = "DELETE * FROM tblImportData;"
dbs.Execute strSQL, dbFailOnError

' change True to False if the EXCEL file's spreadsheet
' DOES NOT have headers in first row
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, _
strImport, strFile, True

datDate = Me.txtDate.Value
strSQL = "INSERT INTO [" & strTable & "] " & _
"SELECT T.*, #" & Format(datDate, "mm\/dd\/yyyy") & _
"# FROM [" & strImport & "] AS T;"
dbs.Execute strSQL, dbFailOnError
End If

Exit_Code:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Msg:
MsgBox "Error " & Err.Number & " has occurred: '" & Err.Description & "'."
Resume Exit_Code
End Sub
' END of code

Also, the code above assumes that tblImportData has the fields from the
EXCEL file in the same order as the fields in the permanent table.
--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Here's one way to do this:

1) Create a table (name it tblTempImport) that can hold the imported data
from the EXCEL file (we'll use this as a temporary holding place for the
data).
2) Create a form that has a textbox and a command button on it.
3) Name the textbox txtDate and the button cmdImport.
4) Set format of the textbox to Short Date.
5) Use this programming code for your command button's Click event (Code
assumes that the name of the permanent table is tblPermanent, and assumes
that you have a fixed path and file name for the EXCEL file):

' START of code
Private Sub cmdImport_CLick()
Dim dbs As DAO.Database
Dim datDate As Date
Dim strSQL As String
Const strImport As String = "tblImportData"

' change tblPermanent to real name of permanent table
Const strTable As String = "tblPermanent"

' change string to actual path and file name of EXCEL file
Const strFile As String = "C:\MyFolder\EXCELFile.xls"

On Error GoTo Err_Msg
If Len(Me.txtDate.Value & "") = 0 Then
MsgBox "Enter a date!"
Else
Set dbs = CurrentDb()
strSQL = "DELETE * FROM tblImportData;"
dbs.Execute strSQL, dbFailOnError

' change True to False if the EXCEL file's spreadsheet
' DOES NOT have headers in first row
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, _
strImport, strFile, True

datDate = Me.txtDate.Value
strSQL = "INSERT INTO [" & strTable & "] " & _
"SELECT T.*, #" & Format(datDate, "mm\/dd\/yyyy") & _
"# FROM [" & strImport & "];"
dbs.Execute strSQL, dbFailOnError
End If

Exit_Code:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Msg:
MsgBox "Error " & Err.Number & " has occurred: '" & Err.Description & "'."
Resume Exit_Code
End Sub
' END of code


Now, you can open this form, put the date in the textbox, and click the
button to import the data.

--

Ken Snell
<MS ACCESS MVP>

Tandy said:
Sorry! I didn't mean to be vague. Currently I'm in the beginning of
making
this database. So I have just been importing the data from an Excel file
directly into the Access table using the wizard. The Excel file
basically
has the two field names, EmployeeID and MonthlyHours, and then all the
information. I would love to have a form where the user just enters the
date, clicks a button and it's done. But I don't have any experience
with
importing and such. So any help will be greatly appreciated!!!

Tandy
Click to expand...
Click to expand...
 
Back
Top