Display the result of a dropbox on other forms..

[Sniper-BoOyA-]

Good afternoon everyone,

Before i start explaining the problem i'd like to say that these forums have been a great help, and a good resource for information regarding Access and Visual Basic.

Ok back on topic:

I am dutch so there's a chance that the names of tables and records are dutch aswell, but i will translate where needed.

I made a database-program where people from the lab can enter test results and request calculations etc..

In order to get a good view on who worked on what, the have to enter their initials everytime they enter new data into the database.

I do not like that idea and i believe it can be fixed by using a login screen where they can select their initials from a dropdown menu, and the result will be saved into a public string, which i can use in any form on a later stage..

All the initials are stored into a simple table, named tblAfkortingen (tblInitials).

Ive made a simple login form where they can select their initial, and click on 'Login' to login.

Here is the code behind that form:


Option Compare Database

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboafkorting.SetFocus
End Sub

Private Sub cboafkorting_AfterUpdate()

strAfkorting = Me.cboafkorting
End Sub

This is where i wanted to 'save' the result of the drop down menu as StrAfkorting

Private Sub Login_Click()
On Error GoTo Err_Login_Click

If IsNull(Me.cboafkorting) Or Me.cboafkorting = "" Then
MsgBox "Selecteer uw afkorting a.u.b..", vbOKOnly, "Attentie!"
Exit Sub
End


Else

DoCmd.Close acForm, "frmLogin", acSaveNo
stDocName = "start"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

This part of the code just checks if the user in question has entered her or his initial. If they havent, they get a message that said they need to select one..

Exit_Login_Click:
Exit Sub
Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub
Private Sub Annuleren_Click()
On Error GoTo Err_Annuleren_Click
Dim stDocName As String
stDocName = "afsluitendb"
DoCmd.RunMacro stDocName
Exit_Annuleren_Click:
Exit Sub
Err_Annuleren_Click:
MsgBox Err.Description

If the user clicks on Annuleren (Cancel) it will execute a macro to close the database.

Ive put StrAfkorting in a public Variable in a seperate Module:

Option Compare Database
Option Explicit
Public strAfkorting As String

To test it i made a simple test form with just 2 objects. 1 text object and 1 button to close the form.

I linked the text object to StrAfkorting.

code:

Private Sub Form_Load()
Afkorting = strAfkorting
End Sub

Afkorting (Initial) is the name if the textbox i made on the form.

But unfortunately it still shows as an empty text box.

Ive tried about everything, and i am really getting desperate. Could anyone tell me what im doing wrong? And how to fix this?

I'd like to say thanks in advance!

Greetings,

Michael Schrijver
The Netherlands

 

[JamesMcS]

Have you declared strAfkorting as a public variable?

Ignore me, I can't read

You might have to declare it in a module that's called on form_open

 

[namliam]

Ive made a simple login form where they can select their initial, and click on 'Login' to login.

This isnt very solid is it? have you considered using environ("Username") ?

And a "usual" way to deal with login's is to not close the form, but hide it.
That way you dont need any variables, you just fetch your infomration of the (hidden) login screen.

Greets from Amsterdam

 

[Sniper-BoOyA-]

This isnt very solid is it? have you considered using environ("Username") ?

And a "usual" way to deal with login's is to not close the form, but hide it.
That way you dont need any variables, you just fetch your infomration of the (hidden) login screen.

Greets from Amsterdam

I have actually, but the problem with Environ Username is that it takes the username of the account that is logged into Windows.

And thats not what i want since all the people in the lab login with the same username and pw, because its just one computer and it needs to stay on all the time due to the programs and machines that are on it.

The funny thing is, i saw another topic who had the same concept, allthough it asked users to enter their password aswell, with the help of the Dlookup function it looks for the pw in the table, and gets the Employee ID etc..

Ive tried to do to that way and it worked, the only extra thing is the Dlookup function to see if the passwords are correctly entered..

But, since the original designer of the Database im working on, has alleady used a login popup screen to login to a secured workgroup he made, i think having to enter another password, would be a little bit to much.

Unless theres a way to retrieve the username logged in the workgroup? But i am pretty new when it comes to VBA programming....

I have a feeling that it doesnt put anything in the Variable strAfkorting, is there a way to put the strAfkorting in a messagebox as soon as the person clicks on 'Login'? That way i can find out if it saves the result of the dropdown menu as a variable String.


Anyways, thanks for the 2 quick replies, i am looking forward to the next one.. Untill that i will mess around with it myself, and if i happen (unlikely) to come with a solution, ill let you know.

Greetings,

Michael Schrijver
The Netherlands

 

[namliam]

Private Sub cboafkorting_AfterUpdate()
msgbox "str: " & me.cboafkorting
strAfkorting = Me.cboafkorting
msgbox "str: " & strafkorting
End Sub

 

[Sniper-BoOyA-]

Thanks, ill give it a try.

edit:

It looks like it works just fine..

So im guessing its the Public String that is not working right, or needs to defined differently..

Right now, ive defined it in a seperate module (Module1) as:

Option Compare Database
Option Explicit
Public strAfkorting As String

Is this correct?

 

[namliam]

That should be correct

 

[Sniper-BoOyA-]

I put the following code on a different form (Start) to see if the Public Variable strAfkorting was still working..

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
MsgBox "Welkom " & strAfkorting

And to my suprise, all i see is 'Welkom '

Which means that there is something not right, and that can be either in the module, or in frmlogin (where cboafkorting is put into a String)..

Ive tried about everything, and still nothing. I might just start all over again (new table / new form / new VBA )

Any suggestions how i can display the logged in (database) user on different forms?

It might be better to make a new table with Initials,Username and Password..

Then make a dropdown list which displays the initials and username

And when the user clicks on 'login' button, it checks the password using

Dlookup (password, (tablename), [Initials]=" & Me.cboafkorting.Value)...
strAfkorting = Me.cboEmployee.Value

Pretty much the same VBA code as the one posted by drschlong 7 years ago..

What do you think?

 

[JamesMcS]

Have you called the module that the string declaration is in, on the form's open, before you try and populate the variable?

 

[vbaInet]

Also check that you've saved the module after it was created. It would be nice to have your finished code in code tags

 

[Sniper-BoOyA-]

Have you called the module that the string declaration is in, on the form's open, before you try and populate the variable?

To be honest,

i havent..

Can i do that by using de DoCmd.OpenModule command ?