Sorry i'm in a fix here...can someone be kind enough to solve my query..
I have some fields namely..
Staffid
Fname
Lname
contact
and so on..i want that : " if required fields are null " the "add record " button is disabled, if all required fields are filled in then only the "add record" button should be enabled..
can someone guide me in detail..what event and what code ??
Thanks in advance
Do you have code that prevents users from saving a record where the required fields are Null? That code would go in the before update event for the form and is the only reliable way that I know of to prevent users saving a record when a required field is null.
I would use the Add record button as the gate keeper that decides if the current record can be saved or whether users need to add the missing information for the required fields.
If you want to disable the add record button, you would need to first check if the form is dirty, then check each required field to see if it is null.
On the forms’ dirty event, put code to check every required field for an entry. If any of them are missing an entry, then disable the add record button.
Then on the after update event of each required field, put code to check all the required fields for an entry. If any of them are missing an entry then disable the add record button.
‘---------- start of Sample code If IsNull(Me.StaffID) And IsNull(Me.Fname) and IsNull(Me.Lname) then Me.AddRecordBtn.Enabled = False Else Me.AddRecordBtn.Enabled = True End If ‘ ---------- end of Sample code
Note: replace my control names with yours, and add all the required fields into the code, not just the three that I used.
When user has just moved to a new record, you would also disable the add record button.
‘ --------- start of Sample code If Me.NewRecord = True Then Me.AddRecordBtn.Enabled = False End If ‘ -------- end of Sample code
NO unfortunately i've spent sleepless nights to figure out the code for the form restriction as well...maybe i'm just very new to VBA..if you could help me wit hthat as well..
The way I approach this is to have a generic validation function that is saved in a general purpose module (ie not in a form module).
Code:
Public Function validateform(myform As Form) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
For Each ctl In .Controls
With ctl
If .Tag = "required" Then
If .Value & "" = "" Then
boolresponse = False
strError = (strError + ", ") & .Name
End If
End If
End With
Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
Then, for any fields that are absolutely required, you just set the Tag property of the control to 'required'
You can then, for example, call the function from the onclick event of the 'save' button.
Code:
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
If Me.PrimaryID & "" <> "" Then
If validateform(Me) Then DoCmd.Close
Else
DoCmd.Close
End If
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
this essentially checks to see if the auto pk field has a value indicating that the record has been created. If it has, then it validates the required fields. If not, it closes without checking. If the record has been created, it validates the form and only closes if all the required fields are filled in. If more data is required, then it pops up a message reminding the user which fields to fill in.
The nice part of this is that you can set conditional requirements using the vba to set the tag property of certain controls to required if another field is updated to a value that you want to trigger an additional requirement. Of course, if you do that then you also need to initialize those tag value in the on_current event for when you switch records.
OK, now we know that you are very new to vba, we can go slowly. Step 1. Forget about trying to disable the AddRecordButton.
Step 2. Put code in the Before Update event for the form. ‘ ------ start sample code Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMissingInfo As String Dim strMsg As String
If IsNull(Me.[FnameControlName]) Then Cancel = True strMissingInfo = "Fname" End If
If IsNull(Me.[LnameControlName]) Then Cancel = True strMissingInfo = strMissingInfo & " Lname" End If
If IsNull(Me.[StaffIDControlName]) Then Cancel = True strMissingInfo = strMissingInfo & "FnStaffID" End If
If Len(strMissinginfo) <> "" Then strMsg = "The following are required: " & strMissginInfo MsgBox strMsg End If
End Sub ‘ --- end sample code Note: replace my control names with your control names that suit your form. Add code for each control that is not allowed to be null. My sample code just does 3 controls to get you started.
HI everyone, thanks a lot for posting replies, well maybe its just me , I just don't seem to understand what to do..I will attach my form with this message, so that you guys can help me better
MY REQUIREMENT :
1. If the user clicks "Add record" on a blank form directly when the form is loaded, propmt "Fields are empty"
2.The Record should not be saved if user directly exits the form with incomplete data.
3.The Record should only be added if required fields( * Fields) are filled in
and msgbox " Record was successfully added.
Thanks evryone who cared to help, i would be very grateful, if i can finish my assignment with this..
That is a big ask for a homework assignment. There are several posts explaining the steps to take.
If I were running a course in access, or trying to hire a person who could develop a database in access, I would not want that work to be done by a third party.
That would make it look like you have some understanding of developing a database using access, when clearly you haven't done quite enough learning or work yet.
That is a big ask for a homework assignment. There are several posts explaining the steps to take.
If I were running a course in access, or trying to hire a person who could develop a database in access, I would not want that work to be done by a third party.
That would make it look like you have some understanding of developing a database using access, when clearly you haven't done quite enough learning or work yet.
Well..we have been given an assignment, and havent been taught enough..but thers no option i have to submit this assign, i have done some coding, learnt coding on my own and have made a complete database application, and thers just one thing that i need now and thts this..if someone could really really help me out on this, or aaply a custom approach on this..i would be very grateful..
I was looking for a solution to my problem and ran across your suggestion, it works great however I have additional questions. I also want to include this code which is currently in the BeforeUpdate event of the form:'*********************************************************************
' Require bound control Hours Worked to have data entered before saving if marked "Completed"
If Me.Status = "Completed" And Nz(Me.HoursWorked, "") = "" Then
MsgBox "When you have selected Completed, you must enter Actual Hours Worked below", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.HoursWorked.SetFocus
Cancel = True
Exit Sub
End If
This will not work with your code. I am rather new to coding and need some assistance. Can you please contact me.
The way I approach this is to have a generic validation function that is saved in a general purpose module (ie not in a form module).
Code:
Public Function validateform(myform As Form) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
For Each ctl In .Controls
With ctl
If .Tag = "required" Then
If .Value & "" = "" Then
boolresponse = False
strError = (strError + ", ") & .Name
End If
End If
End With
Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
Then, for any fields that are absolutely required, you just set the Tag property of the control to 'required'
You can then, for example, call the function from the onclick event of the 'save' button.
Code:
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
If Me.PrimaryID & "" <> "" Then
If validateform(Me) Then DoCmd.Close
Else
DoCmd.Close
End If
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
this essentially checks to see if the auto pk field has a value indicating that the record has been created. If it has, then it validates the required fields. If not, it closes without checking. If the record has been created, it validates the form and only closes if all the required fields are filled in. If more data is required, then it pops up a message reminding the user which fields to fill in.
The nice part of this is that you can set conditional requirements using the vba to set the tag property of certain controls to required if another field is updated to a value that you want to trigger an additional requirement. Of course, if you do that then you also need to initialize those tag value in the on_current event for when you switch records.
While desiging the table, in the general tab, make the required tab as "yes" and zero length allowed as "no" that will fix your problem. No need for any coding. See the attached figure in the word document zip.
Thank you Raghu for your reply. I realize that you can set controls within the table; this is not what I wanted, it gives a nasty error. I really like the use of this code; it's clean simple and does what I want. I just need to understand how to manipulate it to my advantage. Also the Status field is not required on a initial new record only if the recorded is marked “Completed” under the Status field.
[Status] this control CAN BE EMPTY and is "not required" BUT if user selects from the control [Status]="Completed" then the control
[HoursWorked] must have a value, not null
before saving.
Also, if [HoursWorked] is null this is ok, as long as, the [Status] is not "Completed"
After doing more research to find a solution to my problem and with a little help found a soloution. See below.
Private Sub Form_Current
If Me.Status = "Completed" Then
Me.HoursWorked.Tag = "Required"
Else
Me.HoursWorked.Tag = vbnullstring
End IF
End Sub
Private Status_AfterUpdate()
If Me.Status = "Completed" Then
Me.HoursWorked.Tag = "Required"
Else
Me.HoursWorked.Tag = vbnullstring
End IF
End Sub
While desiging the table, in the general tab, make the required tab as "yes" and zero length allowed as "no" that will fix your problem. No need for any coding. See the attached figure in the word document zip.
Huzinac, sorry I missed your question. I haven't been on the forum much for a while. I'm glad you found a solution that works for you. It's basically the same as I would have suggested, and is an example of what I meant in my last paragraph about conditional requirements.
While desiging the table, in the general tab, make the required tab as "yes" and zero length allowed as "no" that will fix your problem. No need for any coding. See the attached figure in the word document zip.
Very nice and helpful work you all are doing to help beginners like me!!!
I was struggling with the vba code to prevent users from adding new record to the database if any of the field is left blank. The information given here assisted me well.
However, i am facing couple of problem.
1) The code doesn't work for blank fields in subform which in datasheet view.
2) I want to setfocus on the blank field. (if there are too many blank fields then at least first one)
Let me tell design of my database first. I know I am talking its too lengthy but this will explain my problem better
The purpose of the database is to keep record of order emails received from customers. The email contains email date, time, subject and details of goods customer has ordered. email body contains list of n number of goods.
So there are two tables say table A with fields emailID(auto number primary key) email date, email time, email subject.
say table B with fields orderID (auto number) goods name, goods quantity, rate, date of delivery.
Table A and Table B are linked via database relationship.
Now i credited one form which contains Table A as main form and Table B as subform with is in data sheet view. Main form contains one submit button.
User enters email details in main form (Table A) then they proceed to Subform for entering order details (Table B) which is in datasheet view.
The main purpose of keeping subform in datasheet view because user feels it like MS excel. Also I have not kept any field as Is required because my staff may enter all list of goods name first then they may enter goods quantity and rate and date of delivery like we do in excel.
Now what is happening is some time unknowingly user left some of the fields blank and access allows them to enter data as no field is mandatory.
So I was searching for some VBA code to enter in submit button to restrict user from adding new record with blank field.
I tag all fields i want user must enter as required.
As suggested I entered below code in separate module.
Public Function validateform(myform As Form) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
For Each ctl In .Controls
With ctl
If .Tag = "required" Then
If .Value & "" = "" Then
boolresponse = False
strError = (strError + ", ") & .Name
End If
End If
End With
Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
and below code as onclick event of submit button
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
If Me.PrimaryID & "" <> "" Then
If validateform(Me) Then DoCmd.Close
Else
DoCmd.Close
End If
The problem is code works very well for main form i.e Table A (email date, time, subject) and throws error
But if user kept any field blank in subform (which is in datasheet view) i.e. Table B (orderID (auto number) goods name, goods quantity, rate, date of delivery.) then code doesnot work and other than restricting user it allows to add new record.
Request you all to provide any code modification to restrict user if any field in subform which is in datasheet view is kept blank and setfocus on it
Holy thread revival Batman! (Just proves the search works!)
I'm having the Subform issue with this validation check too, anyone have any ideas? I have tried various options to get the subform name, but it doesn't appear to make any difference:
Thanks,
Matt
Code:
'Check for any missing responses and grades
On Error GoTo Err_cmdSubmit_Click
If validateform(Me & "!" & [B]Me.TheSubForm.Properties.Item(3))[/B] Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAdd-" & Me.FocusedArea & "-Grading", acViewNormal
DoCmd.OpenQuery "qryLogCallComplete", acViewNormal
DoCmd.OpenQuery "qryDELETECallBack", acViewNormal
DoCmd.SetWarnings True
MsgBox "'Survey Completed' has been logged against this customer and " & [Forms]!Switchboard.DbUser & ".", vbInformation + vbOKOnly, "Thank you"
DoCmd.Close acForm, "frmCustomerQuestions", acSaveNo
End If
Exit_cmdSubmit_Click:
Exit Sub
Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
Public Function validateform(myform As [B]SubForm[/B]) As Boolean
'returns true if all required fields have data, or false if not.
'It will also create a popup message explaining which fields need data
Dim boolresponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolresponse = True
strError = Null
With myform
For Each ctl In .Controls
With ctl
If .Tag = "required" Then
If .Value & "" = "" Then
boolresponse = False
strError = (strError + ", ") & .Name
End If
End If
End With
Next ctl
End With
If strError & "" <> "" Then MsgBox "The following information must be entered first: " & strError, vbInformation
validateform = boolresponse
End Function
Call using:
Code:
Private Sub cmdSubmit_Click()
'Check for any missing responses and grades
On Error GoTo Err_cmdSubmit_Click
If validateform([B]TheSubForm[/B]) Then 'This is my Subform name (it's easier to switch the subform used as I have criteria on the parent to switch subforms)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAdd-" & Me.FocusedArea & "-Grading", acViewNormal
DoCmd.OpenQuery "qryLogCallComplete", acViewNormal
DoCmd.OpenQuery "qryDELETECallBack", acViewNormal
DoCmd.SetWarnings True
MsgBox "'Survey Completed' has been logged against this customer and " & [Forms]!Switchboard.DbUser & ".", vbInformation + vbOKOnly, "Thank you"
DoCmd.Close acForm, "frmCustomerQuestions", acSaveNo
End If
Exit_cmdSubmit_Click:
Exit Sub
Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub
