Adding an option to shortcut menu

[Joanne]

Hello,
I've posted this question before with no luck, so I'll try again! I have a
subform within a form, which is basically a task list. Users of the form may
want to add a blank row in order to add a task which they might have
forgotten. I would like to add and "Insert Row" option to the quick menu.
Is it possible to do this?

Thanks very much for any help.

 

[Albert D. Kallal]

Yes, you can quite easily do this.

Two things:

I assume that you turned "off" the allow additions in the sub-form (this
does give you "extra" space, as you will not see the "extra" blank line
where a user could just cursor into and start adding that new record (is
this assuming correct???).

So, our code will

add the new record
requery the sub-form to display this new record (because we used
code..it don't display automatic)
move the focus/cursor to that new record

Also note that the code that the custom menu calls HAS TO BE placed in the
PARENT form. So, that simply means that the right click code has to be in
the main form, as the menu bar can't resolve to the sub-form.

So, lets first write out the code needed to add the new record in the
sub-form, and then move the cursor/focus to the new record.

Public Function AddToSub()

Dim rst As dao.Recordset
Dim lngNewID As Long
Dim f As Form

Set f = Me.child1_test.Form
Set rst = f.RecordsetClone
rst.AddNew
rst!contact_id = Me.ContactID
lngNewID = rst!ID
rst.Update
Set rst = Nothing

' now re-load sub-form to display new record
f.Requery
' now move to this new reocrd
f.Recordset.FindFirst "id = " & lngNewID

' now set the focus to the sub-form
Me.child1_test.SetFocus
' now set the focus to the field on teh sub-form
Me.child1_test.Form.desc.SetFocus


End Function

So, the above is only about 10 lines of code, but I spread it out with
comments.

Note that since we are using *code* to add the record, you have to set the
child/master link field in our code. Also, much of the above code would NOT
be needed if your sub-form is set to allow additions (but, then why do you
need the right click feature then? -- you have to correct me if my assuming
is wrong..as then I am sending you on a wild goose chase and wasting your
time).

Ok, I would then test the above code by placing a button on the main form
(that we will remove later).

The code behind this "test" button would be:

AddToSub

Does the above work...get his code working....

Ok....got the button working?

Now, lets add this to a right click for the sub-form.

So, we need to create a custom right click for the sub-form.

first step, create a new menu bar.

(right click on your menu bar-> customize)

You should be on the "toolbars" tab, and simply click on eh "new button"

Lets call the new menu bar MySubAdd

Now, lets add a command to this menu bar

click on the command tab, and then on the right side, drag the "custom"
setting to the new menu bar

now, right click on the new menu bar (custom), select "properties". You can
now change the 'custom' text to

Add a new child record

In the "on-action" setting, we put the name of the function. In our example,
it i s AddToSub, so,

AddToSub()

Close this

Now, while in customise mode, go back to the toolbars tab, and select the
new menu we made. Then, hit the properties button.

Change the type of menu bar to a popup.

Please make note of and read the message you get at this point.

Now, simply open up your sub-form in design mode, and in the other tab of
the properties sheet, specify our menu as the

ShortCut menu Bar

(in fact, the dropdown for that setting will only show your new shortcut
menu you built).

That is it.

Now, when you right click on the sub-form, you get that new shortcut
menu....

You can also add additional features (such as cut/paste) etc. to this
shortcut menu if you wish (just use the standard approach to create menu
bars...and use drag and drop (hold down the control key when you "steal"
other built in menu bars, as you want to *copy* those menu items...not MOVE
them!!!). Not that you must display the "shortcut" menu bar, as that is
where ALL YOUR shortcut (popup) menus will be placed (and hat nasty long
message I told you to read tells you the same thing!!!).

 

[Joanne]

I cannot thank you enough! Your instructions are so explicit, detailed and
clear. I really, really appreicate it.

 

[Joanne]

Hello,
I'm working with this code and what I was hoping to be able to do (though I
realize I was not clear at all in my description of the problem), is to be
able to insert a row in the middle of rows that have already been filled in.
As an example, we use this database to write down tasks that have to be done
in order to achieve a certain goal. I might write tasks 1,2, 3 and 4 then
suddenly realize that in between 2 and 3, I need to add another task. So I
suppose it's more than just adding a record. I don't know if this is
possible. I've been researching it and can't seem to find much. Thank you
very much again.

 

[Albert D. Kallal]

Sure, we can still use our approach.

The trick is asking the user *where* they want to insert this record.
further, I assume you have some task numbering scheme you use now?

(how are you ordering these records).

So, the next question is:

-- do you want to prompt the user as to what position to add the record?

*** or ***

shall we just use the right click position? (and, do you want to insert
before, or after the current record selected?
Further, note that it is a bit difficult for the user to right click on a
particular record (mouse skill wise).

As I said, it CRITICAL in my question for you to explain if you are using
the built in "allow additions", and do you have that "extra" line in the
sub-form that is "ready" for new records to be added? I assume you turned
off the allow additions for the sub-form (correct, or incorrect?).

I mean, at this point in time, what have you done (or what do you plan) to
do with the normal sub-forms that display that "extra" record? (hint: I am
suggesting that by disabling the allow additions in the sub-form, and use my
previous code example, this is a acceptable solution - you not cleared this
issue yet.

Additionally:
yes, you can do this, but we do have to have some type of "tasknumber"
scheme that you designed to order the task records. How do you now
order/number the tasks now?

 

[Albert D. Kallal]

There is currently no numbering on

the tasks, though I think it would be fine if we added numbering, then I
guess we could just sort the form numerically.

Yes, we have to do the above (even the user does NOT have to see the above,
but we still must provide numbering.
The reason for this is that order of records in ms-access is random and ONLY
if you set the order will records return in that order.
(this means that now you been "lucky" that the records are in the same order
that they are entered, but is not a given unless you actually set the order
of the sub-form )

But I would love to do the
right click, insert row option. I just think it's much more elegant.

Sure, we can still use the code example given, but we just need to
incorpoate a numbering sheme into that code....

Assuming we add a field called onum (integer)
Assuming you change the sub-form to a query that is ORDERD by onum,
, then we can use:

Public Function AddToSub()

Dim rst As dao.Recordset
Dim lngNewID As Long
Dim f As Form
Dim intNext As Integer
Dim strSql As String

Me.Refresh
Set f = Me.child1_test.Form
If f.RecordsetClone.RecordCount = 0 Then
intNext = 0
Else
intNext = Nz(f!onum, 0)
End If

If intNext > 0 Then
f.Refresh
' we have to move down all records
strSql = "update contactchild set onum = onum + 1 where onum >= " & _
intNext & " and contact_id = " & Me.ContactID
CurrentDb.Execute strSql
Else
intNext = 1
End If

Set rst = f.RecordsetClone

rst.AddNew
rst!contact_id = Me.ContactID
rst!onum = intNext
lngNewID = rst!ID
rst.Update
Set rst = Nothing

' now re-load sub-form to display new record
f.Requery

' now move to this new reocrd
f.Recordset.FindFirst "id = " & lngNewID

' now set the focus to the sub-form
Me.child1_test.SetFocus
' now set the focus to the field on teh sub-form
Me.child1_test.Form.desc.SetFocus


End Function