how to reset form (which is bound to joining query)

In my Access 2010 database I have two tables: clients (with columns: client_id, name) and orders (with columns: order_id, client_id, description). I have a query called orders_with_clients which joins clients and their orders (something like "select orders.client_id, orders.description, clients.name from orders join clients on orders.client_id=clients.client_id"). I can insert into this query. I can insert into it in two ways:

1. I can provide client_id and description (something like "insert 开发者_StackOverflowinto orders_with_clients (client_id, description) values (3, 'three pairs of socks')") - in such case Access creates a new row in orders table connected (by the foreign key) with existing row from clients table.

2. I can provide name and description (something like "insert into orders_with_clients (name, description) values ('Zenon', 'three pairs of socks')") - in such case Access creates a new row in clients table and then a new row in orders table connected (by the foreign key) with this new client.

I have a form bound with query orders_with_clients. It has three fields: client_id (it may be a combobox or a textfield - I don't care), name and description (both textfields). Of course at any time user uses only two of those fields: client_id and description or name and description.

The field client_id is controlled (set) by some VBA code based on what happens in some other forms. I mean, when some events occur, my VBA code sets client_id field to some value. I also want that sometimes VBA code resets this form to clear, empty state - including setting client_id field to clear new empty state. And this is where I have problem. I tried to do "fieldname.value=0" and "fieldname.value=null", but Access says it is an error to put null here. I also tried to do something like "formname.requery", but it caused it current state to be saved to database.

How can I do this?

---

This is a bound form and client id will, hopefully, be bound to a key field, you do not want to change it.

After that, it all depends on what you want to do. Do you wish to use the form for data entry? In which case open the form with new arguments. Do you wish to open with a different ID? Then reopen the form or set the recordsource.