Finding Record ID of newly inserted record (1 Viewer)

[Gasman]

Really?, I thought it would be GetDomainUsername() ?

 

[JMongi]

Maybe someone more knowledgeable can comment about why the function works when a string variable needs delimiters still.

For reference, here is the function code:

Public Function GetDomainUsername() As String
'Get the users Windows Domain Login username
Static sUser As String
If sUser = "" Then
    sUser = CreateObject("WScript.Network").UserName
End If
GetDomainUsername = sUser
End Function

 

[Gasman]

What would be a normal value, as in the bank I was a user something like 15697845 ?

 

[JMongi]

Me personally, even if the identifier is a number, I would store it as a string variable because you aren't actually USING it as a number (performing calculations, etc.). Obviously, since PK-FK pairs have to have the same type that overrides my personal preference!

In this case the normal value is the windows domain username of the database user i.e. a string.

 

[Pat Hartman]

Maybe someone more knowledgeable can comment about why the function works when a string variable needs delimiters still.

The difference whether or not you are creating a string that gets passed to the query processor. Your query string contains a non-string element and that is the name of the function. The interface with the function defines what kind of variable is being returned.so it doesn't need to be delimited.

So:
sqlWhere = " Where MyField = #" & Format(Forms!yourform!txtMyField, "mm/dd/yyyy") & "#" -- Not only must the date be enclosed in octothorps, it must also be in mdy format OR in an unambiguous format such as yyyy/mm/dd. This is because SQL Server assumes US format for ambiguous dates so 1/2/21 = Jan 2 but in Europe, you would see this date as Feb 1. If you are using a windows date setting of mdy, you do NOT need the format.
However:
sqlWhere = " Where MyField = Forms!yourform!txtMyField" -- will work without any octothorps or formatting provided the form control is bound to a date data type or if unbound, the control specifies a date format which implies it will hold a date.

Most of the time, I use saved querydefs so the second style is what I would use to pass arguments to a query. It is not wrong to build SQL strings in code. It is purely a matter of choice. In older versions of Access, the queyrdef had some technical advantages but in current versions, there is little difference unless your SQL string is embedded in a loop. It could be slower because of the extra overhead of computing an accces plan each time the query executes. For querydefs, the access plan is calculated and stored the first time a query runs.