Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    12

    Unanswered: stupid, easy question regarding relationships

    How do you make the recordsource of a form come from a query that contains two tables. For the life if me I can't get it so the form data is updatable, but otherwise it works fine.
    I have two tables, 'tblLeadtimes' and 'forecast'. The primary key's of these tables should match up, although there can be a record in forecast that is not in tblLeadtimes, but this current application is primarily for leadtimes. If I delete a record, i want it to delete only from leadtimes, so in my form that comes from the query from both of them, forecast data can be locked as long as I still can alter leadtimes data. Right now i have the recordsource come directly from tblleadtimes and on current event I do a dlookup for the forecast information in the record that has the same key value as my 'current' key in the leadtimes. This is not an optimal way of doing this, but I can't get the recordsource query to work properly. What am I doing wrong??? thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    6

    Re: stupid, easy question regarding relationships

    Go to the Form Properties, on the data tab, change the Recordset Type property to the value Dynaset (Inconsistent Updates).

    This allows the form to update the information on either side of the relationship.

  3. #3
    Join Date
    Jun 2003
    Posts
    12

    problem with deletes

    If on the form that contains data from both if I do a delete record, won't it delete the record that exists in both then? Should I just have the delete button execute an update query that goes through and makes sure it only deletes from the leadtimes table. I then need to make sure that the database as a whole doesn't have a relationship between these fields, right?

  4. #4
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    6

    Re: problem with deletes

    Relationships are great for keeping a database tidy, if you have set up a cascade delete relationship between the two tables, and you delete a record from the primary key table, then the related records will be deleted, this is a very useful feature in most cases. However, if you do not want to delete related records, do not set "Enforce Referential Integrity" on the relationship.

    As for deleting out of a specific table, yes the most reliable would be to use code to do this.

    eg:

    dim DB as database
    dim strSQL as string

    strSQL = "DELETE * FROM TABLENAME WHERE [FIELDNAME] = " & me.ID
    db.execute strSQL

    Note me.ID would be the unique identifier in the table that identifies the record to be deleted. If this is a number, the above syntax is fine, if it is a string, then use the following syntax:

    strSQL = "DELETE * FROM TABLENAME WHERE [FIELDNAME] = '" & me.ID & "'"

    db.execute strSQL

  5. #5
    Join Date
    Jun 2003
    Posts
    12
    THe not checking 'enforce referencial integrity' didn't do anything for me, I tried that a while ago. I am still a little confused on why this is. What I will do is in the query have the relationship 1to1, that as you said will make both records editable, but then on the form I will lock all of the fields from the table I don't want edited. Then, if I were to use the default 'delete record' it deletes records from both, but if I run the update query that you wrote i certainly should be able to delete just from the table I want, right??? Thanks a lot!!
    Jordan

  6. #6
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    6
    In a nutshell, yes you are right. You might want to concider using a form and sub form as an alternative way of displaying and managing relational information.

    Alos, if your data is a true 1 to 1 between 2 tables, it could be argued that the information would be easier and more logical to deal with in one table.

    Always try to look outside the square, see if there are better ways to do things than the original way you started out!

  7. #7
    Join Date
    Jun 2003
    Posts
    12

    i agree

    It isn't that true of a 1 to 1, forecast pages are made before leadtimes are, leadtimes may never be made for a forecast, but if it is in leadtimes it must be in forecast. I can't specify that type of criteria in these queries, can I? I know if I were making the tables in sql I would make a foreign key... but... i'm not.
    forms/subforms wouldn't work out so well... or at least I couldn't make it look the same, and the 'look' is actual carried over from an approach database that i'm converting and would like to keep as similar as possible.

  8. #8
    Join Date
    Jun 2003
    Posts
    12

    Talking thanks

    thanks a lot for your help by the way, i really appreciate it

  9. #9
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    6

    Talking Re: thanks

    Originally posted by jramrus
    thanks a lot for your help by the way, i really appreciate it
    your welcome, good luck with your project

Posting Permissions

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