Join 132,362 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,260 people online right now. Registration is fast and FREE... Join Now!
Once again, I'm faced with an Access challenge. Well, it's more like establishing a shortcut route rather than stuck at a roadblock.
I have a form designed for data entry into a table. My client provides me the data in, let's say MS Excel format. I know how to manually upload this data- 1- Export Table into MS Excel 2- Delete all the data in the Excel sheet (making not of the last ID #), while maintaining the same structure 4- Set up the client-provided data in the Excel sheet, starting at the new ID#, while maintaing the same structure 5- Import the data into the table.
Is there a way I can implement the above algorithm in a command button on my data-entry form in Access? Or at least perform most of the 5 functions?
The other thing I wanted to check out is the Inputbox() procedure in VBA form coding. Everytime I hit cancel, it continues to run the procedure I have set up with a null value. Below is the code I have implemented. It is a search function for editing purposes. If you can think of an easier or shorter way of coding this, please advise. Thanks Note that the 'package no' field is a 3digit text field (from 000-999)
Dim num, A, B As String num = InputBox("Enter the Package #", "Edit Package") If Len(num) > 3 Then A = MsgBox("Invalid Package No.", vbCritical, "Input Error") = vbOK Exit Sub ElseIf Len(num) = 2 Then num = "0" & num 'padding the input to 3 digits ElseIf Len(num) = 1 Then num = "00" & num 'padding the input to 3 digits End If DoCmd.GoToRecord , , acLast B = txtpkgid.Value DoCmd.GoToRecord , , acFirst A = 0 Do Until txtpkg_no.Value = num If txtpkgid.Value = B Then 'Exiting loop if at the last record If txtpkgid.Value <> num Then A = MsgBox("Invalid Package No.", vbCritical, "Input Error") = vbOK A = 1 Exit Sub End If End If DoCmd.GoToRecord , , acNext Loop
If A = 0 Then txtdate_opened.Enabled = True txtdate_closed.Enabled = True cmbjob_code.Enabled = True txtcomments.Enabled = True txtpkg_desc.Enabled = True cmbpkg_type.Enabled = True Exit Sub End If
Is there a way I can implement the above algorithm in a command button on my data-entry form in Access? Or at least perform most of the 5 functions?
Well, here's a generic Import procedure I use. It doesn't name the fields on the table (it's designed to import into an existing blank table), but it should be enough to at least get you started.
CODE
Function ImportFileIntoTable(PathAndFile As String, TableName As String) As String 'On Error GoTo ErrorHandle
Dim Counter As Integer Dim x As Integer Dim rs As DAO.Recordset Dim Item_Type As String Dim CurLine As String Dim CurItem As String
'INITIATE THE TYPE AS HEADER FOR HEADER INFORMATION. Item_Type = "Header"
' Make sure that there's a file path If Len(Dir(PathAndFile)) = 0 Then ImportFileIntoTable = "Error: Could not locate path and file" Exit Function End If
' initialize variables DoCmd.SetWarnings (False)
' Open the file as read-only and read the first line to get the field names out of the way Open PathAndFile For Input As 1 Line Input #1, CurLine
If Left(TableName, 1) = "[" Then TableName = Right(TableName, Len(TableName) - 1) If Right(TableName, 1) = "]" Then TableName = Left(TableName, Len(TableName) - 1)
' Open the current database Set rs = CurrentDb.OpenRecordset(TableName, dbOpenTable)
' Loop through the whole file While Not EOF(1) Line Input #1, CurLine ' get the current line CurItem = GenGetLineData(",", 0, CurLine) ' check the line If Len(CurItem) > 0 Then ' if the field is not empty... rs.AddNew For x = 0 To (rs.Fields.Count - 4) CurItem = GenGetLineData(",", x, CurLine) CurItem = Trim(CurItem) If Len(CurItem) > 0 Then rs.Fields(x).Value = CurItem Else rs.Fields(x).Value = " " End If Next
rs.Update Counter = Counter + 1 DoEvents End If Wend Close 1 ImportFileIntoTable = CStr(Counter) Exit Function
Once again, I'm faced with an Access challenge. Well, it's more like establishing a shortcut route rather than stuck at a roadblock.
I have a form designed for data entry into a table. My client provides me the data in, let's say MS Excel format. I know how to manually upload this data- 1- Export Table into MS Excel 2- Delete all the data in the Excel sheet (making not of the last ID #), while maintaining the same structure 4- Set up the client-provided data in the Excel sheet, starting at the new ID#, while maintaing the same structure 5- Import the data into the table.
Is there a way I can implement the above algorithm in a command button on my data-entry form in Access? Or at least perform most of the 5 functions?
The other thing I wanted to check out is the Inputbox() procedure in VBA form coding. Everytime I hit cancel, it continues to run the procedure I have set up with a null value. Below is the code I have implemented. It is a search function for editing purposes. If you can think of an easier or shorter way of coding this, please advise. Thanks Note that the 'package no' field is a 3digit text field (from 000-999)
Dim num, A, B As String num = InputBox("Enter the Package #", "Edit Package") If Len(num) > 3 Then A = MsgBox("Invalid Package No.", vbCritical, "Input Error") = vbOK Exit Sub ElseIf Len(num) = 2 Then num = "0" & num 'padding the input to 3 digits ElseIf Len(num) = 1 Then num = "00" & num 'padding the input to 3 digits End If DoCmd.GoToRecord , , acLast B = txtpkgid.Value DoCmd.GoToRecord , , acFirst A = 0 Do Until txtpkg_no.Value = num If txtpkgid.Value = B Then 'Exiting loop if at the last record If txtpkgid.Value <> num Then A = MsgBox("Invalid Package No.", vbCritical, "Input Error") = vbOK A = 1 Exit Sub End If End If DoCmd.GoToRecord , , acNext Loop
If A = 0 Then txtdate_opened.Enabled = True txtdate_closed.Enabled = True cmbjob_code.Enabled = True txtcomments.Enabled = True txtpkg_desc.Enabled = True cmbpkg_type.Enabled = True Exit Sub End If
Thanks jjsaw. A start is all I needed. Just to confirm- this will only import data into an existing but empty table. (Might have to create an append or update table)
Thanks jjsaw. A start is all I needed. Just to confirm- this will only import data into an existing but empty table. (Might have to create an append or update table)
Correct. If the table format isn't going to change, you can add the following line at the beginning of the function:
CODE
DoCmd.RunSQL ("DELETE TableName.* FROM TableName")
25 Sep, 2008 - 11:46 AM
| 