Error 2759 - Text Box linked to a combo box query returns error message when I sort on a split form

[Eddy Sincere]

I have started using text boxes to display information from a combo box query on a split form. The combo box will display (and change) an item number and the text bow will display the item description (can't change) (Attachment 1). It works perfectly (Attachment 2).



However, when I sort the information on the split form by clicking the little triangle at the top of the column I get error message 2759 (attachment 3).

When I click the OK button the sort finishes and the form works fine (attachment 4).

I regularly compact & repair and haven't had anything go wrong. The error message only started showing up when I began using the text boxes. I'm concerned that I'm building an error into my DB and that it will eventually fail. Is this concern valid? Is there a way to avoid the error or fix something so the error doesn't appear?

Attachment 1



Attachment 2



Attachment 3



Attachment 4

[rpeare]

hrm any chance you could create a sample database with some data in it to play around with, hard to tell (for me) without something to use since I don't use bound forms.

[Missinglinq]

...any chance you could create a sample database with some data in it to play around with...

I agree with this suggestion; to be honest, screen shots are very seldom helpful in trouble-shooting, as they show the 'what,' i.e. the end product in one case, without showing the 'how,' which is what you need help with.

Is the field in question defined as one of the new multivalued Fields? I seem to remember that they cannot be sorted or (I think) filtered on.

Linq ;0)>

[Eddy Sincere]

Linq - Thanks for letting me know the screenshots don't add value! Is it better to just describe my situation and just avoid the shots altogether?

Also, I've cleaned up the data and compacted and repaired the DB but am not sure how to post it. Can you give me some quick instructions?

[June7]

Follow instructions at bottom of my post.

[Eddy Sincere]

Here you go. Here's the pathway to get to the problem:

COMPONENTS → Physical → Click on the triangle in the column heading to sort by the Item # → Sort A to Z → Error statement displays → Click "OK" → The statement disappears and the records are sorted

The error statement only started appearing after I began using text field to display query data from a combo box. I can probably just get rid of the text field but they are super helpful.

Ultimately, I'd like to get rid of the statement. I'd also like to know if I'm building in an error that will compound over time.

Specifications - Post.zip

[June7]

I do get the error when trying to sort on any field of frmTstCSpc. You are right, removing the expressions that reference combobox columns eliminates error.

An alternative for displaying related data is to include tstP and mthd tables in the form's RecordSource. This will make the related data available. Bind textboxes to fields from tstP and mthd and set them as Locked Yes and TabStop No. Maybe even Enabled No.

SELECT tstPSpc.*, tstP.TstP, tstP.MthdRcrdNmbr, tstP.CtgryTstRcrdNmbr, mthd.MthdDscrptn
FROM mthd RIGHT JOIN (tstP RIGHT JOIN tstPSpc ON tstP.TstPRcrdNmbr = tstPSpc.TstPRcrdNmbr) ON mthd.MthdRcrdNmbr = tstP.MthdRcrdNmbr;

[Eddy Sincere]

I'll give that method a try if I can't figure out why the error message keeps popping up. While it seems a lot more complicated it's probably far more reliable. Thanks!

[ssanfu]

I haven't/don't used split forms, but what I find interesting is that if you go to

GENERAL → Items → Click on the triangle in the column heading to sort by the Item # → Sort Z to A

no error.

If you try
TESTS → Heavy Metals → Click on the triangle in the column heading to sort by the Item # → Sort Z to A

no error.

If you try
COMPONENTS → Heavy Metals → Click on the triangle in the column heading to sort by the Item # → Sort Z to A

errors.

Maybe some corruption???



PS
Just curious....Is there a reason that table "tstA" does not have a PK? (why the autonumber field is not set as PK?)

[Eddy Sincere]

Thanks for pointing out the lack of a PK, I've fixed it. A while back I switched from manual numbering in the PK to autonumbers and I must have forgotten to make the new field the PK.

Regarding your observations, they are consistent with the problem I'm experiencing. The reason only one of the three forms you tried gave you the error message is that it uses a text field which displays data from a combo box. The other two forms do not use this type of field and, therefore, don't give the error.

I'm trying to determine is using this type of field is going to cause long term problems, like corruption. It's weird because it gives the error and, as soon as the OK button is hit, the form acts normally. I really want to use this method of displaying data because it's easy and quick. But if I can't figure out the reason I'll switch to the method June7 mentioned.

Also, I was just in another DB I built that uses the same text boxes tied to a combo box query and I don't get the error message when I sort. Very strange. Maybe your'e correct in that I have a corrupted table or DB. Maybe I should try copying the information into a fresh tables.

Thanks for taking the time to look at my DB. As always, any observations are much appreciated. I'm still (very) new to this whole thing and want to improve.

[ssanfu]

You have a table "tstPSpc" and a field in the same table named "tstPSpc"???

I see that in the "Heavy metals" table form also....

[ssanfu]

I've been experimenting with your dB, mostly because I've never used a split form.
I only messed (technical term) with COMPONENTS → Physical. (and changed the button color - easier for me to find)

It is the two text boxes that are causing the problem. Deleting the two text boxes eliminates the error.

Your form is based on a table.... I always use a query for form/report record sources, even if the query is based on one table.

I created a query and changed the form record source to the query "query1".
I don't know your naming convention, so you should rename the query.
Now the sorting works without an error. (modified FE attached)


-------------------------
One other thing. I noticed the PK field for table "ItmNmbrs" is a text type field. It works, but not the best thing to do. If for any reason, the PK field gets changed, you will have a lot of orphaned records in the many side of the relationship. I always use an autonumber type field for the PK field.


If you're interested, read these:

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[Eddy Sincere]

Interesting. A lot cleaner too.

(Please forgive my ignorance) But isn't using a query a roundabout way to enter information into a table? Is the point that you do everything you can to prevent a user from accessing the table directly? Because you want to prevent any sort of (un)intentional tampering with the tables?

Also, I know I broke a rule with the PK on the ItmNmbrs table but thanks for pointing it out. I had actually designed the entire bleedin' DB without auto numbers before I figured out I needed them. I went back and changed the everything but that one table. Because that table is linked to all sorts of stuff, changing the PK was just too much trouble and would require all the queries, tables, forms and reports to be revised.

Please marked this thread as solved! Thanks a million.

[ssanfu]

But isn't using a query a roundabout way to enter information into a table?

Not at all. Think of a query as a virtual table. Use the query just like a table.
A form is still used to enter/edit/view data. In a query, you can sort by multiple fields and you can limit the records displayed. It is just something I've always done.

[Eddy Sincere]

Just a quick follow up. I've created queries for my forms and everything is running much smoother. No more errors and the queries have a lot more flexibility. Thanks again for the help, it's been a lifesaver.