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]

@NeoPa
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]

@NeoPa
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,Fullname,Task,Sub 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]

@DavidPT
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.

[DavidPT]

Yeah thats it im using Access 2000

[DavidPT]

@NeoPa
um i havent modified the query since adding a logon form which is based of different tables

You sure it isnt easier if i send you the database?

Thanks again for your help

[NeoPa]

So "The expression you entered refers to an object that is closed or doesn't exist." doesn't appear anywhere?

I'll look at this again tomorrow. It's getting late here and I'm not feeling very patient just now.

[DavidPT]

Yeah sorry,

I checked again your right it says "The expression you entered refers to an object that is closed or doesn't exist.

[NeoPa]

It sounds to me like you may have missed out a comma.

If the WhereCondition parameter is put in as the FilterName one, then it may complain in such a way.

Can you post in (exactly - copy / paste it into a post) what you used for line #30 when this error occurred.

[DavidPT]

I just tried the below. Im not the best at coding so im sure its my mistake

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

I also tried [ID] but this didnt work either.

[NeoPa]

This seems perfect, except of course the one I'm interested in seeing is the latest version as per post #10.

I can't imagine it's vastly different. The one shown is perfectly maintained and exactly reflects the form it needs to be in.

That only leaves us with the type of the field to look at. What do you see in the [ID] field when you run the [ID to Hours] query?

[DavidPT]

When i run the ID to Hours query there are numbers in the id field which look like they refer to the number of records recorded.

so in the ID field the numbers will keep on going up as more records are added

[NeoPa]

That was my original expectation David.

Maybe we've come to the time where you compile your database, Compact / Repair it, Zip it up & attach the resultant file to this thread.

That way I can have a look and see what strange things may be hapenning. Hopefully, the one strange thing which hasn't been mentioned or noticed so far will leap out at me.

[DavidPT]

Ok thanks Neopa,

I have attached the database

[NeoPa]

I'm running out of time to do anything with it tonight David, and tomorrow I'm out for the evening, but I'll have a proper look when I get in on Thursday (I can only do work on actual databases at home).

[DavidPT]

No problem any help would be much appreciated when you get time (no hurry).

Thank you

[NeoPa]

My curiosity got the better of me...

As the first line after Private Sub Command15_Click() enter :

Dim MyEmployeeID As Long

Next, change line #30 again to :

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

I missed the significance of your line #29 which closes the Logon form. This means that objects referenced from that form (EG. Me.cboEmployee) are no longer available.

[NeoPa]

@DavidPT
Cancel that last post (well some of it anyway). Your post #9 left off the StaffPin field from the query, which, it turns out, is the relevant field to use. [ID] is an id for a completely separate table.

What was line #30 should read :

DoCmd.OpenForm "Enter Hours", , , "[StaffPin]=" & MyEmployeeID

[DavidPT]

Thanks for your persistants

Ok i think we are getting somewhere. I changed this and it opened the timesheet without any errors. Although it is still allowing me to see what other users have added. Example if i logon as say Craig and enter hours and then logon as Jarry i can still see Craigs hours)


The fullname box is also still blank ( i wanted to try and get it so it showed the persons name that logged in)

Ok just read your last post i have changed line #30 as above post this also seems to work but still the same issue as the example when the timesheet opens

[NeoPa]

Please check my earlier post (#29).

[DavidPT]

ok,

this is how the first part of the code looks

Private Sub Command15_Click()
 
'Check to see if data is entered into the UserName combo box
 
    Dim MyEmployeeID As Long
    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

and this is how line #30 looks

DoCmd.OpenForm "Enter Hours", , , "[StaffPin]=" & MyEmployeeID

Is this correct?

If so it still allows me to see other user information when logging in as above example.

[NeoPa]

The Dim statement should really go at line #2. Because comments are not processed, doesn't mean they are unimportant. This is not you problem of course.

What I would like you to now then, is to get into the situation you're referring to (where an unrelated record is showing as available on your form), and then open the query ([IDto Hours] and NOT [ID to Hours]) to display all the records. Next, copy and paste that information into a post for me to look at.

You will also need to explain, accurately if possible, which user (name and code) has been selected and which we should expect the data to be filtered on.

[DavidPT]

IM not sure if this is what you where after. Below shows the ID to hours query results;


ID DateWorked Fullname Task Sub Task Hours Comments
35 10/02/2009 Jarry Wijaya HDT Report Required 4 test
37 12/02/2009 John Doyle HDT Report Required 6 Why is this not working
39 13/02/2009 Craig Dunn HDT Bug Report 2 maybe
47 17/02/2009 Craig Dunn HDT Bug Report 4 test 2
48 17/02/2009 Craig Dunn Project Report Required 2 test 3
49 Craig Dunn
50 20/02/2009 Craig Dunn Project Bug Report 2 test
53 25/02/2009 Jarry Wijaya HDT Bug Report 1 test
54 26/02/2009 Craig Dunn HDT Bug Report 2 test 1

I also noticed that in the timesheet if i dont add a name to each line the record wont appear on the query

@NeoPa
Im not quite sure what you mean by this, sorry.

[NeoPa]

I struggled with this for ages until I tried selecting all the columns (in datasheet view) and double-clicking on a separator bar (to set the widths automatically). I was after the StaffPin column, which is there, but has a column width of 0, so is neither shown, nor included in a copy operation. This was not something you could have known about and explains why you, quite reasonably, were not able to provide the data I was asking for properly in post #9.

Please could you run it again after setting all the widths to something other than 0 (as explained above).
@NeoPa
I'm really just after knowing which user was selected on your [Login] form (and specifically which StaffPin).

[DavidPT]

Hows this;

ID  DateWorked  StaffPin  Fullname      Task     Sub Task         Hours  Comments
35  10/02/2009      2     Jarry Wijaya  HDT      Report Required    4    test
37  12/02/2009      4     John Doyle    HDT      Report Required    6    Why is this not working
39  13/02/2009      1     Craig Dunn    HDT      Bug Report         2    maybe
47  17/02/2009      1     Craig Dunn    HDT      Bug Report         4    test 2
48  17/02/2009      1     Craig Dunn    Project  Report Required    2    test 3
49                  1     Craig Dunn
50  20/02/2009      1     Craig Dunn    Project  Bug Report         2    test
53  25/02/2009      2     Jarry Wijaya  HDT      Bug Report         1    test
54  26/02/2009      1     Craig Dunn    HDT      Bug Report         2    test 1

Dont know if this will help but these are the 2 tables that have staff data

Employees table;

Employee IDLogin Name Password
1 Craig Dunn *****
2 Jarry Wijaya *****
3 Ryan Briggs ****
4 Lorena Galindo ******
5 John Doyle ****
6 Danny Jin *****
7 Jasjit Multani ******
8 David Weir *****
9 August Dieckhaus ******

StaffData table;

StaffPIN Fullname
1 Craig Dunn
2 Jarry Wijaya
3 Ryan Briggs
4 John Doyle
5 Lorena Galindo
6 Danny Jin
7 August Dieckhaus
8 Jasjit Multani
9 David Weir
10

[NeoPa]

That's good data, but I also need to know :

1. Which user was selected.
2. Which records were displayed on your form (Please refer to the records by the ID value).

[DavidPT]

It doesnt seem to matter who i login as i have tried logging on with a few different people it always seems to open up the timesheet with what ever previous record was added. So for example i have logged on as Craig and then i enter his hours in. I then close the timesheet form and log back into the timesheet form with Jarry's details it will take me back to the timesheet but show me the values that had been entered in for Craig.

Hope this helps,

@NeoPa
No records are being displayed on my timesheet form unless i physically add them using the combo boxes

[NeoPa]

I suspect this is an issue quite unrelated to the question then David. That's not a criticism, you weren't to know.

I will need to look again at your database (only when I'm at home) to see if I can see where the actual problem lies.

If the version I currently have displays the same behaviour I should already have what I need. If you have made any changes however, I suggest you attach the latest copy for me to find available when I get home (quite late) this evening.

I'm convinced the actual question (this area we've worked on already) is properly resolved and works logically.

[mshmyob]

My 2 cents for what they are worth. I haven't taken the time to really read each response in this thread in deatail but I did look at your database.

I would say that before any more time is spent, that the structure be redesigned from scratch and it will probably then work.

You will notice that your main table [Hours Worked] is linked to numerous other tables but not via PK's and FK's but via standard fields such as descriptions, etc. You only link tables via PK's which will become FK's in other tables not by description fields etc. None of your queries that are looking to related records via ID's will work.

I would guess that your problems are because of poor design practices. Also don't put spaces in table names or field names.

Also why is [Task Description] a MEMO field?


Read up on normalization.

Hope that makes sense. Views in this post don't necessarily represent the views of the BYTES community. :)

cheers,

[NeoPa]

I have to say that I didn't dig that far in as I was focused on the current issue. Check out Normalisation and Table structures.

David, you never told me if this was a database you'd created or one that you'd inherited.

[NeoPa]

I've just had another look at the database and the Relationships window in particular.

Tasks & [Sub Tasks] are both incorrectly linked to the description field rather than the code.

It could simply be a case of missing the correct field when creating the link. If so, such a lack of attention to detail doesn't bode well for developing databases.

However it came about, it should be fixed.

[DavidPT]

@NeoPa
The only modifications i have done to the database since i attached it is the VB code corrections which you have advised in the above post.

The database is inherited but i have done quite a few modifications to it from the original.

@NeoPa
So you are saying to change the description field to Text in these tables?

Just re read what you said about the relationship window. How would you suggest i link these?

[NeoPa]

@DavidPT
No. But do anyway. I hadn't noticed that.
@DavidPT
I was saying link them (define the link) on the ID field and not the name/description fields.

[DavidPT]

@NeoPa
Sorry for my ignorance but this would be changing the relationship so it is now Task ID to ID (Hours Worked table) and for subtask table Project ID to ID (Hours Worked table)

[NeoPa]

I'd very much doubt that. I'm only guessing you understand, but I doubt the change would be needed at the [Hours Worked] end.

Have a look at this and see if you can match it. I'm afraid referential integrity rules are not supported as there is simply too much wrong in too many places. Fields should match if they're to be related. The data (which can be removed I guess) is also blocking the correct settings of some of the relationships.

[DavidPT]

Ok i have changed the relationships so they look like you setup in the screenshot.

I have also changed the description for task and subtask tables to Text

Would you recommend changing the order of ID's in both my staff tables so they match? ie..in staff data table id for Lorena is 5 but in employees table Lorena's ID is 4

[mshmyob]

I will butt in again if you don't mind Neo.

David, Neo just gave a quick idea to you to think about. Your design needs to be redone.

For instance you have a table for login and then another for a fullname for the employee. Why not combine the two into a single table so that tblEmployee table has EmployeeID, EmployeeFName, EmployeeLName, EmployeeLogin, EmployeePassword. Notice the name is split into First and Last not as a FullName as you have (atomization is a key part of normalization).

2nd I don't believe you actually need a table for Hours. The hours worked on a task would just be in the main table.

3rd - I would assume Tasks and SubTasks are related to each other and not independently to the main table. If you could give examples and explain the tasks and subtasks I can be more sure.

Therefore you may need only 4 tables for this application.

cheers,

[NeoPa]

Please refer to the link in post #41. This should never occur. You should hold the data for the staff in one place only. It seems to me that the [StaffData] table is wholly redundant.

[NeoPa]

@mshmyob
Please help yourself. I never was a strong swimmer, and this treacle stuff really gets to you after a while ;D

I would comment that my guess as to why there is an Hours table (rather than just entering the value) is that the designer wanted a ComboBox to select from. There's no real need for an hours key though, separate from the hours value.