Help with INSERT/UPDATE on unbound form.

[sjd]

I am working on a time ticket database and am having trouble with the form / VBScript. I wanted to make a 'smart' form (my first mistake :banghead, but I am having a lot of trouble for something that should be really simple.

I've attached a zip archive including a picture of the table, and the form for reference.

What I am trying to do:
UPDATE table WHERE job#, Department#, Operation#, Employee# are the same as the form values; AND there is no entry in the stop {date/time} field. [Implying that this is still an open job ticket, and we are now closing said job ticket.] That last bit is important since multiple tickets are acceptable (ex: starting a job Monday and finishing Tuesday is 2 [or more] records; 1 per start/stop.)

Current problems:
I can't get the stop {date/time} field to filter for blank/empty/null entries. If I have ... WHERE ... AND ([stop]=NULL OR [stop]=''); It simply skips all entries and inserts a new record. Without that line it finds the last record and updates it correctly, but this is incorrect. It updates previously closed entries instead of opening new ones.

EDIT: I solved this by making sure the table stop field defaults to NULL; and changing the Query to [stop] IS NULL; I've updated the code below to reflect the fixes so anyone searching in the future can have a reference to a functional insert/update structure -- ugly as it may be.

Other Issues:
I tried putting in "Job's Finished" for comment and BOOM Debug Window. Is there a VBScript way to ensure entries with single/double quote craziness does not explode the database?

Edit: Fixed this by using the VBScript Replace function

Side Note:
Is there a cleaner way to code this? It's pretty ugly, but the only way I knew of doing it.

OnClick Code: (Fixed Up as Noted Above)

Private Sub Button_Save_Click()

    Dim dbs As DAO.Database, sql As String, rCount As Integer
    Dim reasonTXT As String, notesTXT As String
    Set dbs = CurrentDb
    
    ' fix string data
    If Not IsNull([reason].Value) Then
        reasonTXT = Replace(Replace([reason].Value, "'", "''"), "chr(34)", "chr(34)chr(34)")
    Else
        reasonTXT = ""
    End If
    
    If Not IsNull([notes].Value) Then
        notesTXT = Replace(Replace([notes].Value, "'", "''"), "chr(34)", "chr(34)chr(34)")
    Else
        notesTXT = ""
    End If
    
    
    ' Verify data integrity
    If False Then
        ' Place holder for checks
    
    ' All is good, so run query
    Else
        ' Try to UPDATE values
        ' -- Consider adding check for comments/notes exist before overwriting them.
        ' -- Alternatively, make those fields only for closing jobs.
        sql = "UPDATE [JOB_TICKET] SET "
        sql = sql & "made=" & [made].Value
        sql = sql & ", scrap=" & [scrap].Value
        sql = sql & ", reason='" & reasonTXT
        sql = sql & "', notes='" & notesTXT
        sql = sql & "', stop='" & Now() & "'"
        sql = sql & " WHERE [job_id]=" & [Job].Value
        sql = sql & " AND [department_id]=" & [Department].Value
        sql = sql & " AND [operation_id]=" & [Operation].Value
        sql = sql & " AND [employee_id]=" & [Employee].Value
        sql = sql & " AND [stop] IS NULL;"
        
        ' Debug Message Box
        'MsgBox (sql)
        
        ' Run SQL
        dbs.Execute sql, dbFailOnError
        rCount = dbs.RecordsAffected
        If rCount > 0 Then
            ' Update Successful, Reset the form
        Else
            ' No Fields Updated, Insert new data
            sql = " INSERT INTO [JOB_TICKET]"
            sql = sql & " (job_id, department_id, operation_id, employee_id, made, scrap, reason, notes, start) VALUES("
            sql = sql & [Job].Value & ", "
            sql = sql & [Department].Value & ", "
            sql = sql & [Operation].Value & ", "
            sql = sql & [Employee].Value & ", "
            sql = sql & [made].Value & ", "
            sql = sql & [scrap].Value & ", '"
            sql = sql & reasonTXT & "', '"
            sql = sql & notesTXT & "', '"
            sql = sql & Now() & "');"
            
            ' Debug Message Box
            'MsgBox (sql)
            
            dbs.Execute sql, dbFailOnError
            rCount = dbs.RecordsAffected
            If rCount > 0 Then
                ' Success -  reset form
            Else
                ' Failure - print message
                MsgBox ("Save Error - Please verify the data is correct and try again.  If another failure occurs, please contact the system administrator or assistance.")
            End If
            
            
        End If
    End If
    
End Sub

 

[spikepl]

NULL is not a value, so you cannot test something=NULL or something <>NULL

In SQL the test is MyField IS NULL or MyField NOT IS NULL. A DateTime field either contains a Date or is NULL, so it can never contain a "".

In VBA you can test IsNull(MyControl)

Why are you having an unbound form? This is throwing the baby out with the bathwater, and forcing you to do all the code that does what Access would otherwise do by itself, and would free you from dealing with "" and ' in SQL.

 

[sjd]

Thank you for that. I've gotten it to work now checking for nulls; and also allowing ' and " in the notes and scrap reasons. So the basic system will at least function.

I'm honestly not sure how to accomplish what I want using a bound form. I don't expect the employees to remember ticketID's; It makes more sense for them to scan in the Job/Dept/Op code right from the print they have, select their name -- and let the system sort it out from there.

Maybe there is a much simpler way with forms/subforms that are bound. I am just not familiar enough with Access to do them. If there is a better way to do what I was thinking with bound forms please point me in the right direction.

EDIT:
I have been working on this to see about making a bound form version. I am having some success now using 4 unbound drop downs for Job / Department / Operation / Employee -- and using a sub-form on a query with criteria = to the drop down boxes. Adding the IS NULL for stop forces it view the last open ticket (or create a new one). Seems that this will end up working after all.