By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Trouble linking Forms

P: 44
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.
Feb 23 '09 #1
Share this Question
Share on Google+
72 Replies


NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 23 '09 #2

P: 44
@NeoPa
Thanks for your reply,

Ok what would you like to know?
I can attach the database if it makes it easier.
Feb 23 '09 #3

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 23 '09 #4

P: 44
@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.
Feb 23 '09 #5

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 23 '09 #6

P: 44
  1. Employee ID
  2. Expand|Select|Wrap|Line Numbers
    1. Private Sub Command15_Click()
    2.  
    3. 'Check to see if data is entered into the UserName combo box
    4.  
    5.     If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
    6.       MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    7.         Me.cboEmployee.SetFocus
    8.         Exit Sub
    9.     End If
    10.  
    11.     'Check to see if data is entered into the password box
    12.  
    13.     If IsNull(Me.Text13) Or Me.Text13 = "" Then
    14.       MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    15.         Me.Text13.SetFocus
    16.         Exit Sub
    17.     End If
    18.  
    19.     'Check value of password in tblEmployees to see if this
    20.     'matches value chosen in combo box
    21.  
    22.     If Me.Text13.Value = DLookup("Password", "Employees", _
    23.             "[EmployeeID]=" & Me.cboEmployee.Value) Then
    24.  
    25.         MyEmployeeID = Me.cboEmployee.Value
    26.  
    27.         'Close logon form and open enter hours
    28.  
    29.         DoCmd.Close acForm, "Logon", acSaveNo
    30.         DoCmd.OpenForm "Enter Hours"
    31.  
    32.     Else
    33.       MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    34.             "Invalid Entry!"
    35.         Me.Text13.SetFocus
    36.     End If
    37.  
    38.     'If User Enters incorrect password 3 times database will shutdown
    39.  
    40.     intLogonAttempts = intLogonAttempts + 1
    41.     If intLogonAttempts > 3 Then
    42.       MsgBox "You do not have access to this database.Please contact admin.", _
    43.                vbCritical, "Restricted Access!"
    44.         Application.Quit
    45.     End If
    46.  
    47. End Sub
  3. Fullname.
Thanks
Feb 23 '09 #7

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Enter Hours", , , "[EmployeeID]=" & Me.cboEmployee
Feb 24 '09 #8

P: 44
Thanks for your reply,

The fields in the query ID to Hours are ID,Dateworked,Fullname,Task,Sub Task,Hours and Comments
Feb 24 '09 #9

NeoPa
Expert Mod 15k+
P: 31,418
Ah, that makes sense. In that case I suspect you need for line #30 :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Enter Hours", , , "[ID]=" & Me.cboEmployee
Feb 24 '09 #10

P: 44
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
Feb 24 '09 #11

NeoPa
Expert Mod 15k+
P: 31,418
Do I get to hear what line the error occurred on and what the message is?
Feb 24 '09 #12

P: 44
Sorry,

Line 30 run-time error '2467'
Feb 24 '09 #13

NeoPa
Expert Mod 15k+
P: 31,418
Are you sure there was no message other than a simple number. That's quite unusual.
Feb 24 '09 #14

NeoPa
Expert Mod 15k+
P: 31,418
@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.
Feb 24 '09 #15

P: 44
Yeah thats it im using Access 2000
Feb 24 '09 #16

P: 44
@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
Feb 24 '09 #17

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 24 '09 #18

P: 44
Yeah sorry,

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

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 24 '09 #20

P: 44
I just tried the below. Im not the best at coding so im sure its my mistake
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Enter Hours", , , "[EmployeeID]=" & Me.cboEmployee
I also tried [ID] but this didnt work either.
Feb 24 '09 #21

NeoPa
Expert Mod 15k+
P: 31,418
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?
Feb 24 '09 #22

P: 44
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
Feb 24 '09 #23

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 24 '09 #24

P: 44
Ok thanks Neopa,

I have attached the database
Attached Files
File Type: zip 102.zip (200.6 KB, 60 views)
Feb 24 '09 #25

NeoPa
Expert Mod 15k+
P: 31,418
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).
Feb 24 '09 #26

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

Thank you
Feb 24 '09 #27

NeoPa
Expert Mod 15k+
P: 31,418
My curiosity got the better of me...

As the first line after Private Sub Command15_Click() enter :
Expand|Select|Wrap|Line Numbers
  1. Dim MyEmployeeID As Long
Next, change line #30 again to :
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 24 '09 #28

NeoPa
Expert Mod 15k+
P: 31,418
@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 :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Enter Hours", , , "[StaffPin]=" & MyEmployeeID
Feb 25 '09 #29

P: 44
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
Feb 25 '09 #30

NeoPa
Expert Mod 15k+
P: 31,418
Please check my earlier post (#29).
Feb 25 '09 #31

P: 44
ok,

this is how the first part of the code looks
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2.  
  3. 'Check to see if data is entered into the UserName combo box
  4.  
  5.     Dim MyEmployeeID As Long
  6.     If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  7.       MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  8.         Me.cboEmployee.SetFocus
  9.         Exit Sub
  10.     End If
and this is how line #30 looks
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 25 '09 #32

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 25 '09 #33

P: 44
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.
Feb 25 '09 #34

NeoPa
Expert Mod 15k+
P: 31,418
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).
Feb 25 '09 #35

P: 44
Hows this;
Expand|Select|Wrap|Line Numbers
  1. ID  DateWorked  StaffPin  Fullname      Task     Sub Task         Hours  Comments
  2. 35  10/02/2009      2     Jarry Wijaya  HDT      Report Required    4    test
  3. 37  12/02/2009      4     John Doyle    HDT      Report Required    6    Why is this not working
  4. 39  13/02/2009      1     Craig Dunn    HDT      Bug Report         2    maybe
  5. 47  17/02/2009      1     Craig Dunn    HDT      Bug Report         4    test 2
  6. 48  17/02/2009      1     Craig Dunn    Project  Report Required    2    test 3
  7. 49                  1     Craig Dunn
  8. 50  20/02/2009      1     Craig Dunn    Project  Bug Report         2    test
  9. 53  25/02/2009      2     Jarry Wijaya  HDT      Bug Report         1    test
  10. 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
Feb 25 '09 #36

NeoPa
Expert Mod 15k+
P: 31,418
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).
Feb 26 '09 #37

P: 44
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
Feb 26 '09 #38

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 26 '09 #39

mshmyob
Expert 100+
P: 903
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,
Feb 26 '09 #40

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 26 '09 #41

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 26 '09 #42

P: 44
@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?
Feb 26 '09 #43

NeoPa
Expert Mod 15k+
P: 31,418
@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.
Feb 26 '09 #44

P: 44
@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)
Feb 26 '09 #45

NeoPa
Expert Mod 15k+
P: 31,418
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.
Attached Files
File Type: zip TimesheetRels.Zip (57.7 KB, 64 views)
Feb 26 '09 #46

P: 44
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
Feb 26 '09 #47

mshmyob
Expert 100+
P: 903
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,
Feb 26 '09 #48

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 26 '09 #49

NeoPa
Expert Mod 15k+
P: 31,418
@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.
Feb 26 '09 #50

72 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.