New combo box entry to add record in table

[jamierbooth]

Hi all, I'm a bit stuck...

On a form to enter some new client info, I want users to be able to select their UserID from a combo box for future reference to other users (ie; Who made these notes?). For various reasons I want them to be able to add their UserID to the combo list for future selection if its not already on there.

There is a table set up for users, simply called tblUsers, with a single field, UserID. A query from this sorts the list alphabetically, and the combo uses this query to populate its list - qryUserID.

Users can currently select from a list or write their own UserID in the box, however when they write their own ID it doesn't get saved.

I've googled all morning and have found various explanations of how to fix this, ie using "if not in list" and some code, but haven't yet got one to work. It seems to me this ought to be simple but I still can't work it out!!:banghead: (I say that, but I bet its not simple at all.....)

I don't need any message boxes or checking, just add it and move on kind of thing. It doesn't need to refresh the list immediately, as the user moves on swiftly once completing 2 more fields.

Any help with this would be splendid.
Jamie.

 

[Mihail]

simply called tblUsers, with a single field, UserID

From your explanation

A query from this sorts the list alphabetically

I understand that this field contain names.
This is not OK.
Your tblUsers should look like this:

tblUsers
ID_User - AutoNumber (PK)
UserName - Text

 

[jamierbooth]

Not names, it contains a unique userID which is unique to each user and not repeated by anyone else. I have also made this the Primary Key for this table since the reference will be unique.

I can add an auto-number column if this would help, but what would I then need to do to let the combo box add new UserID's to the list?

Cheers, Jamie.

 

[Mihail]

Show us your DB structure (Tables, fields in tables, relationships etc)
Really I can't understand what is with your combo.
Why yo not design a form based on that table ? How you intend to use the combo ? Where you wish to add (store) the new IDs ?
A lot of questions.
The first answer is to use, indeed, to use the NotInList event. Take a look to this thread and see if will help you. But I have huge doubts.

 

[David R]

Are you working in a Windows environment? Look up the custom function fOSusername(), you may not need a combo at all.

 

[jamierbooth]

Are you working in a Windows environment? Look up the custom function fOSusername(), you may not need a combo at all.

I'm using MS access 2000. This DB is a simple "Log customer interactions" database, which staff are using until I have created a bigger, more involved client management DB. The newer DB will hopefully (depending on my ability!) have a username login, that should do this. I just haven't investigated/learned that yet.

In the meantime, staff have already started using the more simple DB, and I did not have time to build a login into it. Hence me trying to just have them add their userID to each interaction manually, in case we need to track down who said what to which customer.

MIHAIL - bit more info...

I have 3 tables- Clients, Interaction, Users.
Clients table is related to Interactions table, one to many.
Users table is related only to the UserID combo on the New Interactions form. Combo rowsource is the tblUsers, UserID and is currently not 'limited to list.'
I want to avoid another seperate form to add a new user if one is not on the list as simplicity and speed is key to this DB.

As mentioned in my first post, I just want the user to be able to look for their UserID in the combo list, and if its not there enter their ID which will be inserted onto the list for next time.

 

[David R]

What else will you be using login for? Security/rights control?

If not, seriously, google that function. It will insert the name they login to Windows wherever you want it, and they don't have to type a thing as long as you code it correctly. It also means they can't 'spoof' and login as someone else.

Even if you do want to tie it to user rights later, having that function will error-proof a lot of your later efforts.

 

[Mihail]

Much better. Not enough but better.
I'll assume that Users interact with Clients in table Interaction.
So, your tables structures and relationships should be:

tblUsers
ID_User - AutoNumber (PK)
Other fields with information about a user

tblClients
ID_Client - AutoNumber (PK)
Other fields with information about a client

tblInteractions
ID_Interaction - AutoNumber (PK)
ID_User - Number (FK on tblUsers)
ID_Client - Number (FK on tblClients)
Other fields with information about a interaction

 

[jamierbooth]

What else will you be using login for? Security/rights control?

OK... So we all use Windows 7 on our works computer network, and the UserID I refer to in this DB is actually the same UserID we all log onto when we switch on each day.

If what I'm reading is correct, this function is basically going to (with some work) automatically put our OS login user ID into a field, rather than the user having to do it with a combo?

If so, that is precisely what I need. I will investigate further!!

Cheers!

 

[David R]

That's exactly what it does. It's astonishingly useful (there's some built-in stuff in Access called DB.CurrentUser, but it doesn't always work on all OSes/security settings. fOSusername() goes down to a low-level API and digs up the login every time).

 

[jamierbooth]

IT WORKS!!! Thats much better than my Combo box solution which has already been replaced!

Thanks very much.

 

[David R]

Excellent. When you get to the point of managing rights you may find DLookup() useful for querying your usertable.

For instance one of my forms asks:

Private Sub CaseStatus_BeforeUpdate(Cancel As Integer)
Dim uname As String
Dim rank As String
    If Me.CaseStatus.Value = 2 Or Me.CaseStatus.Value = 3 Or Me.CaseStatus.Value = 4 Then 'attempting move to 'street' status
        uname = fOSUserName()
        rank = DLookup("InspectorStatus", "tableLookupInspectors", "[InspectorKIVA] = '" & uname & "'")
        
        If rank <> "M" And rank <> "T" And rank <> "S" Then 'only managers or admin staff are allowed to change this value
            MsgBox "Only a manager or supervisor can assign a case to this status.", vbOKOnly + vbCritical, "Unauthorized choice"
            Cancel = True
            Exit Sub
        End If
    End If
End Sub