Popup when opening form (Access 2002/WinXPSP1)

[Ryan Boyes]

Hi all,
When I open a form from the forms section of the Database window I get the attached popup. If I open it using VB from within another form it opens fine. I have a feeling it is an issue with WinXP SP1 but I can't find anything in the MS KB about it. Anybody have any ideas?

Thanks,

Ryan

[HansV]

Do you have code in the On Current event of the form? If so, what does it do?

[Ryan Boyes]

Yes, just a few lines to enable or disable a few controls depending on the value of one of the fields in the underlying table to the form:

Private Sub Form_Current()
If Me.SandType = "Chemically Bonded" Then
Me.SandQty.Enabled = True
Me.SandUOM.Enabled = True
Me.TxtSandCost.Enabled = True
Else
Me.SandQty.Enabled = False
Me.SandUOM.Enabled = False
Me.TxtSandCost.Enabled = False
End If
End Sub

[HansV]

Hmm, nothing special there in itself, but the error message indicates that there may be duplicate names. Check carefully whether you have a field or control named Current, or a procedure named SandQty or something like that. See <!mskb=279124>Microsoft Knowledge Base Article 279124<!/mskb> (it is for Access 2000 but no doubt applies to Access 2002 too.)

[Ryan Boyes]

Nope. All the forms procedures are of the form "CmdButtonName_click" except for 2 functions which do not have similar names to controls. I checked all modules and code for all forms in the DB just to be thorough. I tried putting a breakpoint in the OnCurrent event procedure but it doesn't get to the breakpoint before the popup appears. Is this significant?

[HansV]

You might check Tools | References... in the Visual Basic Editor for MISSING references (uncheck them), but the error message you get doesn't point in that direction; you can also try if Debug | Compile <projectname> results in a more specific error message.

Otherwise, I am afraid we're not going to get much further with this without seeing the database. Perhaps you could attach a stripped down version of the database to a post.
<UL><LI>Make a copy of the database and work with that.
<LI>Remove all database objects that are not relevant to the problem.
<LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
<LI>Remove or modify data of a confidential nature.
<LI>Do a compact and repair (Tools/Database Utilities).
<LI>Make a zip file containing the database; it should be below 100KB.
<LI>Attach the zip file to a reply.[/list]I would wait a bit to see if others come up with something better before doing this. Who knows...

[charlotte]

What happens if you compile the code? With a message like that, I would suspect something like a function with the same name as a module or something equally esoteric. I just thought of another question. What is the significance of the "template" in the window title of the message box? That sounds as if you were trying to create a copy of an existing object, which might or might not be allowable, depending on what you're doing and how.

[Ryan Boyes]

I've found the conflict but it's a bit wierd. I compiled the code like Hans and Charlotte suggested and it pointed to the second function "MatOnCost" which is in the forms module. I changed the function name and the references to it to "MatOnCost1" and the problem went away. Suggests there is a control somewhere called "MatOnCost" but I can't find it. I've looked at every text box, label and button but nothing has that name apart from the function. Is there anything else it could be?

Thanks everybody for your help on this. I really appreciate it.

[charlotte]

Do you have a container object (form, report, module or macro) with that name? Do you have a reference set to another database that might have something with that name in it?

[Ryan Boyes]

There is a report designed to print out the form that has 2 functions identical to the ones in the form in its module. I tried changing the names of them and the reference to them but it doesn't fix the problem. I prefix all Form, Report and module names with Frm, Rpt or Mod so there is no chance of a container object being called "MatOnCost". I haven't used any macros at all. I've attached a zipped version of the db thats been cut down a bit as Hans suggested.

[charlotte]

It appears that the VE engine is objecting to your having a function named MatOnCost. If I compile the code from the VBE window, I get the same error and it breaks on the function declaration line. You do have a field named MatOnCost in tblTemplates, and since it is included in the recordset behind the form, that may be what is causing the problem and why renaming the function cures it. Having the field and the function with the same names is probably confusing Access as to which you are referring to.

I don't find any place in the code that uses this function anyhow. What is it there for?

[Ryan Boyes]

Thanks Charlotte, I think your right. I don't even remember adding that field to the table or why.
In a text box on the "Costing Tab" I use the function in an expression. The function cycles through the materials table till it finds the right material and then plugs the on cost value of that material into the expression. Can you suggest a better way of doing this?

Thanks again.

[charlotte]

OK, I missed that one when I looked through those text boxes. If you want the form to work, you'll have to change that function call to MatOnCost1 as well. If I were doing it, I would either use a DLookup or open a snapshot with criteria that would give me the exact record I needed instead of looping through anything. Here's a DLookup that works:

<pre>MatOnCost1 = DLookup("[Oncost]", "tblMaterialCosts", "[Material]= '" & Me.Mat & "'")</pre>


You could also use the same criteria in a where clause in the SQL to open a single record recordset, assuming that any given material will be unique in the table. The SQL might look like this:

<pre>Dim strSQL as String
Dim dbs AS DAO.Database

strSQL = "SELECT OnCost FROM tblMaterialCosts WHERE Material ='" & Me.Mat & "'"

Set dbs = CurrentDb()
Set rstMat = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rstMat.EOF Then
MatOnCost1 = rstMat!OnCost
End If</pre>




However, if you're going to use recordsets like this, make sure you close them and any database objects you create and set them to nothing at the end of the routine or you will suffer tremendous resource leaks and eventually crash the application. Not to mention that leaving them undestroyed as you did in this routine is a good way to have the database hang when you try to close it.

[Ryan Boyes]

Thanks Charlotte. The dlookup seems a much easier way of doing things. A big whoops on the cleanup of recordsets. I usually remember to set the recordsets to nothing but only because I was told that was the correct thing to do, never why. Now I know...

Once again, thanks for all your help on this.