Programming "Next Record"

[Combo box type - help]

Hi.. I have a problem.. I have a form where i created a button to click when
i want the form data to be inputed on the respective table.. I don't want to
use the macro button for that because i want to add some routines to it,
before the "next record" part. As you can see:

Private Sub Command17_Click()
Text15.SetFocus
If Text15.Text = Empty Then
MsgBox "ERRO: Empty field"
Else
Text40.SetFocus
If Text40.Text = Empty Then
MsgBox "ERRO: Empty field"
Else
Combo34.SetFocus
If Combo34.Text = Empty Then
MsgBox "ERRO: Empty field"
Else
Combo35.SetFocus
If Combo35.Text = Empty Then
MsgBox "ERRO: Empty field"
Else

DoCmd.GoToRecord , , acNext

If MsgBox("New Record?", vbOKQuit, "Next:") = vbOK Then
Else
Quit
End If
End If
End If
End If
End If
End If
End Sub

Before sending the new values, on the form, to the tables i check if every
combobox and textbox are filled. Only then the values are sent to the
table..the problem is that the program is not working right. The values are
being sent to the table but the table places are not being incremented.. I
will explain what happens, everytime i press the button the values are sent
to the same place on the table, so, the previews values are substituted by
the new ones.. i want my button to work exactly as the "Next Record" button
on the down tab!

What do i need to add to my programming so it works?
Thanks
Nuno

 

[BruceM]

Here are some observations, in no particular order:
The form's Before Update event is often a good place to validate fields. In
any case there is no need to set the focus to a control if all you are doing
is checking its value.
Use the format Me.Text15. If you are in a form's code module, Me represents
the form. What follows Me is a property of the form. Fields and controls
are considered form properties.
Don't use the Text property. Use the Value property, or leave out the
property, which is assumed to be Value if not specified. Either:
Me.Text15.Value
or:
Me.Text15
"Empty" is not a value you can check. You can check for Null instead:
If IsNull(Me.Text15) Then
etc.
If the field contains an empty string, the IsNull test will not produce the
expected result. However, IsNull will probably do what you want. For more
information about Null:
http://allenbrowne.com/casu-11.html

Why go to the next record? What do you mean by "the table places aren't
incremented" and "the Next Record button on the down tab"?

You can use the form's Before Update event to check for values:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Text15) Then
MsgBox "Value needed"
Me.Text15.SetFocus
Cancel = True
ElseIf IsNull(Me.Text40) Then
MsgBox "Value Needed"
Me.Text40.SetFocus
Cancel = True
End If

End Sub

This is just a sketch of Before Update. There are a lot of options,
depending on your specific needs, but in any case Cancel will stop the form
from updating (and therefore prevent the record from being saved).

You can save the form in the Command17 Click event, using Me.Dirty = False.
This will cause the form's Before Update code to run. However, the intent
of asking the user if it is a new record when they may be at an existing
record is unclear.

 

[Combo box type - help]

Bruce, first thank you very much for your help.. In fact the advices you gave
me are usefull and i will change the program writting by following them.

The part i'm more concerned is about the Record part.. I will try to explain
me as good as i can, the problem is that i'm just starting using VBA language
and Access.
So, being this a database, the form is suppose to be used to input values on
it. I created the input fields and the way i have to send the values i add to
the input fields (combobox and textboxes), i suppose, it's by pressing a
button down in the access interface that looks like the "Play" button in any
VCR or DVDplayer. That button is called Next Record. What happens is that
everytime i press it the values on every BOX are sent to the respective field
in the table i created before. Imagine the first time i press the "next
record", when i check the table, the fields correspondent to each input value
have the "value" i inputed in the form(the value in inputed in the
Temperature input box is now in the Temperature column in the table). If i
press the "next Record" button again after changing the input values, those
new values are sent to the table again but to position 2 (o ID column), and
the first ones stay there in the ID position one.. and so on..
The problem is that the button i created is sending the input values to the
table, but everytime to the same position, so, everytime i try to add new
values from the form to the table (by pressing my button) the old ones are
changed by the new ones, everytime in the ID position one.. So of course this
database is senseless considering the table is only saving the last input
values..
I hope i made myself clear..sorry for the non very experienced explanation..
Thanks again



"BruceM" escreveu:

 

[BruceM]

There may be a language barrier here. How are you sending the values to the
table? Is the form bound to a table or query (is a table or query its
Record Source)? If you are using code to write to a recordset, what is the
code?

 

[Combo box type - help]

Sorry, i'm really trying to make myself clear..
The form is bound to the table.. logically i have to have a button to click
when all the form is filled so that the values get registered on the database
( = values get registered in the table).. Right? The values need to be stored
in the table..thats where the data is stored in a database..

"BruceM" escreveu:

 

[BruceM]

If a form is bound to a table, and a control such as a text box is bound to
a field in the table, then clicking the built-in Next Record button will
save the record. Other things that save the record include going to any
other record, (first, last, previous, next, or new), closing the form or
database, going to a subform, and explicitly saving the record. There may
be other ways of saving the record, but these are the main ones, I think.
The record includes any bound text boxes you filled in, so they should be
written to the table when you do any of these things.
If there is a custom navigation button, maybe extra code is doing something
you don't want. You are correct that the values are stored in a table. I
don't see why you keep overwriting the old stored values, if that is what is
happening.

 

[Larry Linson]

The Implicit Constant "Empty" evaluates to a zero-length string, but a Text
Box into which no data has been entered is a Null value. Thus, your code is
executing, but you are not, I suspect, getting the results you expect.

As BruceM suggested, you can validate in the BeforeUpdate event, and if you
find an error, set Cancel = True to prevent the auomatic update and return
the user to the form. In Access, you do not have to explicitly Save -- as
soon as you move off the record, it will be saved. So, even if you have an
error and issue the MsgBox, the erroneous data is going to be saved, anyway,
when the Quit is executed.

Also, Quit (which means close the database application) seems a rather
stringent action for someone who just did not type in one value. Wouldn't it
be better to return then to the form to let them correct the error?

Hint: if you will properly indent your source code, it won't run any
differently, but it will read much easier. Most of us don't have time to
read through and debug source, anyway, much less to reformat it to carefully
analyze to see what it may be doing wrong. Our quick scan was enough to
show the potential problems with (1) = Empty vs Is Null, (2) Quit being the
improper action, and (3) Quit causing a save of the erroneous data. There
may also be logical problems with the code, once those are corrected, but I
didn't expend the time and effort to go that far in debugging.

Larry Linson
Microsoft Office Access MVP

 

[Combo box type - help]

I understand what you say about the built-in Next record button.. Thats
exactly what i want the personalized button i created to do.. exactly the
same thing the built in "Next record" button does.. but the code i wrote must
be incomplete.. i need the VBA code to make my costum button work exactly
like the built in Next Record button..
Thanks for your help

"BruceM" escreveu:

 

[BruceM]

I didn't know that about Empty. I checked Help, but didn't see anything.
There is a vbEmpty constant, defined as "uninitialized (default)", which
does little to advance my understanding. The information is probably in
there somewhere, but a search for "Empty" didn't produce anything specific.
At least now I'll know what it means when I see it in code samples, etc.

 

[BruceM]

OK, I think I understand that Command17 is the custom Next button. In that
case, just use it to go to the next record:
DoCmd.GoToRecord , , acNext

Attempting to go to the next record will cause Access to attempt to save the
current record. If there is code in the form's Before Update event it will
run at that time.

Again, the Text property will not do what you need. VBA Help states: "To
set or return a control's text property, the control must have the focus, or
an error occurs". As Larry Linson described, Empty is the same as an empty
string ("") which is *not* the same as Null. If you received no error, it
was apparently because you did not attempt to set or return the Text
property, but rather you just checked to see if it was Empty. No error was
returned, but it was not testing the controls to be sure they contained
values, which is what you intended.

The form's Before Update event would be a better way to check the text boxes
and combo boxes since it runs every time you try to update a record rather
than just when a specific button is used. I expect you would want to check
the controls if you go to the previous record or to a new record. As things
are, you need to copy the code into each command button, but there is still
no way to check the code if the user closes the database. The Before Update
event will test the record no matter how it is updated.

In any validation code you should have a way to stop the code when a problem
is discovered. In Before Update you would use Cancel = True, as I
described. In the command button code you would need to use Exit Sub, since
a Click event does not accept the Cancel argument (you can't cancel a Click
that already occurred, but if code runs Before Update you can cancel the
update).

 

[Combo box type - help]

I understand what you say about the built-in Next record button.. Thats
exactly what i want the personalized button i created to do.. exactly the
same thing the built in "Next record" button does.. but the code i wrote must
be incomplete.. i need the VBA code to make my costum button work exactly
like the built in Next Record button..
Thanks for your help


"BruceM" escreveu:

 

[BruceM]

This line of code will make the custom Next button behave like the built-in
one:

DoCmd.GoToRecord , , acNext

The built-in button will become disabled if there are no more records, and
the built-in navigation buttons include a record counter. The custom button
will not do these things unless you add some coding, but I repeat that
validation should occur in the form's Before Update event rather than in
each individual navigation button.

This link includes navigation buttons that can be added to a form:
http://www.lebans.com/recnavbuttons.htm
These buttons behave just like the built-in ones. It is one of the many
fine examples that Stephen Lebans makes available at his site.