Five issues with VBA commands and forms

[Ivkosky]

Hi all,

I have several issues with my form. I designed a command button for saving the actual record and doing more things in background, please take a look:

Private Sub Command194_Click()
On Error GoTo Err_Command194_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Me.Date = Date
Me.Local_Time = Time()
   
    
If Me.Reporter_s_surname = Null Then
   MsgBox "Please enter your surname."
Else: DoCmd.Close
End If

Exit_Command194_Click:
    Exit Sub

Err_Command194_Click:
    MsgBox Err.Description
    Resume Exit_Command194_Click
    
End Sub

These are my problems:

1. Automatic filling of time works perfectly, but the date doesn't work... Everytime I write the expression Date() after =, VBA puts away the parentheses automatically. I tried to rename the assigned field, cos I thought this may cause a problem, but nothing changed, it still puts Date instead of Date() and the command doesn't work then.
2. I tried to design my own error message if there is a null value for mandatory field (Reporter_s_surname), but it doesn't work.
3. I also need to design a code for one check box, which has to be automatically checked after administrator (manager in my case) opened and saw the particular record.
   Simply: if the form with the particular record was opened, check box 'Validated' must be automatically checked to yes for that record.
4. I also have to design an if clause that if a particular check box is checked as yes then several other fields have to become mandatory (required), but I wasn't able to find any command for it in VBA.
5. The last issue is that I have to design an indicator of the number of non-validated records in my 'Main Switchboard' form. I thought I would use 3 text boxes with different background colour in this way:
   if there are less than 20 unseen records the green text box will become visible;
   if between 20 and 50 yellow will become visible; and
   if more than 50 red text box will be visible.
   I have no idea how to program this. Probably I will need to use something like SUM for the 'Validated' check box field, but I wasn't able to find the solution.

As you can see, I am new to VBA, so I apologize in advance if those questions are kind of silly... Any idea or thought will be highly appreciated! Thank you in advance!


Ivan

 

[gemma-the-husky]

Point 1

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

is this saving the record? (this verison is outdated now, although the code is still generated by access)

if so use this instead - its more readable

application.runcommand accmdsaverecord


Point 2 - at this point you have already saved the record, so now setting the data and time will achieve nothing - you need to set these BEFORE you save the record


Point 3 - you dont HAVE to separate the data and time. an access date/time field will store both data AND time, and can be set with the code

mytime = now()

point 4.
Me.Date = Date

the problem here is that you have a field called date, ehich is conflicting with the attmept to use the function date. Rename the field to local-date, or something else


point 5 - you cant test if a name = null

you need to say
If isnull(Me.Reporter_s_surname)
MsgBox "Please enter your surname."
Else: DoCmd.Close
End If

HOWEVER - again, you are caught by the fact that you have already saved the record BEFORE your test - you need to do all these tests before you save the record

Point 6 - Note also that you can achieve some of these by setting validation rules/default values in your table

eg,
with the localtime, just set a default value =now()
with the surname, just set required= true, then access will not let you save the record

 

[Ivkosky]

Thank you very much, problems solved, thanks.


Ivan