Need help

[tmcrouse]

I am having a time here. I have been to tons of help sites, pulled out all my Access and VB books and I can't seem to get any real help here. My boss needs me to do this form and I have not done forms with VB or any of that for 4yrs. Can't make her understand this is no immediate thing for me. You don't use it you lose it. I have tried looking at this dlookup and all sorts of things but can't seem to find the right thing to do. I attached the form. The userID is a listbox and when the userID is selected or typed in they want the other fields to populate that are textboxes. As I said I have researched and can't find an answer. Too much information. The skill items are drop down because once it prefills the information the user will have to choose what skill they are taking and then click submit or if they mess up clear and start over. I programmed the clear or reset or whatever you want to call it. That is an easy code. The submit to save it to the table that is already set up but just has no values is another issue I am having problems with. My ADO connection doesn't seem to be correct yet it will write to the table but overwrite the entry everytime. So if I manaully put in information in the fields that are to prefill it writes to the table but I get a debug error and it will overwrite whatever is there everytime. I had insert into and a sql query but that doesn't seem to be doing anything but overwriting. If anyone can help I would so appreciate it. The Internet is just not providing the assistance I need. People say do a dlookup but neglect to tell me where that goes. In the user_id list box and I have to put all the fields in there but then how do I tell where to put the information? Do I code it in the user_last_name for user_last_name and have it look at the user_id box to see what is there and if so how do I code that in, dlookup? And if dlookup where does that go exactly. They do not want drop downs or list boxes or combo boxes for the majority. They want all text boxes because they said too confusing for the users. Thank you if anyone can help with this. If not I am just going to have to tell my boss it is your project and unless you are taking over mine I can't help you. I don't have time.

[pbaldy]

Typically you would not save the related information, just the ID. In that instance you'd include the other fields in the row source of the combo and use a textbox with a control source of:

=ComboName.Column(x)

where x is the column containing the desired info (zero based). If you really need to save the related data, in the after update event of the combo you'd have code like:

Me.LastName = Me.ComboName.Column(x)

Hard to say why you're overwriting the record instead of adding a new one without seeing the code.

[jzwp11]

I see Paul got his answer in ahead of mine, sorry for the redundancy

I would switch the user list box to a combo box and bring in all of the other information (necessary to fill the textboxes) about the user from the table that holds the user information. I assume that your user table looks something like this:

tblUsers
-UserID
-userfirstname
-userlastname
etc.

To populate the textbox controls on the form with the user information, set the control source of the textbox to the following

=comboboxname.column(x)

where x corresponds to the column in the combo box's row source. Remember that Access starts counting at zero not 1.

You'll have to repeat for each textbox control, adjusting the x for each

As to the skills, I assume that a user can have many skills, if that is the case, then the skills related to a user must be in a separate, but related table. Then you would use a subform for the entry of the skills pertinent to each user.

If you have a set of predetermined skills and levels which I assume is the case since you are using a dropdown boxes, you need tables for each of those (unless you are using a list box and you specify them there--cannot tell from you attachment)

So in terms of structure, this is what I would have:

tblUsers
-pkUserID primary key, autonumber
-UserID
-userfirstname
-userlastname
etc.

tblSkills
-pkSkillID primary key, autonumber
-txtSkill

tblLevels
-pkLevelID primary key, autonumber
-txtLevel

tblUserSkills
-pkUserSkillID primary key, autonumber
-fkUserID foreign key to tblUsers
-fkSkillID foreign key to tblSkills
-fkLevelID foreign key to tblLevels

[tmcrouse]

There is no code yet. This is a brand new database with brand new form. The given details are the users information as well as a skills table. Those are linked already and create a tri_adv table which is where the records write too. The purpose of this database is when the users use the form their information will populate based on their userid. Then they need to go in and use the dropdown or combox box for the skill they accomplished as well as the level they accomplished. They will then click add record and it writes it to that tri_adv table which at present is blank. This is to keep them from doing too much data entry work. I am creating all the code from scratch except for what auto gets put in like when I did add record it auto did that stuff for me and does write just fine to the tri_adv table cuz I have tested it. Then refresh clears all fields. Then exit will exit my form. The only code I have to figure out is with the userID being a combobox now and if I type in say my last name it pops up but I want the other items to prepopulate based on my userID being put in. That is the problem I am having. I have tried putting in a row source to say:

select tri_adv.[user last name] from tri_adv where ((([tri_adv.[user id]) = [user id]));

That didn't work. I was hoping it would point to what my input in the top field was but it doesn't. I am not sure how to make everything go back and look at this userid was entered to now prepopulate this from the table or query. I have both a table and query and have tried both options.

[tmcrouse]

changed the userID to a combobox. Right clicked to change. The control source says user id. nothing in input mask. row source type says table/query

then the row source has select user id, user last name, user first name, machine name, manager last name, manager first name, office, current grade level from tri_adv order by user id;

The rest are text boxes. with the =comboboxname.column(2) and so on for each one.

when i go back to form view in all text boxes it says #name?

[tmcrouse]

I am using Access 2000. My company will not upgrade us so that might also be an issue not sure.

[jzwp11]

You have to substitute the actual name of you userID combobox where I have "comboboxname".
=comboboxname.column(2)

You'll need to click on the combo box and pull up the properties and look for the name property to see what it is called.

Based on your row source column 2 holds the user's first name. You'll have to adjust the column number for each text box.

[tmcrouse]

so are you saying I create my combobox and then put my query in there that has all fields chosen so my name is UserID and controlsource is UserID and then the row source is select tri_adv which is the table that houses all the information. Then I go to the properties for user last name

The name there says userlastname and the control source is set at
=userid.column(2)

Ok so now nothing is in my user last name box and when I choose abarnes for userid nothing prefills in the last name and if I tab to last name it automatically clears the userID entered.

[jzwp11]

No, userID should be the control source and the userID should be the first field in the row source after the SELECT clause.
You will also have a name for the combo box. If you look at the property sheet of the combo box under the Other tab, name should be the first property listed. That is what you reference in the other textbox controls on your form. Please see the attached