Updating blank fields ??

[Dre313]

Hi,

I have a form which user pulls in records.. I also have a button to where the user can update the record.. This works fine as long as there is something being updated..

The problem is When i pull the record and say I want to delete whatever is in the field leaving it blank. then update it to have a blank field. That doesnt work.. When i clear out the field and hit update.. it says its updated but.. i go into my table and the field that I try to leave empty is still there.. I can update it as long as there is something replaced.. but if I leave it blank.. then it doesnt update...

heres the code

Private Sub Command27_Click()
Dim f1 As String
Dim f2 As String
Dim f3 As String
Dim strsql As String

Select Case MsgBox("Are you sure you would like to UPDATE?" & vbCrLf & vbLf & "  Yes:         Updates Record" & vbCrLf & "  No:          Does NOT Update Record" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Clicking UPDATE will ERASE all old information!")
        Case vbYes: 'Updates the changes
            ControlNo.SetFocus
            f1 = ControlNo.Text
            strProject.SetFocus
            f2 = strProject.Text
            strArea.SetFocus
            f3 = strArea.Text
            
            strsql = "UPDATE * WHERE tblQR.ControlNo = '" & f1 & "'"
            CurrentDb.Execute strsql
            
        
        Case vbNo: 'Do not delete or undo
            'Do nothing

        Case vbCancel: 'Undo the changes
            DoCmd.RunCommand acCmdUndo
            Me.tbProperSave.Value = "No"

        Case Else: 'Default case to trap any errors
            'Do nothing

    End Select
End Sub

thanks for the help

 

[ardan]

Access will not run an update query to all blank values,

I know a solution that will work but since it is work around it may not be welcomed in with a programming team. But if this is your own program they will work fine.

You can stick an extra space on the end of the variable holding the updated value

i.e. f3 = strArea.Text & " "

This gives access something to update, so it is happy and will
update. Problem is you will always have an extra space, you could use the trim() command to get rid on it later.......


ardan

End of Line

 

[JPMontreal]

Hi,

Did you try:
If IsEmpty(ControlNo.Text) Then
f1 = vbNull
Else
f1 = ControlNo.Text
End If



Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[Dre313]

Ardan,

I tired this on all fields.. and it works.. with the blank space after which I can deal with.. but i tried this with my date field.. and it gives me an error.. but if I put a date in.. then no error.. is there a way.. I can leave the date field empty..

These are inspections records.. and when a inspec. is closed then .. you can put a date on which day it wsa closed on.. but as of now.. it will not let me update a blank date field...

any ideas ??

thanks

 

[jjlogan]

If a field is not applicable for one entry situation, but is applicable for another situation, then consider using different forms for each occasion and include only fields that are applicable. Thus, you would need a main form where user selects what information he/she needs to enter. Based on this selection, the appropriate form is opened or shown. You can be creative, depending on the variability of the data entry scenarios.
Jeff