2007 issue

[mr moe]

I'm not sure if this is 2007 bug or issue, i've been using access for a long time, what i'm trying to do is modifying a combox query, so I put my expression then i save but nothing is saving, i come back to it and my expression is gone, anybody got a clue. thanks.

 

[pbaldy]

Sounds like the third one down:

http://allenbrowne.com/Access2007.html#Bugs

 

[dkinley]

Yah. Hate that - I've only noticed it when using the QBE to modify the query.

My trick for getting around it is after I have modified the query - I click Save on the QBE. Close that and then click Save on the form design. Close the form and go back into design mode.

If you have multiple combo boxes, you can apply this to all without closing the form as long as you pound the save buttons in that sequence.

It isn't 100% .. more about 99% ... so if the query is a biggun, I copy the SQL in the row source before I exit the form that way I don't have to do it all again but simply paste it back in.

-dK

 

[Simon_MT]

I put the SQL statement in VB and use:

combibox On Enter =LookupWhatever()
combibox On Exit =LookupClear()

Function LookupArtist()
    With Screen.ActiveControl
        .RowSource = "SELECT ...;"
        Call ListDisplay
    End With
End Function

and

Function LookupExit()
'   Set to null when exiting control
    With Screen.ActiveControl
        .RowSource = ""
    End With
End Function

I use this method as the code is totally reuseable. Access does like this treatment on the first control on a form or sub-form but there a trick around that.

Simon

 

[Pat Hartman]

Stored querydefs are a more efficient solution than SQL strings in code because they are preprocessed to calculate an execution plan which saves time and bloat at execution time.

 

[Simon_MT]

Pat,

I would like to know how this is done, as it sounds sensible. Numerous of these lookups are only used to deviate from the default values, on any form, few may actually be used at all but they are there if you want a different presentation.

Simon

 

[Pat Hartman]

Querydefs can be stored with arguments so the value is supplied at runtime. For example, when working with cascading combos, the RowSource of combo2 would be a query that refers to Combo1 to obtain its criteria.

Select ... From ... Where SomeField = Forms!yourform!combo1;