Advertisement

05.13.2008 at 10:45PM PDT, ID: 23400374
[x]
Attachment Details

How do I update a record in sql database table using a form

Asked by sltech8 in Access Coding/Macros, Databases Miscellaneous, MS SQL Server

Tags: Microsoft, access, 2000, company server

I have an excel vb script which dumps data into a table on sql server. I am reasonable with vb so thats not the problem.
Now, I am creating forms in access 2000 to view those records and if necessary add extra info to empty cells of a particular record.  The form is Eng Work Done and already brings up the data from each record to view including the autonumber id.
I have added  textboxes to the form for more info. and already have the extra empty fields in the table to accept the extra information, for now the textboxes and fields are named engwd1, engtime1, engname1.

The original code from the excel vb which allowed the creation of the record is below, I have spent a couple of days searching this site (which I think is brilliant, by the way!!) but cannot seem to find anything close enough to my problem to allow me to find a solution.  

Many thanks in advance.


Start 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:

Private Sub CommandButton1_Click()
' record button
 
If CommandButton1 = OnAction Then
 
' variables
'Dim conn As ADODB.Connection
Dim sSQL As String
Dim lRecordsAffected As Long
 
Const EStoreman = "SL"            ' email address
 Const EEngineers = "Eng1"    ' email address
 Const ESupervisor = "GG"         ' email address
 
' Open DB Connection
' Initialize variables.
Dim cn As New ADODB.Connection
Dim provStr As String
 
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
 
' Specify connection string on Open method.
provStr = "Server=******;Database=Fault1;Trusted_Connection=yes"
cn.Open provStr
 
End If
 
If cn.State = adStateOpen Then
 
sSQL = "INSERT INTO Faultlog100 (Date,Machine,Equipment,Engineer,Engineer2,JobType,Fault,Repair,FurtherWork,Priority,Timeneeded,Partsused,Partsrequired,StartedHr,StartedMin,FinishedHr,FinishedMin) VALUES ('" & Format(Now, "YYYY-MM-DD") & "','" & TextBox9.Value & "','" & TextBox2.Value & "','" & ComboBox3.Value & "','" & ComboBox8.Value & "','" & ComboBox1.Value & "','" & TextBox1.Value & "','" & TextBox5.Value & "','" & ComboBox2.Value & "','" & ComboBox4.Value & "','" & ComboBox5.Value & "','" & TextBox6.Value & "','" & TextBox7.Value & "','" & ComboBox6.Value & "','" & ComboBox9.Value & "','" & ComboBox7.Value & "','" & ComboBox10.Value & "');"
                         
lRecordsAffected = ActionQuery(cn, sSQL)
MsgBox "Record Added to Dbase", vbOKOnly
 
 
cn.Close
 
 
End If
 
Set cn = Nothing
End Sub
 
Public Function ActionQuery(cn As ADODB.Connection, _
sSQL As String) As Long
 
Dim lRecordsAffected As Long
Dim cmd As ADODB.Command
lRecordsAffected = 0
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = cn
    .CommandText = sSQL
    .CommandType = adCmdText
    .Execute lRecordsAffected
End With
Set cmd = Nothing
exitpoint:
ActionQuery = lRecordsAffected
Exit Function
 
End Function
[+][-]05.13.2008 at 11:20PM PDT, ID: 21561636

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.13.2008 at 11:51PM PDT, ID: 21561733

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 12:59AM PDT, ID: 21561937

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 01:08AM PDT, ID: 21561984

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 02:21AM PDT, ID: 21562303

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 02:49AM PDT, ID: 21562434

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 03:04AM PDT, ID: 21562499

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 03:19AM PDT, ID: 21562571

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 03:54AM PDT, ID: 21562742

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 04:24AM PDT, ID: 21562929

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 04:54AM PDT, ID: 21563093

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 06:30AM PDT, ID: 21563910

View this solution now by starting your 7-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: Access Coding/Macros, Databases Miscellaneous, MS SQL Server
Tags: Microsoft, access, 2000, company server
Sign Up Now!
Solution Provided By: koutny
Participating Experts: 3
Solution Grade: A
 
 
[+][-]05.14.2008 at 06:34AM PDT, ID: 21563953

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 08:34AM PDT, ID: 21565318

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628