1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Database Problems.

Discussion in 'Business Applications' started by paperdollx, Jan 24, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. paperdollx

    paperdollx Thread Starter

    Joined:
    Apr 19, 2006
    Messages:
    28
    Hello All.

    I am 17 years old currently studying ICT for As-level. For my coursework we have to create a database for a business(real or not..doesn't really matter). We have been working on this for several months now and we are currently on the implementation section which consists of actually creating the database.

    I have so far, successfully managed to make everything work, until now. My database is for a newsagents but they are unaware of this creation.

    I have created a form entitled Customer Sales Receipt which is based upon all entities from two tables named Customer Sales Receipt and Sales Details. I also have a similar form entitled Purchase Orders which is based upon all entities from two tables named Purchase Orders and Order Details.

    I seem to be having trouble with the SaleID in this two forms. It is meant to be an Autonumber but when i try to add a new record to either form the autonumber does not show up. I then get a pop-up telling me that

    'The field 'SalesDetails.SaleID' cannot contain a Null value because the require property for this field is set to True. Enter a value in this field'

    This comes up after I have selected the ProductID in the subform of the customer sales receipt. Upon attempting to close the form I get a message saying 'You cannot add or change a record because a related record is required in table 'Customer Sales Receipt'.

    I tried using the Access Help files but they are pretty much useless to me. So any help given will be very much appreciated as I have to hand this part of my work in tomorrow.

    Also, a few other questions while Im here:

    1. I have a Telephone Number field and I would like to enforce (028) as the starting code, how do i do this?

    2. In my supplier table I am unable to enter the name of a supplier with two parts to the name and have the second part have a capital, how would i be able to do this?

    3. What is Tab Order? How do I use this?


    I have attached a copy of my database to this thread.
    Also, I will need a detailed description of tasks as I have to print screen them for the Software and Technical Development Document.

    Thank you very much for any help that is given, I truly appreciate it.
    Samantha
    :)
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The Sale ID is not an autonumber in the Sales Details table. If you change the Customer Sales Receipt Query to show all records from the table Customer Sales Receipt, the autonumber should work right.
    For the phone, an input mask of "028 "00\ 000000 should input the 028 automatically.
    The supplier name currently has an input mask of >L<?????????\ >L<?????????. Unfortunately, this causes a space and a capital after so many letters, e.g. Smith comp Any. While I am sure there is a way to do this, I don't know it. But given that companies have any number of letters in their names and may have several capitals (for instance, L. L. Bean) I think I would avoid this.
    Tab order in a form lists all the fields that are displayed on the form and the order in which the cursor goes from one to the next - for instance, from sales id to product id to quantity. On a for you can change this so it goes in any other order you like - perhaps product id first, then quantity. I have mostly used it to make forms more convenient for data entry.
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Samantha, I am impressed with the amount of work that you have done on this database.
    The problem that you have with SaleID -
    'The field 'SalesDetails.SaleID' cannot contain a Null value because the require property for this field is set to True. Enter a value in this field'
    is because you are trying to make na entry in the Subform before you have created a record in the Main Form, this is partly to do with your layout of the form where the Total Price field is under the Sub Form and there is nothing else on the form to "Trigger" that it is a new record to increment the Autonumber, if you enter a dummy value in the Total price field before entering the subform you will find it works OK.
    The other problems that you are experiencing with -
    You cannot add or change a record because a related record is required in table 'Customer Sales Receipt'.
    is due to the way your Table Relationships are set up. As an example the Order DetailsTable has 3 Key fields when it should have just one which is only the OrderID.
    This should be an Autonumber field, the other 2 fields should only be "Indexed" not keys.
    So with your relationships you must ensure that values that will appear in your tables exist in the related table, the most common problem here is to have a "Null" or Zero value, so when the relationship calls for matching value the other tables ID field does not allow "Nulls" or zeroes.
    1. The telephone number an be prefixed with the "028" in the fields "Format".
    2. I am not sure about this, I assume that you mean a Double-Barreled Surname? As it working for the Christian amd Surnames. This may have to be done using Visual Basic.
    3. The Tab Order is the order that you travel throught the fields on a Form when you press the Tab or Enter key. To see and set the Tab Order do this with the Form in Design Mode, from the Main Menu Select View>Tab Order, you can then click on a field and then drag and drop it in the place that you want it. It can also be set when you click on a Field and ihn the Fields Properties it is the Tab Index Property.

    Some improvements that you could make are -
    1. Set all of the Forms Autonumber Field's Tab Stop to "No" this means that the User won't expect to enter a value, or better still set it's "Visible" Property to "No" so that the User can't even see them.
    2. On all Forms in the Form's Design Properties set the "Allow Design Changes" property to "Design View Only". This will hide the "Properties" and "Field Names" when the form is in from mode.

    It is a pity that you left it until today to post on here as it doesn't really leave time to help you "fix" everything, but at least hopefully you now know why it happening.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Sorry Slurpee, I was busy typing (slowly with 2 fingers:) ) and didn't see your post.
     
  5. paperdollx

    paperdollx Thread Starter

    Joined:
    Apr 19, 2006
    Messages:
    28
    Thank you both very much for your help. It has been of great use to me and I have managed to fix most of my problems :)

    If you wish to make any recommendations then feel free to do so, any constructive criticism on what i should and shouldn't have/do is welcome.

    I was wondering, can you make a chart, say like a graph on access?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Samantha, I do not know whether it is still relevant, (re your deadline), but yes Access does charts or graphs, but it is not qiite to user friendly as Excel.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi Samantha,

    I agree - it is possible to make charts and graphs in Access, but, as more of a novice than OBP, all I can say is they are a royal pain. I is actually easier to make a chart in Excel and import it as an object.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/537973

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice