add new in filtered form

[dmckinney]

I have a bound form which is filtered on PortfolioId. AddNew is enabled on the form. Is there a 'best practice' to get the PortfolioId of the new record to be the same as the filter?

Thanks for your help - let me know if you don't understand the question.

Regards,

David McKinney.

 

[Russell Hauf]

How are you filtering your form?

 

[dmckinney]

When I open the form (with docmd.openform) the form is filtered. The filter is passed as the WhereCondition parameter of the openform action. (Which has the same effect as putting a manual filter on the form - or so it seems to me.)

 

[Russell Hauf]

Sorry for taking so long. Here is one solution. Bear with me.

Ok, when you open the form with code, add an OpenArgs argument that is equal to your WhereCondition (filter). (I'm assuming that you are using the WhereCondition to filter your form).

So it might look something like this:<pre>DoCmd.OpenForm FormName:="frmPortfolios", View:=acNormal, _
WhereCondition:="[PortID] = 123", OpenArgs:=123</pre>

I'm sure that you get your ID number (for the WhereCondition) from a form or something, so just use it again for the OpenArgs argument.

Ok, now display the Form's properties, and set AllowAdditions to False. This will grey out the Add New (*) navigation button.
Now add a button to the form. I suggest just using the wizard - select Record Operations under Categories:, and Add New Record under Actions:. Now go to the code and add a few lines. The result might look something like this (I named my button cmdNew):<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Compare</font> Database<font color='#000080'>Option</font><font color='#000080'>Explicit</font><hr align=left width=500><font color='#000080'>Private</font><font color='#000080'>Sub</font> cmdNew_Click()<font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> Err_cmdNew_Click

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec<font color='#008000'>' txtPortID is my text box that has</font><font color='#008000'>' PortID as it's control source.</font>
Me.txtPortID = Me.OpenArgs
Me.AllowAdditions = False

Exit_cmdNew_Click:<font color='#000080'>Exit</font><font color='#000080'>Sub</font>

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click<font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>

Hope this helps,

Russell
This message was edited by Russell Hauf on 2003-01-03 11:18
This message was edited by Russell Hauf on 2003-01-03 11:18

 

[dmckinney]

Thanks Russell,

That's a neat method. I haven't seen it before and I like it.

The only drawback is that you've got to click each time you add.

Figuring that no-one was going to answer I came up with another method which might interest you.

I left allow additions=true and added the following code to the form.

<Code>
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!PortfolioId=me.OpenArgs
End Sub
</Code>

Actually I have
Me!PortefeuilleId=Mid(me.Filter.instr(me.filter,"=")+1) but it's a bit nasty so I'll pinch your openargs method.

Thanks again for your help,

Dave.

 

[Russell Hauf]

Nice one. And what you have is not all bad...it is a trade off between haveing the extra OpenArgs argument or the InStr...I'd just leave it like you have it, personally.

-rh