Solved tempvars

[mloucel]

Hello Everyone..
MY APOLOGIES I DON'T KNOW HOW TO POST CODE..

As you remember I am a newbie in this new world of Access, thru advise I've been learning step by step using anything from a book to youtube.
it's been only 2 months..

I am doing a very simple application.
[this is beta working towards final product]
I enter thru a login page then login opens a Patient charts form:
[chart #] [office] and all related fields to the patient. (I Will add a MENU soon after login)

The flow:
On the login I Add thru a combo box the ID of the office to a temporary variable [OfficeID=1] [OfficeName=Office01]
then I need to transfer [SOMEHOW] to the "charts form" or any form: "The OfficeID and the OfficeName.
The IDEA:
I need to display the OfficeName in a label in the Charts Form and when the NEW RECORD is saved save the ID (KEY), but if the user moves to the first record, then display the value saved in the database (is not a new record), but if it hits the NEW button then has to display again [in the label] the name of the tempvar (OfficeName) and move the OfficeID to that new record.

I have no idea how to:
Save the OfficeID and OfficeName and retrieve them in my label called [lblOfficeLocation] if this is a new record or display the saved name if the user moves to an already existing record.

This is part of the coding I have done SIMPLE and easy, any help suggestion will be greatly appreciated.
(FORGOT TO MENTION) I GET THE OFFICE ID AND OFFICE NAME FROM A DIFERENT TABLE, THERE ARE 3 TABLES, LOGIN / CHARTS / OFFICES
in my login form:

Private Sub cmdLogin_Click()
    Dim rs As Recordset
    Dim OfficeID As String
    Dim OfficeName As String

    Set rs = CurrentDb.OpenRecordset("tblLogin", dbOpenSnapshot, dbReadOnly)
    rs.FindFirst "UserName='" & Me.txtUserName & "'"

    Me.txtUserName.SetFocus

    TempVars.Add "OfficeID", cboOffice.Value

    'TempVars.Add "OfficeName", OfficeLocation.Value '[This Does Not work, I rem out]

    If rs.NoMatch = True Then
        Me.lblIncorrectUser.Visible = True
        Me.txtUserName.SetFocus
        Exit Sub

    End If
    Me.lblIncorrectUser.Visible = False

    If rs!Password <> Me.txtPassword Then
        Me.lblIncorrectPassword.Visible = True
        Me.txtPassword.SetFocus
        Exit Sub

    End If

    Me.lblIncorrectPassword.Visible = False
 
    DoCmd.OpenForm "Patient's Charts"

    MsgBox TempVars!OfficeName
    DoCmd.Close acForm, Me.Name

End Sub

in my CHARTS form:

 If Me.NewRecord Then
        Me.lblRecordCounter.Caption = "Input New Chart #"
        Me.lblOfficeLocation.Caption = "New Chart for " & TempVar![OfficeName] '[This TempVar![OfficeName] does not work MUST be = to LOGIN
    Else
        Me.lblRecordCounter.Caption = _
            " Chart # " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
        Me.lblOfficeLocation.Caption = "WHAT DO I PLACE HERE?" 'I need to control this from CHARTS since the user has moved the pointer to a stored value
    End If

 

[arnelgp]

i assume the Last Code you post goes to the Current Event of the Chart form.

 

[mloucel]

i assume the Last Code you post goes to the Current Event of the Chart form.

YES..

 

[arnelgp]

do you have OfficeID on that Form?
if you have you can just Lookup the OfficeName:

 If Me.NewRecord Then
        Me.lblRecordCounter.Caption = "Input New Chart #"
        Me.lblOfficeLocation.Caption = "New Chart for " & TempVar![OfficeName] '[This TempVar![OfficeName] does not work MUST be = to LOGIN
    Else
        Me.lblRecordCounter.Caption = _
            " Chart # " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
        'Me.lblOfficeLocation.Caption = "WHAT DO I PLACE HERE?" 'I need to control this from CHARTS since the user has moved the pointer to a stored value
        Me.lblOfficeLocation.Caption = Dlookup("OfficeName", "tblOffice", "OfficeID = " & Me!OfficeID)
    End If

Or you can create a Query that Joins the Officetable to your sourceTable.
Create new column for the OfficeName, you just add this column to your form (visible=false).
then set the Caption of the label:

Me.lblOfficeLocation.Caption =Me!textboxOfficeName

 

[Galaxiom]

Set rs = CurrentDb.OpenRecordset("tblLogin", dbOpenSnapshot, dbReadOnly) 
rs.FindFirst "UserName='" & Me.txtUserName & "'"

No point returning a recordset with all users in it then looking for the one that is logging in. Put the Username in the query to just get the one record. If there are no results then they were not in the table.

You could do this in one line with a DLookup.

 

[mloucel]

do you have OfficeID on that Form?
if you have you can just Lookup the OfficeName:

 If Me.NewRecord Then
        Me.lblRecordCounter.Caption = "Input New Chart #"
        Me.lblOfficeLocation.Caption = "New Chart for " & TempVar![OfficeName] '[This TempVar![OfficeName] does not work MUST be = to LOGIN
    Else
        Me.lblRecordCounter.Caption = _
            " Chart # " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
        'Me.lblOfficeLocation.Caption = "WHAT DO I PLACE HERE?" 'I need to control this from CHARTS since the user has moved the pointer to a stored value
        Me.lblOfficeLocation.Caption = Dlookup("OfficeName", "tblOffice", "OfficeID = " & Me!OfficeID)
    End If

Or you can create a Query that Joins the Officetable to your sourceTable.
Create new column for the OfficeName, you just add this column to your form (visible=false).
then set the Caption of the label:

Me.lblOfficeLocation.Caption =Me!textboxOfficeName

yes... I actually do I base the form on a query and I actually have both tables..
so yes, that means I have only 1 issue to solve..

I have to FIX what office is being worked during the login, so I have to move not the ID but the OfficeName to a temp Variable.

* how do I call that tempvariable from the charts form or from any form?
I need to have something thru all the session that tells the program "You are working with this office, set on the login page"
or is there a better way?

 

[mloucel]

No point returning a recordset with all users in it then looking for the one that is logging in. Put the Username in the query to just get the one record. If there are no results then they were not in the table.

You could do this in one line with a DLookup.

Sorry I am very new here, I haven't used the dlookup and I have no idea what to do.

 

[mloucel]

do you have OfficeID on that Form?
if you have you can just Lookup the OfficeName:

 If Me.NewRecord Then
        Me.lblRecordCounter.Caption = "Input New Chart #"
        Me.lblOfficeLocation.Caption = "New Chart for " & TempVar![OfficeName] '[This TempVar![OfficeName] does not work MUST be = to LOGIN
    Else
        Me.lblRecordCounter.Caption = _
            " Chart # " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
        'Me.lblOfficeLocation.Caption = "WHAT DO I PLACE HERE?" 'I need to control this from CHARTS since the user has moved the pointer to a stored value
        Me.lblOfficeLocation.Caption = Dlookup("OfficeName", "tblOffice", "OfficeID = " & Me!OfficeID)
    End If

Or you can create a Query that Joins the Officetable to your sourceTable.
Create new column for the OfficeName, you just add this column to your form (visible=false).
then set the Caption of the label:

Me.lblOfficeLocation.Caption =Me!textboxOfficeName

Hello I do not understand the last part
' Or you can create a Query that Joins the Officetable to your sourceTable. '
my Login form retrieves user name and password from a table called tblLogin
the office location is retrieved from the table tblOffices thru a query in a cbo box, I display the Office name in the combo, but the saved variable is the ID not the name, I have no idea how to save the name instead of the numeric ID, that should [I GUESS] solve some problems but I am really confused..
Should I upload my database so you can see it and hopefully suggest a possible fix..?

 

[arnelgp]

Should I upload

much better to fully understand.

 

[mloucel]

much better to fully understand.

OK, I'll try to explain there..

 

[mloucel]

OK, I'll try to explain there..

Ok here it is.. I placed some comments in the code, since this is not complete I am still missing the menu, should start by login then menu, but to test I did login then go to form patient's chart

 

[arnelgp]

i added Office_ID (hidden, Visible property = No) on chart form.
update the code of current event of that form.
added imgNew (image, you can change/remove it if you do not like).
left justify the lblOfficeLocation.

use login form as usual. i have not touch the combo on login form.

 

[mloucel]

i added Office_ID (hidden, Visible property = No) on chart form.
update the code of current event of that form.
added imgNew (image, you can change/remove it if you do not like).
left justify the lblOfficeLocation.

use login form as usual. i have not touch the combo on login form.

Thanks, I need to investigate [nz] but when at the login screen I choose a different office [say bakersfield] in the charts form lblOfficeLocation does not change the name from 1 or FLORENCE remains in 1 [FLORENCE].

 

[arnelgp]

here check the modification on the Code-behind the login form.
the combobox "overide" the Office location from tblLogin.

 

[mloucel]

here check the modification on the Code-behind the login form.
the combobox "overide" the Office location from tblLogin.

thanks again, I as well modified the code, (I'm learning)
your code was left static with tempvars no mater where the pointer was but my issue was:
If the record is new then use the tempvar and save the value onto the database but if the user moves the pointer then use the data from the database.
I'm proud of myself, I did it, here is what I change, of course if you believe there is an easy way, or better please let me know.

    Else
        Me.imgNew.Visible = False
        Me.lblRecordCounter.Caption = _
            " Chart # " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
        'arnelgp
        'Me.lblOfficeLocation.Caption = "Chart for " & DLookup("OfficeLocation", "Offices", "Office_ID = " & [TempVars]![OfficeID].Value) & ""
        'Maurice:
        'This allows me to use the data from the database..
        Me.lblOfficeLocation.Caption = "Chart for " & DLookup("OfficeLocation", "Offices", "Office_ID = " & Office_ID & "")
    End If

 

[mloucel]

@arnelgp
thanks buddy, I have modified the code accordingly and once it is done it will be appreciated if you can check the whole thing and see where can I improve, I would like if you don't mind to submit my app to you then, and please help me out with the details, I'm guessing it is very minimal what I have to do now (save the record, check for duplicates and fields being empty or not (i'm thinking "dirty").
If I am stuck I hope you don't mind me asking for help again.

Maurice.

 

[arnelgp]

I would like if you don't mind to submit my app to you then

pm me and attach your db.
just like a property, "improvements" only (if any).