Compiled VBA code does not execute

[stukanewss]

I have created an event procedure in a form on access 2000 and it works. I have sent the DB to another Access 2000 user and the code does not execute -- nothing happens. I have checked the references on her machine and they match mine. Here is a sample of the code. Please help!

Private Sub Command14_Click()

Dim icounter As Integer
Dim squery As String
Dim sselected As String

For icounter = 0 To List0.ListCount - 1
If List0.Selected(icounter) Then
sselected = sselected & IIf(Len(sselected) = 0, "", ",&quot & Chr(39) & List0.Column(0, icounter) & Chr(39)
End If

Next icounter

'MsgBox (sselected)

If Len(sselected) > 0 Then

squery = "SELECT BEOpty.Account, BEOpty.Author, BEOpty.Title, BEOpty.Ed, BEOpty.Units, BEOpty.Revenue, BEOpty.Status, BEOpty.[Decision Date], BEOpty.Discipline, BEOpty.Course, BEOpty.Type, BEOpty.[Sales Rep], BEOpty.[Semester of Use], BEOpty.ISBN FROM BEOpty WHERE (((BEOpty.[Sales Rep]) In ( " & sselected & &quot));"

Else

squery = "SELECT BEOpty.Account, BEOpty.Author, BEOpty.Title, BEOpty.Ed, BEOpty.Units, BEOpty.Revenue, BEOpty.Status, BEOpty.[Decision Date], BEOpty.Discipline, BEOpty.Course, BEOpty.Type, BEOpty.[Sales Rep], BEOpty.[Semester of Use], BEOpty.ISBN FROM BEOpty;"

End If

'MsgBox (squery)

On Error Resume Next

DoCmd.DeleteObject acQuery, "BEopty Query Rep"

Dim db As DAO.Database, qdf As QueryDef

Set db = CurrentDb

Set qdf = db.CreateQueryDef("BEopty Query Rep", squery)

db.QueryDefs.Append qdf

DoCmd.OpenQuery "BEopty Query Rep"

DoCmd.OpenReport "Rep Pending Summary", acViewPreview

DoCmd.Close acQuery, "BEopty Query Rep"

End Sub

 

[MichaelRed]

I'm pretty sure I'm not going to resolve your problem, as THAT almost certainly has to do with the configuration of the systems, as in where the db / table is and it's content.

On the other hand, a few questions about the posting will, perhaps, get you thinking ...

I am a proponent of hte "Debug" (immediate) window, so IMMEDIATLY, change the:

'MstBox

to

Debug.Print

in two places. This will allow you to inspect the content of hte variables w/o having to resume the prog execution.

I would generally follow the statments as you would have htem with additional debug.print with a breakpoint in the second one. The code execution will stop after printing to the window. You will be able to see the vars (and others) when the code reaches the breakpoint (but then again - you say is doesn;t, so ...)

When you copied the code, did you "attach" it to the command? In general, MS. A. does NOT recognize code as attached until the appropiate 'event' is seen to have the event code in the properties box of the object (control / command14). You need to click on the "Click" event of the Command14 control and have it actually opne up the code. THEN, you need to SAVE the form.

Why (oh why) do you delect and (re-)create the query? Just change the sql property, or make it a Parameter query. Either will CERTAINLY execute faster and keep the size of the db from growing each time you run this procedure. In the same vein, why are you opneing and closing the query? I assume it is the recordsource of the report, so each instantiation of hte report will cause the query to execute and your opening and closing just 'muddies the water'.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[stukanewss]

Thanks for the response

I'm a newbie to VB and consequently I'm muddling through this. I'll try the debug.print and see if I can find anything there.

I've never worked with the SQL Property in VB. Could you provide me with an example of how this would work in this case?

 

[MichaelRed]

qdf.SQL = squery MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over