Originally Posted by
John_G
That code is completely wrong if you are trying to update an existing record in the Jobs table. The SQL statement "Insert into" does not put a value into a field or form control. It adds a whole new record to the specified table. The code shown will add three new (and probably useless) records to the Jobs table, and one record to Vehicles (and I'm quite sure that is NOT what you want to do). I say the records are probably useless because each contains only one field with a value in it.
If you are going to use SQL to update records, you use the SQL UPDATE statement, not INSERT.
Now, I'll back up a step and ask about your form - what is its record source, i.e. what table is it bound to, if any? I can better answer your question if you post that information.
Hi John,
Thank you for your prompt reply and assistance.
You're right, i definitely do not want useless records.
The form is tricky. It is bound to the Jobs table, but has combo boxes filtering information and dlookups to pick specific information.
The text boxes im trying to save have their input coded into them.
See the screenshots below, and the code snippet of the entire form. Note that i have converted macros to VBA for added functionality.
Code:
Option Compare Database
Private Sub cboAccount_AfterUpdate()
Dim SDateSource As String
SDateSource = "SELECT [Jobs].[JobNumber]," & _
" [Jobs].[Booking Date] " & _
"From [Jobs] " & _
"WHERE [Account Number] = " & Me.cboAccount.Value
If Me.Dirty Then
Me.Dirty = False
End If
Me.cboBookingDate.RowSource = SDateSource
Me.cboBookingDate.Requery
End Sub
'------------------------------------------------------------
' cboBookingDate_AfterUpdate
'
'------------------------------------------------------------
Private Sub cboBookingDate_AfterUpdate()
On Error GoTo cboBookingDate_AfterUpdate_Err
DoCmd.SearchForRecord , "", acFirst, "[JobNumber] = " & Str(Nz(Screen.ActiveControl, 0))
Me.txtVehicleNotes = Me.Text103
Me.txtVehicleNotes.Requery
Me.txtRequiredRepairs = Me.Text105
Me.txtRequiredRepairs.Requery
cboBookingDate_AfterUpdate_Exit:
Exit Sub
cboBookingDate_AfterUpdate_Err:
MsgBox Error$
Resume cboBookingDate_AfterUpdate_Exit
End Sub
'------------------------------------------------------------
' btnExit_Click
'
'------------------------------------------------------------
Private Sub btnExit_Click()
On Error GoTo btnExit_Click_Err
DoCmd.Close , ""
btnExit_Click_Exit:
Exit Sub
btnExit_Click_Err:
MsgBox Error$
Resume btnExit_Click_Exit
End Sub
'------------------------------------------------------------
' btnSubmit_Click
'
'------------------------------------------------------------
Private Sub btnSubmit_Click()
On Error GoTo btnSubmit_Click_Err
' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
' <UserInterfaceMacro For="btnExit" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterfaceMacro>
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
btnSubmit_Click_Exit:
Exit Sub
btnSubmit_Click_Err:
MsgBox Error$
Resume btnSubmit_Click_Exit
End Sub
Private Sub btnComplete_Click()
Dim sql As String
Dim sql2 As String
Dim sql3 As String
Dim sql4 As String
Dim ID As Integer
Dim NUMBER As Integer
Dim Repairs As String
Dim Notes As String
ID = Me.JobNumber.Value
NUMBER = Me.txtVehicleNumber.Value
Repairs = Me.txtRequiredRepairs.Value
Notes = Me.txtVehicleNotes.Value
sql = "INSERT INTO Jobs ([Required Repairs]) VALUES ('" & Repairs & "') WHERE [Jobs].[JobNumber] = " & ID & ";"
sql2 = "INSERT INTO Vehicles ([Vehicle Notes]) VALUES ('" & Notes & "') WHERE [Vehicle].[Vehicle Number] = " & NUMBER & ";"
sql3 = "INSERT INTO Jobs ([Completed?]) VALUES (YES)"
sql4 = "INSERT INTO Jobs ([Completion Date]) VALUES (Date())"
CurrentDb.Execute (sql)
CurrentDb.Execute (sql2)
CurrentDb.Execute (sql3)
CurrentDb.Execute (sql4)
End Sub
If you need anything more, don't hesitate to ask