Having trouble with extra NEXT code

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi,

On the form itself, there is a setting that I'm trying to capture.
(txtTmember = Total family members) I need the ability to allow the user to
add new records for the family and come back with an error when txtTmembers
reaches zero. Only if they happened to hit the NEXT button and all members
have been added. I know a little VB and just learning VBA.

Private Sub cmdNEXT_Click()

Dim txtTmembers As Long 'Capture value in form txtTmembers
Dim members As Long 'Set temp var
txtTmembers = members 'Move that value to members
members = members - 1 'Form default is '1'. adjusting

If members > 0 Then
Do Until members <= 0
members = members - 1 'Countdown
DoCmd.GoToRecord , , acNewRec = acNext 'Create new record
Loop
Else
MsgBox "Warning: " & "Total members of the family have been added." & _
& vbNewLine &_
"To create a New Member record for a NEW Family, " & _
& vbNewLine & _
"please click on 'New Member' button at the top of the form."
End If
End Sub

No errors are generated but the msgbx is always displayed regardless of
txtTmembers value. I don't think I'm capturing the value in txtTmembers. If
this is correct, who do I reference it correctly? Or is there something else
wrong? Thanks for any feedback.
 
On Tue, 16 Oct 2007 18:49:50 -0400, "Sam" <none> wrote:

For sure this line is wrong:
DoCmd.GoToRecord , , acNewRec = acNext

The last argument will be treated by VBA as a boolean expression:
Is acNewRec equal to acNext?
The answer is False, which has a value of 0. The argument value
acPrevious has a value of 0, so this code would make you go to the
previous record.
Replace it with:
DoCmd.GoToRecord , , acNewRec
if you want to go to the new record (as the comment suggests).

There is another logic error as well. If I read your code correctly,
you are trying to add "members" number of records by going to NewRec
several times.
That should fail, because your records should have several required
fields that you're not filling out. Having several completely blank
(perhaps with exclusion of Autonumber) records does not make sense.

-Tom.
 
Thanks for explaining the why behind your solution (DoCmd.), that helps a
lot. The other logic error. I have a Family_ID on the main form that is
generated by a custom counter. It's one of the most critical components
throughout the db. I can't use the same form for each person in a family
because the counter would increment when it should be 1 ID per family. To
work around this, I was going to create a subform to add the rest of the
family members and replicate the Family_ID. That's the reason for the Do
Until code. But I needed to see if this concept would even work first and
then add the subform. It's probably not the best method, but I couldn't
figure out any other way to control the Family_ID. To ensure that every
member had the same ID while making sure that ID didn't get assigned outside
of that family. Do you know of an easier way?

As far as the required fields you mentioned, I'm not sure I understand what
you are saying. Are you saying due to the nature or the way the code is
written, that the user will not have the ability to input anything? Like
going from 3 - 2 - 1 instantly, without waiting for input? Thanks for your
help.

| On Tue, 16 Oct 2007 18:49:50 -0400, "Sam" <none> wrote:
|
| For sure this line is wrong:
| DoCmd.GoToRecord , , acNewRec = acNext
|
| The last argument will be treated by VBA as a boolean expression:
| Is acNewRec equal to acNext?
| The answer is False, which has a value of 0. The argument value
| acPrevious has a value of 0, so this code would make you go to the
| previous record.
| Replace it with:
| DoCmd.GoToRecord , , acNewRec
| if you want to go to the new record (as the comment suggests).
|
| There is another logic error as well. If I read your code correctly,
| you are trying to add "members" number of records by going to NewRec
| several times.
| That should fail, because your records should have several required
| fields that you're not filling out. Having several completely blank
| (perhaps with exclusion of Autonumber) records does not make sense.
|
| -Tom.
|
|
| >Hi,
| >
| >On the form itself, there is a setting that I'm trying to capture.
| >(txtTmember = Total family members) I need the ability to allow the user
to
| >add new records for the family and come back with an error when
txtTmembers
| >reaches zero. Only if they happened to hit the NEXT button and all
members
| >have been added. I know a little VB and just learning VBA.
| >
| >Private Sub cmdNEXT_Click()
| >
| >Dim txtTmembers As Long 'Capture value in form txtTmembers
| >Dim members As Long 'Set temp var
| >txtTmembers = members 'Move that value to members
| >members = members - 1 'Form default is '1'. adjusting
| >
| >If members > 0 Then
| > Do Until members <= 0
| > members = members - 1 'Countdown
| > DoCmd.GoToRecord , , acNewRec = acNext 'Create new record
| > Loop
| >Else
| > MsgBox "Warning: " & "Total members of the family have been added." &
_
| > & vbNewLine &_
| > "To create a New Member record for a NEW Family, " & _
| > & vbNewLine & _
| > "please click on 'New Member' button at the top of the form."
| >End If
| >End Sub
| >
| >No errors are generated but the msgbx is always displayed regardless of
| >txtTmembers value. I don't think I'm capturing the value in txtTmembers.
If
| >this is correct, who do I reference it correctly? Or is there something
else
| >wrong? Thanks for any feedback.
| >
 
To
work around this, I was going to create a subform to add the rest of the
family members and replicate the Family_ID. That's the reason for the Do
Until code.
Click to expand...

If you use a subform, you don't need ANY CODE AT ALL.

Simply make the Family_ID the Master and Child Link Field of the subform, in
the subform properties. It will automatically synchronize the subform records
to the Family_ID on the mainform, and automatically insert the main form's
Family_ID when you create a child record on the subform.

Use the tools Access provides - you don't need to reinvent them!

John W. Vinson [MVP]
 
| On Wed, 17 Oct 2007 01:37:47 -0400, "Sam" <none> wrote:
|
| >To
| >work around this, I was going to create a subform to add the rest of the
| >family members and replicate the Family_ID. That's the reason for the Do
| >Until code.
|
| If you use a subform, you don't need ANY CODE AT ALL.
|
| Simply make the Family_ID the Master and Child Link Field of the subform,
in
| the subform properties. It will automatically synchronize the subform
records
| to the Family_ID on the mainform, and automatically insert the main form's
| Family_ID when you create a child record on the subform.
|
| Use the tools Access provides - you don't need to reinvent them!
|
| John W. Vinson [MVP]

If that works, you will save me a great deal of aggravation. Thanks! I'll
try it first thing in the morning.
 
Back
Top