Simple Question :) (1 Viewer)

[lukekelly]

I have a set of commands in vba to copy a recordset into another table and to open up a form that reads from the new table - It works fine - however it always goes to the first record. As the last record would be the one i just created (99% of the time) is there some code to put with the following?

DoCmd.OpenForm "Projects"

Thanks for any pointers/help in advance

Luke

 

[vbaInet]

Use the WHERE argument of the OpenForm method, example:

DoCmd.OpenForm "Form_Name", , , "ID = 1"

To point yourself to the last record and provided the ID is a Number data type, use the DMax() function to get the last record's ID:

http://www.techonthenet.com/access/functions/domain/dmax.php

So eventually you would end up with something like:

DoCmd.OpenForm "Form_Name", , , "ID = " & DMax()

 

[boblarson]

Just to complement vbaInet's info -

Table data is not stored sequentially. While it may APPEAR at times to be doing so, it really doesn't. You cannot guarantee that you will select the last record entered unless you use a timestamp. Even an autonumber ID is not foolproof for identifying the last record because it can and, depending on the circumstances, will go into negative numbers or a random pattern. So, you are best off if you include a date/time field which enters the date and time when the record was entered. Then you can sort by that and/or select the max date/time.

 

[lukekelly]

OK thanks very much Bob and vbaInet for the information, I shall do what you suggested including date/time field at the point of record creation.

tried

DoCmd.OpenForm "Projects", , , "Project Number = " & DMax("Project Number", "Projects")

but getting a syntax error when it runs (Syntax error (missing operator) in query expression 'Max(Project Number)'. on line 0), is there something obvious ive done wrong there?

Thanks for your help

 

[vbaInet]

If your field/table name includes a space then you must enclose it in square brackets. Like so:

DoCmd.OpenForm "Projects", , , "[B][COLOR=Red][[/COLOR][/B]Project Number[COLOR=Red][B]][/B][/COLOR] = " & DMax("[COLOR=Red][B][[/B][/COLOR]Project Number[COLOR=Red][B]][/B][/COLOR]", "Projects")

 

[lukekelly]

I tried a different method instead:

DoCmd.OpenForm "Projects", acNormal
      DoCmd.GoToRecord acDataForm, "Projects", acLast

Works fine Thanks for your help!

 

[vbaInet]

I would be weary using acLast especially in a multi-user environment and concurrent useage. I think there are issues with First and Last. If it's going to be just one user then your solution is full proof.

 

[boblarson]

DO NOT USE LAST (or FIRST)!!!!

I would be weary using acLast especially in a multi-user environment and concurrent useage. I think there are issues with First and Last.

YES - DO NOT USE acLAST. Unless you have a date/time stamp, and sort on it and then use that query as the source of the form, LAST WILL NOT PROVIDE THE LAST RECORD ENTERED. It is only the last record of whatever recordset you have. And, as I have said earlier, tables DO NOT STORE the records in the order you enter them. It is just that many times it LOOKS like it does. But it does not and the use of last (or first) will appear to be functioning but is ONLY AN ILLUSION.

If it's going to be just one user then your solution is full proof.

No, it is NOT. See above.

 

[lukekelly]

Ok thanks for your help guys, i went with vbaInet's solution instead, Although I still dont understand by what you mean Bob saying that the table does not store the data in the order it is entered. I always use a unique identifier on the tables with no duplicates so I would assume (proberbly wrongly still) that if the data is entered and is assigned say 88, then the next entry would be 89 and there for in order? I know that the acLast obviously didnt check on that particular type of column and understand that a check on the data like that can cause undesired effects. Is DMax the only operator you can use in this instance? What if i wanted to go to the last record created by the unique identifier (which was numeric)? Just want to understand a bit more is all

Thanks

Luke

Edit: Thanks for pointing out my mistake with the tagging! Much appreciated!

 

[vbaInet]

In order as it may appear but logically not in order. If you want to represent your table in Order then you must explicitly tell it to ORDER BY a field in your table properties but it's always safer to do it in a QUERY instead.

Yes DMax() is the only function you can use to get the last record from a table that uses a numeric ID. You can also use a recordset and loop through until you get to the EOF. This recordset would need to be ORDERED as well. You can also use a query too if you sort the numeric ID in DESCENDING order and use TOP 1 to get the last record.

Bob might throw some more light.

 

[boblarson]

What I mean by the data is not stored in the order you entered it is that Access stores the data in the most convenient spot it finds within the file structure at the time the data is added. What you see is not necessarily how it acts. Many times when you open a table, you see the data as it was entered and so you can come to the faulty conclusion that the data is ordered in the order that it was entered. But I assure you that is NOT the case. It is a common misconception and one that can come back to bite you big time if you don't know about it.

In fact, you need to have a field which would be able to tell you which order it was entered in. The most effective of these, especially in a multiuser environment, is a date/time stamp that is added at the time of update (in the before update event of the form). Using an autonumber or other numbering scheme MAY not give you the actual correct order because those are usually added as a person starts a record, but in a multiple user environment, they might not save it before someone else who started one afterwards. Also, an autonumber only guarantees a unique number. It is not guaranteed to give you an incremented number and again, it may not be in the order that the records were saved.

So, in order to use the LAST or FIRST functions you need to be aware of what they actually do. They return the last or first record in a recordset and if you want it to be accurate, you need to use it with an ORDERED recordset. That means you need to apply an order, in a QUERY, to the records so that the order is just right.

Let's say you have 3 records in a table (one field of MyNum) and those records were entered like

1
3
2

Now, using first on the table may produce a 1 but it may not because we have not provided an explicitly ordered recordset. If we sort in ascending order the FIRST function will then return a 1. But if we sort in DESCENDING order, the FIRST function will return a 3.

I hope that helps.