Solved a INSERT query that prints a users Full Name which is stored within the account Table. (1 Viewer)

[mamradzelvy]

Hello,
I Pretty much need to do exactly what's in the title.
I've got a "BElogon" table where i save all my User Information, there's one row for a full name which i would like to save now into a record from an unbound form where i use a simple query like this to populate the records columns:

INSERT INTO dbCash (CashKlient, CashInfo , CashDate1, CashHours, CashPrice, CashUser) VALUES ('" & Me!txtKlient.Value & "','" & Me!txtInfo.Value & "','" & Me!txtDate1.Value & "','" & Me!txtHours.Value & "', '" & Me!txtPrice.Value & "', '" & TempVars("UserName").Value & "');

Now as you can see, i use TempVars to store the currently logged in user's name, so my question is:
A) Would i be able to to save a TempVars which i would pull out of the logon table on the login screen and save 2 TempVars values?
B) Is the query approach easier and or even possible?

TempVars("UserName").Value = Me.txtboxname.Value

This is the code i use to save my TempVars on Login.

 

[Minty]

You can save as many tempvars as you set up. No obvious limit on them to my knowledge.

Your query has two things that I would change.
Using .Value is unnecessary it is the default property of most controls, will save you some typing and shorten your code.
I would switch to using the Me. syntax as it will give you the benefit of Intellisense, using the ! (bang) notation can give you misleading results in certain circumstances.

Is there any reason for not using a bound form?

 

[Gasman]

Using .Value is unnecessary it is the default property of most controls, will save you some typing and shorten your code. .

@Minty
That is what I thought as well. However I was trying to help a member in another forum and not using the .Value (at least for the field) generates the error below?

I then tried

Sub TestTempvars()
Dim rst As DAO.Recordset

Set rst = CurrentDb().OpenRecordset("Select * from Transactions")
Do Until rst.EOF
    TempVars("txtID") = rst!ID.Value
    Debug.Print TempVars("txtID")
    rst.MoveNext
Loop
Set rst = Nothing
End Sub

and that worked. So much so that I noted in the thread

FWIW it seems that when trying to assign the ID Access was *thinking* you were meaning the rst ID object.?
I must apologise as .Value property is the default for controls/recordset fields, and I generally never use it.

Note to self, it is needed when TempVars are used

 

[Minty]

@Gasman - Interesting, I wouldn't refer to a recordset field like that, I always use rst.Fields("FieldName") , just simply because I always have.

 

[Gasman]

@Gasman - Interesting, I wouldn't refer to a recordset field like that, I always use rst.Fields("FieldName") , just simply because I always have.

I would as well, but as the member was using that syntax I did as well. I must admit I have used it in the past, purely for a little less typing, so actually hindered him a little
I just tried that way as well in that little test routine and it failed as well, whereas I was expecting it not to? :-(

 

[Uncle Gizmo]

using the .Value (at least for the field) generates the error below?

As gasman points out there's a problem with temp-vars. There's some information on my website here:-

You cannot Always omit “Value”

TempVars Value Error - Nifty Access

You cannot Always omit “Value” I often see people are advised they do not need to refer to a Control's Value implicitly like this:- Dim strValue As String strValue = cboCurrent.Value This advice is given because "Value" is the default, therefore it is unnecessary to append it, So you could write...

www.niftyaccess.com

And the "Value" property has caused problems elsewhere, which I show here:-

Use "Value" or Not?

Value - Nifty Access

Use "Value" or Not? I've noticed a couple of minor glitches caused by the default of a 'textbox', the value; not being assigned explicitly. It's not a big problem and I don't think it will affect many people, possibly nobody! However I think there's a good blog in it for someone, possibly me...

www.niftyaccess.com

 

[mamradzelvy]

You can save as many tempvars as you set up. No obvious limit on them to my knowledge.

Your query has two things that I would change.
Using .Value is unnecessary it is the default property of most controls, will save you some typing and shorten your code.
I would switch to using the Me. syntax as it will give you the benefit of Intellisense, using the ! (bang) notation can give you misleading results in certain circumstances.

Is there any reason for not using a bound form?

I got too many errors regarding handling objects instead of values so i just try to make sure by using .value
for example right now as i was trying to establish this full name tempvars i attempted using recordset to provide the value and i do not understand how recordsets really work, so my first attempt was TempVars("FullName").Value = rs!logon_full (logon_full being the column name in my logon table which im trying to grab) was unsuccessful, resulting in an error which told me that i can not save objects into tempvars. i put .value at the end of the line of code and now i got the desired effect!
The application i'm putting together is using very simple code as it's mainly meant to teach me access.
For the Me. instead of Me! : i honestly am in a purely trial and error state right now and for the most part Me. resulted in more errors than Me! did, so that's my only reasoning for this.
I always appreciate your input Minty, you've already taught me a lot, so i will try to not use .value in the future on first attempts to learn when and where it's needed.
Thank you for your feedback.

 

[Gasman]

As shown though, you do not need .Value for the TempVar, so confusing as hell?

 

[mamradzelvy]

As shown though, you do not need .Value for the TempVar, so confusing as hell?

Oh yes! I'm new to this still, but as far as i understand, this would be one of those times for a "well, because it's Access" remarks.

 

[The_Doc_Man]

The simplest explanation for needing to use .Value in TempVars must be that the default property of a TempVar isn't .Value so needs to be expressly specified. The default property of a bound control usually IS .Value so can be omitted. We would have to see how MS implemented TempVars to know it, but there is a mechanism in defining class objects that allows you to define which property would count ad "Default." This mechanism IS optional and in such cases, there might well be NO default property.