Database/Access Question

[at05gt]

I'm building a small access database at work to help me inventory the office computers, on a form I'm building I want to simplify something by copying the data from one text box into the field of another.

Example:

Box 1 Input box for user name its first initial/last name:

[jsmith]

Box 2 is the email address using the same format but first initial/last name/company email:

[jsmith@companyname.com]

What I want to do is input the username in box 1 and have it auto populate to box 2 with the @companyname.com appended on the end. So input [jsmith] into box 1 and box 2 would display [jsmith@companyname.com], is this possible?

[Legevia]

I am not sure if this is entirely possible in Access. It probably is and I am not seeing it but basically what you need to do is to tell the second field to do a look up on the first field. I know you can do this quite easily in Excel so I am sure it is possible i just don't have time to mess with it. To try to help you out though in Excel if you were to do this column 1A would be jsmith in column 1B you would use =A1&"@companyname.com" this would give you the result of jsmith@companyname.com which then you could use that formula to fill down for as many rows as you have. Now there is the possibility that you generate the list in Access export it to excel run this formula and then upload the result back into Access.

[Spiralphoenix]

edited: - Nevermind

[at05gt]

I know I can do it within a Query in Access, what I'm trying to do is embed it in the input form if that's even possible. I know I can do it with SQL but I'm using Access for convenience.

[Deleted]

You can do this quite easily using VBA.

For this example I'll assume the first textbox is called txtUsername and the second is called txtEmail. (Just substitute in whatever you have called them).

Select txtUsername, and in the Property Sheet, go into the Events tab and find After Update. Click the 3 dots on the right hand side and go into the code builder.

All you need to do then, is enter the following: Me.txtEmail.Value = Me.txtUsername.Value & "ATcompanyname.com" (the forum thinks I'm posting a link, so substitute AT for the at symbol!)


When you switch from the username textfield to another control (i.e. by clicking another field or tabbing), the email will automatically update. You could also bind this code to a button if you preferred by using the button's On Click event instead.

[at05gt]

You can do this quite easily using VBA.

For this example I'll assume the first textbox is called txtUsername and the second is called txtEmail. (Just substitute in whatever you have called them).

Select txtUsername, and in the Property Sheet, go into the Events tab and find After Update. Click the 3 dots on the right hand side and go into the code builder.

All you need to do then, is enter the following: Me.txtEmail.Value = Me.txtUsername.Value & "ATcompanyname.com" (the forum thinks I'm posting a link, so substitute AT for the at symbol!)


When you switch from the username textfield to another control (i.e. by clicking another field or tabbing), the email will automatically update. You could also bind this code to a button if you preferred by using the button's On Click event instead.

Desticle you rule, this worked perfectly, I was trying to put the code in the second box instead of the first and it was never auto populating.

---------- Post added 2013-02-13 at 09:20 AM ----------

Next question same form, I want to list another value from another table based on a value in an existing box.

So the form I have is based off my User table, I have that linked to my Workstations table via UserID and Workstation ID tags in the tables to a linking table that contains FK's to both tables, I wanna display the WorkstationID on the user form based on the UserID input box. So I input the UserID and the WorkstationID auto populates its value based on the lookup from the linking table. So if jsmith uses WKS01, when I input jsmith into the form it will populate WKS01 into the appopriate box.

[Deleted]

So the form I have is based off my User table, I have that linked to my Workstations table via UserID and Workstation ID tags in the tables to a linking table that contains FK's to both tables, I wanna display the WorkstationID on the user form based on the UserID input box. So I input the UserID and the WorkstationID auto populates its value based on the lookup from the linking table. So if jsmith uses WKS01, when I input jsmith into the form it will populate WKS01 into the appopriate box.

Just to clarify, you have a Users table, a Workstations table, and an additional table that would contain a reference to a user and a workstation? Can users use more than one workstation? (If it is that they only use one, you are better off using just two tables, and having a FK in the users table that references the Workstations table.) Also, is all of the user data already in the database? I am a little confused as to how this would work, as if a new user is being added, there will not yet be a reference in the junction table for the database to know what workstation to pull up.

Working with your current structure, I will assume that there can be more than one workstation per user. As such, this is how I would implement it:

Create a new form (this will be a subform on your users form). Set the record source (data tab) of this form as your junction table, and the default view to datasheet (format tab). Add a combobox and set the control source (data tab) to your workstation field. The row source type should be table/query. For the row source, click the 3 dots and select the workstation table. Drag the workstation field down first, followed by the ID. Close the query designer and save when prompted. Set the bound column to 2, and make sure the column count is set to 1 (format tab). Save and close the form.

Go back to your users form, and drag the new form from the list of Access Objects on the left hand side onto the form. This will add it as a subform. Click the newly added subform once, and go to the data tab on the property sheet. Check to see if the Link Master Fields property refers to the ID field for your users form, and that the Link Child Fields refers to the User FK in the junction table. If you have set up the relationships correctly this should be done automatically - if not you can manually add them by clicking the 3 dots.

This has linked the sub form to the main form. For each user record it will pull up a list of associated work stations which you can amend. If I understand correctly, this should do what you want it to.

[at05gt]

Just to clarify, you have a Users table, a Workstations table, and an additional table that would contain a reference to a user and a workstation? Can users use more than one workstation? Also, is all of the user data already in the database? I am a little confused as to how this would work, as if a new user is being added, there will not yet be a reference in the junction table for the database to know what workstation to pull up.

Yes, yes, and yes. I have a list of workstation names based on department-workstation type (ws, nb mac etc)-name of the ws based on employee position. So for example my IT netbook that I use when I need to be mobile is named IT-NB-Mobile, someone in sales WS would be Sale-WS-SaleAdm. So yes all the data is pre-entered into the linking table. Really I think I'm making more work for myself, I honestly only need 2 input forms, Employee Info, and Workstation Info, and I can cross link from there and do any auto populating I need in queries and reports.