Bookmark to other record

[Ian Baker]

Hi
I have 2 problems but fixing 1 may fix the other.
In 2003 I am using a form that has a combo box, a tab control and a subform
on the tab control. When an entry is entered into the combo box it checks to
see if the entry already exists, and if it does, advises the user that the
entry is already in the system and would they like to view the record. If
they select yes the existing entry is displayed. I also require the subform
in this instance as well as when navigating between each record to go to a
new record at the bottom of the subform displaying above it all previous
records.

My code so far is:
- the BeforeUpdate property of the cbo on the main form
Private Sub ctlContactName_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "ContactName = '" & ctlContactName & "'"

If rst.NoMatch = False Then
If MsgBox("This contact is already in the system. Do you wish to
view this " & _
"contact's details.", vbYesNo, "Existing Contact") = vbYes Then
Cancel = True
Me.Undo
Me.Bookmark = rst.Bookmark
Else
Cancel = True
Me.Undo
End If
End If

rst.Close
Set rst = Nothing
End Sub

- The OnCurrent property of the form
Private Sub Form_Current()
Me!subContactActivity.SetFocus
Me!subContactActivity.Form!ctlActivityDate.SetFocus
DoCmd.GoToRecord , , acNewRec
Me!ctlContactName.SetFocus
End Sub

The subform problem is that it does go to the new record but when there are
more then 13 records it jumps the page and only displays the new record and
I have to scroll up to see more. I need it to show the new record at the
bottom but also filling the subform up with previous records.

The other problem is that when I use the procedure in the first code snippet
above I get an error when it hits the Form_Current() procedure above at the
Me!subContactActivity.SetFocus
line:
2108 - You must save the field before you execute the GoToControl action,
the GoToControl method, or the SetFocus method.
Clicking Help on the dialogue box only presents me with an empty help
window.

I know I must be doing something wrong but for the life of me I can't see
where so your help is as always greatly appreciated.

 

[Matt]

the main form and subform bounds to one-to-many
relationshiop talbes?
it seems the main form doesn't bind to any datasource?
subContactActivity is the subform's name?

 

[Ian Baker]

Matt
The main form is bound to a table tblContact hence why the me.recordsetclone
works. The subform is also bound to a table tblContactActivity. There is a
relationship of 1 to Many between tblContact and tblContactActivity. The
ctlContactName_BeforeUpdate procedure works fine if I don't have the lines
referring to the subform in the Form_Current() procedure. When navigating
between records the Form_Current() procedure also works fine except the
display when getting over 13 records (the 2nd problem).

 

[Alick [MSFT]]

Hi Ian,

Would you zip and send the database file to me so I can research for you?

My email address is: (e-mail address removed)


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Ian Baker" <[email protected]>
| Subject: Bookmark to other record
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| Hi
| I have 2 problems but fixing 1 may fix the other.
| In 2003 I am using a form that has a combo box, a tab control and a
subform
| on the tab control. When an entry is entered into the combo box it checks
to
| see if the entry already exists, and if it does, advises the user that the
| entry is already in the system and would they like to view the record. If
| they select yes the existing entry is displayed. I also require the
subform
| in this instance as well as when navigating between each record to go to a
| new record at the bottom of the subform displaying above it all previous
| records.
|
| My code so far is:
| - the BeforeUpdate property of the cbo on the main form
| Private Sub ctlContactName_BeforeUpdate(Cancel As Integer)
| Dim rst As Recordset
|
| Set rst = Me.RecordsetClone
|
| rst.FindFirst "ContactName = '" & ctlContactName & "'"
|
| If rst.NoMatch = False Then
| If MsgBox("This contact is already in the system. Do you wish to
| view this " & _
| "contact's details.", vbYesNo, "Existing Contact") = vbYes
Then
| Cancel = True
| Me.Undo
| Me.Bookmark = rst.Bookmark
| Else
| Cancel = True
| Me.Undo
| End If
| End If
|
| rst.Close
| Set rst = Nothing
| End Sub
|
| - The OnCurrent property of the form
| Private Sub Form_Current()
| Me!subContactActivity.SetFocus
| Me!subContactActivity.Form!ctlActivityDate.SetFocus
| DoCmd.GoToRecord , , acNewRec
| Me!ctlContactName.SetFocus
| End Sub
|
| The subform problem is that it does go to the new record but when there
are
| more then 13 records it jumps the page and only displays the new record
and
| I have to scroll up to see more. I need it to show the new record at the
| bottom but also filling the subform up with previous records.
|
| The other problem is that when I use the procedure in the first code
snippet
| above I get an error when it hits the Form_Current() procedure above at
the
| Me!subContactActivity.SetFocus
| line:
| 2108 - You must save the field before you execute the GoToControl action,
| the GoToControl method, or the SetFocus method.
| Clicking Help on the dialogue box only presents me with an empty help
| window.
|
| I know I must be doing something wrong but for the life of me I can't see
| where so your help is as always greatly appreciated.
| --
| Regards
| Ian Baker
| Jackaroo Solutions Melb Aust
| Jackaroo IT - an IT Mgmt & Help Desk application at http://jackaroo.net.au
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003
|
|
|

 

[Alick [MSFT]]

Hi Ian,

I received your database file; to get rid of the error, we need to move the
code in BeforeUpdate to AfterUpdateevent, and delete the Cancel=True
statement;

Private Sub ctlContactName_AfterUpdate()

'you code

End Sub

As for the subform displaying issue, I think you can replace
DoCmd.GoToRecord , , acNewRec with DoCmd.GoToRecord , , acLast; that may be
what you are after.

Please feel free to reply to the threads if you have any concerns or
questions.


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Ian Baker" <[email protected]>
| References: <[email protected]>
<[email protected]>
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| Matt
| The main form is bound to a table tblContact hence why the
me.recordsetclone
| works. The subform is also bound to a table tblContactActivity. There is a
| relationship of 1 to Many between tblContact and tblContactActivity. The
| ctlContactName_BeforeUpdate procedure works fine if I don't have the lines
| referring to the subform in the Form_Current() procedure. When navigating
| between records the Form_Current() procedure also works fine except the
| display when getting over 13 records (the 2nd problem).
|
| --
| Regards
| Ian Baker
| Jackaroo Solutions Melb Aust
| Jackaroo IT - an IT Mgmt & Help Desk application at http://jackaroo.net.au
| | > the main form and subform bounds to one-to-many
| > relationshiop talbes?
| > it seems the main form doesn't bind to any datasource?
| > subContactActivity is the subform's name?
| >
| >
| >
| > >-----Original Message-----
| > >Hi
| > >I have 2 problems but fixing 1 may fix the other.
| > >In 2003 I am using a form that has a combo box, a tab
| > control and a subform
| > >on the tab control. When an entry is entered into the
| > combo box it checks to
| > >see if the entry already exists, and if it does, advises
| > the user that the
| > >entry is already in the system and would they like to
| > view the record. If
| > >they select yes the existing entry is displayed. I also
| > require the subform
| > >in this instance as well as when navigating between each
| > record to go to a
| > >new record at the bottom of the subform displaying above
| > it all previous
| > >records.
| > >
| > >My code so far is:
| > >- the BeforeUpdate property of the cbo on the main form
| > >Private Sub ctlContactName_BeforeUpdate(Cancel As
| > Integer)
| > > Dim rst As Recordset
| > >
| > > Set rst = Me.RecordsetClone
| > >
| > > rst.FindFirst "ContactName = '" & ctlContactName
| > & "'"
| > >
| > > If rst.NoMatch = False Then
| > > If MsgBox("This contact is already in the
| > system. Do you wish to
| > >view this " & _
| > > "contact's details.", vbYesNo, "Existing
| > Contact") = vbYes Then
| > > Cancel = True
| > > Me.Undo
| > > Me.Bookmark = rst.Bookmark
| > > Else
| > > Cancel = True
| > > Me.Undo
| > > End If
| > > End If
| > >
| > > rst.Close
| > > Set rst = Nothing
| > >End Sub
| > >
| > >- The OnCurrent property of the form
| > >Private Sub Form_Current()
| > > Me!subContactActivity.SetFocus
| > > Me!subContactActivity.Form!ctlActivityDate.SetFocus
| > > DoCmd.GoToRecord , , acNewRec
| > > Me!ctlContactName.SetFocus
| > >End Sub
| > >
| > >The subform problem is that it does go to the new record
| > but when there are
| > >more then 13 records it jumps the page and only displays
| > the new record and
| > >I have to scroll up to see more. I need it to show the
| > new record at the
| > >bottom but also filling the subform up with previous
| > records.
| > >
| > >The other problem is that when I use the procedure in
| > the first code snippet
| > >above I get an error when it hits the Form_Current()
| > procedure above at the
| > >Me!subContactActivity.SetFocus
| > >line:
| > >2108 - You must save the field before you execute the
| > GoToControl action,
| > >the GoToControl method, or the SetFocus method.
| > >Clicking Help on the dialogue box only presents me with
| > an empty help
| > >window.
| > >
| > >I know I must be doing something wrong but for the life
| > of me I can't see
| > >where so your help is as always greatly appreciated.
| > >--
| > >Regards
| > >Ian Baker
| > >Jackaroo Solutions Melb Aust
| > >Jackaroo IT - an IT Mgmt & Help Desk application at
| > http://jackaroo.net.au
| > >
| > >
| > >---
| > >Outgoing mail is certified Virus Free.
| > >Checked by AVG anti-virus system
| > (http://www.grisoft.com).
| > >Version: 6.0.542 / Virus Database: 336 - Release Date:
| > 18/11/2003
| > >
| > >
| > >.
| > >
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003
|
|
|

 

[Ian Baker]

Hi Alick
Thanks for your help - moving the code to the AfterUpdate event worked
perfectly.

Changing the

DoCmd.GoToRecord , , acNewRec with DoCmd.GoToRecord , , acLast

now just shows the last and new records and still jumping the page when you
enter more activity records. I think the only way around this is to use the
following:
Use a variable x that counts the number of records
IF statement that if x is greater than the subform display capacity then
DoCmd.GoToRecord , , acGoTo, x-subformDisplayCapacity
This would be a large overhead to have in the OnCurrent event though. Any
other suggestions?

 

[Alick [MSFT]]

Hi Ian,

I think we may need to use Windows API to send message to datasheet view
window, however, it is hard to achieve that. I'd like to know if it is very
important? If so, I will spend some time and maybe will come out a sample.

If you have any concerns, please feel free reply to the threads.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Ian Baker" <[email protected]>
| References: <[email protected]> <036701c3af5f$1a1edcd0
|
| Hi Alick
| Thanks for your help - moving the code to the AfterUpdate event worked
| perfectly.
|
| Changing the
| > DoCmd.GoToRecord , , acNewRec with DoCmd.GoToRecord , , acLast
| now just shows the last and new records and still jumping the page when
you
| enter more activity records. I think the only way around this is to use
the
| following:
| Use a variable x that counts the number of records
| IF statement that if x is greater than the subform display capacity then
| DoCmd.GoToRecord , , acGoTo, x-subformDisplayCapacity
| This would be a large overhead to have in the OnCurrent event though. Any
| other suggestions?
|
| --
| Regards
| Ian Baker
| Jackaroo Solutions Melb Aust
| Jackaroo IT - an IT Mgmt & Help Desk application at http://jackaroo.net.au
| | > Hi Ian,
| >
| > I received your database file; to get rid of the error, we need to move
| the
| > code in BeforeUpdate to AfterUpdateevent, and delete the Cancel=True
| > statement;
| >
| > Private Sub ctlContactName_AfterUpdate()
| >
| > 'you code
| >
| > End Sub
| >
| > As for the subform displaying issue, I think you can replace
| > DoCmd.GoToRecord , , acNewRec with DoCmd.GoToRecord , , acLast; that may
| be
| > what you are after.
| >
| > Please feel free to reply to the threads if you have any concerns or
| > questions.
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Product Support Services
| > Microsoft Corporation
| > Get Secure! - <www.microsoft.com/security>
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| >
| > --------------------
| > | From: "Ian Baker" <[email protected]>
| > | References: <[email protected]>
| > <[email protected]>
| > | X-Tomcat-NG: microsoft.public.access.formscoding
| > |
| > | Matt
| > | The main form is bound to a table tblContact hence why the
| > me.recordsetclone
| > | works. The subform is also bound to a table tblContactActivity. There
is
| a
| > | relationship of 1 to Many between tblContact and tblContactActivity.
The
| > | ctlContactName_BeforeUpdate procedure works fine if I don't have the
| lines
| > | referring to the subform in the Form_Current() procedure. When
| navigating
| > | between records the Form_Current() procedure also works fine except
the
| > | display when getting over 13 records (the 2nd problem).
| > |
| > | --
| > | Regards
| > | Ian Baker
| > | Jackaroo Solutions Melb Aust
| > | Jackaroo IT - an IT Mgmt & Help Desk application at
| http://jackaroo.net.au
| > | | > | > the main form and subform bounds to one-to-many
| > | > relationshiop talbes?
| > | > it seems the main form doesn't bind to any datasource?
| > | > subContactActivity is the subform's name?
| > | >
| > | >
| > | >
| > | > >-----Original Message-----
| > | > >Hi
| > | > >I have 2 problems but fixing 1 may fix the other.
| > | > >In 2003 I am using a form that has a combo box, a tab
| > | > control and a subform
| > | > >on the tab control. When an entry is entered into the
| > | > combo box it checks to
| > | > >see if the entry already exists, and if it does, advises
| > | > the user that the
| > | > >entry is already in the system and would they like to
| > | > view the record. If
| > | > >they select yes the existing entry is displayed. I also
| > | > require the subform
| > | > >in this instance as well as when navigating between each
| > | > record to go to a
| > | > >new record at the bottom of the subform displaying above
| > | > it all previous
| > | > >records.
| > | > >
| > | > >My code so far is:
| > | > >- the BeforeUpdate property of the cbo on the main form
| > | > >Private Sub ctlContactName_BeforeUpdate(Cancel As
| > | > Integer)
| > | > > Dim rst As Recordset
| > | > >
| > | > > Set rst = Me.RecordsetClone
| > | > >
| > | > > rst.FindFirst "ContactName = '" & ctlContactName
| > | > & "'"
| > | > >
| > | > > If rst.NoMatch = False Then
| > | > > If MsgBox("This contact is already in the
| > | > system. Do you wish to
| > | > >view this " & _
| > | > > "contact's details.", vbYesNo, "Existing
| > | > Contact") = vbYes Then
| > | > > Cancel = True
| > | > > Me.Undo
| > | > > Me.Bookmark = rst.Bookmark
| > | > > Else
| > | > > Cancel = True
| > | > > Me.Undo
| > | > > End If
| > | > > End If
| > | > >
| > | > > rst.Close
| > | > > Set rst = Nothing
| > | > >End Sub
| > | > >
| > | > >- The OnCurrent property of the form
| > | > >Private Sub Form_Current()
| > | > > Me!subContactActivity.SetFocus
| > | > > Me!subContactActivity.Form!ctlActivityDate.SetFocus
| > | > > DoCmd.GoToRecord , , acNewRec
| > | > > Me!ctlContactName.SetFocus
| > | > >End Sub
| > | > >
| > | > >The subform problem is that it does go to the new record
| > | > but when there are
| > | > >more then 13 records it jumps the page and only displays
| > | > the new record and
| > | > >I have to scroll up to see more. I need it to show the
| > | > new record at the
| > | > >bottom but also filling the subform up with previous
| > | > records.
| > | > >
| > | > >The other problem is that when I use the procedure in
| > | > the first code snippet
| > | > >above I get an error when it hits the Form_Current()
| > | > procedure above at the
| > | > >Me!subContactActivity.SetFocus
| > | > >line:
| > | > >2108 - You must save the field before you execute the
| > | > GoToControl action,
| > | > >the GoToControl method, or the SetFocus method.
| > | > >Clicking Help on the dialogue box only presents me with
| > | > an empty help
| > | > >window.
| > | > >
| > | > >I know I must be doing something wrong but for the life
| > | > of me I can't see
| > | > >where so your help is as always greatly appreciated.
| > | > >--
| > | > >Regards
| > | > >Ian Baker
| > | > >Jackaroo Solutions Melb Aust
| > | > >Jackaroo IT - an IT Mgmt & Help Desk application at
| > | > http://jackaroo.net.au
| > | > >
| > | > >
| > | > >---
| > | > >Outgoing mail is certified Virus Free.
| > | > >Checked by AVG anti-virus system
| > | > (http://www.grisoft.com).
| > | > >Version: 6.0.542 / Virus Database: 336 - Release Date:
| > | > 18/11/2003
| > | > >
| > | > >
| > | > >.
| > | > >
| > |
| > |
| > | ---
| > | Outgoing mail is certified Virus Free.
| > | Checked by AVG anti-virus system (http://www.grisoft.com).
| > | Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003
| > |
| > |
| > |
| >
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.542 / Virus Database: 336 - Release Date: 18/11/2003
|
|
|