Auto fill existing record, allow new entry

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I think this should be fairly simple to fix. I currently have a form with a
sub form (with another subform) and they are linked One-to-Many from the main
form down to the the third subform (three tables in total). In the main form
header, I created a combo box that allows me to type in a customer's phone
number and if that phone number exists in the table, the rest of the form
automatically fills with the existing record.

Background
The main form is TS Contacts and contains strictly the phone number, company
name, and address info. The first subform/table, Associates, contains names,
email addresses, extensions, etc., specific to the individual. The final
subform/table, Issues, contains the details of the phone call: Date, product
category, stock code, reason code, action code and narrative. We provide
technical services so having things separated like this allows us to track
repeat callers, and repeat issues and so on.

The problem is that the combo box only allows me to type in existing
numbers. What I need is to do both, enter existing numbers and have the form
auto fill and enter a new number, have access recognize it is a new number
and only autofill the phone number section of the form so I don't have to
retype it and I know it is a new number.

The text box's row source is

SELECT [TS Contacts].ContactID, [TS Contacts].CustomerPhNbr FROM [TS
Contacts];

The Limit to List is set to 'yes', and it will not let me set it to 'no', I
assume because of the current source info. How can I modify the source to do
what I need? Or what code do I place where (afterUpdate) to do this? Sorry
if I gave too much info, but I understand you guys are working in a vaccuum
so I wanted to provide as much as possible. Thanks

Jason
 
Also, the AfterUpdate for the combo box is set to


Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
You did not say whether or not Combo25 is unbound, but if not it should be,
like any search text box or combo box.

BTW, consider giving combo boxes, etc. useful names such as cboFindPhone.
It will make your life easier in the long run than if you need to remember
arbitrary numbers when you are writing code.

You mentioned the text box row source. In context it seems clear you mean
the phone number combo box row source.

Why are you converting Combo25 to a string? If ContactID is numeric, why
convert?
rs.FindFirst "[ContactID] = " & Nz(Me.Combo25, 0)

If ContactID is a string in the first instance, and a number in the row
source, you need a different arrangement of quotes:
rs.FindFirst "[ContactID] = " " " & Str(Nz(Me![Combo25], 0)) & " " " "
(spaces added between the quotes for clarity)

I can't quite sort out how your database is put together, so this is aimed
at the code you posted rather than the general approach, which raises a few
questions. Is the main table a Company table that includes the company't
main phone number? Is there a company table? How does that fit into the
picture? Can several people at the same company have the same phone number?
Many companies have a general number, then extensions.

The Associates table contains information specific to the individual. Which
individual is that?

Back to the combo box, in what way will the combo box not let you change the
Limit To List property? As with any error or malfunction about which you
post, it is best to provide details.

Jason said:
Also, the AfterUpdate for the combo box is set to


Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub




Jason said:
I think this should be fairly simple to fix. I currently have a form with
a
sub form (with another subform) and they are linked One-to-Many from the
main
form down to the the third subform (three tables in total). In the main
form
header, I created a combo box that allows me to type in a customer's
phone
number and if that phone number exists in the table, the rest of the form
automatically fills with the existing record.

Background
The main form is TS Contacts and contains strictly the phone number,
company
name, and address info. The first subform/table, Associates, contains
names,
email addresses, extensions, etc., specific to the individual. The final
subform/table, Issues, contains the details of the phone call: Date,
product
category, stock code, reason code, action code and narrative. We provide
technical services so having things separated like this allows us to
track
repeat callers, and repeat issues and so on.

The problem is that the combo box only allows me to type in existing
numbers. What I need is to do both, enter existing numbers and have the
form
auto fill and enter a new number, have access recognize it is a new
number
and only autofill the phone number section of the form so I don't have to
retype it and I know it is a new number.

The text box's row source is

SELECT [TS Contacts].ContactID, [TS Contacts].CustomerPhNbr FROM [TS
Contacts];

The Limit to List is set to 'yes', and it will not let me set it to 'no',
I
assume because of the current source info. How can I modify the source
to do
what I need? Or what code do I place where (afterUpdate) to do this?
Sorry
if I gave too much info, but I understand you guys are working in a
vaccuum
so I wanted to provide as much as possible. Thanks

Jason
 
Yes, Combo25 is unbound. To be honest, until January 08, I had never touched
Access; I have been teaching myself since my boss asked me if I could set up
a database to track calls. So I don't do much with code; I try to let Access
do most of the work for me, and when I need a code, I try to find it in the
forum and then paste my field names in where I think they work until
something works. I built the combo box using the wizard that popped up. So,
I don't really know what you mean by 'string', since it was all done
automatically.

Yes, the main table contains the Company info: Company phone number, Company
Name, Customer Level (Distributor, Contractor, Architect/Engineer, Consumer,
etc.), and Address information. The one-to-many child to the main table is
the Associates Table which includes the individuals name, Extension (from the
main number if a company), alternate phone number (cell or office) and a
comment field (mostly to warn each other of past issues with the customer).
I wanted a one-to-many from the main to the associates because there are many
different people who could call us from a distributor or engineering firm.
There likely wouldn't be many different 'consumers' calling from the same
number, but this was the only way to keep everything sorted the way I needed
it. We will just leave the Company Name blank or put 'Private' in that field
for those who are not with a company.

The last table, named Issues, is so that we can track all of the issues each
individual has called with and track its progress.

ContactID is just an autonumber which I use to link the main table to the
associate table. Likewise, the Associate table has autonumber AssociateID
(as well as ContactID to create the link) and the Issues table has autonumber
IssueID (and AssociateID for the link).

I'm still trying to learn all the quirks that go with creating code, and a
lot of it still confuses me. Hope this clarifies what I am doing better.

BruceM said:
You did not say whether or not Combo25 is unbound, but if not it should be,
like any search text box or combo box.

BTW, consider giving combo boxes, etc. useful names such as cboFindPhone.
It will make your life easier in the long run than if you need to remember
arbitrary numbers when you are writing code.

You mentioned the text box row source. In context it seems clear you mean
the phone number combo box row source.

Why are you converting Combo25 to a string? If ContactID is numeric, why
convert?
rs.FindFirst "[ContactID] = " & Nz(Me.Combo25, 0)

If ContactID is a string in the first instance, and a number in the row
source, you need a different arrangement of quotes:
rs.FindFirst "[ContactID] = " " " & Str(Nz(Me![Combo25], 0)) & " " " "
(spaces added between the quotes for clarity)

I can't quite sort out how your database is put together, so this is aimed
at the code you posted rather than the general approach, which raises a few
questions. Is the main table a Company table that includes the company't
main phone number? Is there a company table? How does that fit into the
picture? Can several people at the same company have the same phone number?
Many companies have a general number, then extensions.

The Associates table contains information specific to the individual. Which
individual is that?

Back to the combo box, in what way will the combo box not let you change the
Limit To List property? As with any error or malfunction about which you
post, it is best to provide details.

Jason said:
Also, the AfterUpdate for the combo box is set to


Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub




Jason said:
I think this should be fairly simple to fix. I currently have a form with
a
sub form (with another subform) and they are linked One-to-Many from the
main
form down to the the third subform (three tables in total). In the main
form
header, I created a combo box that allows me to type in a customer's
phone
number and if that phone number exists in the table, the rest of the form
automatically fills with the existing record.

Background
The main form is TS Contacts and contains strictly the phone number,
company
name, and address info. The first subform/table, Associates, contains
names,
email addresses, extensions, etc., specific to the individual. The final
subform/table, Issues, contains the details of the phone call: Date,
product
category, stock code, reason code, action code and narrative. We provide
technical services so having things separated like this allows us to
track
repeat callers, and repeat issues and so on.

The problem is that the combo box only allows me to type in existing
numbers. What I need is to do both, enter existing numbers and have the
form
auto fill and enter a new number, have access recognize it is a new
number
and only autofill the phone number section of the form so I don't have to
retype it and I know it is a new number.

The text box's row source is

SELECT [TS Contacts].ContactID, [TS Contacts].CustomerPhNbr FROM [TS
Contacts];

The Limit to List is set to 'yes', and it will not let me set it to 'no',
I
assume because of the current source info. How can I modify the source
to do
what I need? Or what code do I place where (afterUpdate) to do this?
Sorry
if I gave too much info, but I understand you guys are working in a
vaccuum
so I wanted to provide as much as possible. Thanks

Jason
 
Hi Jason,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would like to use combo box to
show the existing numbers and also would like to make the form auto fill
according to some rule. Is it correct? If I have misunderstood anything,
please let me know.

To better understand the issue, please confirm the following to me. my
email address is (e-mail address removed)(remove online).
1 the sample database.

2 the screen-shot of the result you want.

Besides, I do some additional research for you.
How to: Allow Users to Add Items to an Unbound Combo
Box:http://msdn.microsoft.com/en-us/library/bb243766.aspx
Scrolling Records with Unbound Combo Box Takes
Longer:http://support.microsoft.com/kb/119708
Building a Better
ComboBox:http://msdn.microsoft.com/en-us/library/ms996411.aspx
Combo Box:http://msdn.microsoft.com/en-us/library/ms697637(VS.85).aspx

Hope the above helpful.
If there is anything unclear, please let me know.

Have a nice day!

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
A string is text (maximum 255 characters), as opposed to number, date,
yes/no, and a few others. The Str function converts a number to a text
representation of the number, with a leading spce if it is a positive
number. Frankly, I'm not quite sure when the Str function would be used. I
can tell you that Access may handle the number 10 and the characters "10"
differently.

I noted that you used this:
Set rs = Me.Recordset.Clone

That should be:
Set rs = Me.RecordsetClone

Note that a period was removed before "Clone". When you have written code
you should click Debug >> Compile in the VBA editor (the code editor). That
will tell you when you have written code with incorrect syntax and other
technical problems.

Consider this expression:
"[ContactID] = " & Str(Nz(Me![Combo25], 0))
ContactID is a number field (autonumber, which is a type of Long Integer).
Str converts Combo25 (the bound column, which you can determine from the
combo box property sheet) into a text representation of the number. The
expression is trying to match a text field with a number field. Access can
often do that rather well, but I don't know if this is one of those cases.
It is far better to compare a number to a number or text to text than to
rely on Access sorting out dissimilar data types.

The use of quotes within quotes is described in some detail here:
http://allenbrowne.com/casu-17.html

It sounds as if you have the database set up properly, at least in general.
There is one company, identified by the CompanyID, with any number of
associates. I don't quite follow how you are identifying unaffiliated
Associates. You say you may use "Private" as the company name in the main
table, but is there a separate main table entry for each such Associate, or
are they all grouped under a single "Private" record?

When you search by phone number, what number exactly do you want to use?
The expression you are using for After Update in Combo25 is looking for a
record in which ContactID matches the bound column of Combo25, which is
probably CustomerID. In other words, Combo25 presents you with a listing of
phone numbers that appear in the main records, along with the Contact
(Company) ID with which the number is associated. If the phone number for
Acme company is 111-2222, selecting or typing 111-2222 in Combo25 should
cause the Acme record to appear on the main form, along with all Associates
for Acme company. It will not look for a phone number in the Associates
table. If you want to add a phone number you will need to do so in a text
box (or maybe a combo box) that is bound to the PhoneNumber field. If there
is only one main phone number per company, a search that does not turn up a
phone number means you will first need to create a Company record.

I'm not sure I'm explaining this well. Part of the problem is that I am
trying to understand the exact situation. If you could provide some sample
data with fake names and numbers it may be clearer what you are trying to
do.

Jason said:
Yes, Combo25 is unbound. To be honest, until January 08, I had never
touched
Access; I have been teaching myself since my boss asked me if I could set
up
a database to track calls. So I don't do much with code; I try to let
Access
do most of the work for me, and when I need a code, I try to find it in
the
forum and then paste my field names in where I think they work until
something works. I built the combo box using the wizard that popped up.
So,
I don't really know what you mean by 'string', since it was all done
automatically.

Yes, the main table contains the Company info: Company phone number,
Company
Name, Customer Level (Distributor, Contractor, Architect/Engineer,
Consumer,
etc.), and Address information. The one-to-many child to the main table
is
the Associates Table which includes the individuals name, Extension (from
the
main number if a company), alternate phone number (cell or office) and a
comment field (mostly to warn each other of past issues with the
customer).
I wanted a one-to-many from the main to the associates because there are
many
different people who could call us from a distributor or engineering firm.
There likely wouldn't be many different 'consumers' calling from the same
number, but this was the only way to keep everything sorted the way I
needed
it. We will just leave the Company Name blank or put 'Private' in that
field
for those who are not with a company.

The last table, named Issues, is so that we can track all of the issues
each
individual has called with and track its progress.

ContactID is just an autonumber which I use to link the main table to the
associate table. Likewise, the Associate table has autonumber AssociateID
(as well as ContactID to create the link) and the Issues table has
autonumber
IssueID (and AssociateID for the link).

I'm still trying to learn all the quirks that go with creating code, and a
lot of it still confuses me. Hope this clarifies what I am doing better.

BruceM said:
You did not say whether or not Combo25 is unbound, but if not it should
be,
like any search text box or combo box.

BTW, consider giving combo boxes, etc. useful names such as cboFindPhone.
It will make your life easier in the long run than if you need to
remember
arbitrary numbers when you are writing code.

You mentioned the text box row source. In context it seems clear you
mean
the phone number combo box row source.

Why are you converting Combo25 to a string? If ContactID is numeric, why
convert?
rs.FindFirst "[ContactID] = " & Nz(Me.Combo25, 0)

If ContactID is a string in the first instance, and a number in the row
source, you need a different arrangement of quotes:
rs.FindFirst "[ContactID] = " " " & Str(Nz(Me![Combo25], 0)) & " " " "
(spaces added between the quotes for clarity)

I can't quite sort out how your database is put together, so this is
aimed
at the code you posted rather than the general approach, which raises a
few
questions. Is the main table a Company table that includes the company't
main phone number? Is there a company table? How does that fit into the
picture? Can several people at the same company have the same phone
number?
Many companies have a general number, then extensions.

The Associates table contains information specific to the individual.
Which
individual is that?

Back to the combo box, in what way will the combo box not let you change
the
Limit To List property? As with any error or malfunction about which you
post, it is best to provide details.

Jason said:
Also, the AfterUpdate for the combo box is set to


Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub




:

I think this should be fairly simple to fix. I currently have a form
with
a
sub form (with another subform) and they are linked One-to-Many from
the
main
form down to the the third subform (three tables in total). In the
main
form
header, I created a combo box that allows me to type in a customer's
phone
number and if that phone number exists in the table, the rest of the
form
automatically fills with the existing record.

Background
The main form is TS Contacts and contains strictly the phone number,
company
name, and address info. The first subform/table, Associates, contains
names,
email addresses, extensions, etc., specific to the individual. The
final
subform/table, Issues, contains the details of the phone call: Date,
product
category, stock code, reason code, action code and narrative. We
provide
technical services so having things separated like this allows us to
track
repeat callers, and repeat issues and so on.

The problem is that the combo box only allows me to type in existing
numbers. What I need is to do both, enter existing numbers and have
the
form
auto fill and enter a new number, have access recognize it is a new
number
and only autofill the phone number section of the form so I don't have
to
retype it and I know it is a new number.

The text box's row source is

SELECT [TS Contacts].ContactID, [TS Contacts].CustomerPhNbr FROM [TS
Contacts];

The Limit to List is set to 'yes', and it will not let me set it to
'no',
I
assume because of the current source info. How can I modify the
source
to do
what I need? Or what code do I place where (afterUpdate) to do this?
Sorry
if I gave too much info, but I understand you guys are working in a
vaccuum
so I wanted to provide as much as possible. Thanks

Jason
 
Hi Jason,

This is Mark. I am writing to follow up the issue.

If I can assist you anything related to the issue, please let me know.

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
Back
Top