how to Ignore [Enter your text here] if field is already populated

[hightekkrednek]

Good afternoon. I am working with an update query, one field being updated with an [Enter your text here] input box. I would like, if possible, for this to bypass the [Enter your text here] prompt if there is already text populated in that field, otherwise prompt the user with the [Enter your text here] input box if the field in that record is null. Btw, for what it is worth, this field is for comments/notes. Thank you.

[ranman256]

move down to the criteria of that column, set to:
IS NULL

then it wont update items already filled.

[kd2017]

The easiest way would be to simply copy this query and create a second query that omits the update to str_commentsNotes. Then in the button click event (assuming your using the command button from previous threads) you would have vba like

If IsNull(Me!str_commentsNotes) Or Me!str_commentsNotes = "" Then
   'call query with update to str_commentsNotes here:

Else
   'call query without update to str_commentsNotes here:

End if

Again this is based on what you already have and specifically asked for in that *other* thread. But you don't really need to call an update query at all. Instead you could do

Dim truckNumber as Variant
Dim userComments As Variant
truckNumber = InputBox("Truck Number?")

'do some input validation here

If IsNull( Me!str_commentsNotes ) Or Me!str_commentsNotes = "" Then
    userComments = InputBox("Enter Comments/Notes:")
    'possible do some input validation here too

End If

'Update the fields in the current row of the form's recordset:
Me!str_truckNumber = truckNumber
Me!str_commentsNotes = userComments
Me!bool_isComplete = True
Me!dtm_completedTimeStamp = Now()

'Explicitly save the changes to the record
Me.Dirty = False

[kd2017]

move down to the criteria of that column, set to:
IS NULL

then it wont update items already filled.

I believe op still would like to update the other fields in that record, in other words they are looking to effectively implement an optional parameter.

[hightekkrednek]

I tried the IS NULL method in the criteria, it returns no rows to update. thank you for the input though, very much appreciated!!

[hightekkrednek]

KD2017, I will try that, this looks like it will do what I want without the qry. I hope I get to mess with this stuff to pickup on things even half as much as you seem to grasp. Thank you again!

[hightekkrednek]

KD2017 I am getting a runtime error 2448 when it tries to update a record.. stating i cant assign a value to this object. Ive removed references to running the query when i click the button. Now when i click the button(record having comments), it asks for truck load.. i enter any text, confirm and it throws the runtime error. If i select a load that has no comments.. it requests the truck number, i enter text, confirm, then it requests comments/notes.. i enter text, confirm.. then same error with debug highlighting the same line below yellow. any idea? i have modified that line any which way i know how and same thing or different errors. Even crashed it with one of my changes where i couldnt even view my vb code at all, had an Automation Error of some kind.. had to scrap that db and pull my backup(which i have for every change right now). lol


Me!str_truckNumber = truckNumber


Below is exactly what I have now in the code

Private Sub btn_completeLoad_Click()
'DoCmd.OpenQuery "qry_completeLoad"
'Me.Combo21.Requery
'DoCmd.Close
'DoCmd.OpenForm "frm_currentLoads"

Dim truckNumber As Variant
Dim userComments As Variant
truckNumber = InputBox("Truck Number?")


'do some input validation here


If IsNull(Me!str_commentsNotes) Or Me!str_commentsNotes = "" Then
userComments = InputBox("Enter Comments/Notes:")
'possible do some input validation here too


End If


'Update the fields in the current row of the form's recordset:
Me!str_truckNumber = truckNumber
Me!str_commentsNotes = userComments
Me!bool_isComplete = True
Me!dtm_completedTimeStamp = Now()


'Explicitly save the changes to the record
Me.Dirty = False



End Sub

[kd2017]

Are the str_truckNumber, str_commentsNotes, bool_isComplete, and dtm_completedTimeStamp fields all exposed in the forms recordset/data source?

Can you zip and attach your db?

Finally, are you saying you're having a lot of corruption issues?

[hightekkrednek]

The fields all appear to be available to the form.. but part of the form is pulling from qry and the other part from the tbl i think.

The corruption issue was user error, i put something in the vb that flipped it out.. ignorance knows no bounds.

Attaching zip Silo9Loads .zip

[hightekkrednek]

I figured out the runtime error. I had my form record type set to Snapshot instead of dynaset. But now when I complete a load, if there is a comment/note already in that record, it doesnt ask for comment/note.. but still overwrites it to be blank when the update qry_completeLoad completes. gonna try to figure it out but if anyone has any ideas, much appreciated. thanks!

[kd2017]

Oh, sorry. My mistake.

Move this line
Me!str_commentsNotes = userComments
To inside the if statement just before the End If line.

[hightekkrednek]

That actually makes perfect sense.. thank you!

[hightekkrednek]

The original issue and any encountered since have been solved. Thank you again kd2017, you are gnarly! Your help has not only solved my specific issues but assisted me in understanding and knowing what to research to find my own solutions to other issues. Folks like you(and the many others) are what makes a forum like this successful.

[kd2017]

Happy to help. Good luck with your project!