Open Form in last Category or record

[Guest]

Posted this question a few weeks ago but never got an answer... and I don't
know a way to get it back to focus other than reposting. Anyway...

I am trying to have my form open with the last edited record (which is a
subform). The subform info is accessed by a combobox in the footer. I
already have the following so updates are verified.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Data has changed."
strMsg = strMsg & "Click Yes/Save or No/Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
'do nothing
Else
Me.Undo
Cancel = True
End If

End Sub
I read that in order to have the form open the Table needed a field
LastEdited (is what I used) Date/Time set to Now() so I have done that but I
don't know HOW to put the code in the FORM'S BeforeUpdate event, set the
value of the LastUpdated field to Now()
Because I already have the above in there and I don't know how to/where to
insert it or ?? Or is it suppose to be attached to the combo box. Or is it
suppose to go after my code IF the info is updated.

I just want the form to open in the category where was last working. The
category is chosen by the combobox it doesn't actually have to open in the
exact record just in the last category... either way. Is this possible?
Thanks!

 

[Steve Schapel]

Lmv,

You can modify the code like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Data has changed. Click Yes/Save or No/Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
Me.LastEdited = Now
Else
Me.Undo
Cancel = True
End If
End Sub

Then, in the procedure you use to open the form, something like this...

DoCmd.OpenForm "YourForm", ,
,"[LastUpdated]=DMax('[LastUpdated]','YourTable')"

 

[Guest]

Thanks Steve, that does open to the last record... now can it
open IN my form within the list instead of a seperate window with only that
record.

I am new to access...

I wasn't sure which event to put it in (current? open? gotfocus?)...
I wasn't sure which form to put it in. (main form or cldform)

I tried it on both the form and the cldform but an additional window popped
up rather than staying within my forms' cldform. Hopefully my questions are
clear.
Thanks!
lmv

Lmv,

You can modify the code like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Data has changed. Click Yes/Save or No/Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
Me.LastEdited = Now
Else
Me.Undo
Cancel = True
End If
End Sub

Then, in the procedure you use to open the form, something like this...

DoCmd.OpenForm "YourForm", ,
,"[LastUpdated]=DMax('[LastUpdated]','YourTable')"

--
Steve Schapel, Microsoft Access MVP

Posted this question a few weeks ago but never got an answer... and I don't
know a way to get it back to focus other than reposting. Anyway...

I am trying to have my form open with the last edited record (which is a
subform). The subform info is accessed by a combobox in the footer. I
already have the following so updates are verified.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Data has changed."
strMsg = strMsg & "Click Yes/Save or No/Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
'do nothing
Else
Me.Undo
Cancel = True
End If

End Sub
I read that in order to have the form open the Table needed a field
LastEdited (is what I used) Date/Time set to Now() so I have done that but I
don't know HOW to put the code in the FORM'S BeforeUpdate event, set the
value of the LastUpdated field to Now()
Because I already have the above in there and I don't know how to/where to
insert it or ?? Or is it suppose to be attached to the combo box. Or is it
suppose to go after my code IF the info is updated.

I just want the form to open in the category where was last working. The
category is chosen by the combobox it doesn't actually have to open in the
exact record just in the last category... either way. Is this possible?
Thanks!

 

[Steve Schapel]

Lmv,

No, the code doesn't go on any event of the form or subform. Once the
form is open and in use, it's a bit late to be running code that says
"OpenForm" being the form you are running it from... if you catch my
drift . I assumed you had a place where you opened the form from,
i.e. click a button on another form or a "menu/switchboard" form or some
such. So my suggestion was to modify the OpenForm method on whatever
that event is.

 

[Guest]

No, the code doesn't go on any event of the form or subform. Once the

form is open and in use, it's a bit late to be running code that says
"OpenForm" being the form you are running it from... if you catch my
drift .

Oh yeah...duh! (I knew that! Just had a sr blonde moment)

I assumed you had a place where you opened the form from,

Ok I tried the following

Private Sub Products_Click()
DoCmd.OpenForm "Products List", , ,
"[LastEdited]=DMax('[LastEdited]','ProductsTTL')"

End Sub

In the form it works when opened alone but when that form is a "cld" form in
another form it still doesn't stay within the form it opens another form. I
tried putting a last edited cmdbutton on the form but it still opens another
form with only that record on it...

My goal is if I am updating prices and I am in the 20th category on my form
each category has 20 product records and I don't remember where I left off I
want to be able to open to that last edited product record within the
category. Category is my main table and sub is products. (by the way sorry I
go back and forth between using "sub"and "cld" because I think they are the
same... are they?)

 

[Steve Schapel]

Lmv,

Ok, I've finally begun to grasp what you are trying to achieve here.
And yes, you are right, you can't "open" a subform!

So can you just confirm I am now on the same wavelength?... You've got
a main form Categories, and a continuous view subform Products. (Is it
the subform that is called Products List?). And every time you go to a
new record on the Categories form, you want the fucus to move to the
most recently edited record on the Products subform. Right?

--
Steve Schapel, Microsoft Access MVP

No, the code doesn't go on any event of the form or subform. Once the
form is open and in use, it's a bit late to be running code that says
"OpenForm" being the form you are running it from... if you catch my
drift .

Oh yeah...duh! (I knew that! Just had a sr blonde moment)

I assumed you had a place where you opened the form from,

Ok I tried the following

Private Sub Products_Click()
DoCmd.OpenForm "Products List", , ,
"[LastEdited]=DMax('[LastEdited]','ProductsTTL')"

End Sub

In the form it works when opened alone but when that form is a "cld" form in
another form it still doesn't stay within the form it opens another form. I
tried putting a last edited cmdbutton on the form but it still opens another
form with only that record on it...

My goal is if I am updating prices and I am in the 20th category on my form
each category has 20 product records and I don't remember where I left off I
want to be able to open to that last edited product record within the
category. Category is my main table and sub is products. (by the way sorry I
go back and forth between using "sub"and "cld" because I think they are the
same... are they?)

 

[Guest]

EXACTAMUNDO!
--
Remember: There are suppose to be no "dumb" questions!

Lmv,

Ok, I've finally begun to grasp what you are trying to achieve here.
And yes, you are right, you can't "open" a subform!

So can you just confirm I am now on the same wavelength?... You've got
a main form Categories, and a continuous view subform Products. (Is it
the subform that is called Products List?). And every time you go to a
new record on the Categories form, you want the fucus to move to the
most recently edited record on the Products subform. Right?

--
Steve Schapel, Microsoft Access MVP

No, the code doesn't go on any event of the form or subform. Once the
form is open and in use, it's a bit late to be running code that says
"OpenForm" being the form you are running it from... if you catch my
drift .

Oh yeah...duh! (I knew that! Just had a sr blonde moment)

I assumed you had a place where you opened the form from,

Ok I tried the following

Private Sub Products_Click()
DoCmd.OpenForm "Products List", , ,
"[LastEdited]=DMax('[LastEdited]','ProductsTTL')"

End Sub

In the form it works when opened alone but when that form is a "cld" form in
another form it still doesn't stay within the form it opens another form. I
tried putting a last edited cmdbutton on the form but it still opens another
form with only that record on it...

My goal is if I am updating prices and I am in the 20th category on my form
each category has 20 product records and I don't remember where I left off I
want to be able to open to that last edited product record within the
category. Category is my main table and sub is products. (by the way sorry I
go back and forth between using "sub"and "cld" because I think they are the
same... are they?)

 

[Steve Schapel]

Lmv,

Well, I think the Current event of the main form is therefore the
applicable event to make this happen. There are a couple of approaches
you could use here. Here's one...

1. Make a query to identify the most recently edited subform record.
The SQL view of this query will look a bit like this:
SELECT TOP 1 [YourIdField]
FROM YourProductsTable
WHERE YourCategoryID = [Forms]![YourCategoryForm]![YourCategoryID]
ORDER BY [LastUpdated] DESC
Let's say you name this query LatestChange

2. Enter code along these lines on the Current event of the Categories
form:

Me.NameOfYourPruductsSubform.SetFocus
Me.NameOfYourPruductsSubform.Form![YourIdField].SetFocus
DoCmd.FindRecord DLookup("[YourIdField]","LatestChange")

Hope this is clear enough. If you need more explicit help with this, we
would need more detailed information, for example, the names of your
tables and fields involved, the names of the form and subform control,
the name of the query/table that the subform is based on, etc. Thanks.

 

[Guest]

Steve,
First thank you for all the help. But my skill doesn't match your
instruction level.
My Table is roductsTTL
Formetails All Categories
uses
Formroducts List
called cldTableProducts in properties of Details All Categories
I got the query made...

I already have code in the current event and I don't know how to add more
code.
BUT I decided I'd rather have a cmdbutton if that is possible on the
Categories form. So that IF the person wanted to get back to their last
edited record they could.
I tried putting this in the click event of my button but I get a "you
cancelled previous operation...??"

So following your instructions (I think) I put this
-------------------------------------------
Private Sub cmdLastEdited_Click()
On Error GoTo Err_cmdLastEdited_Click
Me.cldTableProducts.SetFocus
Me.cldTableProducts.Form![ProductName].SetFocus
DoCmd.FindRecord DLookup("[LastEdited]", "qryLatestChange")

Exit_cmdLastEdited_Click:
Exit Sub

Err_cmdLastEdited_Click:
MsgBox Err.Description
Resume Exit_cmdLastEdited_Click

End Sub

 

[Steve Schapel]

Lmv,

My suggested code included these lines...
Me.NameOfYourPruductsSubform.Form![YourIdField].SetFocus
DoCmd.FindRecord DLookup("[YourIdField]","LatestChange")

You translated it into...
Me.cldTableProducts.Form![ProductName].SetFocus
DoCmd.FindRecord DLookup("[LastEdited]", "qryLatestChange")

You are trying to equate ProductName with LastEdited, which will not
work. In my example, by 'YourIdField' I was trying to refer to the
primary key field in the table that the Products List form is based on.
This is also the same field (not LastEdited) I was trying to get you
to return by the suggested query. The ProductName field may be
applicable, I don't really know because you have not told us anything at
all about the form or the table so we are shooting in the dark here.
But I somehow doubt it, as I suppose it is possible for the same
ProductName to occur more than once in the subform's record source. So
I would go with the primary key field.

 

[Guest]

Steve,
I had tried it with the Key (ProductID) where the lastedited is. I also
tried the following

Me.cldTableProducts.SetFocus
Me.cldTableProducts.Form![LastEdited].SetFocus
DoCmd.FindRecord DLookup("[LastEdited]", "qryLatestChange")

Then I changed it to ProductName just because I was trying different
combinations but I got the error "xcelled previous operation" same as if I
had "last edited"or if I used the Key the error was "object doesn't support
this property or method"

I am trying to do this in the "click" event of my cmdbutton which is on the
Category form rather than where you directed ("current" event of form)
because of changing my idea. Is this a reason for the code not to work? Sorry
if I am not giving you enough information...I'm trying
lmv

My suggested code included these lines...
Me.NameOfYourPruductsSubform.Form![YourIdField].SetFocus
DoCmd.FindRecord DLookup("[YourIdField]","LatestChange")

You translated it into...
Me.cldTableProducts.Form![ProductName].SetFocus
DoCmd.FindRecord DLookup("[LastEdited]", "qryLatestChange")

You are trying to equate ProductName with LastEdited, which will not
work. In my example, by 'YourIdField' I was trying to refer to the
primary key field in the table that the Products List form is based on.
This is also the same field (not LastEdited) I was trying to get you
to return by the suggested query. The ProductName field may be
applicable, I don't really know because you have not told us anything at
all about the form or the table so we are shooting in the dark here.
But I somehow doubt it, as I suppose it is possible for the same
ProductName to occur more than once in the subform's record source. So
I would go with the primary key field.

--
Steve Schapel, Microsoft Access MVP

Steve,
First thank you for all the help. But my skill doesn't match your
instruction level.
My Table is roductsTTL
Formetails All Categories
uses
Formroducts List
called cldTableProducts in properties of Details All Categories
I got the query made...

I already have code in the current event and I don't know how to add more
code.
BUT I decided I'd rather have a cmdbutton if that is possible on the
Categories form. So that IF the person wanted to get back to their last
edited record they could.
I tried putting this in the click event of my button but I get a "you
cancelled previous operation...??"

So following your instructions (I think) I put this
-------------------------------------------
Private Sub cmdLastEdited_Click()
On Error GoTo Err_cmdLastEdited_Click
Me.cldTableProducts.SetFocus
Me.cldTableProducts.Form![ProductName].SetFocus
DoCmd.FindRecord DLookup("[LastEdited]", "qryLatestChange")

Exit_cmdLastEdited_Click:
Exit Sub

Err_cmdLastEdited_Click:
MsgBox Err.Description
Resume Exit_cmdLastEdited_Click

End Sub

 

[Steve Schapel]

Lmv,

No, this should be fine on the Click event of a command button on the
Categories form.

What is the table/query that the cldTableProducts subform is based on?

What are the fields in this table/query? In particular, does this
table/query contain the ProductID field and the LastEdited field?

Do you have a control (e.g. textbox) ProductID on the cldTableProducts
subform?

What is the SQL view of the qryLatestChange query?

 

[Guest]

What is the table/query that the cldTableProducts subform is based on?

ProductsTTL
Form Name: Products List
KeyroductID
subform Name: cldTableProducts

The category form combo box popluates the cldTableProducts subform it uses a
txttodetail txtbox to move focus into the subform.

FORM: Details ALL Categories (contains subform cldTableProducts)
Based on Table: Categories
Key:CategoryID

Relationship:CategoryID

What are the fields in this table/query? In particular, does this
table/query contain the ProductID field and the LastEdited field?

table/query name: qryLatestChange

SELECT TOP 1 [ProductsTTL.LastEdited]
FROM ProductsTTL
WHERE CategoryID=Forms![Details All Categories]![CategoryID]
ORDER BY [LastUpdated] DESC;

Do you have a control (e.g. textbox) ProductID on the cldTableProducts
subform?

Yes in the header HOWEVER you asked about productID... and that is not in
the Details All Category form

What is the SQL view of the qryLatestChange query?

Above, this qry works in an on click event on another form I am using...
only problem it pops up as a new window...

I don't know enough about SQL yet to understand what the WHERE... means

This is ALL very new to me and I read as much as I can and copy code from
other places but I have only been doing this for a month and I really
appreciate the step by step on HOW to change something.
Thanks!!

 

[Steve Schapel]

Lmv,

Ok, thanks for the further information. There are a couple of things
that will need changing.

1. You have a ProductID control in the *header* of the subform? That's
odd. You will need to put it in the Detail section.

2. The qryLatestChange query is not correct. My original suggestion
was like this...
SELECT TOP 1 [YourIdField]
FROM YourProductsTable
WHERE YourCategoryID = [Forms]![YourCategoryForm]![YourCategoryID]
ORDER BY [LastUpdated] DESC
Well, when I said 'YourIdField' I was trying to refer to the unique
(i.e. probably Primary Key) field in the table, and it now transpires
that this field is called ProductID, so the query should look like this...
SELECT TOP 1 [ProductID]
FROM ProductsTTL
WHERE CategoryID=Forms![Details All Categories]![CategoryID]
ORDER BY [LastUpdated] DESC

Try that.

--
Steve Schapel, Microsoft Access MVP

What is the table/query that the cldTableProducts subform is based on?

ProductsTTL
Form Name: Products List
KeyroductID
subform Name: cldTableProducts

The category form combo box popluates the cldTableProducts subform it uses a
txttodetail txtbox to move focus into the subform.

FORM: Details ALL Categories (contains subform cldTableProducts)
Based on Table: Categories
Key:CategoryID

Relationship:CategoryID

What are the fields in this table/query? In particular, does this
table/query contain the ProductID field and the LastEdited field?

table/query name: qryLatestChange

SELECT TOP 1 [ProductsTTL.LastEdited]
FROM ProductsTTL
WHERE CategoryID=Forms![Details All Categories]![CategoryID]
ORDER BY [LastUpdated] DESC;

Do you have a control (e.g. textbox) ProductID on the cldTableProducts
subform?

Yes in the header HOWEVER you asked about productID... and that is not in
the Details All Category form

What is the SQL view of the qryLatestChange query?

Above, this qry works in an on click event on another form I am using...
only problem it pops up as a new window...

I don't know enough about SQL yet to understand what the WHERE... means

This is ALL very new to me and I read as much as I can and copy code from
other places but I have only been doing this for a month and I really
appreciate the step by step on HOW to change something.
Thanks!!

 

[Guest]

Though your patience is admirable... I have changed all of the things you
said and I cannot get it to work. I still get the same error.

The reason I didn't put the productID in the detail is because it is just a
number and doesn't mean anything other that it is a unique identifier. And so
I didn't need it taking up space. I didn't know there would be any point in
having it in the detail if visiblity was turned off. But I did put it in and
it still got an error.

lmv

--
Remember: There are suppose to be no "dumb" questions!

Lmv,

Ok, thanks for the further information. There are a couple of things
that will need changing.

1. You have a ProductID control in the *header* of the subform? That's
odd. You will need to put it in the Detail section.

2. The qryLatestChange query is not correct. My original suggestion
was like this...
SELECT TOP 1 [YourIdField]
FROM YourProductsTable
WHERE YourCategoryID = [Forms]![YourCategoryForm]![YourCategoryID]
ORDER BY [LastUpdated] DESC
Well, when I said 'YourIdField' I was trying to refer to the unique
(i.e. probably Primary Key) field in the table, and it now transpires
that this field is called ProductID, so the query should look like this...
SELECT TOP 1 [ProductID]
FROM ProductsTTL
WHERE CategoryID=Forms![Details All Categories]![CategoryID]
ORDER BY [LastUpdated] DESC

Try that.

--
Steve Schapel, Microsoft Access MVP

What is the table/query that the cldTableProducts subform is based on?

ProductsTTL
Form Name: Products List
KeyroductID
subform Name: cldTableProducts

The category form combo box popluates the cldTableProducts subform it uses a
txttodetail txtbox to move focus into the subform.

FORM: Details ALL Categories (contains subform cldTableProducts)
Based on Table: Categories
Key:CategoryID

Relationship:CategoryID

What are the fields in this table/query? In particular, does this
table/query contain the ProductID field and the LastEdited field?

table/query name: qryLatestChange

SELECT TOP 1 [ProductsTTL.LastEdited]
FROM ProductsTTL
WHERE CategoryID=Forms![Details All Categories]![CategoryID]
ORDER BY [LastUpdated] DESC;

Do you have a control (e.g. textbox) ProductID on the cldTableProducts
subform?

Yes in the header HOWEVER you asked about productID... and that is not in
the Details All Category form

What is the SQL view of the qryLatestChange query?

Above, this qry works in an on click event on another form I am using...
only problem it pops up as a new window...

I don't know enough about SQL yet to understand what the WHERE... means

This is ALL very new to me and I read as much as I can and copy code from
other places but I have only been doing this for a month and I really
appreciate the step by step on HOW to change something.
Thanks!!

 

[Steve Schapel]

Lmv

... I didn't know there would be any point in
having it in the detail if visiblity was turned off.

There isn't. It will have to be visible.

 

[Guest]

I did make it visible before my last post but it still didn't work ... so at
this point I just have a cmdbutton
Screen.PreviousControl.SetFocus
DoCmd.OpenForm "Products List", , ,
"[LastEdited]=DMax('[LastEdited]','ProductsTTL')"

And it opens in the new window... not what I want but it does give me quick
access to the information as to where I left off.

By the way does the lastedited only work in a form or can it work in a table
as well?
Thanks
lmv

 

[Steve Schapel]

Lmv,

I can't believe that something so simple and standard has got us
flummoxed like this! I really apologise for my lack of clarity in my
explanations. If you would care to email me your MDB file, I am keen to
see where this is going wrong. Just remove the .ns from the end of my
return email address. If you do, I will still respond here in the
newsgroup.

 

[Steve Schapel]

Lmv,

I have received the database you sent me. Are you sure that you sent
the correct file? This bears very little resemblance to the solution we
have been discussing. In fact, I can't see where you have implemented
any of the suggestions I made.

Here's what you need to do...

1. Change the qryLatestChange query to this:
SELECT TOP 1 ProductsTTL.ProductID
FROM ProductsTTL
WHERE (((ProductsTTL.CategoryID)=[Forms]![Details All
Categories]![CategoryID]))
ORDER BY ProductsTTL.LastEdited DESC;
(threre was a confusion between the name of the field being LastEdited
or LastUpdated)

2. In the subform, put the LastEdited control in the Detail section.

3. In the subform, put the ProductID control in the Detail section.

4. Where is the code that moves you to the most recently updated
record? It should look like this...

Me.cldTableProducts.SetFocus
Me.cldTableProducts.Form![ProductID].SetFocus
DoCmd.FindRecord DLookup("[ProductID]", "qryLatestChange")

 

[Guest]

Well, I have made all of the changes you suggested...In fact I did it in the
scaled down mdb I sent to you. (I will be happy to send it back to you so you
can see that I have done it. )
In any case I get the same error... you "cancelled previous operation"
so I must be daft...
(Maybe you could just send the changed one back to me so I can see what I am
doing wrong)
Were you able to make it work?
Thanks!

--
Remember: There are suppose to be no "dumb" questions!

Lmv,

I have received the database you sent me. Are you sure that you sent
the correct file? This bears very little resemblance to the solution we
have been discussing. In fact, I can't see where you have implemented
any of the suggestions I made.

Here's what you need to do...

1. Change the qryLatestChange query to this:
SELECT TOP 1 ProductsTTL.ProductID
FROM ProductsTTL
WHERE (((ProductsTTL.CategoryID)=[Forms]![Details All
Categories]![CategoryID]))
ORDER BY ProductsTTL.LastEdited DESC;
(threre was a confusion between the name of the field being LastEdited
or LastUpdated)

2. In the subform, put the LastEdited control in the Detail section.

3. In the subform, put the ProductID control in the Detail section.

4. Where is the code that moves you to the most recently updated
record? It should look like this...

Me.cldTableProducts.SetFocus
Me.cldTableProducts.Form![ProductID].SetFocus
DoCmd.FindRecord DLookup("[ProductID]", "qryLatestChange")

--
Steve Schapel, Microsoft Access MVP

Lmv,

I can't believe that something so simple and standard has got us
flummoxed like this! I really apologise for my lack of clarity in my
explanations. If you would care to email me your MDB file, I am keen to
see where this is going wrong. Just remove the .ns from the end of my
return email address. If you do, I will still respond here in the
newsgroup.