Access form suddenly started giving duplicate field error message!!

Hi Experts,

I have a continuous form bound to a query, and all of a sudden I am getting an error message while opening the screen that the ID field could refer to more than one field.

However I have no idea about that other field, and as you can see in second attachment, the query does not have two ID fields..

There must be something else triggering it, can you please help me find out what..!!
untitled.bmp
untitled1.bmp
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Is PatientEmployeesSchedule a table or a query?

If it is a query, check that query to make sure that there are not ID fields in two or more queries and make sure you explicitly identify which table the ID field is to come from?
0
bfuchsAuthor Commented:
Hi, Its a table..
0
PatHartmanCommented:
Rather than posting the graphic, post the actual SQL string.  For all we know, there cold be another table hidden behind the one we see.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
@Pat,
there you go.
SELECT PatientsEmployeesSchedule.*, 0-([VisitDateOnNotesYN]+[VisitShiftYN]+[NurseSignatureYN]+[NurseCredentialsYN]+[PatientsSignatureYN]) AS CountVisits, IIf([VisitDateOnNotesYN]+[VisitShiftYN]+[NurseSignatureYN]+[NurseCredentialsYN]+[PatientsSignatureYN]+[MemoFilledYN]=-5,"Yes","No") AS VisitNotesAppr, Format([Day],"ddd") AS WD, IIf(IsNull([employeeid]),0,mydatediff([from],[to])) AS Minutes, IIf(IsNull([employeeid]),mydatediff([from],[to]),0) AS Minutes2
FROM PatientsEmployeesSchedule
WHERE (((PatientsEmployeesSchedule.Day)>=Date()-365))
ORDER BY PatientsEmployeesSchedule.Day, PatientsEmployeesSchedule.From;

Open in new window

0
PatHartmanCommented:
Thanks.  I don't see any duplication in the query.  
Delete the RecordSource from the form.  Save it,  close it.  Then open the form and put back the RowSource.  If that doesn't fix the problem, try a compact and then a decompile.  Hopefully you won't have to rebuild the db by importing all the objects.  It looks like the form itself might be corrupted.

PS - Day is a reserved word as is From.  Both will be a problem when used in VBA so be careful to use square brackets.  Best practice of course is to not use embedded spaces, special characters, or reserved words as column names.
0
bfuchsAuthor Commented:
@Pat,

I realized this has to do with the other issue I am currently facing with this form, see below
http://www.experts-exchange.com/questions/28711782/Access-combo-box-limitation.html#a40967177

Will keep you posted

Thanks,
Ben
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Hi Pat,

Ok by now I know that the issue is as follows, I have a combo box named EmployeeID displaying 2/3 columns ID, Name (and title).

When I sort by that field the form order by property is "Lookup_EmployeeID.Name", that is how access refers to these ComboBox.Column(x)...

Now that approach seems to have a limit how many records it can deal with, apparently we ran over the limit...(somewhere between 10/20K).

As you can see on link above, I still don’t have a satisfactory solution for that issue, I guess one solution would solve both problems.

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Pat,

See the following link for the final solution to this problem.

http://www.experts-exchange.com/questions/28711782/Access-combo-box-limitation.html

Thanks,
Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.