Require data entry; message on edit

CarlyS

Registered User.
Local time
Today, 02:35
Joined
Oct 9, 2004
Messages
115
I seem to be stuck in a tricky situation...
I have a subform where records can be added or edited. There are two fields: firstname and lastname.
I want a message box to appear when a first or last name is edited.
I do not want that message box to appear when a new name is being added. I use this code:

Private Sub FirstName_AfterUpdate()
If Me.NewRecord = False Then
MsgBox "Message.", vbOKOnly, "Name Change"
End If

End Sub

Now I want to also prevent the user from leaving a record before both fields have data (first and last name are required). My efforts to do this seem to conflict with my message box issues.

Can anyone see what I'm doing wrong or how to do this?
Thank you,
Carly
 
You need to put your code in the FORM's BeforeUpdate event. You would cancel the event if the data is incorrect or missing.

Code:
If Not IsNull(Me.FirstName) and Not IsNull(Me.LastName) Then
    If Me.NewRecord = False Then
        If MsgBox("Message.", vbYesNoCancel, "Name Change") = vbYes Then
        Else
            Cancel = True
            Msgbox "Update was cancelled", vbOKOnly
        End IF
Else
    MsgBox "Both First Name and Last Name are required", vbOKOnly
    Cancel = True
End If
 
Almost there...

Thank you for the reply Pat!
I think I need to explain a little more about what goes on with this form and simply what I am trying to do a bit. The code didn't quite work and I can't figure out how to make it work.

Each record has fields for first and last names and then a few command buttons that open other forms, going to the record matching that specific full name.

I am having trouble making the combination of the following things happen:

-I want to make sure they enter the first and last name before leaving the record or clicking the control buttons to open any other forms.
-I want to refresh after the full name has been added so they can click the command buttons and begin entering data on the other forms for the newly added student. Without refreshing the forms open to no name.
-After a new record is created (student is added) in the table, I want to disallow edits to the name.

How do I address these issues?
Thanks again for all the help!
Carly
 
-I want to make sure they enter the first and last name before leaving the record or clicking the control buttons to open any other forms.
-I want to refresh after the full name has been added so they can click the command buttons and begin entering data on the other forms for the newly added student. Without refreshing the forms open to no name.
Add one line of code to your button click events to save the record before opening the next form:

DoCmd.RunCommand acCmdSaveRecord

-After a new record is created (student is added) in the table, I want to disallow edits to the name.
That is not what you said originally. Which is it? Do not allow a name change or prompt before saving if they make one?

If your code is not working, post it. Include the procedure header so we know what event the code is in.
 
I originally was going to just have a message if the names were incomplete, but only because I got that working without pestering anyone, not because it's the best option. I tried preventing them from editing the name and the problem was, you could misspell a first name, start typing a last name and then not be allowed to fix the first name. This is why I gave up on preventing edits to the name. Now, nothing is working and I'm pestering you anyway. If there is a way to do this, disallowing them to change the name would be ideal.

There is a macro that creates a full name field which is used throughout the database. This is why I don't want them to change the name and I want the name complete before it runs or they proceed with any other forms. I would like to run this as soon as both names are complete, but before they open any other forms or create new records. I have probably been approaching this in a very round about way since I don't know much about code. Maybe these things should happen in the OnClick event of the command buttons similar to your suggestion:

Add one line of code to your button click events to save the record before opening the next form:
DoCmd.RunCommand acCmdSaveRecord


This is great-but is there some way to tell it not to save the record or proceed with opening the form if the first and last name are not complete? And then, if they are complete, save the record, run the query and proceed with opening forms???

Thank you so much for your patience--I am learning so much from everyone's help!
Carly
 
This is great-but is there some way to tell it not to save the record or proceed with opening the form if the first and last name are not complete?
That is the code I posted earlier. You need to place it in the FORM's BeforeUpdate event. But, you should also change the table definition so that both first and last name are required. Then if you don't mind seeing the Access message, you don't need any code at all. If you want your own message, you need to either use the code I posted, or add your code in the error trapping section.

If no data can be changed once a record is saved, change the form's AllowEdits property to NO. This will allow you to add new records, view existing records, and delete records but not change them. You can prevent deletes by setting the AllowDeletions property to No. If it is just those two fields that you don't want to be able to change, you need to add code to the FORM's Current event.

Code:
If Me.NewRecord Then
    Me.txtFirstName.Locked = False
    Me.txtLastName.Locked = False
Else
    Me.txtFirstName.Locked = True
    Me.txtLastName.Locked = True
End If
 
Ok, this does sound less complicated than I was making it, but it's not quite working yet.
I am unable to open the form and when I try to open it manually I get this error "Compile error: Method or data member not found" and this is highlighted:
.txtStudentFirstName

from the code you posted (the fields are called "StudentFirstName and StudentLastName, so that is what varies from your posting to the code in my db):

If Me.NewRecord Then
Me.txtFirstName.Locked = False
Me.txtLastName.Locked = False
Else
Me.txtFirstName.Locked = True
Me.txtLastName.Locked = True
End If


Here is the code added to my form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.StudentFirstName) And Not IsNull(Me.StudentLastName) Then
If Me.NewRecord = False Then
If MsgBox("You may not make changes to a student's name.", vbYesNoCancel, "Name Change") = vbYes Then
Else
Cancel = True
MsgBox "Update was cancelled", vbOKOnly
End If
End If
Else
MsgBox "Both First Name and Last Name are required", vbOKOnly
Cancel = True
End If
End Sub

Private Sub Form_Current()
If Me.NewRecord Then
Me.txtStudentFirstName.Locked = False
Me.txtStudentLastName.Locked = False
Else
Me.txtStudentFirstName.Locked = True
Me.txtStudentLastName.Locked = True
End If
End Sub
 
Ok, apparently I just needed to take out the "txt" and now the form opens. The problem is getting my StudentFullNameQuery to run after the record is saved but before the new forms are opened. At least I think this is the problem. Right now the new forms will open, but to a blank record instead of the student's name. Any ideas? Do I need to use Refresh somewhere?
 
Remove the prefix txt.

RV
 
.txtStudentFirstName should be the name of the CONTROL. I prefix all my control names with txt, cbo, lst, etc. That way I can avoid VBA issues when I want to refer to a property of the CONTROL rather than to the data value. If your control name is the same as the name of the bound field, use the name of the bound field. If you have any trouble, rename all your controls. WARNING! If you have code already associated with the controls it will be "lost" since it will be left hanging associated with the old name. You would need to cut and paste to put everything back together again.
 
Thank you--that is good advice for the naming convention. Sorry I am so lost with this stuff.

I am struggling with getting my query to run with the new data before anymore forms are opened. I tried using this code in the open form command button code, but nothing happens--not even the form opening anymore. Can someone take a look:

Private Sub Command30_Click()
DoCmd.RunCommand acCmdSaveRecord
Me.Form.Refresh
DoCmd.OpenQuery (StudentFullNameQuery)
On Error GoTo Err_Command30_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "StudentRegistrationSubform"

stLinkCriteria = "[StudentFullName]=" & "'" & Me![FullNameLink] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub
 
Did you rename the button? If you did, the code is orphaned. You need to create a new click event and paste the old code. Don't forget to delete the code associated with the old name.

What is the point of the refresh and query anyway? There is no reason to refresh the form at this point and unless the query is an action query, there is no point to running it here.
 
No, I didn't rename anything (but I will use that naming convention in the future).

The query is an update query that creates a StudentFullName field from the first and last names. It is followed by an append query to add the student to other tables since the full name becomes the identifier for that student for the remainder of the db. I have an unbound field in this form, the control source of which is

=[StudentFirstName] & " " & [StudentLastName]

I use that field for the link to open the forms that the command buttons go to.

I think the problem is that I need those queries to run before opening the forms so that the link has a record to refer to. That is why I am trying (and failing) to get the new data into the table (refresh???) and then run the action queries before they open any other forms. Does this sound like I properly identified the problem? (And is it possible to solve it??)

Thanks again for your patience with me.
Carly
 
I've been trying a few more things without any success.
That latest thing I've tried without success is putting this code in the after update event of the unbound link control (=[StudentFirstName & " " & [StudentLastName]):

Private Sub FullNameLink_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
Me.Form.Refresh
DoCmd.RunMacro (RegistrationCompleteMacro)
End Sub

RegistrationCompleteMacro runs the action queries I mentioned. It seems like it isn't doing that though. Does anyone know why my action queries aren't running? Or why the data isn't being saved in the table before they are run? One way or the other, the update query doesn't udate my information.

PS I've also tried DoCmd.OpenQuery(StudentFullNameQuery) and it didn't run for that either. I've also tried taking out the line of saving the record. Still no difference.

Thanks!
Carly
 
Ok, my problem is most definitely that my update query isn't running as a result of my code. It runs fine when I double click the query manually. What is wrong with my code?

Private Sub FullNameLink_AfterUpdate()
Me.Form.Refresh
DoCmd.OpenQuery "StudentFullNameQuery", acViewNormal, acEdit
End Sub

(StudentFullNameLink is an unbound field on the form.)

Thanks!
 
You need to rethink your tables and process.
1. NEVER, NEVER, NEVER under any conditions concatenate fields to make a "key". Access supports multi-field keys of up to 10 columns. Just define a 2-column key. Storing a concatenated field violates first and second normal forms. To define a multi-column key, open the table in design view. Click on the first key field, then use Ctl-Click to select up to 9 more fields. When all the fields are selected, press the key icon.
2. A person's name is a poor choice for a primary key. You will find over time that you need to make "exceptions" and that will cause problems. It is far better to use an autonumber as the primary key when there is no truely unique candidate key. Autonumbers are better than multi-field keys also. There are situations such as combos where you will not be able to use tables with multi-column keys.
3. You do not have to run an update query to updata in the current record. In fact, doing so frequently causes update conflicts since you are updating the current record which may be in a dirty state with a disconnected process. This can cause record lock issues. To update a column in the current record:
Me.SomeField = "some value"
That's it!!!!! No DAO/ADO, No query!
4. Macros do not belong in a production database. They do not provide any error handling and so can cause serious problems when used by inexperienced users.
5. Don't quote me here since I NEVER use these methods but I think that .OpenQuery is intended to open a select query and .RunSQL is intended to run an action query (append, delete, or update query).
 
Thank you so much--that is so incredibly helpful!
 

Users who are viewing this thread

Back
Top Bottom