Using Form to update table

N

Noemi

Hi

I am a little lost.

I have a form which has a subform.
In the header of the form there is a combo box to selete numbers and a text
box to enter a date (not current date)

On the subform I have 2 text boxes which onces the 2nd textbox is populated
another set is to come underneath ready for completion (continues form).

On the main forms footer I have an update button which is to perform a task.

What I am lost with is how can I get the subform to work as a continues form
so once the 2nd text box is completed another set is ready for entering.
and
How do I then click on the button to first check to make sure the data
entered is not already located in the table and if not then add the data to
the table.

Is this possible as I have been trying this for days and I am very
frustrated with it.

Thanks
Noemi
 
A

Andy Hull

Hi Noemi

Open your subform in design view.
Go to the Format tab of its properties.
Set "Default View" to "Continuous Forms"
Also, make sure you set the height of this form so that your text boxes just
fit. If the form is too long - ie there is a lot of space between the bottom
of your text boxes and the bottom of the form then the next set of text boxes
will appear a long way after the first.

Now you should be able to continue adding rows one below the other although
at this point we aren't checking to see if the 2nd box is filled - that comes
later.

If your text boxes are bound to columns of a table or query then the data
you enter will automatically be saved to the underlying table when you move
to the next record.

If you want to check the data entered before saving then go to the form's
properties (for your subform). Then got to the Event tab and for "Before
Update" choose [Event Procedure]. Then click the 3 dots to the right of this.

You will then be in the VBA editor where you can check your fields with code
like the following...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me.Field1) = "" Then
MsgBox "Please enter a value for Field1"
Cancel = True
End If
If Nz(Me.Field2) = "" Then
MsgBox "Please enter a value for Field2"
Cancel = True
End If
If dcount("*", "MyTable", "Field1 = """ & Me.Field1 & """ and Field2 =
""" & Me.Field2 & """") <> 0 Then
MsgBox "This record already exists"
Cancel = True
End If
End Sub

Note that the line starting "If dcount" is all one line upto and including
the "Then" (which may have wrapped in this post)

You will need to replace the names I've used with your own field and table
names and I have assumed your 2 text boxes are for text and not numbers. If
not, then the above code will need to be changed slightly.

If your button's only function is to save your record you may not need it as
the data will be saved automatically (if it passes your checks).

Hope this points you in the right direction - post back with more questions
if needed

Regards

Andy Hull
 
Top