Splitting Database Issues

[shaunk]

Working on Access 2000 database.

I have a very simple database that consists of only one table containing data relevant to the User. The other tables are for lookup and menu navigation.

I have split the database to a front-end and back-end as per recommendations.

In addition, I have decided to split the processes of browsing records and adding records to two seperate forms.
(also based on recommendations to avoid record locking).

The 'Add record' form has an 'add record' button and a 'save record' button and it's 'Data Entry' property set to true. I have removed the forms's navigation buttons. This gives the User only one way to add records.

The problem I have is that the table has a validation rule set on the 'Gender' field that is being ignored when the User moves on to add a new record via the button.

The code is as below:
On both form_load and add_record, the 'DoCmd.GoToRecord , , acNewRec' command is exexuted. The Form_Current proc is then executed for both Form_Load and Add_Record, and contains an initialisation routine.

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo Err_Form_Load
   
    DoCmd.GoToRecord , , acNewRec
    
Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
End Sub

Private Sub Form_Current()

    Dim MaxUnitNumber As Long
    
    Me![MRN].SetFocus
    ' Determine the max for Unit_Nmber and increment
    MaxUnitNumber = DLookup("Max([Unit_Number])", "Archive_Register")
    MaxUnitNumber = MaxUnitNumber + 1
  
    Me![Unit_Number] = MaxUnitNumber
    Me![Storage_Location] = CStr(Date)
    
End Sub

Private Sub add_Click()
On Error GoTo Err_add_Click

    
    DoCmd.GoToRecord , , acNewRec
    
  
Exit_add_Click:
    Exit Sub

Err_add_Click:
    MsgBox Err.Description
    Resume Exit_add_Click
    
End Sub
    
Private Sub save_Click()
On Error GoTo Err_save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
Exit_save_Click:
    Exit Sub

Err_save_Click:
    MsgBox Err.Description
    Resume Exit_save_Click
    
End Sub

Thanks










The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[Remou]

What is the validation rule?

 

[shaunk]

As follows:

"M" Or "F" Or "U"

I tested the rule in a test database on a local table and it works.

Thanks

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[Remou]

I guess you mean that it allows nulls? If that is the problem, you can set the required property of the field.

 

[shaunk]

You are correct in saying I don't want Nulls. I have previously set the required field to true. However, I think the message that Access provides the user is obscure, and I wish to control this myself thru the validation rule on the table. It doesn't seem to work for a linked table.

The risk with keeping an open mind is having your brains fall out.
Shaunk