Values changing unexpectedly

[jazminecat]

Hi, I"ve inherited a database with some issues.

I have the following query:

SELECT tbl_main.wccontract, tbl_fed.cfda, tbl_main.title, tbl_main.desc, tbl_main.origgrantawd, tbl_main.amendgrantawd, tbl_main.begdate
FROM (tbl_main LEFT JOIN tbl_fed ON tbl_main.wccontract = tbl_fed.wccontract) LEFT JOIN (tbl_agencies RIGHT JOIN tbl_gcontacts ON tbl_agencies.vendorid = tbl_gcontacts.vendorid) ON tbl_main.gcontact = tbl_gcontacts.gcontact;

which populates a form that my user uses daily.


tbl main is related to tbl fed in a one to one relationship on wccontract, with wccontract being the primary key in *both* tables. I feel this is bad design, since this number is entered by the enduser, rather than autogenerated by access. But I can't change it all overnight..

However, I'd like to avoid having to redesign the entire thing. I'm not sure if thats possible as there a lots of lookups in the main table to other tables. . .

at any rate, first things first. This query behaves strangely. It populates the form, but if my user adds information to cfda, the value in the wccontract field is deleted. This only happens if a record is created which has a value in wccontract, and no value in cfda, which is a regular occurrence. If there is already something in cfda, changing it doesn't cause the problem. Just if the record is saved with cfda being blank.

Can anyone tell me why this would happen, and how to fix it?

Thank you!!

 

[jebry]

Hi!

It sounds like someone set up the link between the tables in the relationships window. Just open the window and right click on the line between the tables and choose Edit Relationship. The person before you probably has the Cascade Delete box checked. Uncheck the box, if it is.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Golom]

Good catch Jeff ... and here's something else to consider ...

It looks a bit like this

LEFT JOIN (tbl_agencies RIGHT JOIN tbl_gcontacts ON tbl_agencies.vendorid = tbl_gcontacts.vendorid) ON tbl_main.gcontact = tbl_gcontacts.gcontact;

contributes nothing to the query. You should get exactly the same results with

FROM tbl_main LEFT JOIN tbl_fed 
     ON tbl_main.wccontract = tbl_fed.wccontract

Since fields from only those two tables appear in the SELECT.

We could probably try to work our way through all those OUTER joins but I suspect that what's happening is that inputting a value into a field that doesn't exist causes Access to add a new record to the corresponding table. (tbl_fed in this case.) With all those outer joins however, it is trying to do something (God knows what) with tbl_agencies and tbl_gcontacts as well. One of those (tbl_gcontacts) is being joined to tbl_main and the logic is sufficiently screwy with the outer joins that a tbl_main record gets deleted.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jazminecat]

Hi guys - thanks for the help. The query originally had a ton of other stuff in it that I pulled out for testing - but forgot to pull out the tables as well. Now I have this:

SELECT tbl_main.wccontract, tbl_fed.wccontract, tbl_fed.cfda, tbl_main.title, tbl_main.desc, tbl_main.origgrantawd, tbl_main.amendgrantawd, tbl_main.begdate
FROM tbl_main LEFT JOIN tbl_fed ON tbl_main.wccontract = tbl_fed.wccontract;

I put the tbl_fed.wccontract in to see what was happening.

if I run the query and look at the info, I get data as follows:

tbl_main.wccontract tbl_fed.wccontract tbl_fed.cfda
1 1 20.6
3
199010001 199010001 93.563

All records have something in tbl_main.wccontract, and this is the primary key for that table. The join is on that field for both tables. As you can see, not all records from tbl_main have corrensponding records in tbl_fed, so when the user is entering info in the cfda field, it is indeed creating a new record in tbl_fed, but it should then use the wccontract number from tbl_main. That field is infact a lookup field to tbl_main. (lookups are evil)

the joins in the relationship window to not have referential integrity or cascade deletes checked.

I'm wondering if creating an autonumbered primary key in both of these tables, and leaving the relationships as they are will fix it. I'll try that while I wait for your sage advice. and thank you for your help!

 

[jazminecat]

HM,well, adding primary keys only complicated matters. now the recordsets is not updatable when I run the query. I'll check back in a bit and see if anyone has figured out my dilemma yet.

 

[Golom]

Your life will become a mean desparate struggle; the sun will not shine; stray dogs will snap at you; you won't be able to get a date, etc., if you do that.

Well maybe it's not that bad ... but it's close.

Autonumbers are assigned independently by Access. There isn't any way to tell it, "Make the next autonumber in this table the same as the one in that table." Your tables will probably get immediately out of sync and remain thus forevermore.

You could make one of them (tbl_main probably) an autonumber and then allow tbl_fed to use the same number in a Long Integer primary key field when adding new records. If you do that just be really careful about it (lotsa backups) because you will have to manually relink the keys that match now with new autonumber ones that initially will not match.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jazminecat]

I just added a field and made that an autonumber field and the primary key. I left the wccontract fields in there as is, it just it isn't the pk on both tables anymore. but it still doesn't solve my problem, of course.

 

[jazminecat]

ok, I've decided to just try a workaround. What i want to do is hold the value for wccontract each time the form loads, in a public variable, and then when the user changes the value in cfda, which deletes the value in wccontract, reload wccontract with the public variable's value, which should in effect, cancel out this problem. (Currently the user has to go in and retype it)

here's what i have so far - I've checked using debug.print, and the first bit is indeed grabbing the value as I scroll through the records. The second bit does set the focus to the wccontract, but it doesn't plug in the value. Any help is again greatly appreciated:

Public Sub Form_Current()
'populate variable with wccontract number
'to reload when cfda is changed
Dim stwccontract As String
stwccontract = Me.wccontract

______________________
End Sub
Private Sub cfdacombo_Change()
Me.wccontract.SetFocus
Me.wccontract = stwccontract
Me.Refresh

End Sub

 

[PHV]

Option Explicit
Public stwccontract As String
Public Sub Form_Current()
stwccontract = Me.wccontract
End Sub

Private Sub cfdacombo_Change()
Me.wccontract.SetFocus
Me.wccontract = stwccontract
End Sub

As for updatable recordsets:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

ok cool, this gets me a lot closer. Thanks PHV! I"m still having a little trouble - when the user goes to add a new fecord, he gets an error 94, because there isn't anything in the wccontract field - I think I can fix that with an IF statement. But the real kicker is this - if the user changes the cfda now, it does keep the wccontract in the field, so that's good, but if he changes his mind, he can't delete the value in the cfda field. And he could before. Also, it only works if he selects something from the dropdown box that is cfda. if he types in a value, even though it is a value from the list, it still is clearing the wtccontract field.

Any insights? thanks y'all! you're an irreplaceable resource!