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
