Intermittent disappearing data in subform (1 Viewer)

[B&R]

I have written a DB in 97 and converted to 2002. I then secured my DB in 2002. This is a multi user environment. My problem is that I have two different forms that has data disappear in them approximately once a day. Both forms have main forms with subforms based on the same table. Sometimes when we enter the data into the subform of a multiple line order, after hitting the enter key on the last field of one of the records the information just entered disappears from the record we just left. It also happens on single line item orders. However, this does not happen everytime. I cannot find a common thread to the disappearances based on part number used, the line in which the record is located that disappears. Nothing. The subform is based off of a query and I have checked to make sure that both tables have primary keys and relationships properly marked. I have also enforced referential integrity thinking this might be part of the problem. I have replaced the form with an original copy but no luck. Does anybody have any ideas. This is my first DB and it is quite complex. For the most part, the database works well except for the problem described above and an occasional corrupt record. I wanted to start with the disappearances first. Any suggestion is appreciated.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

A form will generally tell you if you have violated the constraints
defined by the table(s). If you see no error messages, then you
might find a problem with either a (.Requery) or a problem with
the stored query that drives the form. Maybe you just can't
see the data on the form, but it is really in the tables.

Need more info on this one ...

Wayne

 

[B&R]

Wayne - Thanks for your reply! I can definately use the advice. When the line disappears, we do not get any error messages. We then go and check the table and the data for that one line item is not there. The query for these forms pulls up 3 fields of information already in the table with 3 fields that need to be filled in. When the disappearances occur, only the 3 fields that were filled in disappear. One other piece of information. When we tried to fill in the information that disappeared directly into the table, one of the fields would not allow us to enter the information. The message search key not found in any records came up. I thought that this was odd because no search was being performed! I also noticed that this field was indexed. I removed the index and we then could enter the data with no message. However, both forms are using the same query to pull up the information. Maybe, I should create the query over just in case the query was corrupted in some way? The confusing part is that it works 98% of the time. Is there any other information that I could provide you with that might help me solve this problem? Please let me know.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

This requires seeing the database. If you can remove any
sensitive/unneeded data, forms, reports, etc.

Then compact/repair,
Then ZIP

Then attach it to your post.

Wayne

 

[B&R]

Wayne,

Again, I would like to thank you for taking the time to help me with this problem. We all need a little help sometimes. I will do as you asked but it will take me a couple of days to get access and copy. I will send by Monday. Do you have an email address that I could send it to versus the general post? I would appreciate it.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

This is a public forum and many here would benefit if it was
posted. Plus your problem will get more exposure.

If you can abide by the size limits (compact and ZIP) it would
be nice.

Wayne

 

[B&R]

Wayne - Well, I finally got the database small enough to send. There is not much left but you will find the one of the forms that seems to have the disappearances. You can use RA#11770 to open. Also, a few days ago I noticed that the primary key was gone from the RA Order Details table. I re-applied the key to the RC# field and the disappearances slowed dramatically and then stopped all together. However, the primary key disappeared again on Monday and we had a couple of disappearances. What would make the primary key disappear? Also, this is the same table that we occasionally have a record delete problem. Please let me know what you can determine from the database.

Thanks

 

[B&R]

Wayne - Have not heard back from you so I was wondering if you found anything in the database I sent. I have some additional info. I have the following tables in my database that I did not send. They are:
MSysAccessObjects1
MSysACEs 1
MSysObjects 1
MSys Queries 1
MSysRelationships 1

I don't know if somehow I accidently got these system files copied into my tables or what. I first remember seeing them when I secured the system. If I do not need them, I will delete. Also, I was looking in the database properties and under custom, I see properties Replicate??? Does this mean anything to my database and also I was wondering if you looked at the record locking? A lot of questions I know but I am still struggling for answers. Any response is appreciated.

Rhonda

 

[WayneRyan]

Rhonda,

Don't worry, If you IMPORT items into a database (Select ALL),
then if they exist, they will get the numeral "1" appended to the
end of their name.

I'll look at your download, I just have to finish my electrician's(1)
task.

see you soon,
Wayne

 

[B&R]

Wayne - Since I sent the database, I have changed a couple of things grasping at straws. I put an autonumber primary key on the Equipment and Archive tables since they had no unique key and no primary. Just thought it might be best to have one and I removed the index off of the prodid field in the RA Order Details table. Things had been fairly good but the primary key in the RA Order Details table was still disappearing every few days so I made these changes. Well, the disappearances are worst now. I will put the index back on the prodid field tomorrow in hopes that this will get us back to a little bit better. Look forward to your input.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

Can you re-send your current db? If you compact/repair, then
ZIP, it should fit with some data in it.

As it is, I can't make any sense out of it.

Wayne

 

[B&R]

Wayne - I have attached another copy with a couple of additional orders added to the RA order and RA order details tables. I understand how the DB could be confusing since I had to take out all the forms that direct you through. I will try to let you know to expect.

First, go to form (SHIP RA ORDER), it will ask for an order # you can use 11770, 11798 OR 11799. This is one of the screens where the disappearances occur. You will see three fields that need info, locid, serial# (if applicable) and ship date. The SAN-LS-9100 is a serialized item and requires a serial # to ship. You will find serial#'s available in the equipment table. These are the 3 fields that disappear on occasion. It will generally disappear after they go to the next line item. There are macros in each of these fields that go into the equipment table, find the equipment being shipped and make the necessary inventory transactions. A copy of the record that is going to be changed in the equipment file is first sent to the archive file. You can look into the RA order and Ra order details table to find the current orders ready to be shipped and then go to the Ship RA order (form) to ship them. I have another form that works off these same tables that has disappearances also but I did not have enough space to include. I tried to copy all tables, queries, forms and macros to ship an order. If you need additional information, please let me know.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

The ZIP file is "not in valid format".

Wayne

 

[B&R]

Wayne - Sorry for the delay, holiday travels. This is in Access 2002, please let me know if you have any problems opening this copy.

Thanks,
Rhonda

 

[WayneRyan]

Rhonda,

I opened it fine. At first look, the query that feeds your form is
too complex. It is making one record for each unique occurrence
of Order, Product and location.

Since you are using the Master-Child relationships you don't
need all of that in the query.

Your query just has to retrieve info for your main table (Orders).
Access will use the Master-Child relationships to control the
display and update of your other forms.

I didn't really look closely, but I think that on the surface you
should have one main form for orders.

The subform products is good, but I think you should make
the Product subform a parent to a new Location subform.
Use your same Master-Child relations.

If I can later, I'll take a look at it. If you do the above, it should
be as easy as making your query a little simpler. It is good that
you based your form on a query, because this paves the way
for you to do some nice searches with combo boxes or list boxes.

You'll also find examples here in the Search Facility.

That's it for a start, be seeing you ...
Wayne

 

[B&R]

Wayne - Great suggestion, I will wait to try until you have completed your review of the database. I have limited time to access the system so I would like to make all changes at one time. Please look at the indexes, structure of tables and record locking. I just want to make sure they all look good for a multi user system. Thanks again for your help.

Rhonda

 

[B&R]

Wayne - Sorry for the delay but I wanted to thank you for all your help! You suggestion was the answer to the problem. Again, thanks for taking your time to review my issue.

Thanks,
Rhonda