how to goto last record, add new record

[Lloyd]

I have a subform on a tab.

The subform is NOT set for dataentry.

When the user selects the tab with this form on it, I want it to display a
new form so the user doesnt accidently type over an existing entry, but I
want the user to be able to page back through the previous entries in case
they need to change one of them.

I have tried various code (listed below) in the on current event of the sub
form

DoCmd.RunCommand acCmdRecordsGoToLast

Me.Form.Recordset.MoveLast

DoCmd.GoToRecord , , acNewRec

Each of these in one way or the other will take me to a new record, but when
I select the previous button on my form, it says there is no previous
entries. Its like its treating the form like a data entry form.

Maybe I'm approaching this wrong, but I want to start on a new page, but
still be able to page back through the previous entries on the form, what am
I doing wrong?

 

[ruralguy via AccessMonster.com]

The code you posted would work on the MainForm recordset but not the SubForm;
at least as you posted it. You need to set the focus to a control on the
SubForm first which is a two step process.

Me.SubForm.SetFocus '-- Set Focus to the SubFormControl
1st
Me.SubForm.FORM!txtControl.SetFocus '-- Now set Focus to a control on SubForm

DoCmd.RunCommand acCmdRecordsGoToNew '-- And move to the new record

 

[Lloyd]

I used the following code and when I try to run it, I get an error "Method or
data member not found" as it refers to the subform name. I placed the below
code in the subforms on current event so when the user switches to the tab
that has this subform the code runs, but when I try and compile the code, the
error is displayed.

Me.subfrmCaseReview.SetFocus 'Set focus to the subFormControl 1st
Me.subfrmCaseReview.DateReviewed.SetFocus 'Now set Focus to a control on
SubForm
DoCmd.RunCommand.acCmdRecordsGoToNew 'and move to a new record

 

[Stuart McCall]

Me.subfrmCaseReview.DateReviewed.SetFocus 'Now set Focus to a control on

SubForm

That code refers to the subform control, not the form hosted within it. To
make the distinction, you must add the .Form property, like this:

Me.subfrmCaseReview.Form.DateReviewed.SetFocus

 

[Lloyd]

Thank you for your help, I tried the revised code, but I still receive an
error, "method or data member not found" when I compile the code, it
highlights "subfrmcasereview" giving that error. The code I am now using is:

Me.subfrmCaseReview.Form.SetFocus 'Set focus to the subFormControl 1st
Me.subfrmCaseReview.Form.DateReviewed.SetFocus 'Now set Focus to a control

 

[Stuart McCall]

Thank you for your help, I tried the revised code, but I still receive an
error, "method or data member not found" when I compile the code, it
highlights "subfrmcasereview" giving that error. The code I am now using
is:

Me.subfrmCaseReview.Form.SetFocus 'Set focus to the subFormControl 1st
Me.subfrmCaseReview.Form.DateReviewed.SetFocus 'Now set Focus to a control

Hmm. I'm not entirely sure about this, but try:

Me.subfrmCaseReview.SetFocus
Me.subfrmCaseReview.Form.DateReviewed.SetFocus
DoCmd.RunCommand ...

or (better) :

With Me.subfrmCaseReview
.SetFocus
.Form.DateReviewed.SetFocus
End With
DoCmd.RunCommand ...

 

[Lloyd]

Stuart,

Thanks again for the help, I tried both codes and it still gives the same
error message. When I compile the code, it gives an error Method or data
member not found and highlights subfrmcasereview on the first line of code

With Me.subfrmCaseReview

 

[Stuart McCall]

Stuart,

Thanks again for the help, I tried both codes and it still gives the same
error message. When I compile the code, it gives an error Method or data
member not found and highlights subfrmcasereview on the first line of code

With Me.subfrmCaseReview

<snip>

When you type Me. does the intellisense list show an entry for
subfrmCaseReview? If so, are you certain it's a subform control? I can't
come up with anything else to cause that error...

 

[ruralguy via AccessMonster.com]

Stuart: Your code is correct if the name of the SubFormControl is as stated.

Lloyd: You know you are looking at the SubFormControl when the Data tab has
the LinkChild/MasterFields properties. Then move to the Other tabe for the
actual name of the control.

Stuart,

[quoted text clipped - 3 lines]

With Me.subfrmCaseReview

<snip>

When you type Me. does the intellisense list show an entry for
subfrmCaseReview? If so, are you certain it's a subform control? I can't
come up with anything else to cause that error...

 

[Lloyd]

I'm still somewhat new to designing access databases, maybe what I'm calling
a subform is not then. I created a form called subfrmCaseReview. I then
dropped that form into my tabbed form on tab #7. Since I'm in a tabbed form,
I though the first tab which has actualy fields on it was the "main form" and
then my other tabs are comprised of other forms (I thought they were called
subforms) that I then drop onto the various tabs.

When I select the form properties and the data tab, it shows the record
source as being a table that the data is stored in. It does not show a
child/master fields property. So I guess that means I'm not in a true sub
form environment which would explain why the code does not work?

I'm sorry for the confusion, I guess its my missunderstanding of what a
subform is.

when I do ME. (subfrmCaseReview) does not come up...

So what are my options?

Stuart: Your code is correct if the name of the SubFormControl is as stated.

Lloyd: You know you are looking at the SubFormControl when the Data tab has
the LinkChild/MasterFields properties. Then move to the Other tabe for the
actual name of the control.

Stuart,

[quoted text clipped - 3 lines]

With Me.subfrmCaseReview

<snip>

When you type Me. does the intellisense list show an entry for
subfrmCaseReview? If so, are you certain it's a subform control? I can't
come up with anything else to cause that error...

 

[Lloyd]

Stewart,

Apperantly I am using the wrong terms...typing me. does not bring up the
name...I put more info in the next post...sorry for the confusion.

 

[ruralguy via AccessMonster.com]

What version of Access are you using? You just used Drag and Drop to place
the subfrmCaseReview on a tab page? Does the subfrmCaseReview show up in the
list of forms in your system?

I'm still somewhat new to designing access databases, maybe what I'm calling
a subform is not then. I created a form called subfrmCaseReview. I then
dropped that form into my tabbed form on tab #7. Since I'm in a tabbed form,
I though the first tab which has actualy fields on it was the "main form" and
then my other tabs are comprised of other forms (I thought they were called
subforms) that I then drop onto the various tabs.

When I select the form properties and the data tab, it shows the record
source as being a table that the data is stored in. It does not show a
child/master fields property. So I guess that means I'm not in a true sub
form environment which would explain why the code does not work?

I'm sorry for the confusion, I guess its my missunderstanding of what a
subform is.

when I do ME. (subfrmCaseReview) does not come up...

So what are my options?

Stuart: Your code is correct if the name of the SubFormControl is as stated.

[quoted text clipped - 12 lines]

 

[Lloyd]

I am using access 2007. Yes I just dragged and dropped the form called
subfrmCaseReview on one of my tabs. Yes subfrmCaseReview does show up on the
list of form objects.

What version of Access are you using? You just used Drag and Drop to place
the subfrmCaseReview on a tab page? Does the subfrmCaseReview show up in the
list of forms in your system?

I'm still somewhat new to designing access databases, maybe what I'm calling
a subform is not then. I created a form called subfrmCaseReview. I then
dropped that form into my tabbed form on tab #7. Since I'm in a tabbed form,
I though the first tab which has actualy fields on it was the "main form" and
then my other tabs are comprised of other forms (I thought they were called
subforms) that I then drop onto the various tabs.

When I select the form properties and the data tab, it shows the record
source as being a table that the data is stored in. It does not show a
child/master fields property. So I guess that means I'm not in a true sub
form environment which would explain why the code does not work?

I'm sorry for the confusion, I guess its my missunderstanding of what a
subform is.

when I do ME. (subfrmCaseReview) does not come up...

So what are my options?

Stuart: Your code is correct if the name of the SubFormControl is as stated.

[quoted text clipped - 12 lines]

subfrmCaseReview? If so, are you certain it's a subform control? I can't
come up with anything else to cause that error...

 

[ruralguy via AccessMonster.com]

I just tried having two forms open in design mode in ac2007 and dragging one
over the other and dropping it. It is just two separate forms. It does not
create a SubForm on the 1st form.

I am using access 2007. Yes I just dragged and dropped the form called
subfrmCaseReview on one of my tabs. Yes subfrmCaseReview does show up on the
list of form objects.

What version of Access are you using? You just used Drag and Drop to place
the subfrmCaseReview on a tab page? Does the subfrmCaseReview show up in the

[quoted text clipped - 24 lines]

 

[Lloyd]

So if its not subform how would I adapt the code suggestion you gave me to
goto a new form when I select the tab the form is on, but still allow the
users to page back through the existing records....or does this mean it cant
be done since its not a subform?

I just tried having two forms open in design mode in ac2007 and dragging one
over the other and dropping it. It is just two separate forms. It does not
create a SubForm on the 1st form.

I am using access 2007. Yes I just dragged and dropped the form called
subfrmCaseReview on one of my tabs. Yes subfrmCaseReview does show up on the
list of form objects.

What version of Access are you using? You just used Drag and Drop to place
the subfrmCaseReview on a tab page? Does the subfrmCaseReview show up in the

[quoted text clipped - 24 lines]

subfrmCaseReview? If so, are you certain it's a subform control? I can't
come up with anything else to cause that error...

 

[Stuart McCall]

I just tried having two forms open in design mode in ac2007 and dragging
one
over the other and dropping it. It is just two separate forms. It does not
create a SubForm on the 1st form.

<snip>

I don't have A2007 so I can't check, but I believe it should create a
subform if dragged from the database window onto the design view of a form.
This has always worked in previous versions.

 

[Lloyd]

I thought it was considered a sub form, I remember having to "link" the form
to the main forms primary key so the records stayed within the same record,
but now I'm not sure...do you have any other suggestions?

 

[Stuart McCall]

I thought it was considered a sub form, I remember having to "link" the
form
to the main forms primary key so the records stayed within the same
record,
but now I'm not sure...do you have any other suggestions?

I'm sure A2007 will offer multiple creation methods in the GUI. I'd better
let someone with experience of the product take this one.

One thing you could look for is a way of selecting a wizard to create a new
form with subform.

 

[ruralguy via AccessMonster.com]

Hey Stuart, I never know that worked. I've always brought down a
SubFormControl an put it on the form. Thanks for the tip.

 

[Stuart McCall]

Hey Stuart, I never know that worked. I've always brought down a
SubFormControl an put it on the form. Thanks for the tip.

You're welcome. I think I learned the trick from the ADH for '97. Since when
I've always done it that way for no better reason than because it sizes the
subform control nicely.