Database Requesting Parameters on Close

[Phred]

The parent and sub forms below function fine however when I close the database it requests Parameters:



Pick Category combo box populates from select query for Category names

When the category has been selected it populates the Pick Site combo box with the sites related to that category.

The end user then picks a start date and an end date.

The MealStartDate and MealEndDate fields are not bound.

The end user then clicks the Find Meals button and the following code executes fine.

Private Sub CmdFindSchMeals_Click()

Dim dtstart As Date, dtend As Date

dtstart = Me.MealStartDate
dtend = Me.MealEndDate

If IsNull(Me.CmbPickCat) = True Then
   MsgBox "You must pick a Category first.", vbOKOnly + vbExclamation, "Pick Category"
   Exit Sub
Else
End If
If IsNull(Me.CmbPickSite) = True Then
   MsgBox "You must pick a site first.", vbOKOnly + vbExclamation, "Pick Site"
   Exit Sub
Else
End If
If IsNull(Me.MealStartDate) = True Then
   MsgBox "You must pick a Start Date first.", vbOKOnly + vbExclamation, "Pick Start Date"
   Exit Sub
Else
End If
If IsNull(Me.MealEndDate) = True Then
   MsgBox "You must pick an End Date first.", vbOKOnly + vbExclamation, "Pick End Date"
   Exit Sub
Else
End If
If (dtend < dtstart) = True Then
   MsgBox "Your end date is earlier then your start date. Please correct your date range.", vbOKOnly + vbExclamation, "Correct your Dates."
   Exit Sub
Else
End If
Me.Refresh
End Sub

The sub form refreshes with the correct data.

The sub form datasheet is based on a select query. The query is in the access query builder but I have included it her in SQL for simplicity.

SELECT Tbl_ScheduledMealItems.DayAssigned, WeekdayName(Weekday([DayAssigned])) AS [Day], Tbl_ScheduledMealItems.MealName, Tbl_ScheduledMealItems.ItemName, Tbl_ScheduledMealItems.VendorName, Tbl_ScheduledMealItems.IUDescription, Tbl_ScheduledMealItems.PriceperUnit, Tbl_ScheduledMealItems.InventoryID, Tbl_ScheduledMealItems.Category, Tbl_ScheduledMealItems.SiteName, Tbl_ScheduledMealItems.ScheduledMeal3ID
FROM Tbl_ScheduledMealItems
WHERE (((Tbl_ScheduledMealItems.DayAssigned)>=[Forms]![Frm_Main]![Frm_SiteCategorySelection].[Form]![MealStartDate] And (Tbl_ScheduledMealItems.DayAssigned)<=[Forms]![Frm_Main]![Frm_SiteCategorySelection].[Form]![MealEndDate]) AND ((Tbl_ScheduledMealItems.Category)=[Forms]![Frm_Main]![Frm_SiteCategorySelection].[Form]![CmbPickCat]) AND ((Tbl_ScheduledMealItems.SiteName)=[Forms]![Frm_Main]![Frm_SiteCategorySelection].[Form]![CmbPickSite]))
ORDER BY Tbl_ScheduledMealItems.DayAssigned;

The sub form functions fine and refreshes with the correct information.

But when I close the database I get four "enter parameter" boxes asking for the data from the query:

StartDate
EndDate
PickCategory
PickSite

The message on the parameter box specifies this form and these exact fields.





It's like it is trying to run the query again when I exit the database and can't find the parameters. I have this type of functionality elsewhere and have no problem.

I cannot figure out why this is happening or how I can prevent it.

Also I see people in the forum putting their code in nifty little scroll boxes so it doesn't take up as much space. How do you do that?

Phred

The graphic below is not a part of my post. I don't know how to get rid of it. Please ignore.

[June7]

Do you want to provide the project for analysis?

Use code tags. Find CODE tag button above the Advanced post editor window or type them (without spaces): [ code]code here[ /code]

Also, the Attachment manager is below the Advanced post editor. You should be able to edit your post and remove the attachment.

The code indentation used in the VBA procedure will be retained if you copy/paste.

The Else lines are not needed.

[Phred]

June7 thanks. I don't know what you mean by "provide the project for analysis?" Is this something other than what I am doing?

Thanks for the tip on the CODE tag.

I can't figure out how to delete anyting under the Attached Manager.

Are you saying the Else statement is not needed in my code? Each test should end with:

Exit Sub
End If

I didn't know that. Thanks.

[June7]

You can attach your project to post with the Attachment manager. Make copy, remove confidential data, run Compact & Repair, zip if still large (up to 2mb zip allowed).

Files attached to post should show in the bottom part of the manager. Other files previously uploaded to other posts will show in the upper section. Remove an attachment from post by hovering cursor over the thumbnail and a little x will show, click the x then just close the manager window.

You have nothing happening under the Else. It doesn't hurt to be there, it's just meaningless with the code as is.

[Phred]

June7, I don't know if I have uploaded this correctly but the zip file name is multiselect.zip. It is the entire database.

Attachment 6935MultiSelect.zip

Open the database, click the Edit Menus tab. Select category=Box Lunch, Site = Crete Monee 6th grade, start date 3/1/12 - 5/31/12 and click find meals. The datasheet should populate. Now close the database. You will be hit with the parameters request.

Let me know if you need more information. The query that is generating the parameters is the query behind the datasheet sub form.

Thanks for all you help.

Fred

[June7]

I tracked the parameter prompts to the Qry_Category_Site_Date_Edit. I don't know why Access was confused by these parameter references and I couldn't make it work as is. I tried giving the subform container a name different from the form it holds and adjusting the query. That usually resolves this confusion issue but didn't work this time.

I think your Main form is unnecessarily complex for several of the tabs. For the subforms that don't have a RecordSource, those unbound controls and the command button can be placed directly on the tab page. This was my resolution for the Edit Meals tab. Eliminated the unbound subform and moved everything from that form (including the subsubform) onto the tab and fixed the parameters in the query. No more prompts popup. Copy code from the removed subform and test. Unfortunately, I don't know what combination of parameters should return records and the Find Meals procedure isn't returning any.

Yesterday had a message 'Undefined Date function in expression' popup. Not seeing that today.

[Phred]

June7 where do I find the modified database? I don't see a link. In the Attachment Manager I don't see any way to download anything. I can't find anything in the FAQs. Where would I find it and how do I download it?

Thanks Fred

[June7]

I did not upload database. Attachment manager is only to manage your uploads.

You can make the mods as you wish.

[Phred]

June7 Thanks for your help.

I got what you were saying immediately. I simplified by eliminating the parent forms and transfering the fields to the tab and debugging till it all functioned. It really helps me when you give me the bigger picture. It not only eliminated the problem but I cleaned up a ton of stuff. I removed the "Else" from my code as per your suggestion. I think my product is much better now.

I will mark this solved with a big thanks.

Fred