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.
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?
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.
dim DB as database
dim strSQL as string
strSQL = "DELETE * FROM TABLENAME WHERE [FIELDNAME] = " & me.ID
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 & "'"
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!!
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.