Access forms

[raveen]

Hi, I am using access 2016, have created a textbox in forms, which captures the username from a query, using Dlast function, (to capture the last record for username, who has currently logged into the current database). This is working fine. But my problem is that this username gets changed by itself, after the next user login. I am not good in VBA. I want to lock the field (username), once it is captured. Dont want to change the field value, when the next user login for this particular record.

Can get help please.

[CJ_London]

have you considered using environ("username") instead of dlookup?

I appreciate your comment about 'not good in VBA' but if you want to do things like this, you need to learn sometime!

[Missinglinq]

You also need to go to Help and enter DLast as your search term; it doesn't do what its name suggests!

From Help: You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field.

The Help article will tell you how to do what you're trying for.

Linq ;0)>

[raveen]

I am using a DLast function in a form textbox to bring a value from a field in query y field's last record. This value in the forms is getting updated everytime, the value is changed in the query's last record.
Is there anyway to lock or fix the value in the form.

[ssanfu]

Hi, I am using access 2016, have created a textbox in forms, which captures the username from a query, using Dlast function, (to capture the last record for username, who has currently logged into the current database).

But my problem is that this username gets changed by itself, after the next user login.

If I understand correctly, every time someone logs in, you want to capture their username/ID and save it in a table? If yes, then right now you are saving the username into the LAST record, not a NEW record. You don't need to lock the fields, just add a new record, then save the username.

Maybe you would like to post your dB for analysis??

[Micron]

You need to elaborate on what you want to happen, complete with scenario examples.
It doesn't make sense to get the logged in user name, then attempt to lock that. What happens to the next person trying to log in when someone else has it open? What if the next user logs in and no one else is logged in? In one case, you need to append, in the other, replace, as noted by ssanfu. And did you consider what needs to happen if a user forces an abnormal shutdown (as in using Task Manager to kill Access), in which case they probably remain recorded as being logged in?

Also, Missinglinq is correct on the oddities of the DLast (and First, Last, DFirst) functions but in some cases it would work as one would expect. AFAIK, the record returned is not really random, it is supposed to be (when using Last or DLast) the last record entered into the table. That certainly would appear to be random when looking at a table recordset, especially if it is not sorted.

https://support.microsoft.com/en-us/kb/208190

The First(), Last(), DFirst(), and DLast() functions ignore sort orders, indexes, and primary keys. These functions return the first or last undeleted record based on the order in which the records were entered into the table, not the first or last record in a specified sort order.

That last record entered is not always going to appear at the end of the table rows.

So if you want to know
- who is logged in
- who is logged in and who logged in last
- only who logged in last
these require different approaches.

A straightforward answer to getting the last value* from a table field is to use a sorted query and use the Top 1 predicate.
*not to be confused with the max value

I'm curious; is this db split?

[raveen]

You need to elaborate on what you want to happen, complete with scenario examples.
It doesn't make sense to get the logged in user name, then attempt to lock that. What happens to the next person trying to log in when someone else has it open? What if the next user logs in and no one else is logged in? In one case, you need to append, in the other, replace, as noted by ssanfu. And did you consider what needs to happen if a user forces an abnormal shutdown (as in using Task Manager to kill Access), in which case they probably remain recorded as being logged in?

Also, Missinglinq is correct on the oddities of the DLast (and First, Last, DFirst) functions but in some cases it would work as one would expect. AFAIK, the record returned is not really random, it is supposed to be (when using Last or DLast) the last record entered into the table. That certainly would appear to be random when looking at a table recordset, especially if it is not sorted.
That last record entered is not always going to appear at the end of the table rows.

So if you want to know
- who is logged in
- who is logged in and who logged in last
- only who logged in last
these require different approaches.

A straightforward answer to getting the last value* from a table field is to use a sorted query and use the Top 1 predicate.
*not to be confused with the max value

I'm curious; is this db split?

Thanks for the response. No this db is not split.

ssanf, you are right, I will try your advise to add new records.

I am having another problem; I am using Serial number field , Autonumber property, in a table and updated from a user form. I found the numbers jumped from 14 to 16 and 25 to 27, etc. Is there a way to get it right.
Many thanks

[CJ_London]

see this link about autonumbers - http://www.utteraccess.com/wiki/index.php/Autonumbers

in essence, they should only be used to uniquely identify the record, they should otherwise have no meaning at all.

For your purposes, you need to use what is referred to as 'DMax+1' - see this link for an example http://www.databasedev.co.uk/automat...ent_value.html

or google 'access vba dmax+1' for more examples