Combo Box Requery

[Guest]

Hi. I'm new to Access ADPs and am using Access 2003 to create a SQL Server
2000 database.

I'm using a combo box in Table 1 to look up Table 2. With Table 1 still
open, after adding a record to Table 2, then doing F9 on the combo box, the
added record still doesn't appear in the list. Only when I close down Table
1 and reopen it does the added record in the other table appear in the combo
box list. I've tried F9, SHIFT-F9 and CTRL-F9.

How do I force a requery of a combo box in a table in datasheet view ? Is
there another shortcut key ?

 

[Robert Morley]

This isn't just a problem on your end, it's a problem with Access (both 2002
and 2003, and presumably 2000 as well). If anybody has an answer to this,
please share!


Rob

 

[Tim Ferguson]

How do I force a requery of a combo box in a table in datasheet view ?

.... use a form rather than a table sheet. That is what forms are for.
Tables are for storing data, not displaying or editing them.

B wishes


Tim F

 

[Robert Morley]

While I agree with that on a user level, at the admin level (especially with
the options that Access affords you), it's nice to just go directly to a
table without having to create a form for every last little thing. It's
really annoying that the lookup feature has this one small bug in ADPs.


Rob

 

[Malcolm Cook]

Paul,

How do I force a requery of a combo box in a table in datasheet view ? Is
there another shortcut key ?

If anyone has a simpler way than the below, I'd be obliged, but the below works in most cases for me (and could be refiined a bit
I'm sure)

Good luck
--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


Public Function ACControlRequery(Optional ctl As Access.Control)
'HowTo: force a requery of a combo box in a table in datasheet (or table) view
' add to your AutoKeys macro an entry with:
' Macro Name: "^+{F9}" (which is control-shift-F9)
' Action: "RunCode"
' Function Name: "=ACControlRequery()"
' (note: F9 _should_ do this but does NOT in ACC2002)
If ctl Is Nothing Then Set ctl = Screen.ActiveControl
On Error GoTo HandleErr
With ACControlParentForm(ctl)
If .Dirty Then .Dirty = False ' which saves the record - this may raise an error - i.e. record can't be saved for some reason!
End With
With ctl
Select Case .ControlType
Case 115 'which is a "Table View Text Box" - not documented AFAIK
.Requery
Case acListBox, acComboBox
If (.RowSourceType = "Tables/Views/Functions") Then
.Requery
End If
End Select
' TODO: what if ctl has no RowSource, but rather has a (unbound) recordset? Then, ctl.recordset.requery? probably. untested.
End With

ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else ' unanticipated!
MsgBox Err.description
End Select
Resume ExitHere
' End Error handling block.
End Function





Public Function ACControlParentForm(ctl As Access.Control) As Access.Form
'returns the form the control is 'on', searching up through parents if needed
'(which may be intervening tab controls / pages). Works also if ctl is on
'native access table
Dim Parent As Object
Dim ParentTypeName As String
Set Parent = ctl
Do
Set Parent = Parent.Parent
ParentTypeName = TypeName(Parent)
Loop Until ParentTypeName Like "Form_*" Or ParentTypeName = "Subform" Or (ParentTypeName Like "T_*") 'NB: T_ is used by acces for
naming table objects displayed without a form"
Set ACControlParentForm = Parent.Form
End Function

 

[Robert Morley]

Of course, you WOULD send such an interesting solution so late on a Friday
night when I'm not going to be at work until Monday morning! <sigh>

Thanks for the tip, though...like I said, looks interesting, will be curious
to see how well it works (and from the OP if it works in 2003 as well...no
reason it shouldn't).

 

[T.G.]

Make a form to open and edit your tables.

Each control on your form has a "after update" event. When you change
information in one table by use of a control (Combobox, textfield or what
ever) use the after update event to change the source data for your other
combobox. (me.comboboxname.requery in visual basic)

If you edit a table directly or in a datasheet be aware that the data will
not be updated in the database until you move the cursor to the next record
or cick outside the control. You can use an event on the client to force
storage of the record, for instance after change of one specific field in a
datasheet.

Regards

Tore

 

[Tim Ferguson]

, it's nice to just go directly to a
table without having to create a form

It's "nice" to get in my car and drive it about without putting in any
petrol or checking the tyre pressures or cleaning the windscreen -- but
that don't make it a sensible thing to do. If you want to use Access like a
dumb spreadsheet, why not get a freeware copy of 123?

All the best


Tim F

 

[Robert Morley]

Who said anything about using it like a dumb spreadsheet? Especially if
you're using lookup tables for a lot of things, you don't NEED to build a
form for every last table, nor would you probably want to.

I mean, think about it...suppose you have a Gender column looking up genders
in tblGenders. Well, typically there are only two genders, so why would you
build a form to maintain tblGenders? Maybe you decide to expand it to
include transgendered people, etc., but chances are you're only ever going
to have a few rows at most. Why on earth would you build a form for that?


Rob

 

[Sylvain Lafontaine]

Yeah but in the case where you have to add a new gender, you only have to do
it a very few times in a life.

This is clearly not the case with the original post; where you have to add
new foreign records on a very regularly basis.

 

[Robert Morley]

Okay, true enough.


Rob

 

[Robert Morley]

By the way, I just got a chance to try this out and it works like a charm.
Thanks for the post, Malcolm! Taught me something new, too...I've been
using Access since 2.0 first came out, and I'd never even considered the
idea that Screen.ActiveControl would be functional in a table, much less the
whole chain of logic that derives from that (i.e., forms, parent forms,
etc.).



Rob

 

[Robert Morley]

Further to that, though, let me give you an example of how I use tables
where forms would be useless to me.

I have a main mailing list table (tblContacts). I have a very nice form
that allows users to edit the contacts, but because of the nature of the
business, all contacts at the user level are linked to an account, so the
form is designed with account-specific contact info (acctTeam table) on the
same form to make it easier for the user. Now, further to this, we also
have divisional contacts in a separate table, similar in concept to the
acctTeam table, but with different info for people who manage an entire
division, not just a few accounts.

Now, the divisional contacts table also has a ContactID column with a combo
box lookup which pulls from tblContacts, but I'm the only person who will
EVER edit this table. Why on earth would I bother to create a form for it?
Isn't this the EXACT sort of thing that combo box lookups in tables were
designed for...to be used in tables where (hopefully) only administrators
go? Obviously someone thought so, or they never would've designed combo box
lookups to work in tables. (Of course, by the same argument, obviously
others think differently or they would've noticed the bug before it spanned
at least two-maybe three-different versions of Access, including all their
Service Packs and other bug fixes.)

What it really comes down to is that each of us has our own opinions and
ways of doing things. Don't get upset with someone just because they think
and work differently than you do.



Rob

 

[Malcolm Cook]

Robert,

Glad my chiming in helps.

What does 'OP' mean in your previous post? "original poster"?

Also, I used it in both AC2002 and AC2003/XP with equal success.

Cheers,

Malcolm

 

[Robert Morley]

Yes, "OP" means "original poster".

Glad to hear that it works an Access 2003. I'm on Access 2002, so wasn't
sure if the bug still existed in 2003.


Thanks,
Rob

 

[Tim Ferguson]

you don't NEED to build a
form for every last table, nor would you probably want to.

I never ever ever build a form for every table.

I build a form for every process. If there is a process associated with
maintaining a table, then I build a form to support it.

Tim F

 

[Tim Ferguson]

Obviously someone thought so, or they never would've designed combo
box lookups to work in tables.

There are several things in Access that relegate it into the "toy"
platform in the eyes of other database developers on "real" systems. The
quaint but misguided fashion for putting "tbl" in front of object names
is one; the presence of the "look up field" is another. I regret this
because when you get up close, Jet is a pretty fine database engine and
Access is a flexible and usable rapid development platform, but it will
continue to get a rotten press as long as it's aimed at the Janet and
John level of user. The type of users, in effect, that get drowned in any
case as soon as they step off the dumb-spreadsheet kind of appliction.

FWIW, it seems that the Access-as-toy party has won the debate because
Jet development is being taken over by the Access UI team. I think it's
time to be off to MySQL before they put in the paper clip telling you not
to put financial data into an integer field.

What it really comes down to is that each of us has our own opinions
and ways of doing things. Don't get upset with someone just because
they think and work differently than you do.

I get upset because of two things. Firstly, posts like yours may be seen
by people who know about databases but not much about Access, who will
merely have their suspicions confirmed that access is a plaything for
people who don't know their way round Codd or Date. The second reason is
that they may be seen by people who don't know much about Access or
databases, and who will then think this is a good and reasonable way to
use it; and whose horizons will forever be shortened.

All the best


Tim F

 

[Robert Morley]

Like I said, different ways of thinking. I look at most of your points and
disagree with them either in part or in whole, but frankly, this isn't the
place to get into this kind of discussion. The original post has been
answered with two different solutions, and that's the end of it as far as
I'm concerned. I just bitched someone else out in another NG for exactly
this kind of "mine is bigger than yours" discussion that serves no purpose
but to bicker pointlessly. Everybody's got their favourite apps and the
apps they think are toys, we simply disagree on which ones are which.

Oh and just for the record, Hungarian Notation became popular with languages
like VB/VBA, but actually pre-dates it. The inventor, Simonyi Károly, was
working for Xerox at the time and only much later did he move to Microsoft.




Rob

 

[Craig Alexander Morrison]

Tim

Jet development is being taken over by the Access UI team. I think it's
time to be off to MySQL before they put in the paper clip telling you not
to put financial data into an integer field.

You should also check out IBM DB2 Express-C this is free and has no filesize
restrictions unlike the express versions of SQLS and Oracle. Its only limits
are 4GB of memory and 2CPUs.

 

[Craig Alexander Morrison]

Oh and just for the record, Hungarian Notation became popular with

languages like VB/VBA, but actually pre-dates it. The inventor, Simonyi
Károly, was working for Xerox at the time and only much later did he move
to Microsoft.

For the record "tbl" and other such fripperies first appeared in a 1993
Smart Access article and has subsequently appeared in the ADH books. I don't
mind one using tags in code but not for database objects. Charles Simonyi
actually worked on Access 1; I am not sure what he thinks of the
Lesynski/Reddick extensions.

It is a good sign of an amateur with limited experience of other products.
Some amateurs are very good programmers though.

Nearly all formally trained Relational (or SQL) Database designers would
find this "tbl" tag laughable.