Results 1 to 11 of 11
  1. #1
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24

    Need VBA Wizard to review my code please

    Hi,

    I have the below code i am trying to use.

    I copied this exactly from a post on another forum, however it isn't working correctly. I'm not sure if the poster was wrong, or if i'm just useless.

    Im trying to have 2 text boxes save into a table when i press a 'completed' button. I'm then trying to get a check box called 'Completed' to change it's value and input the current date into 'Completion Date'.


    The code all looks right to me, but i get a syntax on 'CurrentDB.Execute (sql) error when i press the button, so i dont know if the rest of the code works.

    I should mention that i don't claim to be good at VBA, and kinda took a guess at the 'sql3' ad 'sql4' variables.

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

    Thanks

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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.

  3. #3
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Quote Originally Posted by John_G View Post
    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.
    Click image for larger version. 

Name:	Capture.jpg 
Views:	32 
Size:	71.4 KB 
ID:	31716
    Click image for larger version. 

Name:	accountstable.PNG 
Views:	32 
Size:	4.0 KB 
ID:	31715
    Click image for larger version. 

Name:	customerstable.PNG 
Views:	32 
Size:	7.1 KB 
ID:	31717
    Click image for larger version. 

Name:	Vehicletable.PNG 
Views:	32 
Size:	5.6 KB 
ID:	31713
    Click image for larger version. 

Name:	jobtable.jpg 
Views:	32 
Size:	11.5 KB 
ID:	31714


    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

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    OK - I see that the combo boxes are used to select a record to be updated - the searchforrecord does that.

    Now, once that record (from the Jobs table) is on the screen, any updates you make on the form (to form controls that are bound to table fields) are automatically saved to the table as soon as you a) move to another record, or b) close the form or c) use me.dirty = false or d) use DoCmd.RunCommand acCmdSaveRecord. (I might have missed some cases there).

    So, to update the record on the screen (the current record) you don't need any SQL at all.

    These two lines: Me.txtVehicleNotes.Requery and Me.txtRequiredRepairs.Requery are not needed - those are textboxes, so requery is meaningless.

    Are the form controls Text103 and Text105 bound to any table fields?
    Why do you refer to those two fields in cboBookingDate_AfterUpdate? It seems to me they won't have any values yet, especially if they are unbound.

    This statement:

    sql2 = "INSERT INTO Vehicles ([Vehicle Notes]) VALUES ('" & Notes & "') WHERE [Vehicle].[Vehicle Number] = " & NUMBER & ";"

    I assume with that you are updating the [Vehicle Notes] field in the record for that particular vehicle in the vehicles table.

    Your SQL for that would then look like this:

    sql2 = "Update Vehicles set [Vehicle Notes] = '" & Notes & "') WHERE [Vehicle Number] = " & NUMBER
    (terminating ";" is not required)

    There is a problem with that though - every time there is work done on that vehicle, the [Vehicle Notes] will be replaced with a new value - older notes will be lost, meaning you will have no service history for that vehicle (at least not in the vehicles table). However, if those notes are in the Jobs table as well, then you don't need that field in the Vehicles table at all.
    From looking at your screen capture, the fields Odometer and [Required Repairs] should not be in the vehicles table either. Both of them can occur multiple times for any one vehicle (along with [Vehicle Notes]). You should perhaps be looking at a new table VehicleService to contain the service records for each vehicle.

    I don't think you need the "Complete" button and its code at all - the "Submit" button saves the information for you.

    These are some issues I can see right now - there will surely be others that crop up. Let us know if you need more help.
    Last edited by John_G; 12-19-2017 at 02:50 PM. Reason: rearrange the text

  5. #5
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Quote Originally Posted by John_G View Post
    Are the form controls Text103 and Text105 bound to any table fields?
    Why do you refer to those two fields in cboBookingDate_AfterUpdate? It seems to me they won't have any values yet, especially if they are unbound.
    Those two boxes are performing a dlookup. They are currently hidden in the footer of the form.
    As you cannot edit a dlookup box, i have them pass their results (which occurs after the update of the cboBookingDate) to the other, unbound text boxes.
    I have tested, and if those text boxes are bound, then they will not receive the information via code - thus needed a button to save the information.


    Code:
    There is a problem with that though - every time there is work done on that vehicle, the [Vehicle Notes] will be replaced with a new value - older notes will be lost, meaning you will have no service history for that vehicle (at least not in the vehicles table)
    This is how it's designed. 'Vehicle Notes' are meant for something specific to the vehicle, such as part numbers, or noticed damage.

    Code:
    From looking at your screen capture, the fields Odometer and [Required Repairs] should not be in the vehicles table either.
    Required repairs is used for anything that is noted by the mechanic that is required for the next time the vehicle comes in, such a brake pads, tyres or oil leaks - it just notes it when the customer calls for their next repair.
    The odometer is something that slipped my mind, i'll add this into the Jobs table as well and force the save to both tables with the code.


    Code:
    I don't think you need the "Complete" button and its code at all - the "Submit" button saves the information for you.
    The 'Submit' button is for a visual 'save' button - some of the guys using this are older and wont have peace of mind if they can't 'see' it save (disappear off the screen after pressing a save button).
    The 'Complete' button needs to add a 'True' or 'Yes' value to a check box in the table (Completed?) and the date the button is pressed (Completion Date).

    There may be cases where a job is left, but is not completed. i.e Lunch break, ordering parts, priority job comes in.

    Thank you for your help, i'll edit the code and be rid of the requeries and change to the correct syntax for saving the data.

  6. #6
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    So i fixed up the code that you pointed out, but now when i run it i get an error.

    Run-Time Error '3061':
    Too few parameters. Expected 1.

    when i hit Debug, it highlights 'Currentdb.Execute (sql)'


    Code:
    sql = "Update Jobs set [Required Repairs] = '" & Repairs & "' WHERE [JobNumber] " & ID

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    That indicates that either [Required Repairs] or [JobNumber] is not a field name in the Jobs table.

  8. #8
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Wow, i mustn't have gotten enough sleep last night.

    Required Repairs is in the Vehicle table. Derp

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Try using True for the checkbox value, instead of Yes.

  10. #10
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Quote Originally Posted by John_G View Post
    Try using True for the checkbox value, instead of Yes.
    Replaced it with 1 and it worked as well.

    My whole form is working perfectly now. Thank you for all of your help!

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Zombai View Post
    Replaced it with 1 and it worked as well.
    Just an FYI.....

    In computing, FALSE is defined as 0 (zero) and TRUE is defined and NOT FALSE.
    In Excel, Microsoft has defined constants FALSE as equal to 0 (zero) and TRUE equal to 1 (one).
    But in Access, Microsoft has defined constants FALSE as equal to 0 (zero) and TRUE equal to -1 (minus one).

    Because Microsoft has defined these two constants, they (MS) can change the values for TRUE/FALSE at any time..

    I agree with Joe, you should use FALSE. (it is safer).

    Look at the attached dB. The check box control source is the same as the text box on the left.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-16-2016, 12:55 AM
  2. Replies: 2
    Last Post: 09-01-2015, 02:57 AM
  3. Wizard - Create code Code vs. Macros?
    By runbear in forum Forms
    Replies: 3
    Last Post: 01-08-2014, 12:52 PM
  4. Replies: 4
    Last Post: 05-07-2013, 11:48 AM
  5. Can somebody review the code in attached database?
    By A Abbas in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 04:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums