Combo Box and auto filling fields

[Saudi Mike]

ok, here's something maybe a little different. first off:

MS Access 2010

I have a form "Admin" used to create new records in a table "RCJ"

I have a table called "Projects" that contains detailed information for projects.

I have a table called "Waterfall" that contains basic information and schedule information for each particular project.

I have a combo box "Contract" that selects information based on a query "Core" that gathers certain information I want to use of form "Admin"

I understand it's generally not a good idea to duplicate data in tables, but for specific reasons I am here. My combo button "Contract" has an event "On Click" that gathers information I want copied from table "Waterfall" via query "Core" is:

Private Sub Lookup1_Click()

Me![Project Description] = Lookup1.Column(1) =>goes to a text box
Me![TContract] = Lookup1.Column(3) => goes to a text box
Me![Requestor] = Lookup1.Column(5) => goes to a combo box
Me![AEM] = Lookup1.Column(4) => goes to a combo box
Me![AE] = Lookup1.Column(6) => goes to a combo box
Me![Priority] = Lookup1.Column(12) => goes to a combo box


End Sub

Now, the items listed above are sometimes just a text box and sometimes a combo box (because there are times, when items get entered that don't fit the original combo box.

Now, here's the rub. When I select the pull down on the combo box it lists all available projects. You find the one you want and select it. At that point, only Columns 1 (text box) and 6 (combo box) auto fill. The other information does not fill in .. BUT, if I more to the prior record and back, all information shows up OR if I click on any of the particular fields, the information shows up in that field. In theory, everything should show up right away.

I thought it might be because it doesn't like to autofill a combo box, but one of the combo boxes fills fine. And all of the information is acutally going to the various boxes, it just doesn't show up right away.

Any suggestions (besides telling me to go back to using excel) would be appreciated!

 

[spikepl]

Solving the Rubik's cube without a physical Rubik's cube is a lot of fun. For some selected individuals, surely.

Us mere mortals prefer something to look at. Attach a db exemplifying your issue.

 

[Saudi Mike]

I can do that, but it will have to be tomorrow. Left it at the office. I'll remember to send along the 'cube' next time.

 

[Saudi Mike]

Well, I tried uploading files this am, but they exceed the 2mb limit. errr

 

[spikepl]

compact & repair, zip

 

[Saudi Mike]

ok.. after a little work stripping down data and forms not necessary for this exercise, I got down to an acceptable file size.

Attached is a zip MJS. if you unzip this to a folder on your C drive called "MJS" the tables should maintain their relationships. In it there is:

1 - RunThisFile - Got that? yeah. run that file. This is the db that contains just forms, queries, reports and the like.

2 - Data - This contains only tables with the data. nothing else.

3 - Waterfall2015 - This is a stripped down version of what I get from the guys who believe they everything should be done in massive excel sheets.

When you open RunThisFile it defaults to the screen I'm having issues with, the "Admin" Screen and defaults to add a "new" record. If you click on the pull down combo box "C-Contract" you'll see a list of records querried from the Waterfall2015 file. If you select on a contract, say "021-C06" you'll see information pops up in the "Project Description" box, and the "A/E" combo box. now click on the T-Contract box and you'll see info pop up that was pulled in from the Waterfall2015 file, but wasn't being displayed. Same thing for the "AEM" box and the "Proj. Eng" box. The data's there, it just isn't showing up upon clicking the "C-Contract" box. you'll see the event procedure under properties.

I thought at first it might be because the info in the "AEM" and "Proj. Eng" coming in from the Waterfall2015 doesn't match what's in the pull down for these combo boxes. But the "T-Contract" is just a text box.

So, the info is there, it just isn't showing until you click on a box.

 

[spikepl]

I'll have a look-see tomorrow - perhaps before that. Anyone else welcome to have a go.

 

[Saudi Mike]

Cheers! It's probably something simple that I've overlooked. I'm not all that great a programmer. But I appreciate your assistance!

 

[spikepl]

Many somethings! You have a naming mess. Here is a convention you could use: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Using non-alphanumerics like spaces or ":" in object, field or column names is asking for trouble.

Specifically:

1. Lookup1 - name it for what it displays. When you return to your stuff to fix things in 4 months time it will make things easier. Call it fx. cboCcontracts
   
2. Lookup1 - to set values for other controls use the AfterUpdate event of the combo, not OnClick.
   
3. A control is one of the "things" on the form like textbox or combobox. WHich may or may not be bound to a field in a record. When assigning values in a form, do that for the controls, so
   
   Me.Controlname2= Me. Controlname1
   
   You had
   
   Me.FieldName2 = Me.Controlname1 (as in Me.[t-contract]=Me.Lookup1(x))
   
   which can give funny effects as you've experienced. It should be (with your naming, which ought to be changed too):
   
   Me.[Job no:]= Me.Lookup1(x)
   
   It is also best to give controls names that preserve some relation to the field to which the control is bound. Your names however are all over, and will make it very hard to fix anything in a few weeks.

 

[Saudi Mike]

Thanks for your advice! Believe me, I understand the naming thing. I didn't start the db, a fellow worker did, got it kinda running and then left. I've been shaking my head the entire time as he did a bunch of things in a rush and literally left the day after he said "Here you go". I've just trying to get things sorted out and fix many of the operational errors before I tried to sit down and sort out all the naming issues. Guess that's what's been giving me grief from the get go. Seeing as how three months ago I hadn't used Access since, oh, like 1990, it's taken some time to get up to speed. I'm also trying to get the guys who run the Waterfall spreadsheet to fix some of their naming issues. They have people's names in many different ways, J Schrandt, J. Schrandt, Shrandt, John. Gangs of fun.

Thank you so much for your efforts!! I'll work on it tonight and see how it goes!

 

[Saudi Mike]

Dude! You ARE the man! Things are working now and I've been working through the Name Conventions. Great Help to someone who has had to learn on the fly. I also now understand the difference between Me! and Me. Can't tell you why the guy who set it up did it the way he did. I'm just trying to fix this jumbled mess.

Thank you again!!

Saudi Mike