Solved Parameter Dialog on Open Form (1 Viewer)

[silentwolf]

Hi again,

sorry just came accross an error in my openForm Event

I open another form within my application

    If IsNull(Forms!frmCustomers!CustomerID) Then
        MsgBox "Bitte wählen Sie zunächst einen Datensatz aus."
        Exit Sub
    End If
    
    DoCmd.OpenForm "frmOrders", datamode:=acFormEdit, _
                    WindowMode:=acNormal, WhereCondition:="CustomerID=" _
                    & Forms!frmCustomers!CustomerID
    Me.Requery

    DoCmd.OpenForm "frmOrders", acNormal
    DoCmd.GoToRecord , , acNewRec

    Forms![frmOrders].Form![CustomerCombo] = Me.CustomerID

However the Orders Form opens after I have been asked to enter an CustomerID "Parameter" which I left blank and continioued to run my code.

The Orders Form opens with the correct CustomerID.

How can I get rid of this dialog?


Can't seam to find where the Error is causing this.

Has someone an idea where I can find it?

Checked the Controls on each form but they seam to be fine.

 

[theDBguy]

Check the record source of the form, the order by and filter on properties, and any combobox or listbox row sources.

 

[silentwolf]

Hi DBguy,

thanks for your reply!

Currently I do have in the Orders Form a combobox control. "CustomerCombo" Recordsource = "
SELECT qryCustomerSelection.CustomerID, qryCustomerSelection.Kunde
FROM qryCustomerSelection;"


Also a textbox Control CustomerID Recordsource is from the Table.

I will continou to check !

 

[Pat Hartman]

The fact that you are getting prompted means that Access can't find the CustomerID field.

Since CustomerID is in the current form, use the "Me" style reference so instead of
Forms!frmCustomers!CustomerID
use
Me.CustomerID

put a stop in the code and make sure that you are seeing a value for CustomerID. If CustomerID is NOT bound to a control on the form, you will need to do that. The control can be hidden so the user doesn't see it and very tiny. When I put hidden controls on forms, I set their backgrounds to bright yellow so they pop out when I open the form in design view.

When you create a report, Access silently rewrites your RecordSource and saves it in some hidden place. It removes all fields that are not bound do controls. You might have run into this if you wrote code that referenced some field that you included in your query but which wasn't bound to a control on a form. I think Access has started "helping" us by rewriting the RecordSource for forms as well as reports. I can't put my finger on when the rewrite happens but I've run into this problem and the solution is to replace the RecordSource or to add a hidden bound control with the field you need to reference.

Also, your code doesn't make any sense. You are opening the form to a specific record. Then you run a requery that requeries the current form. Then you open the form again. Then you go to a new record on the current form, not the form you opened.

I can't tell exactly what your intention is. I'm going to assume you want to open a form to an existing record. If there is none, you want to add the ID as the FK to the new record.

Do it this way instead:

DoCmd.OpenForm "frmOrders", datamode:=acFormEdit, _
WindowMode:=acDialog, WhereCondition:="CustomerID=" _
& Me.CustomerID, OpenArgs = Me.CustomerID

Then in the BeforeInsert event of frmOrders, populate the FK

Me.CustomerID = Me.OpenArgs


Using that method, you will NOT dirty a record before the user types anything AND every record the user adds will get the correct FK. I also changed the WindowMode to acDialog to limit access to just the popup form until it is closed. Having multiple forms open can be very confusing to the user. Using dialog this way prevents issues.

 

[silentwolf]

Hi Pat,

thanks I will work through your suggestion and get back to you!

Cheers

 

[silentwolf]

Hi just a quick update on this topic.

The issue was in the query where I used a Alias and that seamed to caused the issue.

Cheers for pointing all out also to Pat for the new Code!

Cheers