Query Trap!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an IF statement in the on click event of several yes/no controls. If
it's marked true, it runs two append queries. If it's false, it runs two
delete queries. The problem is that, when I open the form, and click one
control, it works fine. Then if I choose another control, it doesn't make
the append. No error and no appending. I can append and delete all I want,
as long as it's the same first control. The only way I can choose another
one is to click false to delete, close the form, then open it and choose
another control. What's going on?!?!? I have a feeling it may have
something to do with the fact that the two append queries are going to two
tables that have a one to one relationship. And the first table is linked
with a one to many relationship with the form's table. Why didn't I have the
append query go to both tables rather than making two separate queries?
Mainly because I don't know how to make an append query with one source and
two destinations. If that shouldn't matter, does anyone know what else could
be wrong?
 
Several aspects to this issue.

1. Try the AfterUpdate event of the check box rather than its Click event.

2. How are you firing the action queries? Try the Execute method rather than
RunSQL or OpenQuery. This way you
a) know if anything when wrong, and
b) know if the query yielded no new records to append.
Example:
Dim db As DAO.Database
Set db = dbEngine(0)(0)
db.Execute "SomeQuery", dbFailOnError
Debug.Print db.RecordsAffected & " records written by SomeQuery"
'etc
Set db = Nothing

3. How are you retrieving the primary key value from the first append query
to use in the 2nd? Presumably the related table has a Number type field as
its primary key (rather than an AutoNumber), as an AutoNumber could easily
get out of sync.

4. More fundamentally, why is there a one-to-one relationship? These are
reasonably unusual, and we often see people using them just because their
data structure is unnormalized, and so they have just too many fields to fit
into one table. The better solution might be to create a related table where
the can be many records instead of repeating fields in one table (or pair of
tables linked 1-to1).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"FrustratedAccessUser" <[email protected]>
wrote in message
news:[email protected]...
 
1. I tried the AfterUpdate and BeforeUpdate events, and it keeps on
appending the same control, disregarding the fact that I selected a different
control.

2. I went ahead and tried running it a different way besides the OpenQuery
command, and it runs in the same fashion. And it doesn't report any errors.

3. The tables are linked through a primary key, AutoNumber unfortunately.
But I haven't had any problems with them being out of sync, as of yet anyway.
And I'm positive I'm doing this wrong, but I'm not appending the tables
through use of relationships. I'm just running two append queries.

4. You hit the nail on the head with having too many fields. That's
exactly the reason why I have two tables with a one-to-one relationship. But
I think it's the only way. Because I have three main fields, then with each
record there are a total of 18 sub fields. And with each sub field, there is
a total of 21 additional sub fields. But they are all unique. You can't
break them up, I need this for my database. I blame it all on my client!

I liked your AfterUpdate idea. I'm going to try to run these in the Form's
AfterUpdate then Close events. And if that doesn't work, I'm fresh out of
ideas. I'll just have to resort to my original plan of opening the subform
for the corresponding tables (After doing the math, 384 fields on one form,
not pretty and makes for unbearably long load time), and inputting fields as
necessary. It worked with my other form.
 
The real problem here is with the data structure. There is no way that this
is correctly normalized.

A place to start might be to run the table analyzer:
Tools | Analyze | Table

If you prefer to read up on how to design these tables you could start at:
http://support.microsoft.com/?id=209534
or the webcast at:
http://support.microsoft.com/?id=324613


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"FrustratedAccessUser" <[email protected]>
wrote in message
 
Back
Top