0

I'm working on a form that contains 6 fields, and all must be filled out before the form is submitted and a new record is created. Instead of loading a form with each field hardcoded to show up blank, I used:

Private Sub Form_Load()

DoCmd.GoToRecord , , acNewRec
Me.Username = CreateObject("WScript.Network").Username

End Sub

This begins the new record, but when a user closes without finishing the form it creates a incomplete record and throws.

How can I have that line deleted if a user closes with an incomplete form? Or should I only create a new record when a user submits and all fields match a criteria? What is the best practice here?

Thanks in advance.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Grant
  • 903
  • 1
  • 16
  • 24

2 Answers2

3

The simplest and most efficient option is to properly set the fields properties in the underlying table:
- set the Required property to True,
- and the Allow zero length property to False.
No programming required, and will apply to other interfaces you build later as well.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • How will this help? Null may be perfectly acceptable for some fields. The problem is that data fas already been entered into the form and the user is getting a bunch of errors. This would just lead to another bunch of errors. – Fionnuala Jan 14 '13 at 15:45
  • @Remou: The first line of the question says it clearly: **a form that contains 6 fields, and all must be filled out before the form is submitted and a new record is created**. So Nulls are definitely NOT acceptable here, and I still think my proposal is the easiest and most efficient one. – iDevlop Jan 14 '13 at 16:21
  • 6 fields from how many? What is your proposal for the errors generated when a record is started but then abandoned? – Fionnuala Jan 14 '13 at 16:22
  • use the Form_error event and tell the user: "Fill the 6 required fields or press Escape twice to Cancel current entry". Or if the target users is a small group, give an explanation could suffice. – iDevlop Jan 14 '13 at 16:28
2

Instead of setting the contents of the textbox, set the default value to the relevant user name when the form loads.

You can open your form to add in the first place with :

DoCmd.OpenForm "MyForm",,,,acFormAdd

See: http://msdn.microsoft.com/en-us/library/office/ff820845.aspx

You can then set default values for any control that you know the contents like so:

Private Sub Form_Load()
    Me.Username.DefaultValue = _
        """" & CreateObject("WScript.Network").Username & """"
End Sub

Don't forget that something like Date Created can be set up as a default value in the table design and you may have a default value for some controls that apply to all users, so those can be set up in form design.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • The username is a seperate field used to track who submits forms. It is disabled, but still visible. I'm trying to get the 6 fields, like Date Started, Total Minutes, etc. to open blank and clear if closed and not completely filled out. I'm working on 'Form_Close()' to see if that can work. – Grant Jan 14 '13 at 14:53
  • Use the default value properties to present a seemingly completed form and ask the user to fill in just one bound control to commit the defaults. The reason you have an incomplete record is that you have filled data into a control, so the form is started, a default value just looks like data, but nothing is actually filled in until some control is edited. – Fionnuala Jan 14 '13 at 14:54
  • I'm new to vba so I'm sorry for all the questions, but does that mean to create a default value for each field? Because I commented out the 'DoCmd' and modified the other but it showed my first record and I don't want that. – Grant Jan 14 '13 at 15:04
  • You can open a form for adding of for editing with DoCmd.Open form. Your original question showed that you were completing a control called username and that this was causing you problems. I suggested an alternative that would not cause you problems. You can still go to new record, but you would be better setting the form to an add only form or using DoCmd.Openform to open to an add-only form. – Fionnuala Jan 14 '13 at 15:08