1. Your Update query cannot update anything because one part is missing and it does not specify how to update the Tbl_Booking table (the SET part of the query is missing):
Code:
UPDATE Tbl_Booking SET
WHERE (((Tbl_Booking.BookingID)=[Forms]![Booking]![BookingID]));
2. The Append query adds a row into the table and dos not update an existing row:
Code:
INSERT INTO Tbl_Booking ( CustomerID, Course, BookingID )
SELECT Forms!Booking!CustomerID AS Expr1, Forms!Booking!BookingID AS Expr2, Forms!Booking!Course AS Expr3;
Moreover there is no
Course field (as the form is unbound) nor any
Course control on the
Booking form, so this query cannot execute.
3. It would be more efficient to use VBA functions than macros. That way it would be more easy to debug and to maintain your application. Here is the Update macro re-written in VBA, you can immediately see that something is wrong:
Code:
Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
' Dim stDocName As String
'
' stDocName = "Update"
' DoCmd****nMacro stDocName
If Nz(Me.CustomerID, "") = "" Then
Beep
MsgBox "Please Enter a CustomerID"
ElseIf Nz(Me.CourseID, "") = "" Then
Beep
MsgBox "Please Enter a CourseID" ' The string part is missing in the macro.
ElseIf Nz(Me.BookingID, "") = "" Then
Beep
MsgBox "Please Select a BookingID"
ElseIf Me.BookingID = Me.BookingID Then ' ???
DoCmd.OpenQuery "Append", acViewNormal, acEdit
'
' ' Could be
' '
' Dim strSQL As String
' strSQL = "INSERT INTO Tbl_Booking ( CustomerID, Course, BookingID ) " & _
' "VALUES ( " & Me!CustomerID & ", " & Me!BookingID & ", " & Me!Course & " );" ' Me!Course does not exist!
' CurrentDb.Execute strSQL
'
Beep
MsgBox "Course Has Been Booked!"
End If
Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub
4. It's always a good idea to give significant names to your objects: controls, functions, queries, etc. Here you have a
Command23 command button that opens an
Update macro that conditionally opens an
Append query. If your project grows it will soon become very difficult to understand the logic of it.
dBforums
>
Update Table From Form
02-09-10, 06:18
Linear Mode
