Avatar of bfuchs
bfuchs
Flag for United States of America asked on

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
Microsoft AccessVBA

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Dale Fye

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?
bfuchs

ASKER
Hi, Its a table..
PatHartman

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
bfuchs

ASKER
@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

PatHartman

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.
ASKER CERTIFIED SOLUTION
bfuchs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
Hi Pat,

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

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

Thanks,
Ben