Hello am new here.
I am developing an access application . I want to write a code to check for a null value in multiple fields and list which fields are empty in a text box on my form.
Example
I have 5 textboxes and I want to be able to check which textboxes are empty and display a the names of the empty textboxes in a new textbox
It isn't often done that way, but it is possible, I suppose. We need just a little bit more info on your intent here. Note I said "intent" not "mental image." It is important for us to know what you would do with the information when you got it.
The questions are
1. Will there ever come a time when you need to change the number of text boxes to test on this form?
2. Will you ever need to do this on more than one form?
3. Did you really want a list in another text box or would it be OK to just visually highlight the textboxes for which your "null" test is TRUE?
use your Form's BeforeUpdate Event to check
and get the names of those empty textboxes:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strControlNames As String
strControlNames = EmptyTextBoxes(Me.Form)
' put the empty textboxes names here, change Text0 to the correct textbox name
Me.Text0 = strControlNames
If Len(strControlNames )>0 Then
Msgbox "There are textboxes left blank."
Cancel = -1
End If
End Sub
Private Function EmptyTextBoxes(Byref frm As Access.Form) As String
Dim ctl As Control
Dim strControlNames As String
For Each ctl In frm.Controls
Select Case TypeName(ctl)
CASE "Textbox", "Combobox"
If Trim(ctl.Value & "")="" Then strControlNames = strControlNames & ctl.Name & vbNewLine
CASE ELSE
End Select
Next
EmptyTextBoxes = strControlNames
End Function
Well the thing I have this combo box. And there values in it are like 6 .
And for each option u choose , there are some fields that u need to complete. After the completion, u a new textbox appears with it's value been 'validated " .
But when one or more fields are empty then that textbox which shows "validated" now shows "not validated" and the system should be able to list the fields that are empty in a text box which is on the form .
The purpose of the system is to validate teachers in Ghana so that their arrears in salary will be paid to them .
Please help me so I can make the system available early for the teachers to be paid because some have like 6months salary arrears.
This project was given to me by my boss to develop. And I have a deadline to meet .
From your description in post #4, it sounds like you have a dependence among more than one text box regarding what you put in that box and what you need to put into other boxes. My idea here is that since this depends on a combo box, the place to put your code is either the OnClick event of that box (best choice) or the LostFocus event (lesser choice). You can look up these two events and make your decision as to which one will be right.
You can use VBA code to decide which choice has been made from the combo box and use that to drive the states of the text boxes. Now if I were doing it, I might do something like take the value of the combo box (because in the OnClick event, you got there when you clicked to select an option). In that event code, I would make some kind of SELECT CASE "ladder" and for each of the possible cases, do something like this:
Define your combo box using the combo wizard so that when it is selected, it returns a number. Remember, you can make the combo box say one thing but return another by choosing the width of the displayed column. So you make the bound column a number and that is what gets returned, but then you make its displayed width 0 so if there is a text value, you see that.
Code:
Private Sub comboboxname_Click()
Dim lSelection as Long
lSelection = [comboboxname]
SELECT CASE lSelection
CASE value1
[BoxA].backcolor = vbYellow
[BoxB].backcolor = vbWhite
[BoxC].backcolor = ...etc
... {do something for all of the boxes}
CASE value 2
[BoxA].backcolor = vbWhite
[BoxB].backcolor = vbWhite
[BoxC].backcolor = vbYellow
... {do something for all of the boxes}
CASE value3
... {set up the boxes}
END SELECT
End Sub
It sounds to me like you have a limited number of possible cases so you only have to have as many CASE choices as you have selectable cases. You said six, and that is not a particularly large number so should be easy enough to manage.
If you want to reset the colors of the boxes after someone has updated them, you do that using the boxname_LostFocus event or the boxname_Change event. Again, read up on those events to see which one sounds better. Again, all you need to do to show a problem is to change the color of what you are showing. For instance, if you didn't like the contents of the box, you could change its .ForeColor property to vbRed.
As part of this, you need one more event to consider for the validation: the OnCurrent event, which will occur if you navigate to a new record or save the record on-screen. After you save or navigate, your form shows you the contents of a record that it just loaded or saved so the values on the form are "current" and the Form_Current event fires. When that happens, reset the boxes because at that moment you have made no selection in your combo box that applies to this record. If the combo box is bound, this might be automatic. If it is unbound, you need to reset the combo box, too.
the moment a user select a value from the combo box called "application type", you realize immediately some fields on the form disappears . the remaining text boxes that are left are what the user needs to fill .
when the user fills all of them then the auditor's status should be "validated" and the auditor's remarks value should be empty but if the user leaves some of the fields out then the auditor's status value should be "not validated" and the auditor's remark should list the empty text-boxes .
this goes for all the application types.
Example
let's say the user chose application type value "New Entrants" , then that user needs to fill these
Me.ACCEPTANCE_LETTER.Value
Me.EVIDENCE_OF_ASSUMPTION.Value
Me.RECRUITMENT_FORM.Value
Me.ESTABLISMENT_WARRANT.Value
Me.INPUT_FORM.Value
Me.NAME_OF_CERTIFICATE.Value
Me.YEAR_OF_CERTIFICATE.Value
if any of these fields are empty then the auditor's status.value should be "NOT VALIDATED"
and the auditor's remark.value should be able to list the empty fields.
else Auditor'S status.value should be "VALIDATED" and auditor's remark.value should be " "
see attached.
i added Tag "Excluded" for few field that need not to be checked, like auditor's status and auditor's remark.
also included Comments field as Excluded for checking. you can include this if you want by Removing the Exclude from the Tag (Property->Other->Tag) on Design view of your form.
also i only checked those Visible property is set to Yes.
@arnelgp i sent it to them to try it out only for them to come to me to and say there is another condition needed
the condition is
if application_type.value ="Replacement" or "Re-engagement" and CAGD JOB TITLE.value <> RANK BEFORE PROMOTION/UPGRADING/DELETION.value then
the auditor's status.value = not validated
and the auditors remark must be able add this text "re-engagement on a higer rank not acceptable" or "replacement on a higer rank not acceptable"
based on the application type value
the text should appear together with the fields which empty for the auditor's remark. kind of need help on that too.
@ arnelgp
have attached a new db file
your code for the re-engagement and replacement works perfectly as long as some fields are empty . and it will show the 'not validated' auditor's status .
but the moment all the required fields are filled and me.CAGD JOB TITLE.value <> RANK BEFORE PROMOTION/UPGRADING/DELETION.value , the auditor's status returns "validated " instead of "not validated" and the audito's remark that tells u that re-engagement on a higher rank not acceptable or replacement on a higher rank not acceptable based on the application type , will return a null value.
2. i have a user form that allows a user to log on to the system .
is there a way that when the user's acess level is standard the system should straight away open 'payroll2017' form and when the acess level is adminstrator ,it should open "admin" form.
3. also is there a way we can tire a user to every record he or she has worked on .
4. is there a way we can add an audit trail where the admin can see if a user have modified a record and which date and time it was done
When I try to save a record that has audit status as validated , I get "argument not optional" compile error .
And module 1 opens up . Highlighting function auditeditbegins with yellow colour and
Call logerror (err.number,err.description,conmod & ".auditeditbegns()", , false)
