Form close parameter prompt problem

I have a form loaded from code like: DoCmd.OpenForm "frmWebEvent",
whereCondition:="zeid = [forms]![frmSwitchboard]![lstWebEvent]"

All the functionality on the form works fine.

Form opens, has subforms, lists, combo boxes, etc. When I close the form using a button (docmd.close), I get a parameter prompt of: Enter parameter query for forms!frmWebEvent!zeid I've also tried the full command of doCmd.close acForm, "formName" Same thing.

But, if I use the windows close x box, I don't get the prompt. There's nothing behind the close command to do anything - just close the form. If I hit cancel, everything closes fine.

Anybody know what causes it or how I can get rid of it?

what exactly is your DoCmd.Openform line

Knookie,
I think I've got it narrowed down by recreating the form one control at a time. Have a parent form (frmWebEvent) that loads a specific record based on a key field (zEID). I have two list box controls on the form that need the be restricted to reflecting just those items based on the key. When I specify zEID=[forms]![frmWebEvent]![zEID] in the list box's row source to limit the reflected rows, I then get a parameter prompt upon closing of the form frmWebEvent requesting the paramter for [forms]![frmWebEvent]!zEID. It doesn't matter which list box I place the criteria into - either will produce the prompt. I've done this on other forms, but is there something I'm missing? (In fact, this form is almost a duplicate of a form for non-web events and that form has almost idential list boxes with the same criteria and no problems.) I've tried deleting and recreating the form, but it always hangs at this point. Subforms seems to be okay. But, I need to open another form based on the click event in the list box, so I'd really like to keep the list boxes. FYI - there are no other functions on the form running inteference at this point.

Any help appreciated.

not sure if this is viable in your app but have you tried clearing the listbox rowsource before closing the form ?

If i'm reading your post correctly it as you say shouldn't be prompting you.

Also as an alternative to referencing frmWebEvent have you tried passing the zEID info to the form via the DoCmd.OpenForm's OpenArgs property ?

What are your listbox rowsources in their entirety ?

The original call to open the main form (frmWebEvent) is
DoCmd.OpenForm "frmWebEvent", whereCondition:="zeid = " & [Forms]![frmSwitchboard]![lstWebEvent]

Haven't tried clearing the row source.

lstWebParticipants:
SELECT dbo_zTblParticipant.zPartID, dbo_xTblOrg.org & ", " & dbo_xTblRoom.roomName AS Org, nz(dbo_xTblPerson.firstName & " " & [dbo_xTblPerson.lastName] & ", " & [dbo_xTblPerson.phone],"Unknown") AS Contact, dbo_xTblNetworkType.networkType AS Network, dbo_xTblStatusP.partStatus AS Status, IIf([zOrigYN]<>0,"Yes","No") AS Orig, dbo_zTblParticipant.zEID
FROM (((((dbo_zTblParticipant LEFT JOIN (dbo_tblSite LEFT JOIN dbo_xTblOrg ON dbo_tblSite.oid = dbo_xTblOrg.oid) ON dbo_zTblParticipant.zSiteID = dbo_tblSite.siteID) LEFT JOIN dbo_xTblRoom ON dbo_zTblParticipant.zRID = dbo_xTblRoom.rid) LEFT JOIN dbo_xTblStatusP ON dbo_zTblParticipant.zStatusID = dbo_xTblStatusP.pStatusID) LEFT JOIN dbo_tblRoomNetwork ON dbo_zTblParticipant.zNetwork = dbo_tblRoomNetwork.roomNetID) LEFT JOIN dbo_xTblNetworkType ON dbo_tblRoomNetwork.networkTypeID = dbo_xTblNetworkType.networkTypeID) LEFT JOIN dbo_xTblPerson ON dbo_zTblParticipant.zPID = dbo_xTblPerson.pid
WHERE (((dbo_zTblParticipant.zEID)=[forms]![frmWebEvent]![zEID]));


lstWebEventFee:
SELECT dbo_zTblEventFee.zTblEventFeeID, dbo_zTblEventFee.zeid, "$" & [rate] & " " & [rateFrequency] & " " & [rateTypeDesc] AS Fee, dbo_zTblEventFee.zOtherDesc AS [Other Desc], dbo_zTblEventFee.zOtherAmt AS Amt
FROM dbo_zTblEventFee LEFT JOIN ((dbo_tblRate LEFT JOIN dbo_xTblRateFrequency ON dbo_tblRate.rateFreqID = dbo_xTblRateFrequency.rateFreqID) LEFT JOIN dbo_xTblRateType ON dbo_tblRate.rateTypeID = dbo_xTblRateType.rateTypeID) ON dbo_zTblEventFee.zrateID = dbo_tblRate.rateID
WHERE (((dbo_zTblEventFee.zeid)=[forms]![frmWebEvent]![zeid]));

it's hard to say without actually having a look at the app itself.

if you for example remove one of the list boxes and try to exit the form do you still get the message.

hmm i notice you have 2 fields called zeid...

dbo_zTblEventFee.zeid

and...

dbo_zTblParticipant.zEID

i wonder if this is causing any confusion ?

You read my mind. I've been playing with removing and putting the two list boxes back in.

If I remove both boxes and put the lstWebParticipant list box back with the criteria, it works fine. If I put in the second list box - lstWebEventFee without limiting it with criteria, everything works fine. But, I need to limit the rowsource to matching records, so I enter the criteria of dbo_zTblEventFee.zEID=forms!frmWebEvet!zEID. After that, it starts the prompt business. The key is in both list boxes (I just didn't capitalize the one - tried it with the correct capitalization, didn't matter) because it's the matching key.

Anything you can think of? Do you have an example of clearing the rowsource on close? Although, I don't get why I need to do this and it's causing the prompt, but I'm getting zonkered on this one.

jb

well you can clear the listbox like so...

MyListBoxName.RowSource = ""
DoCmd.Close

obviously this is not ideal as you say.

Can you post a copy of the application itself ?

Yep, but since the back-end is a local sql, you're going to get bunches of data connection errors. If you can tell anything, though, here you go. Please don't be too brutal (although I do appreciate constructive criticisms); I'm a one-person shop.

was it meant to be attached ?

you can import the sql tables which will turn them into unlinked Access tables.


here's a nice piece of code for removing the annoying dbo_ part of table names (although don't with your current database or you'll need to change all the queries)

Code:

---

Public Function Remove_dbo()
''''remove the annoying dbo_ from the front of linked tables

    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim i As Integer
    Dim ttext As String
   
    Set dbs = CurrentDb
   
    For i = 0 To dbs.TableDefs.Count - 1
   
        ttext = dbs.TableDefs(i).Name
     
        If Left(ttext, 4) = "dbo_" Then
            dbs.TableDefs(i).Name = Mid(ttext, 5, 200)
        End If
       
    Next i
   
End Function

---

Thanks for the tip! As it's probably obvious, most of my experience is web-based with sql, so my Access is limited.

Oops, sh*%. Let me try that again.

The rowsource="" works, but why? Know what I mean? Probably something really simple and stupid I'm doing.

yes ideally you shouldn't need the rowsource="" to get around the problem.

It's hard to say why though without seeing the app itself.

good luck anyway

Sorry, I'd love to have you look at it, but I'm at an impasse. I've deleted everything I thought possible out of the db - no reports, pages, modules. Deleted some of the records in the tables. But, everything I've tried leaves the zip file at 152 and the limit is 100. If I start deleting tables and such I'm afraid it will break so much you won't be able to get to the real problem.

jb

for an access database that is very big. you've tried compacting it i guess ?

Compacted and repaired twice. Did reduce it quite a bit. Not sure why it's so big. There's about twenty tables, twenty forms. But max records in the tables is about 70 records.... Go figure.