Problem adding a subform record

[BruceM]

I have a Contacts database that includes a table for the basic stuff
(FirstName, LastName, Street, City, etc.) and a main form based on that
table, and a linked table (and subform fsubPhone based on that table) for
phone numbers, since there can be several. I contrived to make the phone
number subform look and act like a text box. When there is zero or one
phone numbers for the contact, there is no scroll bar on the subform; for
two or more phone numbers there is a scroll bar. I set this in the
subform's current event (the subform itself, not the subform control).
A further note is that when there are two or more phone numbers, the
subform's Allow Additions property is set to No so that there isn't a blank
record when scrolling, which I already know will confuse people who are
determined to be confused.
Here are a few issues, and the ways I found to deal with them:
1) I found I couldn't tab out of the subform control, but I could click out
of it. To solve the tabbing problem I added a tiny text box on the subform,
with transparent border and back color. Its Got Focus event is to set the
focus to the next control on the main form. I think that's sort of standard
procedure.
2) Since there is no scroll bar with one phone number, and Allow Additions
set to False with two or more phone numbers, I added a command button (a
label, acutally) to the main form with the following as its Click event:

blnPhone = True
Me.fsubPhone.Form.AllowAdditions = True
Me.fsubPhone.SetFocus
(blnPhone is a Public Boolean I defined in a standard module)

The subform's Enter event is:
If blnPhone = True Then
DoCmd.GoToRecord , , acNewRec
End If

This works fine as long as the subform does not have the focus, but if the
cursor is in the subform it doesn't work. The cursor just stays there, and
I need to click a main form control (i.e. leave the subform) before the
Click event will work.

What I wonder here is first, whether I have made this more difficult than it
needs to be. If so, what can I do to improve this thing? Training the
users is not really an option. I will do that, but many people will use the
database only occasionally, so I can't expect them to remember the finer
points (even if they were all willing to try, which some are not, but that's
a topic for another forum). Second, why can't I get the Click event to work
with the cursor in the subform's text box? Finally, it seems I have the
choice of running events in one of several different places. For instance,
I can set Allow Addition to true when needed to add a number as I have done,
or I can do it in the subform's Got Focus event (or some other subform
event, no doubt). Is one preferable to another as a general rule? For what
its worth, this database will never have more than a few hundred records in
any one table.

 

[Nikos Yannacopoulos]

Hi Bruce,

My penny's worth:

What I wonder here is first, whether I have made this more difficult than it
needs to be.

IMHO, yes, you have... I would use a plain listbox instead of a subform,
which I guess has by default the behavior you tried to impose on the
subform with all those tricks... If you also want people to be able to
add phone numbers, just a command button and some simple code will do
it, while the UI is more intuitive to the Access-illiterate user.

HTH,
Nikos

 

[BruceM]

Your penny has appreciated in value if I can simplify this, not only for
this project but for others with a similar situation. The trouble is that I
cannot figure out how to do this. It is frustrating because I know that it
is relatively simple, but I can neither figure it out on my own or figure
out how to phrase a search.
Here is the basic set-up. I have a Contacts form based on a Contacts query
that is based on a Contacts table. tblContact contains ContactID (PK),
FirstName, LastName, etc. It does not include a phone number field. For
that I have the linked table tblPhone, which contains PhoneID (PK),
ContactID (FK), and Phone.
Here's where I don't get it. Is tblPhone included in the record source
(qryContacts) for the main form? Is the list box bound to a field? I'm all
for a command button and some simple code, but I don't know what that code
might be. Is the list box unbound, and the button opens a pop-up form for
adding a phone number? I'm sure I could understand a brief explanation, but
I am completely lost here.

 

[BruceM]

By the way, the default behavior of a list box apparently is to display all
records. There does not seem to be a way to filter them to display, in this
case, just the phone numbers related to the contact whose record appears on
the form, if hours of searching through the newsgroups is any indication.
Please excuse my frustration, but I am even more discouraged than before now
that you have suggested there is a way to solve the problem, but I cannot
discover how to implement it.

 

[BruceM]

OK, I think I got it. The Phone table contains PhoneID (PK), ContactID
(FK), and PhoneNumber. I added the Phone table to the main form's record
source (which is a query based on tblContacts); I added
[Forms]![frmContacts]![ContactID] (the PK from the main form's record
source) as the criteria for ContactID in the list box's row source SQL; and
I had to requery the list box in the main form's Current event, otherwise it
would stay with the phone number from the first record.
The only thing left (I'll leave it until Monday) is to add a command button
to add a phone record. You say that a command button and some simple code
will do it. I don't know if you mean a command button to open a pop-up form
for adding a phone number, but that's all I can think of.

 

[Nikos Yannacopoulos]

Hi Bruce,

First of all, sorry for the confusion. On the other hand, it looks like
I gave you something to think about over the weekend!

To begin with, I would not include tblPhione in the form's recordsource,
the first and foremost reason being that this would result in getting as
many records per contacts as the phones for it in tblPhone.

I would leave the listbox unbound, and use the form's current event to
assign its rowsource, something like:

Me!lstPhones.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " Me.ContactID

assuming (a) there is a ContactID control in the form (which may not
even be visible), and (b) ContactID is numeric. This approach does not
require a RowSource declared in the form's design.

Alternatively, the listbox might have a RowSource property like:

"SELECT Phone FROM tblPhone WHERE ContactID = " & _
[Forms]![frmContacts]![ContactID]

in which case the Current event would simply be:
Me!lstPhones.Requery

To add a phone, maybe you don't even need a pop-up form, a simple
InputBox should be enough, since the only piece of input required is the
phone number. The code behind the command button could be something like:

Dim strPhone As String, strSQL As String
strPhone = InputBox("Please type in phone", _
"Phone Addition for Contact")
strSQL = "INSERT INTO tblPhone ( ContactID, Phone ) " & _
"SELECT " & Me!ContactID & ", '" & strPhone & "';"
CurrentDB.Execute strSQL, dbFailOnError

where I have assumed PhoneID to be Autonumber, and the Phone field to be
text.

I hope my naming assumptions above are obvious.
HTH,
Nikos

 

[BruceM]

Nikos,

Thanks for the reply. Yes, the naming conventions are obvious, and the
input box works well for adding a phone number. I removed tblPhone from the
form's Record Source, and used the form's Current event to assign the list
box Row Source. I added Me.lstPhone.Requery to the command button in order
to get the new number to show up on the list without having to navigate away
from the main record and then back to it. I also added error handling to
the command button code in case the user selects Cancel from the Input box
(otherwise Error 3315 shows up to say that Phone can't be a zero-length
string). However, I can't find a way to display the newly-added number as
the current selection in the list box. In other words, after clicking OK in
the Input box, I would like the number I just added to appear in lstPhone.
I expect that MoveLast needs to be in there somewhere, but I can't discover
where, nor whether my assumption is correct.
By the way, when I experimented with the Row Source SELECT statement you
offered as a second choice it only worked when I removed all of the quotes.
I'm starting to get the hang of all of the quote marks, but it still puzzles
me at times.
The reason I have given so much attention to this is that I will be using a
similar system in other places in various projects, so it is worth the time
to figure it out.

Hi Bruce,

First of all, sorry for the confusion. On the other hand, it looks like I
gave you something to think about over the weekend!

To begin with, I would not include tblPhione in the form's recordsource,
the first and foremost reason being that this would result in getting as
many records per contacts as the phones for it in tblPhone.

I would leave the listbox unbound, and use the form's current event to
assign its rowsource, something like:

Me!lstPhones.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " Me.ContactID

assuming (a) there is a ContactID control in the form (which may not even
be visible), and (b) ContactID is numeric. This approach does not require
a RowSource declared in the form's design.

Alternatively, the listbox might have a RowSource property like:

"SELECT Phone FROM tblPhone WHERE ContactID = " & _
[Forms]![frmContacts]![ContactID]

in which case the Current event would simply be:
Me!lstPhones.Requery

To add a phone, maybe you don't even need a pop-up form, a simple InputBox
should be enough, since the only piece of input required is the phone
number. The code behind the command button could be something like:

Dim strPhone As String, strSQL As String
strPhone = InputBox("Please type in phone", _
"Phone Addition for Contact")
strSQL = "INSERT INTO tblPhone ( ContactID, Phone ) " & _
"SELECT " & Me!ContactID & ", '" & strPhone & "';"
CurrentDB.Execute strSQL, dbFailOnError

where I have assumed PhoneID to be Autonumber, and the Phone field to be
text.Nikos,


I hope my naming assumptions above are obvious.
HTH,
Nikos

 

[Nikos Yannacopoulos]

Bruce,

input box works well for adding a phone number. I removed tblPhone from the
form's Record Source, and used the form's Current event to assign the list
box Row Source. I added Me.lstPhone.Requery to the command button in order
to get the new number to show up on the list without having to navigate away
from the main record and then back to it. I also added error handling to
the command button code in case the user selects Cancel from the Input box
(otherwise Error 3315 shows up to say that Phone can't be a zero-length
string). Good!

However, I can't find a way to display the newly-added number as
the current selection in the list box. In other words, after clicking OK in
the Input box, I would like the number I just added to appear in lstPhone.

It should already be appearing, as a result of the:
Me.lstPhone.Requery
you (correctly) added after the CurrentDb.Execute. To select it, you
need one more line:
Me.lstPhones = strPhone

I expect that MoveLast needs to be in there somewhere, but I can't discover
where, nor whether my assumption is correct.

MoveLast is a recordset action, but you are not dealing with a recordset
here!

By the way, when I experimented with the Row Source SELECT statement you
offered as a second choice it only worked when I removed all of the quotes.
I'm starting to get the hang of all of the quote marks, but it still puzzles
me at times.

I know the feeling! I suppose we all go through that stage.

The reason I have given so much attention to this is that I will be using a
similar system in other places in various projects, so it is worth the time
to figure it out.

I couldn't agree more!

Regards,
Nikos

 

[Nikos Yannacopoulos]

By the way, when I experimented with the Row Source SELECT statement you
offered as a second choice it only worked when I removed all of the quotes.

But of course! My stupid mistake, my brain was still in VBA mode... you
wouldn't use the quotes in query design.

Sorry about this!

Nikos

 

[BruceM]

Nikos,

Thanks again. Adding Me.lstPhone = strPhone did the trick. I see what you
are saying about it not being a recordset.
Here's an additional curiosity: When I add the phone number, that record is
highlighted until I either close the database and reopen it, or I add
another phone number to any record. For example, if I add a phone number
999-0001 to Contact1, the list box contents are highlighted for 999-0001
only. If I scroll in the list box to a previously-entered number
(999-0000), that old number is not highlighted. Now if I navigate to
Contact2, then back to Contact1, 999-0001 is again highlighted, even though
I scrolled to 999-0000 before navigating away from the Contact1 main record.
If I add a new phone number to either Contact1 or Contact2, that new phone
number is highlighted for as long as the database is open or until I add a
new phone number for any company. The same thing occurs if I click in the
list box: that number is highlighted, and appears every time I navigate to
that record until I highlight another number by either selecting it or
because it is the most recently entered phone number record.
Summary: there can be only one highlighted phone number record for one
Contact; that record is highlighted every time I move to that record during
the current database session. When I open the database the phone numbers
are sorted by the order in which they were added, which is fine. However,
if I click the most recently entered phone number for Contact1, that is the
number that appears every time I navigate to that record until I select
another phone number anywhere in the database. Tabbing into the list box
does not cause the record to be highlighted.
I can set the list box Locked property to Yes, which eliminates the problem
of highlighting a record by clicking it, but it does not change the behavior
when I add a phone number. Do you know what is causing this behavior? It's
not a big deal here, but as I mentioned I intend to use this technique in
other places.
Also, can I edit an existing record using the Input box, or will I need a
form for that? If the latter, I think I will just go with the same form
both for adding and editing.

 

[Nikos Yannacopoulos]

Bruce,

Add this line of code:

Me.lstPhones = -1

at the end of the Current event code, so whenever you move into another
record no phone is selected.

To edit, you can still use an InputBox. The idea is that you first
delete the old phone by means of a DELETE query, and then add the new
one in the same way as you did with the Add button. Sample code:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf & _
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
Set db = CurrentDb
strSQL = "DELETE FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblPhone ( ContactID, Phone ) " & _
"SELECT " & Me!ContactID & ", '" & strNew & "';"
db.Execute strSQL, dbFailOnError
Me.lstPhones.Requery
Me.lstPhones = strNew
Set db = Nothing
End Sub

Of course, you could use a recordset operation instead:

Private Sub cmdEditPhone_Click()
Dim strOld As String, strNew As String, strSQL As String
Dim db As Database
Dim rs As New ADODB.Recordset
If Me.lstPhones = -1 Then
MsgBox "You must select a phone number to edit.", vbExclamation, _
"Cannot Edit Number"
Exit Sub
End If
strOld = Me.lstPhones
strNew = InputBox("Please type in the phone number to replace:" & vbCrLf & _
"* " & strOld, "Edit Phone Number", strOld)
If strNew = "" Then Exit Sub
rs.Open "SELECT Phone FROM tblPhone WHERE ContactID = " & _
Me.ContactID & " AND Phone = '" & strOld & "'", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
rs.Fields(0) = strNew
rs.Update
rs.Close
Set rs = Nothing
Me.lstPhones.Requery
Me.lstPhones = strNew
End Sub

Regards,
Nikos

 

[Nikos Yannacopoulos]

Oops! In the recordset operation solution, you do not need to

Dim db As Database

It was forgotten there from the action query solution.

Nikos

 

[BruceM]

Thanks once again, Nikos. I have it working smoothly now, and with much
less effort than I had originally used. This stuff will be valuable far
beyond the current project. That new line of code is wonderfully simple,
and does the trick. I really appreciate the time you have taken to explain
all of this and to write the sample code. This thread is going into my
personal library of Access Help documents. I am using the first option for
editing the number because I understand it; I prefer to use code I
understand, so if it needs adjusting I know what to do. The recordset
option is a bit beyond me in a few places, so I will save it for future
reference.

 

[Nikos Yannacopoulos]

By the way, the recordset operation option is more efficient in terms of
resource requirements, as each action query employs a recordset
operation behind the scenes, so it's one vs. two.
Of course, for this use (one record in a local table) you will never be
able to tell the difference in performance, but in more demanding
situations it might become noticeable.

Regards,
Nikos

 

[BruceM]

There's one more thing I need to learn, then. Seriously, though, thanks for
the information. Again, it could be significant beyond the current project.