Clear contents of a Form when opening 1

[harrisharris]

I have a form based on one table called products. The form is used to display product details based on a part number selected from a combo box.

Whenever I open the form, all the fields other than the combo box display data.

I would like to be able to open the form and have all the fields blank until I select a part number from the combo box.

Someones assistance would be much appreciated.

 

[384437]

Hi
You could always open the form in add new entry mode
I.e. DoCmd.OpenForm "formname", acNormal, , , acFormAdd

This would bring up a blank sheet. Then in the code for the combo box put it so that when you select an option the form closes and then opens again in edit or read only mode.

There may be a lot easier way of doing this but heres an option if you get no other answers.

Good luck

Andy

 

[Mute101]

Easiest way would be to remove your control source from the form, populate your combo box from an sql statement(Query) and reset your control source/filter in the after update event of your populated combo box.

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[harrisharris]

Thanks Andy, I will hang on a bit and see if anything else comes up, if not will let you know what happens.

Laurence

 

[harrisharris]

Morning Mute101,

I have done the Query, but need an explanation of how to reset the control source/filter. I am capable of getting into the code of the after update event, but need to know what additional code to put in.

Many thanks
Laurence

 

[Mute101]

In your after update you can simply put in;

Me.RecordSource = "Original Query"
Me.Filter = "PartNumber = " & Me!PartNumber
Me.Requery

Just replace "Original Query" with either the query you were opening the form with or the table it was bound to. Then change the PartNumber to the correct syntax for your field names and that should sort you out.

Simon

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[harrisharris]

Simon,
Thanks, but I seem to be getting into a bit of a mess.

The code I already have for my combo box after update event is
quote
Private Sub Combo30_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartID] = '" & Me![Combo30] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
end sub
unquote

Correct me if i'm wrong, I tagged your code onto the end of the above. When I open the form, no matter what Part Number I select in the combo box, the product details on the rest of the form all remain as those for record no 1, this also happens when the form is opened.

What have I done wrong?

 

[Mute101]

OK the form should be unbound when it opens therefore you shouldnt be able to call a duplicate recordset. In full you should;

1. Remove the control source from your form.
2. Create a query to populate your part numbers combobox and set the control source of the combo box.
3. change the after update event of the combo box to my code and amend to fit your form/table syntax.
ie.

Me.RecordSource = "Original Query"
Me.Filter = "[PartID] = '" & Me![Combo30] & "'"
Me.Requery

Also dont open the form in Add New Entry mode. I take it you have the form bound to a recordset at the moment, is this really neccessary or can you simply have it bound directly to a table or a query?


----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[harrisharris]

Thanks a lot Simon, - on shift last night, My son helped me out as well. Your help and patience appreciated.

Laurence