Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Problem?? (Access 2000)

    I submitted this problem a couple days ago, but I decided to start over explaining my problem: There is an attachment.

    I

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    Here is the attachment in a zip file.
    Attached Files Attached Files

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking Problem?? (Access 2000)

    It appears that you (or someone) has established one-to-many relationships between tables in which Referential Integrity is being enforced. This is ordinarily a good thing. For example, in an order entery situation it prevents you from entering a new order unless you have already put the customer in the database. However, you have a situation in which you want to add records to the "many" side of such a relationship without having a master record in the table on the "one" side.

    There are 2 things you can do. One is to remove RI between the tables, although I really hate to do this. The other is to make certain that the linking field on the "many" side does not have a DefaultValue. If the linking field is a number, than its DefaultValue is probably 0. You may also have to set the Required property for that field to No.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Problem?? (Access 2000)

    It isn't a good idea to have two threads on the same question because the answers get fragmented. You started this in this thread. Were none of the responses you got there helpful?
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Problem?? (Access 2000)

    A workaround, which I suggested in the other thread, is to have a "zero" record in the master table and make that record apply to unregistered vehicles. That allows you to retain referential integrity as long as the default value on the many side matches the "zero" key on the one side.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    As i said, I'm not good at Access. I went to the Permit Information Table and added a record. Under permit # field I entered unreg. I didn't fill out anything else in that record. Went back to Notification form and added a record (under permit number I entered unreg. I still have the same error message as before "cannot find a record in the table 'Driver Information' with key matching field(s) "Violation Information_License#). Could you modify the attachment file i sent to you and mail it back to me?

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    It is a Text field.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    Do you have a number I can call you at? You could talk me through my changes??

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    Mark. It is a text field and there are no Default values.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking Problem?? (Access 2000)

    A "zero" record might be a good solution in this particular situation. It certainly eliminates the pain of having to use outer joins between the violations and license tables, so that you get all violations regardless of whether/not a registered license in on file.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Problem?? (Access 2000)

    What is a "zero" record. Please explain?? How do I do it.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Problem?? (Access 2000)

    We can't provide off-line assistance because then you would be the only one who benefitted from it.

    I think you need to think through your table design a bit more. There are some peculiarities that baffle me. For example, you have a License# field as the primary key (PK) in the Driver Information table. Is that a driver's license # or a car license #? Can a driver have only one vehicle? Can a vehicle have only one driver? You also have a License# primary key in Vehicle Information. You have the License# from both the Driver Information and the Vehicle Information tables joined to the same License# field in Violation information and you've used an inner join (only matching records from both tables) in each of the joins. That is what's causing your basic problem. If you don't have a record in one of the table with that License#, you won't see any records. If you have it in one of the tables, you still won't see any records because there's no match in the other tables.

    You need to decide what the central piece of information is for each query. Is your query "qryVehicleRegistration" supposed to show permits, with the driver information and the vehicle information or is it supposed to show vehicles, which related driver and permit information? If the former, you need to change the joins to all the records from Permit Information and only matching records from Driver Information, then change the other join to link Permit Information to matching records in Vehicle information. If the License# is (for whatever reason) the vehicle license number, then there is no earthly reason to go through Driver to get to Vehicle. If you want the vehicle record to drive the query (so to speak <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>), change the direction of the join and get all the records from Vehcle Information and only the matching records from the other two tables.

    I'm not sure why you're putting the permit# into the violation record, but the way to do it it you must is to use the afterUpdate event of License# to lookup and insert the permit# if any. I made changes to your queries and set DataEntry to No on your forms so that you can actually see the data returned if you open the form from the database window instead of through the menu.
    Attached Files Attached Files
    Charlotte

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking Problem?? (Access 2000)

    A "zero" record is just one in which the value of the PrimaryKey field is 0. Unless you can
    add one via your normal input screen (which is impossible if your PK is an autonumber), your
    best bet is to create an Append query to add a record that sets this field value = 0. In the
    append query, you will also have to create initial values for any fields that are required
    and which don't have a default value.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •