Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2010
    Posts
    7

    Unanswered: Update Table From Form

    I am trying to make this database update from a form i have created.

    The form i am using within the database is called Booking.
    The macro should run and update the booking table from the values entered into the form. However the macro appears to run but doesnt add the information

    Any help would be apreciated
    B Duhig
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    Feb 2010
    Posts
    7
    Thanks for the help. Im struggling to get this to work and only have a very basic knowledge of access

    Would you be able to give me any further help?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sure, you're welcome!
    Have a nice day!

  5. #5
    Join Date
    Feb 2010
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    Sure, you're welcome!
    This kinda confused me. Would you be able to help?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'll probably be able to help. If for any reason I can't, someone else will certainly do. All you have to do is to post your questions here.
    Have a nice day!

  7. #7
    Join Date
    Feb 2010
    Posts
    7
    Im basically stuck..

    I need to get the booking form so that it can update from the set of drop down values although i am not sure how to do it..

    Like i said im not an expert on access and am out of idea on how to get this working

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to precisely know what you want to achieve, however you'll find an example of what can be done in the attached database.
    Attached Files Attached Files
    Have a nice day!

  9. #9
    Join Date
    Feb 2010
    Posts
    7
    That not exactly what i want.

    I need it so that when i select a customerID, course and booking ID it updates my booking table. This is the part i cannot do.

    If you look at the booking table you wil lsee it contains these three pieces of information. Once ive selected these three pieces of information i cannot get it to update them.

    THe only part that needs to be updated is the CustomerID placed in the table with the corresponding course and booking ID.

    Unless there are any better ways which dont involve it being changed too much

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can use the excellent example Sinndho provided. Just tweak it to make it so CustomerID is the first listbox, course and booking ID is the 2nd listbox (or add another listbox for just bookingID), and the booking table is the 3rd listbox. The concept is the same.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2010
    Posts
    7
    Hi sorry about the late reply. This is a project at school and we have been instructed to use a macro to get it to work. Our teacher however also doesnt understand how to get it working this way.

    Is it possible?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •