vba drops/aborts when underlying query/recordset joins an additional query

[sxschech]

I have a piece of vba code that is running successfully, however, when I was asked to exclude certain records the code aborts. I've tried several ideas including turning the query that is the basis for the exclusion into a table (via make table query) and joined with the table rather than the query, with no luck. I have been able to identify where the code aborts, but haven't figured out what else to try.

When I step through the code, it gets as far as .Edit, after that, when I press F8 it drops out of the code. It does not go to the line following .Edit:

 rs.FindFirst "ncessch = '" & rsText!ncessch & "'" 
 If Not rs.NoMatch Then
   .Edit
   rs!type = rsText.Fields(6)

I can open the queries individually and they run fine.

The queries that do not abort when run in vba are:

This is for recordset "rs"

SELECT tbl_schools_xyr.unique_schid, tbl_schools_xyr.ncessch, tbl_schools_xyr.state, 
tbl_schools_xyr.seasch, tbl_schools_xyr.stid, tbl_schools_xyr.schoolname, tbl_schools_xyr.type, 
tbl_schools_xyr.operating_status, tbl_schools_xyr.locale, tbl_schools_xyr.leaid, tbl_schools_xyr.gr_lo, 
tbl_schools_xyr.gr_hi, tbl_schools_xyr.level, tbl_schools_xyr.ccd_charter_status, 
tbl_schools_xyr.num_students, tbl_schools_xyr.date_edited
FROM tbl_schools_xyr
WHERE left(tbl_schools_xyr.sch_yr,4) = '2005' AND tbl_schools_xyr.state = 'NJ';

This is for recordset "rsText"

SELECT tbl_schools_xyr.unique_schid, tbl_schools_xyr.ncessch, tbl_schools_xyr.state, 
simplified.seasch05, simplified.stid05, simplified.schnam05, simplified.type05, 
simplified.status05, simplified.ulocal05, simplified.leaid05, simplified.gslo05, 
simplified.gshi05, simplified.level05, simplified.chartr05, simplified.member05, 
tbl_schools_xyr.date_edited
FROM tbl_schools_xyr INNER JOIN Ccd_05_appended_simplified AS simplified 
ON tbl_schools_xyr.ncessch = simplified.ncessch
WHERE left(tbl_schools_xyr.sch_yr,4) = '2005' AND tbl_schools_xyr.state = 'NJ';

These are rs and rsText respectively with the additional table/criteria in red. The revised queries do not contain additional display cols, only an additional WHERE statement.

SELECT tbl_schools_xyr.unique_schid, tbl_schools_xyr.ncessch, tbl_schools_xyr.state, 
tbl_schools_xyr.seasch, tbl_schools_xyr.stid, tbl_schools_xyr.schoolname, tbl_schools_xyr.type, 
tbl_schools_xyr.operating_status, tbl_schools_xyr.locale, tbl_schools_xyr.leaid, tbl_schools_xyr.gr_lo, 
tbl_schools_xyr.gr_hi, tbl_schools_xyr.level, tbl_schools_xyr.ccd_charter_status, tbl_schools_xyr.num_students, 
tbl_schools_xyr.date_edited
FROM tbl_schools_xyr [COLOR=#EF2929]LEFT JOIN tblSkip 
ON (tbl_schools_xyr.sch_yr = qrySkip.sch_yr) AND (tbl_schools_xyr.ncessch = tblSkip.ncessch)[/color]
WHERE left(tbl_schools_xyr.sch_yr,4) = '2005' AND tbl_schools_xyr.state = 'NJ' [COLOR=#EF2929]AND tblSkip.ncessch is not null[/color];

This is for recordset "rsText"

SELECT tbl_schools_xyr.unique_schid, tbl_schools_xyr.ncessch, tbl_schools_xyr.state, 
simplified.seasch05, simplified.stid05, simplified.schnam05, simplified.type05, simplified.status05, 
simplified.ulocal05, simplified.leaid05, simplified.gslo05, simplified.gshi05, simplified.level05, 
simplified.chartr05, simplified.member05, tbl_schools_xyr.date_edited
FROM (tbl_schools_xyr INNER JOIN Ccd_05_appended_simplified AS simplified 
ON tbl_schools_xyr.ncessch = simplified.ncessch) [COLOR=#EF2929]LEFT JOIN tblSkip 
ON (tbl_schools_xyr.sch_yr = tblSkip.sch_yr) AND (tbl_schools_xyr.ncessch = tblSkip.ncessch)[/color]
WHERE left(tbl_schools_xyr.sch_yr,4) = '2005' AND tbl_schools_xyr.state = 'NJ' [COLOR=#EF2929]AND tblSkip.ncessch is not null[/color];

If further code is needed, please let me know.

 

[VBAPrincess]

Can you edit the data manually when you run the second set of queries? the ones with red in them? If not, there's your answer. The outer joins are not allowing you to edit the data and that's why your code aborts when you try to do the edit.

HTH

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[sxschech]

Hi Diana,
Thanks for your response. Yes it turns out that the query became non updateable once we added the additional criteria. My workaround was to do a make table query, create the primary key and then add vba to do a delete query and then an append query to the table so I don't have to recreate the keys each time. I'm not to thrilled with the setup because it takes extra time to rebuild the table each time and of course increases file size so after the vba finishes its run, it needs to delete temp table as well as us doing compact and repair more often.

 

[TheAceMan1]

How are ya sxschech . . .

I know its a little late but an observation caught my eye.

[blue]When I step through the code, it gets as far as .Edit, after that, when I press F8 it drops out of the code. It does not go to the line following .Edit:

[blue]rs.FindFirst "ncessch = '" & rsText!ncessch & "'" 
 If Not rs.NoMatch Then
   [red][b].Edit[/b][/red]
   rs!type = rsText.Fields(6)[/blue]

[/blue]

I maybe mistaken but the above code doesn't show any signs of a [blue]With[/blue] statement prior to your [red].Edit[/red]. Including the [blue]With[/blue] statement changes the code to something like:

[blue]   rs.FindFirst "ncessch = '" & rsText!ncessch & "'"
   
   [purple][b]With rs[/b][/purple]
      If Not .NoMatch Then
         .Edit
         !Type = rsText.Fields(6)
         .Update
      End If
   [purple][b]End With[/b][/purple][/blue]

Hope this answered why it failed ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[sxschech]

Hi AceMan1,
Thanks for responding. The With statement is a few lines above the snippet I posted as was trying not to put more code than necessary in my post. I think if I didn't have a Beginning With, there would be an error message. Regarding not getting an error message I found that simply stepping through code didn't show where the problem was, however, when I put in actual error handling code, then I saw where the problem happened. I didn't have purposeful error handling originally, because usually, stepping through the code showed me where the problem was.

 

[TheAceMan1]

Roger That sxschech . . .

[blue]On Error[/blue] error handling is a runtime thingy. The [blue].Edit[/blue] error probably would've been caught by compile. So as a final question (you don't have to answer) ... do you remember compiling before you ran the code?

In any event [blue]compile[/blue] catches alot of things before you jump into runtime. I'm just saying ... [green]Always Compile[/green] before you run ...



See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[sxschech]

I always compile before running and sometimes compile while editing after adding a few lines. The .edit error wasn't an error in the traditional sense, it was that I was pointing out where in the code that it drops out, there wasn't a formal error message or popup box.