Control to get login but store id to table

[June7]

fOSUserName is a custom function that uses the Environ() function?

Me.SCAN_OPERATOR = DLookup("ID", "[EMPLOYEE LIST NEW]", "username field='" & fOSUserName & "'")

or



Me.SCAN_OPERATOR = DLookup("ID", "[EMPLOYEE LIST NEW]", "username field='" & Environ("USERNAME") & "'")

[nmart1230]

When I use your examples I get an error "you can't assign a value to this object. If I leave off the &"'" at the end I get an error "syntax error in string in query expression 'SCAN_OPERATOR='RCDNMART'. I'm confused about what I should do. I am relatively new at coding and vba. Thanks!

[June7]

What is SCAN_OPERATOR - name of a textbox, combobox, or field?

What is the actual name of the username field in table?

Where is this code - in what event? Post the complete procedure code.

The & "'" at the end is necessary because the field is text type and parameters for text type require apostrophe delimiters.

If you want to provide db for analysis, follow instructions at bottom of my post.

[nmart1230]

SCAN OPERATOR is the name of a textbox. The username field on the table, if mean where I want the data store the field name is SCAN OPERATOR. If you mean what table it should be looking up the field in it is called EMPLOYEE LIST NEW. On that table I have a field called UserName and a field called ID. The code is in the on open event. Ok, I understand about the apostrophe for text but the ID field is a number field would that matter? I want it to look at the UserName field(text) and find the corresponding ID(numeric) and insert that into the form. So it's a mix of field types.

[June7]

Is SCAN OPERATOR a text or number field? It should be number type because ID is number, however, even if it were text it should still be able to save the ID.

The code should work in Open event - does for me.

Sorry, all I can do without being able to analyse your code and/or db.

[nmart1230]

It is a number field. Ok, if it works for you what the entire code look like?

[June7]

Correction, code in the Open event is populating UNBOUND control.

Use Load or Current event to populate field. Example:

Sub Form_Load()
...
With Me
If Len(.OpenArgs & "") > 0 Then
strItemNum = Mid$(.OpenArgs, intStart, intPos - intStart)
!StateNum = Left$(.OpenArgs, 5)
!PROJNAME = DLookup("ProjectName", "Projects", "StateNum='" & !StateNum & "'")
!ItemNum = IIf(strItemNum = "", Null, strItemNum)
End If
End With
...
End Sub

[nmart1230]

Here is my database. Have a look and see what I'm doing wrong. Thanks!

VBMS DATABASE_01042014USEMEsafe.zip

[June7]

The various API functions error for me because I am running 64-bit OS. Do they error for you? I had to add the PtrSafe keyword to eliminate the errors.

Why do you have a shutdown warning popup? If this is a multi-user db it should be split design and each user runs their own copy of frontend.

The [SCAN INPUT] form has an odd RecordSource sql. Two fields are created by expressions that require user input. This makes no sense to me.
Expr1: [BATCH HEADER SHEET].[CLAIM NUMBER]
Expr2: [BATCH HEADER SHEET].STATUS
Neither [CLAIM NUMBER] nor STATUS are actual fields in the table. What is the purpose for user input of some values to these popups?

Then [DCS ID] field has an input parameter [ENTER A DCS ID #].

All this means user must respond to 3 popups when the form opens. Popup inputs are poor design, amateurish. Better is to have user input filter criteria into controls on a form and the query references the controls as parameters. Even better than that, IMHO, is VBA to set form Filter and FilterOn properties.

Why did you retain the code from my example that has no relevance to your db?

The code in Load event will change the value in [SCAN OPERATOR] of existing record. Do you really want that to happen?

[nmart1230]

No, they do not error for me. The shutdown popup does not popup unless a checkbox is checked on a form to log everyone out for maintanence. The database is split. I put everything together so I could zip it and upload it for you. Plus, it is completely sanitized. Those popups for claim number and status are required fields for those forms. Again certain fields in the table or deleted for sanitization reasons. No the only popup the user sees is the query parameter box then the form opens for the record the user needs to work on. If they forget those fields then they get the validation popups. I tried using your code your but inserting my information into to see if it would work, idk. What I want to happen is when the user enters the parameter for the query the form opens with the users corresponding ID from the EMPLOYEE LIST table based off the users login then inserted into the control of the form. Then when user clicks SAVE it saves it to the table BATCH HEADER SHEET. That is all I want it to do, that's all I'm asking about.

[June7]

Didn't really answer my last 2 questions but only the last one is important.

Do you want the code to replace [SCAN OPERATOR] value in existing record the form will open to?

What is [RE-SCAN OPERATOR] field for?

What values should I enter into the 3 popups to get past them so I can test code?

[nmart1230]

Oh, sorry I forgot those. First enter 123456789 for claim number then scan for status then test for dcs id. The re-scan operator is if the file is being reworked. As far as, existing record or new record let me tell you this. When the user enters a dcs id for the query it is opening the form with that record but the controls on the form are blank. the user has to enter the page count, change the status, click start and stop checkboxes, and any notes. When the user clicks save it saves to corresponding named fields in the table BATCH HEADER SHEET. So would that be considered existing or new?

[June7]

You have lookups built in table, advise not to http://access.mvps.org/access/lookupfields.htm
tblSecurity is not included with the db so these lookups won't work anyway.

The UserName field in [EMPLOYEE LIST NEW] has number values (also, [user] field is a duplication). Do your users have numbers as their network login? What value is returned by the Environ("USERNAME") function?

People name parts really should be in separate fields.

This test works:

Sub Form_Load()
With Me
'!SCAN_OPERATOR = DLookup("ID", "EMPLOYEE LIST NEW", "UserName='" & Environ("USERNAME") & "'")
![SCAN OPERATOR] = DLookup("ID", "[EMPLOYEE LIST NEW]", "UserName=17")
End With
End Sub

Existing means there is already a record. If these records are incomplete for data entry and you want code to populate field, then fine, run code. Just be aware the code as is will run every time, even if record already has the field populated.

[nmart1230]

Yeah, I saw that it had lookups. I'm on the verge of cleaning up this database. Right now, it works for what management needs and now they want this to work so it's one less thing the user has to do. Yes, the users have numbers in their network login. The values that are return are the actual network login by enivron("username") function. Name parts is another thing that I have to fix. Problems of an inherited database.

[June7]

Okay, try:

![SCAN OPERATOR] = DLookup("ID", "[EMPLOYEE LIST NEW]", "UserName=" & Environ("USERNAME"))