problems with field values changing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Guys,

I am having one of those really weird glitchy problems with one of my forms.
I have an order form with a customer combo box in it. On two or three
occations, my client has reported that they have opened the form with an
existing customer in it, they have printed the order report and closed the
report. At a later date, the customer mysteriously changes to another
customer name(yesterday it happened). I have seen the two reports and indeed
they aren't making it up. I don't see them selecting another customer
manually, that doesn't make any sense. Anyhow, with customer personal info,
this is a big issue. I don't even know how to start to think of what to do.
Has anyone ever experienced this or does anyone have any solutions?

Thanks a bunch,
David
 
Sounds like you have a relationship issue.
The primary key of one table linking to the foreign key of other table(s).

Whenever your database gets corrupted and the links (relationships) are
broken, Access cannot tell which record in one table is related to which
record in another table.

This is where I would start looking if I were you.
 
Hi Eric:

Thanks so much for responding.

So, basically what you are saying is that particular sales order record gets
corrupted and doesnt know which customer to reference in the customer table?

We have experienced that day another record in another table was reported
corrupted and came up gobbledygook. They are on a multiuser network
situation FYI, I'm sure this is a large part of it.

So, what can I do as a database technician prevent these things from
happening in the future?

Thanks again,
David
 
Well, the first thing is to make sure your tables are normalized and
relationships are properly defined.... this is an important issue and can
cause much debate. Secondly, you need to look at what type of record locking
you are using. Thirdly, you should always set Compact on Close to yes in
Tools/Options/General. Fourthly, make sure you run periodic backups.

Make sure everyone is using the save version of Microsoft Access. Using
multiple versions of Access can cause you some grief.

I would clean up this database and after making sure everything works
correctly, I would print a copy of my relationships screen and put that away
for safe keeping. Anytime I start to get network problems with the database,
I would check my relationships. If they are broken, you still have work to do.


Hope this helps and maybe someone else can add some to this.
Hi Eric:

Thanks so much for responding.

So, basically what you are saying is that particular sales order record gets
corrupted and doesnt know which customer to reference in the customer table?

We have experienced that day another record in another table was reported
corrupted and came up gobbledygook. They are on a multiuser network
situation FYI, I'm sure this is a large part of it.

So, what can I do as a database technician prevent these things from
happening in the future?

Thanks again,
David
Sounds like you have a relationship issue.
The primary key of one table linking to the foreign key of other table(s).
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Hi Eric:

Thanks for your excellent answer. I do have some more questions. I hope
you have patience.

In regard to table normalization, is it best practice to create a
relationship between all fields that are dependant on another table. For
example,I do have a relationship between salesorder and salesorderdetail
tables. Do I also need a relationship between salesorder.customer and
customer.customerID, etc...? Should I index this salesorder.customer field
in sales order?
What is a "broken" relationship mean?

Also what record locking level do you suggest.

Thanks again,
David




Eric D via AccessMonster.com said:
Well, the first thing is to make sure your tables are normalized and
relationships are properly defined.... this is an important issue and can
cause much debate. Secondly, you need to look at what type of record locking
you are using. Thirdly, you should always set Compact on Close to yes in
Tools/Options/General. Fourthly, make sure you run periodic backups.

Make sure everyone is using the save version of Microsoft Access. Using
multiple versions of Access can cause you some grief.

I would clean up this database and after making sure everything works
correctly, I would print a copy of my relationships screen and put that away
for safe keeping. Anytime I start to get network problems with the database,
I would check my relationships. If they are broken, you still have work to do.


Hope this helps and maybe someone else can add some to this.
Hi Eric:

Thanks so much for responding.

So, basically what you are saying is that particular sales order record gets
corrupted and doesnt know which customer to reference in the customer table?

We have experienced that day another record in another table was reported
corrupted and came up gobbledygook. They are on a multiuser network
situation FYI, I'm sure this is a large part of it.

So, what can I do as a database technician prevent these things from
happening in the future?

Thanks again,
David
Sounds like you have a relationship issue.
The primary key of one table linking to the foreign key of other table(s).
Click to expand...
[quoted text clipped - 20 lines]
Thanks a bunch,
David
Click to expand...
Click to expand...
Click to expand...
 
David,

After you've set up different tables for each subject in your Microsoft
Access database, you need a way of telling Microsoft Access how to bring that
information back together again. The first step in this process is to define
relationships between your tables. After you've done that, you can create
queries, forms, and reports to display information from several tables at
once.

Whenever you have related data in two or more tables, you need to create a
relationship between those tables. You do the same thing in other, bigger
database engines such as MS SQL or DB2 by setting a primary key in one table
and a foreign key in another.

Use the help feature - turn off the office assistant. Search on RELATIONAL.
This will give you lots to read about relationships in MS Access.

As for Indexing, the right answer to that question is based on your table
structure and will be different for every db out there. I was once told to
index your date fields.

PS. Learn to use the Help function. At first, it may be difficult to find
and/or understand much of what's in there, but if you're serious about
learning MS Access, you will find the examples and documentation within the
Help function to be well worth it's weight in gold! Learning to use the Help
function within MS Access will take you beyond the beginner's level of Access
support/use.

Hi Eric:

Thanks for your excellent answer. I do have some more questions. I hope
you have patience.

In regard to table normalization, is it best practice to create a
relationship between all fields that are dependant on another table. For
example,I do have a relationship between salesorder and salesorderdetail
tables. Do I also need a relationship between salesorder.customer and
customer.customerID, etc...? Should I index this salesorder.customer field
in sales order?
What is a "broken" relationship mean?

Also what record locking level do you suggest.

Thanks again,
David
Well, the first thing is to make sure your tables are normalized and
relationships are properly defined.... this is an important issue and can
Click to expand...
[quoted text clipped - 34 lines]
Click to expand...
 
Back
Top