Help needed urgently

[Mike DFR]

Please see my post 'datasheet problem with newer version of Access'.
My deadline is approaching and I need help urgently.
The database works OK in Access 2002
The companyis now using 2003.
In the new version the subform datasheet flashes the contents and does not
update the list box.
The only way to stop, what appears to be 'flashing' or 'scrolling', is to
click on the row selector on the subdatasheet table.
I am sure I am not the only person with this problem.
I need a fix as soon as possible
Please help someone.

 

[Larry Linson]

Please see my post 'datasheet problem with newer version of Access'.
My deadline is approaching and I need help urgently.
The database works OK in Access 2002
The companyis now using 2003.
In the new version the subform datasheet flashes the contents and does not
update the list box.
The only way to stop, what appears to be 'flashing' or 'scrolling', is to
click on the row selector on the subdatasheet table.
I am sure I am not the only person with this problem.
I need a fix as soon as possible
Please help someone.

One good way NOT to get help in a newsgroup, urgent need or not, is to refer
back to some earlier post by subject, so people would have to look it up to
see what your "updates" signify. For other good suggestions on effective use
of newsgroups, see the FAQ at http://www.mvps.org/access/netiquette.htm. If
your need is truly urgent, make it easy for the participants here (all
volunteers, by the way) to help you by giving full, detailed information,
and to give a descriptive subject rather than to use the Subject to appeal
for urgency.

It's not clear if you are using Datasheet view in a Subform Control, or if
you have defined a Subdatasheet. I do not recommend either of these
approaches. Not using either, I have had no problems similar to these in
either Access 2002 nor 2003. In fact, the detail changes between those two
versions of Access were very minor, except for a few changes that pervade
all of Office.

Larry Linson
Microsoft Access MVP

 

[Mike DFR]

Sorry Larry, I am stll new to this, so to explain fully;
I am using datasheet view in a subform.
The requirement is to show service history for 12 months, if there is no
service the reason needs to be in the cell.
With the new version the subform datasheet is contiuously updating.
It give the appearance of flashing or scrolling, and does not update the
following list boxes..
For the search I am using;-
sub Combo#_AfterUpdate()
Me.ReordsetClone.FindFirst "['tablename']= '" & Me! [Combo#] & " ' "
Me.Bookmark = Me.ReordsetClone.Bookmark
Me.List6.RowSource = "SELECT DISTINCT ['table field etc etc........]
Me! [Combo#]= " "
End sub
The subdatasheet uses an 'Iif' statment to show field 1 or field 2,
(depending which one is empty).
In the subdatasheet design, each month uses a text box named Ser1, Ser2,
etc...
The control source uses the statement;-
=Iif([JAN]="" Or [JAN] Is Null, [JANNS],[JAN])
All the text boxes have the same statement with the field name changed, FEB.
MAR, etc..
If the control source points to only one field, i.e. 'JAN' all is OK.
The problem is trying to display the first or second field.
After trying different methods, this solution was given to me by a
participant here.

 

[Mike DFR]

The reason I posted this is because I had already posted a request with the
details and heard nothing.

 

[Larry Linson]

As I said, I don't recommend using Datasheet, whether directly or in a
Subform Control -- there are too many undesirable things that a user can do,
inadvertently or purposely.

Trying to go through and figure out what you have and what you are trying to
accomplish from the code you posted raised a number of "red flags" for me,
so I am going to have to ask you to back off, go back to the beginning,
explain what you have, and what you are trying to accomplish. Then you'll
need to give precise details of the Tables and Fields that you have (and
what they mean... a field named JAN doesn't tell me anything about it,
except that the field name appears to represent data, which would mean your
database isn't properly normalized, and unnormalized databases are harder to
work with). If your table names also represent data, then some serious
redesign would be in order.

Larry Linson
Microsoft Access MVP

 

[Mike DFR]

Because I am new to VB in Access, I was helped by someone who was quite
experienced in VB.

The database is used to keep a record of machines being serviced.
It was origonally in Excel which made it difficult to find specific data.
This database has evolved. It started out as just one form with a combo box
and several text boxes. The user typed in the machine serial number and all
the fields for that machine fill out the text boxes. (hence 'recordsetclone').
Then I was asked to search by customer, or technician, or salesperson.
So I just repeated what I had for all the combo boxes.
The machines are serviced over a 12 month period, so the next request was to
show all the machines for one customer over the 12 months. Hence the need for
the datasheet subform. (I could see no other way of displaying several
machines and several dates at the same time).
The next problem was that some months some machines are not serviced for
different reasons, now I need to put in extra fields to show the reason for
no service. There is no way I could fit 25 coloums on on page so the problem
started with choosing between two fields for each month, (service date or no
service reason).
So we have about 100 customers, over 600 machines and 16 technicians.
The requirement is to find the data by machine, customer, technician or
sales person, and show the 12 month history.
Each of the above is in seperate table, in the order listed and linked to
the next table.
The machine table contains; Machine model, serial number, and 2 fields for
each month, (service date, no service reason), and a lookup column for
customer.
The customer table contains; Account number, name, 3 address fields,
postcode, telephone, contact name, and a lookup column for technician.
The technician table contains; name, area, bonus level, and a lookup column
for sales person.
One salesperson has several technicians, one technician has several
customers and one customer has several machines.
All parts of the main form are locked except for the combo boxes, to update
a second form is used which is password protected.
I hope this all makes sense and is not to long winded.

It is interesting that I have had advice from programmers in the past and no
one has suggested the design is flawed.
This is in no way a critisism, I am grateful to you for your time and help
and am interested to see the alternatives.
Thank you for your help patience and understanding to date.