DLookup & DSum repaints

[Kidaeshus]

I'm trying to bring in an input form that was designed in Access 2000.
The form takes timecard input in hours, workstations and work
operations assigned to jobs.

When the job number is input, DLOOKUP is used to retrieve the textual
name of the job in one read only text box and the 6 character customer
code (think ACCPAC) in another. The work centre and operation IDs
similarly retieve the 'real name' associated with the 3 digit ID code.
Inputing the manhours uses DSUM to retrieves the manhours per day and
per week in respective read only text boxes.

Everything works as it should, but the text box for the customer code
and both manhour totals do not 'repaint'. The underlying information
IS there because if I click on the read only text boxes, the
information appears. I've tried a 'Repaint Object' macro, but I could
only repaint the whole form triggered by 'on Update'.

I see no discernable difference in the properties between these text
boxes and the ones that DO work. This Access 'frontend' worked fine in
Access 2000. If I copy a DLookup statement from one that isn't working
to one that is, it works. Copy that entire text box to another
location and the no-repaint returns.

I suppose I'm looking for a repaint of these text boxes triggered by
an 'On Change' but I'm unsure how to accomplish that. I've tried the
following module as well (id txtCustNo, event OnChange):

Private Sub txtCustNo_Change()
Me.Repaint
End Sub

Suggestions welcome.

Environment:
Running Office 2007 under Windows 7 Pro on brand new quad core boxes
with ASUS P5KPL-CM motherboards with integrated video and 4Gbs RAM.
The data tables are evenly divided between local and ODBC links to a
MS SQL database.

 

[Oddvar Slettbakk]

skal slettes

 

[Kidaeshus]

Don't know wat Oddvar is talking about. Tht'a a broken link.

UPDATE:

I've since noticed that upon loading the form, "Calculating..." is in
the status bar (lower left). If I click to another window (e.g. mail
or whatever), "Calculating..." changes to "Form View" and everything
displays as it should. Actually USING the form puts "Calculating..."
back into the lower left and no-repaint returns.

I've looked and relooked, but I cannot see anything 'hanging'.
Deleting objects (and retrying) within the form one-by-one does
nothing.

 

[Larry Linson]

Well, I don't know either, but suspect Oddvar is a "kiddie" looking for a
"playpen" on the Internet. No matter what language he is speaking, a
two-word response is unlikely to be helpful to anyone.

As to your problem, consider using a Combo Box to allow the user to _select_
rather than have to type in the value, and include the columns you would
retrieve with the Domain function DLOOKUP as additional columns, then, in
the AfterUpdate of the Combo Box, you can copy the <ComboBoxName>.Column(2)
or <ComboBoxName>.Column(3) to the appropriate Controls on your Form.
Domain aggregate functions can be useful, but generally should be a "last
resort" because they sometimes are "dead-dog-slow" (but not always).

Hmm. You say what versioon of Access you are bringing the form in FROM, but
not what you are bringing it in TO. If you are using Access 2007, someone
else can certainly give you a more accurate answer than I can.

Larry Linson
Microsoft Office Access MVP

 

[Kidaeshus]

Hmm. You say what versioon of Access you are bringing the form in FROM, but

not what you are bringing it in TO. If you are using Access 2007, someone
else can certainly give you a more accurate answer than I can.

Yes it is Access 2007 c/w all appropriate updates.

I've also found that if I run an embedded macro from one input
control's OnExit event that quickly does a GoToControl to one of the
no-repaints, then hops back to the next control in the tab order, it
DOES repaint. Perhaps this is a bandaid solution until I can get the
time to completely rewrite the form in a Access 2007 environment. At
least payroll will be able to be input in the interim.