Form Fields Data Retention

[rdenn_58]

I have a data entry form with multiple fields. I have made a macro in the form Before Update (if/else if) to give a message if data for a field is left blank. That works great.

Now the problem. If a field is left blank and a message box appears, the user presses the OK button for the message box. All the fields in the form then go blank as if the form refreshes. I would like the fields to retain the data that was already entered for that record and the cursor to go to the blank field or, in the case of multiple blank fields, go to the first blank field.

Thanks in advance for the help!!

[pbaldy]

In VBA code it looks like this:

http://www.baldyweb.com/BeforeUpdate.htm

i don't use macros, but I assume you're missing the Cancel = True bit, or its macro equivalent.

[rdenn_58]

Thanks for the quick reply, P!

I went ahead and deleted the macro I had in the form Before Update and added your code as an event procedure. I tested it and it brought the messages up just fine for a blank field. But after selecting OK in the message box, the form still seemed to refresh with all the fields blank instead of keeping the original data. By the way, there was no new record created which is great too! I have listed the code I used in the form Before Update as I altered it from yours. I am sure this could have been cleaned up and worked more efficiently.......or am I going in the right direction?

'------------------------------------------------------------
' Form_BeforeUpdate
'
'------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err


If Len(Me.CDNumber & vbNullString) = 0 Then
MsgBox "CD NUMBER CANNOT BE BLANK"
Cancel = True
Me.CDNumber.SetFocus
End If


If Len(Me.Location & vbNullString) = 0 Then
MsgBox "LOCATION CANNOT BE BLANK"
Cancel = True
Me.Location.SetFocus
End If


If Len(Me.Manufacturer & vbNullString) = 0 Then
MsgBox "MANUFACTURER CANNOT BE BLANK"
Cancel = True
Me.Manufacturer.SetFocus
End If


If Len(Me.Description & vbNullString) = 0 Then
MsgBox "DESCRIPTION CANNOT BE BLANK"
Cancel = True
Me.Description.SetFocus
End If


If Len(Me.Model & vbNullString) = 0 Then
MsgBox "MODEL CANNOT BE BLANK"
Cancel = True
Me.Model.SetFocus
End If


If Len(Me.SerialNumber & vbNullString) = 0 Then
MsgBox "SERIAL NUMBER CANNOT BE BLANK"
Cancel = True
Me.SerialNumber.SetFocus
End If


If Len(Me.Cost & vbNullString) = 0 Then
MsgBox "COST CANNOT BE BLANK"
Cancel = True
Me.Cost.SetFocus
End If


If Len(Me.AcquisitionDate & vbNullString) = 0 Then
MsgBox "ACQUISITION DATE CANNOT BE BLANK"
Cancel = True
Me.AcquisitionDate.SetFocus
End If



I am pretty new to Access and especially writing code. I do appreciate the help!

[pbaldy]

I assume there's more, or that would generate a compile error. That's on the right track, but I'd tweak it depending on how you wanted it to work if there are more than one empty. At the simplest, I'd add

Exit Sub

to the end if each test, like:

If Len(Me.CDNumber & vbNullString) = 0 Then
   MsgBox "CD NUMBER CANNOT BE BLANK"
   Cancel = True
   Me.CDNumber.SetFocus
   Exit Sub
End If

[rdenn_58]

Thanks, Paul! This is working as advertised except the other fields on the form are not holding the data after selecting OK in the message box. All the fields are still blank as if the form refreshed. What needs to be added to keep the other fields data on the form?

Here's an example of what's happening......I put in data in all fields, but left the Serial Number field blank. When I select a command SAVE button, the message box displays "SERIAL NUMBER CANNOT BE BLANK". Perfect so far! I click the message box OK button. The form fields all return to being blank and all the data has to be entered again. Hopefully you can point me in the direction to keep the previous data on the form prior to the message box.

Thanks again for your help!

[pbaldy]

Can you post the db here? The Cancel = True should stop the update and leave the values on the form.

[rdenn_58]

Property 2013 Copy_Backup1.zip

Here it is, Paul. The form is AddAssetF. The database opens to the Main Menu and selecting Add CD gets you to that form.

[pbaldy]

I think the code in your save button is overriding things. Try with just this in there:

DoCmd.GoToRecord , , acNewRec

[rdenn_58]

Thanks, Paul! That took care of it. I commented out the unnecessary error messaging and closing/reopening the form commands. I think I looked at that button quite a few times and never saw the error messaging duplication or form reloading until now. It just needed another set of eyes on it......and expert eyes to boot.

In making my first database, I have learned a lot. While creating this, I have Google'd many times when I got stuck. A lot of hits I got back, you had replied to so many others with easy to follow solutions. I used quite a few of your solutions you provided for others to resolve issues or better my own database. For those new to Access like myself, we appreciate your time and assistance. Thanks again for your help!!!!! I may put you on forum speed dial. Haa.

[pbaldy]

Happy to help, and thanks!