OK, I may have been overly tired when I replied the last time. I'm now awake
and ready to go through this!
First, let me be sure that I am understanding correctly.
One, the row source for the combo box (named FullName) is the table named
mwpat.
Two, the field list for the table named mwpat is the following:
Chart Number
Last Name
First Name
Middle Initial
Street 1
Street 2
City
State
Zip Code
Phone 1
Phone 2
Social Security Number
Signature on File
Patient Type
Patient ID #2
Sex
Date of Birth
Assigned Provider
Country
Date of Last Payment
Last Payment Amount
Patient Reference
Balance Date
Created
Employment Status
Employer
Employee Location
Employee Retirement Date
Work Phone
Work Extension
SOF Date
Billing Code
Patient Indicator
User Code
Unique Health ID
Date Modified
Three, the bound column for the combo box (named FullName) is 7. This means
that the bound column is the field named City in the above list.
Four, the record source for the form is the query named mwpatQuery.
Five, the SQL for the query named mwpatQuery is the following:
SELECT mwpat.[Last Name], mwpat.[First Name], mwpat.[Middle Initial],
mwpat.[Social Security Number], mwpat.[Date of Birth], Trim([Last Name])
& ", " & Trim([First Name]) & " " & [Middle Initial] AS FullName,
mwpat.[Chart Number] FROM mwpat
ORDER BY mwpat.[Chart Number];
Am I correct so far?
Assuming that I am, the value that is stored in the combo box is NOT the
FullName nor the ChartNumber value that you think is there. You need the
bound column of the combo box to be 1, not 7. That way, when you select a
record in the combo box, you'll have the ChartNumber value available for
your FindFirst action.
Then, because ChartNumber is a text field, you need to modify the FindFirst
step as TC and John have stated; namely, you need to surround the value with
' marks (or with " marks):
Me.RecordsetClone.FindFirst "[Chart Number] = '" & Me![FullName] &
"'"
There are ' marks on either side of the Me![FullName]. I'm expanding the
view so that you can see them, but you do not want to leave in the spaces
that I'm using here:
Me.RecordsetClone.FindFirst "[Chart Number] = ' " & Me![FullName] &
" ' "
Now, let me know if this works after you change the combo box's bound column
and you change the code step.
--
Ken Snell
<MS ACCESS MVP>
stork29 said:
It does. See my #3. below. I listed mqquery contents there. It is the
last item in the query. This continues to give me same error msg (3070). I
tried John Spenser's suggestion, but would not compile. I had a form text
box named same (Chart Number; I used form wizard to create original form,
then changed to suite my needs). I changed its name to see if this would
help. It did not. Is there some unknown problem with access and field types?
The field is recognized as a text field by access in it's table properties.
There are 2 primary keys listed for the table (other is [Social Security
Number]). I use this in query also, but have discovered that this field is
sometimes empty, so really is useless at this point. I am going off on
tangents I know, but really have become stumped. Debug box-Watch lists all
parameters as correct values.
Maybe I should start over with the form, clean up name conflicts and
import the table instead of link it. This will create another problem for me
as linked table is updated continuously so importing is not ideal. Query
should be local though, so I can't see why this doesn't work. I will try
TC's suggestion ( please see his/her reply), but there appears to be 1 too
many quotes or 1 too few quotes there. Have already tried variation of TC's
suggestion which did not work.
There must some solution (probably very simple, that I have overlooked). Thanks for any and all help.
Mike Becker
----- Ken Snell wrote: -----
What is the SQL statement for mwpatQuery? This query MUST contain a field
named [Chart Number].
--
Ken Snell
<MS ACCESS MVP>
stork29 said:
Ok
1. mwpat
This is the linked table. This may be an error. I tried putting
the query
for the row source and I get error #3077 instead.
There are over 30 fields. There is no easy way for me to copy
them,
but here goes:
(Chart Number Last Name First Name Middle Initial Street 1 Street 2
City
State Zip Code Phone 1 Phone 2 Social Security Number Signature on File
Patient Type Patient ID #2 Sex Date of Birth Assigned Provider Country
Date of Last Payment Last Payment Amount Patient Reference Balance
Date Created Employment Status Employer Employee Location Employee
Retirement Date Work Phone Work Extension SOF Date Billing Code Patient
Indicator User Code Unique Health ID Date Modified)
This is the form record source.
expression in
the query:
mwpatQuery:
(SELECT mwpat.[Last Name], mwpat.[First Name], mwpat.[Middle
Initial],
mwpat.[Social Security Number], mwpat.[Date of Birth], Trim([Last Name]) &
", " & Trim([First Name]) & " " & [Middle Initial] AS FullName, mwpat.[Chart
Number]
FROM mwpat
ORDER BY mwpat.[Chart Number]
The bound column is 7, as it should be.
When I changed rowsource for combo box to mwpatQuery, then I get
Error #
3077. I will list the debug data for this when I get this:
Watch : + : Me.RecordsetClone![Chart Number] : "ABBDA000" :
Object/Field :
Form_EDCFrm.FullName_AfterUpdate
Watch : + : Me![FullName] : "AUGBR000" : Object/Combobox : Form_EDCFrm.FullName_AfterUpdate
These values appear to be correct. Me![FullName] : "AUGBR000" is
what I
picked.
Me.RecordsetClone![Chart Number] : "ABBDA000" is the first Chart
Number
in the query.
Is the [FullName] a conflict since it is used for both name of
combo box
and the expression in the query? Thanks for any help. box. If
it's a
table name, list the field names; if it's a query name, post
the SQL
of the
query.
2) Post the recordsource of the form.
3) By any chance, is the [FullName] field or control using a
"lookup"
field
in the row source or control source of the combo box?
Ken Snell
<MS ACCESS MVP>>> "stork29"
----- Ken Snell wrote: -----
What is the format of the Chart Name field?
Thanks for reply. No, Chart Name is name of field in both linked table and
query. Query is form record source. I looked at the query and
all is
ok. I
look at debug window and all appears ok. But access keeps
thinking
the value
of Chart Number is not a valid field. The dialog box actually
puts
the
current value of Current Number in it's message. Of course,
that
value is
invalid. My stump is that it should be using Chart Number as
the
field.
DeBug prints it out as correct. What is wrong? Thanks. Mike Becker
----- Ken Snell wrote: -----
Is Chart Number the name of a control on your form? You
must
use the
actual
name of a field in the form's recordset, not the control
name,
unless
the
two are the same and the control is bound to that field.
Using access 97. Linked table with query. Combo box lookup error.::
{Private Sub FullName_AfterUpdate()
'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Chart Number] = " &> Me![FullName]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub}
Where [Chart Number] is query field name for linked table from odbc
connected table. When executes recordsetclone.findfirst statement,
get "The
Microsoft Jet database engine does not recognize <name> as a valid
field
name or expression. (Error 3070)" where <name> is the value
of
[Chart
Number], not value of [FullName]. All fields are correct and Debug
window
shows everything to be correct. What have I done wrong or
how
do I
get
around this problem? Anybody with similar problem? Thanks
"Chart Number" is a defined text field from Advantage
Database table
database. It links just fine. I designated it a unique field
and it
has
primary key designation in my database.( I also have another
key
designated
field, soc. security number, but this field is occasionally
empty, so
cannot
use it.) It is 8 characters in length and is unique field. It
is
compose of
5 alpha characters and 3 numeric on the end. The field name is
just
as you
see it. I have tried taking the space out of it by redefining
it in
the
query (by referring to it by an expression
[ChartNumber:[ said:
Number]), but this did not help. I placed brackets around it,
also
did not
