Access2007 weird error (any suggestions)

  • Thread starter Thread starter Mark Andrews
  • Start date Start date
M

Mark Andrews

Using Access2007 SP2
Here's how to duplicate this weird error (looking for suggestions to fix or
workarounds):

1. database with two tables tblContact and tblDonation (normal design with
ContactID foreignkey in tblDonation)

1. Have a datasheet on tblDonation with one control that is a combobox (use
to pick the contact associated with the donation).
with sql something close to:
Select tblContact.ContactID, tblContact.ContactName from tblContact
hide column 0 in the combobox

So you are looking at the donations made by all the contacts.

I named the control "ContactID" because certain functionality does NOT work
if you try to name it something
like "ComboContact". That's another Access2007 weird behavior.

In code:
2. Set the filter "[ContactID] IN (16,17,18)" Use three values that are
correct ContactID values
and then set the FilterON property to true

this works fine and should filter the list to the donations made by the 3
contacts

3. Try and sort the "ContactID" column by using the little arrow on the top
of the column
Notice it doesn't work

4. In code set the OrderBy property to ""
You will get error runtime error 3079 The specified field "|" could refer to
more than one table...

Would like #3 and #4 to work. Would prefer to filter instead of changing
the recordset (so I get the visual indicator that this column is being
filtered).

It has to do with how Access2007 deals with combo boxes in datasheets.
Example: the "ContactID" control for underlying field "ContactID" uses
[Lookup_ContactID] behind the scenes.

Any help is appreciated,
Mark
 
On Fri, 30 Oct 2009 15:09:10 -0400, "Mark Andrews"

Hi Mark,
I recreated your scenario using the Northwind sample application, and
#3 did work fine for me. I have this in the Form_Load:
Me.Filter = "[Customer ID] in (16,17,18)"
Me.FilterOn = True
And then I manually sort that column ASC or DESC.

#4 also worked for me, although I question the wisdom of this
statement. Rather than:
me.orderby=""
I would write:
me.orderbyon = false

If you like I can send you my sample db so you can compare with yours.

-Tom.
Microsoft Access MVP
 
I'll take a look, you can email it to (e-mail address removed)
take out the ___NOSPAM____

I assume you made a combobox in the datasheet called [Customer ID]? The
problem is only with a combobox.

I agree with your comments on #4.

I did end up modifying the recordsource (for this and some other reasons).

Please zip it, my email strips out mdbs and accdbs,
Mark


Tom van Stiphout said:
On Fri, 30 Oct 2009 15:09:10 -0400, "Mark Andrews"

Hi Mark,
I recreated your scenario using the Northwind sample application, and
#3 did work fine for me. I have this in the Form_Load:
Me.Filter = "[Customer ID] in (16,17,18)"
Me.FilterOn = True
And then I manually sort that column ASC or DESC.

#4 also worked for me, although I question the wisdom of this
statement. Rather than:
me.orderby=""
I would write:
me.orderbyon = false

If you like I can send you my sample db so you can compare with yours.

-Tom.
Microsoft Access MVP


Using Access2007 SP2
Here's how to duplicate this weird error (looking for suggestions to fix
or
workarounds):

1. database with two tables tblContact and tblDonation (normal design with
ContactID foreignkey in tblDonation)

1. Have a datasheet on tblDonation with one control that is a combobox
(use
to pick the contact associated with the donation).
with sql something close to:
Select tblContact.ContactID, tblContact.ContactName from tblContact
hide column 0 in the combobox

So you are looking at the donations made by all the contacts.

I named the control "ContactID" because certain functionality does NOT
work
if you try to name it something
like "ComboContact". That's another Access2007 weird behavior.

In code:
2. Set the filter "[ContactID] IN (16,17,18)" Use three values that are
correct ContactID values
and then set the FilterON property to true

this works fine and should filter the list to the donations made by the 3
contacts

3. Try and sort the "ContactID" column by using the little arrow on the
top
of the column
Notice it doesn't work

4. In code set the OrderBy property to ""
You will get error runtime error 3079 The specified field "|" could refer
to
more than one table...

Would like #3 and #4 to work. Would prefer to filter instead of changing
the recordset (so I get the visual indicator that this column is being
filtered).

It has to do with how Access2007 deals with combo boxes in datasheets.
Example: the "ContactID" control for underlying field "ContactID" uses
[Lookup_ContactID] behind the scenes.

Any help is appreciated,
Mark
Click to expand...
Click to expand...
 
Back
Top