Combo Box Requery

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 ?

--
Paul Anderson

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

=?Utf-8?B?aHVkZGll?= wrote in
:

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

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

"Tim Ferguson" wrote in message
...
=?Utf-8?B?aHVkZGll?= wrote in
:

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

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

ExitHe
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

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).

"Malcolm Cook" wrote in message
...
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

ExitHe
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

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


"huddie" wrote in message
...
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 ?

--
Paul Anderson

"Robert Morley" wrote in
:

, 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

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

"Tim Ferguson" wrote in message
...
"Robert Morley" wrote in
:

, 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

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Robert Morley" wrote in message
...
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

"Tim Ferguson" wrote in message
...
"Robert Morley" wrote in
:

, 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