Add new subform record

[BruceM]

I have been wrestling for a while with a problem, and finally have to admit
that I cannot find a solution (assuming there is one). In a vendor database
I have decided to put phone numbers into their own table rather than having
Phone1, Phone2, etc. fields. I have made a phone table (tblPhone), linked
to the main (Vendor) table. Then on the main (Vendor Information) form I
have added a Phone subform. I have used the subform's Current event to show
the subform's vertical scroll bar if there is more than one phone number;
otherwise there is no scroll bar. The Phone text box on the subform and the
subform control itself are the same size as text boxes on the main form, for
a clean look.
The trouble is that if there are two or more phone numbers (and the
subform's scroll bar appears), the user can scroll down to the new record.
I can eliminate that by setting AllowAdditions to No, but then of course I
can't add a record (from time to time a number will be needed). I can put a
command button in the subform to set AllowAdditions to Yes and to go to a
new record, but if I add the command button to the right of the text box
then the scroll bar gets pushed to the right of the command button. I would
need to redesign my form to put the command button elsewhere. In any case I
should have the option of putting it where I would like it to be.
So I tried adding a command button to the main form, but I can't find a way
to move to a new subform record from there. The only way I can find to move
to a new subform record is first to go to the subform, then navigate from
there.
This (and other databases where I work) are for the most part used only
occasionally. The interface needs to be self-explanatory. I can retrain
people, but I really can't expect them to remember several weeks or months
later.

Here's what I need:
1) A phone number listing (related table and subform) to accomodate several
numbers for each vendor
2) A scroll bar on the subform only if there is more than one phone number;
otherwise no scroll bar
3) When there is a scroll bar, the default should be that there is no new
blank record at the end of the subform
4) A way to add phone numbers (by going to a new subform record)

If there is another way to accomplish this I am open to suggestion.

By the way, I know that data are stored in tables, not in forms and
subforms. It is often convenient to refer to the subform rather than to the
subform's record source. Perhaps I'm being lazy, but there it is.

 

[Guest]

Hi Bruce

I don’t know how you could add a new record with first going to (set focus)
the subform (maybe someone else will tell you). If it were me I would 1st
set the focus to the subform and then can add a new record

You could add a button to your main form (NewButton) and use this OnClick

Private Sub NewButton_Click()
Me.SubFormName.SetFocus
DoCmd.GoToRecord acForm,Forms!MainFormName!SubFormName.Form, acNewRecord
End Sub

____________________________________
Your NewButton
Private Sub NewButton_Click()

Next go to the subform
Me.SubFormName.SetFocus

Then add the record
DoCmd.GoToRecord acForm, Forms!MainFormName!SubFormName.Form, acNewRecord

That’s it (I think <
End Sub


The scroll bar items can all be set using the forms properties box
--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[BruceM]

Thanks for the reply, but I cannot get it to work. I finally got this code
to run without an error message:
Here is the code:

Me.fsubPhone.Form.AllowAdditions = True
Me.fsubPhone.Form.SetFocus
Me.fsubPhone.Form.RecordsetClone.AddNew

I changed the last line from the format you suggested because I kept getting
error 2498, wrong data type for one of the arguments when I used DoCmd.
However, when I tried:

DoCmd.GoToRecord acDataForm, Me.fsubPhone.Name, acNewRec
I received Error 2489, fsubPhone isn't open.

I tried adding a message box to the GotFocus event of the subform, but never
saw the message. However, the text box on the subform did receive the
focus. However, while the code at the beginning of this message finally
allowed me all the way through the code, it never went to a new subform
record.

I won't go into the details of all I've tried, but I've run out of ideas.
Do you see anything amiss here?

By the way, when I started I used your code just as you offered it, except
with my subform and form names, of course.

 

[BruceM]

OK, I think I have it worked out, with a few lingering questions. What I
ended up doing was to set a global Boolean (blnPhone) to True when the
button is clicked to add a new phone number. When the subform (phone
number) text box receives the focus it checks the Boolean. If it is true
(meaning the user wants to add a phone number) it moves to a new subform
record. I could not find a way of moving to a new subform record from the
button on the main form. If the Boolean is false (as when the user tabs or
clicks to the subform) then the subform does not go to a new record.
All I have in the command button on the main form is this (allow additions
is set to No in the property sheet, although I'm not sure that matters):

blnPhone = True
Me.fsubPhone.Form.AllowAdditions = True
Me.fsubPhone.SetFocus

Then in the subform's Current event:

If blnPhone = True Then
Me.ScrollBars = 2
Else
' Scroll bars only if there is more than one phone number, in
which case
' additions are not allowed (so that there is no blank record
when scrolling)
Select Case Me.RecordsetClone.RecordCount
Case Is < 1
Me.AllowAdditions = True
Me.ScrollBars = 0
Case Is = 1
Me.ScrollBars = 0
Case Is > 1
Me.ScrollBars = 2
Me.AllowAdditions = False
End Select
End If

And in the Got Focus event for the phone number text box on the subform:

If blnPhone = True Then
DoCmd.GoToRecord , , acNewRec
End If

There's really only one lingering question for now: I can tab into the
subform text box, but I can't tab out of it. If there are two phone
numbers, pressing the tab key will take the user to the second phone number,
but pressing it again will accomplish nothing. Same if there is one phone
number: the user can leave the subform text box only by clicking elsewhere.
Tabbing does nothing. Do you know what's up with that?