Form closing problem

[John]

Hi

I have a form with a list box. The list box is bound to a query which has
below as the parameter;

[Forms]![MyForm]![Client]

[Client] is a drop down on the same form. So when a user selects a client
the listbox displays the relevant records for the client. This works fine.

The problem is that when I try to close the form by clicking on a button
which has the below code;

DoCmd.Close acForm, Me.Name

I get a dialog that says 'Enter Parameter Value' and the value it is asking
for is for [Forms]![MyForm]![Client]. How can I get rid of this message
while successfully being able to close the form via code?

Thanks

Regards

 

[Allen Browne]

This problem occurs because of the cyclic dependencies between the form and
its controls and the query. The solution is to find another approach that
removes those dependencies.

One solution is to remove the criteria from the query. Instead use the
AfterUpdate event of the combo to create the query string, and assign it to
the list box.

This example assumes that:
- the Client combo is unbound;
- the Bound Column of the combo is the primary key of the Clients table, and
that's a numeric field.

Private Sub Client_AfterUpdate()
Dim strSql As String
strSql = "SELECT ClientID, ClientName FROM Clients WHERE ("
If IsNull(Me.Client) Then
strSql = strSql & "False"
Else
strSql = strSql & "ClientID = " & Me.Client
End If
strSql = strSql & ") ORDER BY ClientName, ClientID;"
'Debug.Print strSql
Me.List1.RowSource = strSql
End Sub

If you need help getting the SQL string correct, switch you query to SQL
View (View menu in query design) for an example.

 

[John]

I have done this with many forms before. Not sure why the problem cones with
only this one.

Thanks

Regards

This problem occurs because of the cyclic dependencies between the form
and its controls and the query. The solution is to find another approach
that removes those dependencies.

One solution is to remove the criteria from the query. Instead use the
AfterUpdate event of the combo to create the query string, and assign it
to the list box.

This example assumes that:
- the Client combo is unbound;
- the Bound Column of the combo is the primary key of the Clients table,
and that's a numeric field.

Private Sub Client_AfterUpdate()
Dim strSql As String
strSql = "SELECT ClientID, ClientName FROM Clients WHERE ("
If IsNull(Me.Client) Then
strSql = strSql & "False"
Else
strSql = strSql & "ClientID = " & Me.Client
End If
strSql = strSql & ") ORDER BY ClientName, ClientID;"
'Debug.Print strSql
Me.List1.RowSource = strSql
End Sub

If you need help getting the SQL string correct, switch you query to SQL
View (View menu in query design) for an example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a list box. The list box is bound to a query which has
below as the parameter;

[Forms]![MyForm]![Client]

[Client] is a drop down on the same form. So when a user selects a client
the listbox displays the relevant records for the client. This works
fine.

The problem is that when I try to close the form by clicking on a button
which has the below code;

DoCmd.Close acForm, Me.Name

I get a dialog that says 'Enter Parameter Value' and the value it is
asking for is for [Forms]![MyForm]![Client]. How can I get rid of this
message while successfully being able to close the form via code?