using a variable from one form as the basis for a query to another

[SnakeEyes909]

Hello all.

I was hoping someone could point me in the right direction. i have a login form where a user inputs a user name and password, and it is verified in the db table.

what i'm not sure how to do is use that username to populate another form with records from another table.

ie i have a "users table" with info about the user (user name, password, real name)

i have another table called "contacts" that has info on contacts that are associated with the User table by the real name field. (they both have this common field where the names match)

how can i populate a second form with the records from the contacts table based on the user table login field?

currently the 2nd form is populated by a strait access query of the contacts table. i would like to filter this down to just contacts of the "login" user.

i hope i made sence.

 

[PHV]

I'd use a query that joins the contacts and users table on the real name field with a criteria for the user name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

To use a form field as a criteria in a query you can write the criteria like:

[forms]![yourFormName]![yourControlName]

I like to build a custom function instead because it makes it easier to trouble shoot

public function getUser() as string
if currentproject.allforms("yourFormName).isloaded then
getUser = NZ(forms("yourFormName").yourControlName,"")
end if
end function

now you can use the function as a criteria

...where [user name] = getUser()

 

[SnakeEyes909]

Thank you all for your help. here is what i did and it is working.

in Form1

Private Sub btnGO_Click()
 Dim sPswd As String
  
  
    If IsNull(Me.txtUserName) = True Or IsNull(Me.txtPassword) = True Then
        MsgBox "Please enter both a userid and password"
        Exit Sub
    End If
  
    'DLOOKUP returns null if no record found therefore give it a default value of ""
    sPswd = Nz(DLookup("PASSWORD", "tblTableName", "LOGON_NAME='" & Me.txtUserName & "'"), "")
  
    If Me.txtPassword <> sPswd Then
        MsgBox "Invalid UserID/Password combination"
        Exit Sub
    End If
  
    'Continue with normal successful login code
        VFname = Me.txtUserName
        DoCmd.Close acForm, "Form1", acSaveNo
        DoCmd.OpenForm "patient list"
        
End Sub

then i created a module with a global variable

Option Compare Database
Public VFname As String

Public Function GetVFname() As String
GetVFname = VFname
End Function

and in my query i used

GetVFname()

as the Criteria for the field LOGON_NAME

this all seems to work. i have Form1 as the first item auto open when this db opens with all options disabled. (i know that the [shift] key bypass is still active, but not worried that someone will use it other than Admin. i now need to be able to let multiple people access the db at one time. from what i have read so far, it is already in multiuser mode, and since i am pulling records by login name (which makes it so no other user can access these records) that i shouldn't have a problem with this. any thoughts?

 

[MajP]

Just to verify:
1) You have a split database
2) The back end resides on the network and holds only your tables
3) Each user has a front end on their local machine which links to the backend tables
4) The front end has all forms, reports, and modules

I not all of the above are true you have problems.

 

[SnakeEyes909]

the db is split, back end is on a network server, front end is on a terminal server, where users will use a secured vpn and login to the network. on their term server desktop is a shortcut to the front end. should i change the short cut to an actual copy of the front end?

 

[MajP]

Yes definitely! Each user should have their own front end. This will be far faster and drastically reduce chances of db corruption.

If you are going to periodically add new bells and whistles to your front end and redistribute the FE, you may want to look at some techniques to ensure that users are maintaining the current front. I think there are some FAQs on using batch files to do this.

I do it very simply (only ~20 users). I keep a field in the backend with the FE version number. I code the front end to verify the number. If the number does not equal the FE number then it pops up a message to go get the new front end.

So with this design no problem with multiusers accessing the db at the same time. If they are hitting the exact same record at the same time they get alerted.

 

[SnakeEyes909]

thanks MajP for all the advice. you have been most helpful.

 

[SnakeEyes909]

MajP,

sorry to bother you again. i have moved a copy of the front end off the server, i run the db and Form1 comes up and i type in the username and password then press the "go" button. i get the following error "c:\servername\foldername\dbname_be.mdb is not a valid path. make sure that the path name is spelled correctly and that you are connected to the server on which the files resides. (i understand the error, but is there a way around it?)

here is the code again from Form1

Private Sub btnGO_Click()
 Dim sPswd As String
  
  
    If IsNull(Me.txtUserName) = True Or IsNull(Me.txtPassword) = True Then
        MsgBox "Please enter both a userid and password"
        Exit Sub
    End If
  
    'DLOOKUP returns null if no record found therefore give it a default value of ""
    sPswd = Nz(DLookup("PASSWORD", "tblNAME", "LOGON_NAME='" & Me.txtUserName & "'"), "")
  
    If Me.txtPassword <> sPswd Then
        MsgBox "Invalid UserID/Password combination"
        Exit Sub
    End If
  
    'Continue with normal successful login code
        VFname = Me.txtUserName
        DoCmd.Close acForm, "Form1", acSaveNo
        DoCmd.OpenForm "patient list"
        
End Sub

when debugging the dlookup line is highlighted as the issue.

sPswd = Nz(DLookup("PASSWORD", "tblTABLENAME", "LOGON_NAME='" & Me.txtUserName & "'"), "")

any advice here?

 

[MajP]

The problems is you have to use UNC. See if that helps and get back if it does not:

http://bytes.com/topic/access/answers/619077-querying-linked-tables-network-drive

I may be mapped S:
and you are T:
\\servername\foldername\dbname_be.mdb
You have to do that in the link table manager manually

 

[SnakeEyes909]

ok i answered my own question....

when i split the db, i used a folder on the original pc, instead of the network path. i resplit the db using the network path and all is working.

 

[SnakeEyes909]

lol.... typing the answer at the same time lol

 

[SnakeEyes909]

Thanks again for all the help

 

[SnakeEyes909]

i have another question. if you don't mind.

is there a way to set the form onclose to log the user out of windows?