Data Entry form; validation rule violation

[shteev]

I'm constructing a form in MS Access to allow the user to enter data into a table. However, I only want the user to be able to enter one record each time the window is opened. Is there an easy way to do this?

What I've done is create a form which is not bound to any table; the user uses objects on the form (text boxes, etc) to enter data, and then clicks on a button which runs this code:

If Len(Trim([Scheme Number])) > 0 And Len(Trim([Portfolio of Schemes])) > 0 Then

    Dim strSQL As String

    strSQL = "INSERT INTO Schemes " & _
    "VALUES(""" & [Scheme Number] & _
    """, """ & [Scheme Name] & _
    """, """ & [Scheme Purpose] & _
    """, """ & [Portfolio of Schemes] & _
    """, """ & [District Code] & _
    """);"

    DoCmd.RunSQL strSQL
    DoCmd.Close
    
Else
    MsgBox ("You must enter a Number and Portfolio")
    Me.[Scheme Number].SetFocus
End If

[Portfolio of Schemes] is a combo box, with 2 columns; whenever it is updated, [District Code] (a text box) is automatically updated to be the second column of the combo box. The remaining fields are entered via text boxes.

[Scheme Name] and [Scheme Purpose] are not required fields in the 'Schemes' table; however, if I don't enter a value for them, I get this error: 'Microsoft Access... didn't add... 1 record(s) due to validation rule violations'. How do I sort this out?

 

[BobStubbs]

Why not do your 'Save record' process via a button? If you use the wizard to create a 'Save' button, you will get some standard code which includes:

Private sub btnSaveRecord_click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

end sub

In the 'Declarations' section of your form, create a variable:

Dim bStopSave as Boolean

Change the code for btnSaveRecord to read:

Private sub btnSaveRecord_click

if bStopSave = False then
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    bStopSave = True
else
    Msgbox "Your error message here",vbExclamation
end if

end sub

How this works:

When the form opens, 'bStopSave' takes the value False.
When you save the first record, this is set to 'True'
If the user tries to save a second record, the 'If' test causes the error message to be displayed.

I hope that this helps.

Bob Stubbs

 

[shteev]

Thanks for your help; the purpose of this form, however, is to allow a user with no experience of Access to enter data, so I don't want the form to be able to display data for more than one record either, to avoid confusion.

I've solved the problem now. I don't fully understand where I was going wrong, but It was certainly something to do with nulls and "" strings in the way that strSQL was created. Here's my ugly solution:

If Len(Trim(Scheme Number)) > 0 And Len(Trim([Portfolios of Schemes])) > 0 Then

    Dim strSQL As String
    Dim strName As String
    Dim strPurpose As String
    
    Me.[Scheme Name].SetFocus
    strName = [Scheme Name].Text
    If Len(Trim(strName)) = 0 Then
        strName = "null"
    Else
        strName = """" & _
        [Scheme Name].Text & """"
    End If
        
    Me.[Scheme Purpose].SetFocus
    strPurpose = [Scheme Purpose].Text
    If Len(Trim(strPurpose)) = 0 Then
        strPurpose = "null"
    Else
        strPurpose = """" & _
        [Scheme Purpose].Text & """"
    End If
    
    strSQL = "INSERT INTO Schemes " & _
    "VALUES(""" & [Scheme Number] & _
    """, " & [strName] & _
    ", " & [strPurpose] & _
    ", """ & [Portfolios of Schemes] & _
    """, """ & [District Code] & _
    """);"

    DoCmd.RunSQL strSQL
    DoCmd.Close
    
Else
    MsgBox ("You must enter a Number and Portfolio")
    Me.USRN.SetFocus
End If
End Sub

If someone could give me some insight into why my original code didn't work I'd be grateful.

 

[shteev]

I'm constructing a form in MS Access to allow the user to enter data into a table. However, I only want the user to be able to enter one record each time the window is opened. Is there an easy way to do this?

Finally found it Set the following properties:
Data Entry: yes
Tab Key Cycle: Current Form

You can then set 'Navigation Buttons' to 'No'; although having just discovered how to do all this I'd be wary of it in case I somehow managed to increment the record, and wouldn't be aware of it.