Question Could someone check this for me please?

[shabbaranks]

Ive attached my database, could someone just check it for me please and possibly give me some guidence? Ive set up an initial relationship and was wondering if I am going about it the right way. As the only form inputs to a single table, am I correct in thinking I need to link the TimeSheetTable table to the UserNames table?

Thanks peeps

 

[NigelShaw]

Hi,

couple of things

1.

environ("username")

) will replace your username function

2. i.ll have a look at your tables etc. i would have thought a one to many between the timesheet & users as one user can have many timesheets

3.The code you have can be shortened down somewhat.

i'll get back to you


Nidge

 

[shabbaranks]

Thanks Nigel, Im just having a look at the environ method - and cant get it to work. I guess I add it as code within VBA?

 

[NigelShaw]

Hi

yes it needs to be in a routine

public Function EnUsername()
 
EnUsername = Environ("username")
 
End function

to use, call

Call EnUsername

or you can call it directly inside a routine

Sub GetUsername()
dim strUser As String
strUser = Environ("username")
Me.MyTxtfield = strUser
End Sub

hth

Nidge

 

[shabbaranks]

Just noticed your sig - not that we're arguing but your dealing with an Access idiot

I cant get the flipping thing working... I added both examples (on separate occasions) to see if they would work and they didnt. But I didnt get an error either

 

[NigelShaw]

Just noticed your sig - not that we're arguing but your dealing with an Access idiot

I cant get the flipping thing working... I added both examples (on separate occasions) to see if they would work and they didnt. But I didnt get an error either

No probs,

upload your latest db and i'll take a look


N

 

[shabbaranks]

Thanks, Ive changed it back to the old method for now as I couldn't get it to work. Just out of curiosity - am I coming across clear as what I am trying to do? If I am, and you have 5 mins could you look at my tables and relationships just to make sure Im going about it correctly - legend

Thanks
Shabba!!

 

[NigelShaw]

Yeah i'll have a look.

how are you populating the loggedUser currently as i cannot see it. I have my function working to populate the loggedUser field when the form opens. is that what you want?


N

 

[shabbaranks]

Oops sorry, I just checked I didnt add it back. Im using the code below

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

 

[NigelShaw]

Oops sorry, I just checked I didnt add it back. Im using the code below

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

Hi

yes but in order for this to work and provide the user name, you have to call fOSUsername and i cant see where you are doing that. Here is what i have changed so far-

Module
deleted fOSUsername Function and replaced it with

Public Function EnUsername() As String
EnUsername = Environ("username")
End Function

added this to the OnLoad event for the form-

Private Sub Form_Load()
Me.LoggedInUser = EnUsername
ClearField
End Sub

So, this now populated the loggedUser field on the form.

Here is a great tip. try to use naming conventions. In your db, you have a table called UserNames. If at a later date you made a query from this table to filter out data, you might inadvertently also name this UserNames and then Access has to try and figure out which to look at. here is a general idea

lst - Listbox
txt - Textbox
cbo- Combobox
lbl - Label
chk - Checkbox
img - Image
frm - Form
qry - Query
tbl - Table
mod - Module
cls - Class
fld - Field

it really helps to do this for so many reasons.

cheers

N

 

[shabbaranks]

And bingo was his name-o.. Worked a treat, and Im not too sure if you noticed I did start appending the names eg table at the end - must have forgotten to do the users etc

Hows she looking in general? Does it make sense what I am trying to achieve?
Thanks again

 

[NigelShaw]

Hi,

i will look at your tables later. Without looking in depth, i think you need a table relatation (one to many) between timesheet table and users. Each user can have many timesheets. You could do a simple query to collate the user info and execute from there. The Relationship between the TimeSheet & User table will automatically update the userID in the Timesheet table. A query can then easily filter the timesheets to display only the sheets related to the current user.

I'll look at it when i get home and try to make a sample for you.

i made a timesheet form recently based on Peter Hibbs FlexGrid module but mine is with a different grid. Peters is a great example though.



Thanks


Nidge

 

[shabbaranks]

Alright Nidge...

If I try and relate the user table with the timesheet table it complains stating that no unique index found.. Does that mean the usernames table needs another column with an auto number? Or have I misunderstood?
Thanks

 

[NigelShaw]

Hi Shab

Theoretically, all tables should have unique indexes otherwise for example, what would happen if you had 2 Mike Smiths? By using the unique id or index, names and duplicates are irrelevant.

I'm just firing my pc up now so I'll take a good look and set it up for you



Cheers

Nidge.

Btw are you in the uk?

 

[shabbaranks]

My unique number is the UserID which is an auto number - that should work? BUt its still complaining

 

[NigelShaw]

My unique number is the UserID which is an auto number - that should work? BUt its still complaining

Where are you getting the error or complaint because i have added the timesheet table to the relationship window and created a one to many relationship. Is the complaint in the creation of the relationship or is it when you execute your sql code in the vba routine?


Nidge

 

[shabbaranks]

Im guessing you didnt enforce Integraty, as this link isnt the same? Ive done it now - I think

 

[NigelShaw]

Im guessing you didnt enforce Integraty, as this link isnt the same? Ive done it now - I think

No No, referential Integrity was enforced. The only thin i changed was the ID names from ID to ProjID, UserID, TimeID etc.

cheers

Nidge

 

[shabbaranks]

Is there any reason why you changed those names? Ive got the relationship working, but if I try and turn on integrity I get the error. Have you changed the names in the timesheettable?

 

[NigelShaw]

This works fine for me

Nidge