Advertisement

09.04.2008 at 10:41PM PDT, ID: 23705223
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.3

export single record data from excel worksheeet (using as frontend) append into access table

Asked by Rajnish_Sharma_ibm in Microsoft Excel Spreadsheet Software, Automation

Tags: , ,

i have a works heet in excel which act as a front end and act as a user interface, i want that as the user enter the data and press submit button ( which is presently on click appendind the data in excel sheet ) the data must be appended into the designated table in the access , i have little knowledge in access please provide complete code on this topicStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:

Sub AddNew()
 
    'Update data on Sheet1 based on new exception entered on Sheet2
 
    Dim Emplid  As String
    Dim Emplname As String
    Dim TLname As String
    Dim HH As Integer
    Dim MM As Integer
    Dim Fmm As Integer
    Dim Exception As String
    Dim Reason As String
    Dim Exptime As Date
    Dim LRow As Long
    Dim LFound As Boolean
    Dim Lnum As Integer
    Dim Edate As Date
    
B:    Sheets("New entry").Select
    'Before adding new customer, make sure a value was entered
    If IsEmpty(Range("I9").Value) = False Then
 
        'Retrieve new information
        Emplid = Range("I9").Value
        Emplname = Range("I11").Value
        TLname = Range("I13").Value
        Exception = Range("E11").Value
        Reason = Range("E17").Value
        HH = Range("E13").Value
        MM = Range("E14").Value
        Fmm = HH * 60 + MM
        Exptime = HH & ":" & MM
        Edate = Date
        Application.ScreenUpdating = False
             
 
        'Move to Sheet1 to save the changes
        Sheets("RAWDATA").Visible = -1
        Sheets("RAWDATA").Select
        Sheets("RAWDATA").Unprotect
        
        LFound = False
 
        LRow = 2
 
        Do While LFound = False
 
            'Encountered a blank project number (assuming end of list on Sheet1)
            If IsEmpty(Range("A" & LRow).Value) = True Then
                LFound = True
            End If
 
            LRow = LRow + 1
        Loop
 
        Range("A" & LRow - 1).Value = Lnum
        Range("c" & LRow - 1).Value = TLname
        Range("D" & LRow - 1).Value = Emplid
        Range("E" & LRow - 1).Value = Emplname
        Range("F" & LRow - 1).Value = Exception
        Range("G" & LRow - 1).Value = Reason
        Range("H" & LRow - 1).Value = Fmm
        Range("B" & LRow - 1).Value = Edate
        
        
 
        'Reposition back on Sheet2
        'Sheets("Sheet2").Select
 
        'Update range for combo boxes
        'ActiveSheet.Shapes("Drop Down 3").Select
        'With Selection
           ' .ListFillRange = "Sheet1!$B$2:$B$" & LRow - 1
       ' End With
 
        'ActiveSheet.Shapes("Drop Down 8").Select
        'With Selection
          '  .ListFillRange = "Sheet1!$B$2:$B$" & LRow - 1
       'End With
 
        'Clear entries from cells
        With Sheets("new entry")
        .Range("I9").Value = ""
        .Range("E11").Value = ""
        .Range("E17").Value = ""
        .Range("E13").Value = ""
        .Range("E14").Value = ""
        End With
        Sheets("New entry").Select
        Range("I9").Select
        Application.DisplayAlerts = False
        Sheets("RAWDATA").Protect
        Sheets("RAWDATA").Visible = 0
        ActiveWorkbook.Save
        Application.ScreenUpdating = True
 
        Application.DisplayAlerts = True
        
        MsgBox ("New Exception was successfully added.")
        GoTo Z
    Else: GoTo A
    End If
       
    
A:     Application.ScreenUpdating = True
MsgBox ("Please fill required fields with *")
 
    
    
Z:  End Sub
Sub reset()
With Sheets("new entry")
        .Range("I9").Value = ""
        .Range("E11").Value = ""
        .Range("E17").Value = ""
        .Range("E13").Value = ""
        .Range("E14").Value = ""
        End With
End Sub
'this is the code which simply append data to Raw Data i have created the same table as raw data in MS access i want the same data to be append the the acess table too on the command button click press by the user on the  new entry worksheet.
 
alsoi want to add an auto num with the every record that is appended in access table
 
Keywords: export single record data from excel w…
Title
1 Appending files
2 Import external data and append to exi…
3 Append to a Litbox
4 Append Text to a file
5 Append Query
6 export single record data from excel w…
 
Loading Advertisement...
 
[+][-]09.05.2008 at 03:31AM PDT, ID: 22396940

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Excel Spreadsheet Software, Automation
Tags: Microsoft, Microsoft Excel and Microsoft Access, version 2003
Sign Up Now!
Solution Provided By: StellanRosengren
Participating Experts: 1
Solution Grade: A
 
 
[+][-]10.08.2008 at 12:37PM PDT, ID: 22672430

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.08.2008 at 03:44PM PDT, ID: 22674223

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-44 / EE_QW_2_20070628