Trouble linking Forms

[DavidPT]

Hi,

I have setup 2 forms one is a logon page requesting username and password once these are filled in correctly there is a go button and when pressed it opens up a timesheet on another form. I am trying to setup the forms so when the person logs in under there name it will also show there name on the timesheet and only limit them seeing details they have enternet and not allowing them to see details others have entered.

Im guessing there is a VB code needed for this.

Any help would be appreciated.

[NeoPa]

I would say it's mainly about the design of your database rather than VBA coding.

We'd need to know a lot more to be in any position to help though.

[DavidPT]

I would say it's mainly about the design of your database rather than VBA coding.

We'd need to know a lot more to be in any position to help though.

Thanks for your reply,

Ok what would you like to know?
I can attach the database if it makes it easier.

[NeoPa]

Mainly the table structure, and the relationships between them.

Attaching a db isn't (generally) too much use as it means anyone wanting to follow the question must download and investigate it. I always see that as an option of last resort.

[DavidPT]

Mainly the table structure, and the relationships between them.

Attaching a db isn't (generally) too much use as it means anyone wanting to follow the question must download and investigate it. I always see that as an option of last resort.

Ok i hope this makes sense the logon form is named logon the table linking it to the form is named employees it has an ID, login name and password field.
The next form is named Enter hours this also has a subform attached. This form links to a query called id to hours. It also gets information from a few tables named hours,staff data,tasks and subtasks the input goes into a table called hours worked.

Hope this helps.

[NeoPa]

OK David, let's see if we can get a bit more specific information :

1. What is the name of the ID control on your [Logon] form?
2. What code do you already use to open your [Enter Hours] form after the user name and password have been entered?
3. What is the name of the field, in your [ID to Hours] query, which identifies the user?

If you can answer these three questions (1, 2 & 3) then we probably have something to work from.

PS. It's mainly about filtering the [Enter Hours] form based on the ID control of your main form, but the code must fit in with what you currently have.

[DavidPT]

1. Employee ID

2. Private Sub Command15_Click()
   
   'Check to see if data is entered into the UserName combo box
   
       If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
         MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
           Me.cboEmployee.SetFocus
           Exit Sub
       End If
   
       'Check to see if data is entered into the password box
   
       If IsNull(Me.Text13) Or Me.Text13 = "" Then
         MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
           Me.Text13.SetFocus
           Exit Sub
       End If
   
       'Check value of password in tblEmployees to see if this
       'matches value chosen in combo box
   
       If Me.Text13.Value = DLookup("Password", "Employees", _
               "[EmployeeID]=" & Me.cboEmployee.Value) Then
   
           MyEmployeeID = Me.cboEmployee.Value
   
           'Close logon form and open enter hours
   
           DoCmd.Close acForm, "Logon", acSaveNo
           DoCmd.OpenForm "Enter Hours"
           
       Else
         MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
               "Invalid Entry!"
           Me.Text13.SetFocus
       End If
   
       'If User Enters incorrect password 3 times database will shutdown
   
       intLogonAttempts = intLogonAttempts + 1
       If intLogonAttempts > 3 Then
         MsgBox "You do not have access to this database.Please contact admin.", _
                  vbCritical, "Restricted Access!"
           Application.Quit
       End If
   
   End Sub

3. Fullname.

Thanks

[NeoPa]

We're making some progress.

Your code tells me that the answer to Q1 is actually cboEmployee.

Unfortunately I'd be very surprised if the answer to Q3 is actually Fullname. Not entirely impossible, but would indicate someone had very little idea of what they were doing. The code I see doesn't give me that impression. I'm assuming you inherited this database from somebody?

Can you list (accurately) the names of the fields returned by the query [ID to Hours] for me please. I'm expecting to see one something like EmployeeID.

The code on line #30 needs to be changed anyway, to something like :

DoCmd.OpenForm "Enter Hours", , , "[EmployeeID]=" & Me.cboEmployee

[DavidPT]

Thanks for your reply,

The fields in the query ID to Hours are ID,Dateworked,F ullname,Task,Su b Task,Hours and Comments

[NeoPa]

Ah, that makes sense. In that case I suspect you need for line #30 :

DoCmd.OpenForm "Enter Hours", , , "[ID]=" & Me.cboEmployee

[DavidPT]

Just tried this came up with a debug error.

Would i need to link my employees table to my staff data table? as the field in my enter hours form for staff is in the table staff data

[NeoPa]

Do I get to hear what line the error occurred on and what the message is?

[DavidPT]

Sorry,

Line 30 run-time error '2467'

[NeoPa]

Are you sure there was no message other than a simple number. That's quite unusual.

[NeoPa]

Would i need to link my employees table to my staff data table? as the field in my enter hours form for staff is in the table staff data

I'm assuming that whatever was required here has already been done within the query. If not then we're in serious trouble. I'm confident that it has though.