Combo Box (more info on what I need)

[Parra]

OK Dugantrain...Yeah I'm at work and pretty much bored too.

I have a Main Form with the fields "K Name" and "K ID". Just in case you are interested "K" means contract.

Then I have a sub form with many more fields, but 2 in particular "MSO" and "Contract ID#".

What I want is in the Main Form "K Name" to be a drop down box, when I choose a contract I want "K ID" to automatically fill from the record source that pertains to "K Name".

In the sub form I want "K Name" to populate "MSO" and "K ID" to populate "Contract ID#".

I hope I made sense, but I don't think this will be too hard or too easy.

Thanks
Parra

 

[Parra]

OK, using Dugantrain's Post I was able to to make the dropdown box have both fields appear, but the 1st column is linked, but how do I link the 2nd column to a field in the subform?

Parra

 

[Dugantrain]

Alright, it's easy once you know how, but it takes a bit more effort. The easiest way is to have one text box on your main form which will identify every field on your subform(i.e. Primary Key field on the main form which relates to the Foreign Key field of the Subform. This is why I use Autonums in every table; it's a cheap, easy way to relate data, but that's a whole 'nother debate).
Now, what you're looking to do is Update the two fields in the underlying table to the two fields in the combo box where the table's foreign key is equal to the Main Form's Primary Key. There's about a million ways to do this (ADO, DAO, RunSQL, etc.) but probably the least code intensive is going to be to use a predefined Update Query.
-Open a new query
-Select Update Query under queries
-Pull in the table that is to be Updated
-Pull down the two fields to be Updated.
-In the 'Update to' Section, for MSO, type:

Forms!Your_Form_Name.Form!Your_Combobox.Column(0)

or whichever column number it's to be Updated to, substituting my generic names with the literal names of your form and combo box objects.
-for [K ID], type:

Forms!Your_Form_Name.Form!Your_combobox.Column(1)

Make sure that you have the right Combo Box column # or you're going to be Updating your fields with the wrong data.
-You're now going to need a 'Where' Clause so that you limit the Updating only to records which are currently being displayed on the Subform. You would use the Primary Key of the Main form for this:
-Pull down the Foreign Key field in your query. Leave 'Update To' blank as you're not going to actually be updating this field. In the 'Where' Section of this field, you'd type:

Forms!Your_Form.Form! Your_Form_Primary_Key

This is extremely important as, without a Where Clause, you're going to end up Updating every field in the table to the two fields in the Combo Box!
So now your query's finished. Save it. Finally, you want to call this Query in the AfterUpdate event of your combo box:

Your_Combo_AfterUpdate()
DoCmd.OpenQuery "Your_Query_That_You_Just_Wrote", acViewNormal
Forms!Your_Main_Form!Your_Subform.Requery
End Sub

The 'Requery' code at the end will ensure that, after the Update Query is run, your subform will visually reflect the Updates that you just made.
You may also want to take of Access's built in Warnings by:

Your_Combo_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Your_Query_That_You_Just_Wrote", acViewNormal
DoCmd.SetWarnings True
Forms!Your_Main_Form!Your_Subform.Requery
End Sub

I know that this is a lot to take in; if you still need help, just send me your db and I'll do it for you so that you can see what's going on first-hand.

 

[Parra]

OK, now I have another question. Why do you love pants?

 

[Dugantrain]

I get asked that a lot, so I might as well explain. You remember a sketch comedy show on MTV about a decade ago called "The State"? No? Well, there was this one sketch where a guy never wore pants; the concept of pants just somehow never occurred to him. So a concerned friend decides to educate him. He takes the guy to a clothing store, at first the guy's actually trying to eat the pants, until finally, after several failed attempts, he gets it right. The two are dancing around gleefully, spinning around in circles, and if you watch closely, you can see the now pant-wearing guy mouth the words "I Love Pants!!!" Moronic stuff like this is exactly my sense of humour so...