Combo box woes (1 Viewer)

[JChandler22]

I have a table ("Projects") with two columns, Project and Project Description.

I have a form with the same two fields so that the user can easily add to or modify the Project list. The first field is Project, the 2nd is Project Description.

On the form, I want to make the first field a combo box, so that instead of flipping through the records with the footer to select the appropriate Project, you can just select from a drop list. However, I can't figure out how to link that drop list with the Project Description.

In other words, I can get the combo box in the first field, but when I select different records, the Project Description doesn't change with it.

Any advice is appreciated!

 

[jkl0]

I am assuming that your combo box includes both Project & Project Description.

In the On Change event (of the combo box) you can use:

Me.[Project Description] = Me.[[COLOR="Red"]YourComboboxName[/COLOR]].Column(1)

 

[JChandler22]

View attachment db2.zip

Thanks for the suggestion, but I can't seem to get that to work.

And I'm guessing it's because the answer to your question is no, I only have one column in my combo box.

Check out the attachment to see what I'm trying to do. Again, maybe I'm just way off. The form of concern is "Programs" (related to table: "Programs").

Thanks again for your help!

 

[jkl0]

The Dlookup funtion should work for you.

If Project is Text:

Me.[Project Description] = DLookup("[Project Description]" , "Programs", "[Project] = " & Me.Project & """)

If Project is Numeric:

Me.[Project Description] = DLookup("[Project Description]" , "Programs", "[Project] = " & Me.Project)

 

[JChandler22]

Thanks, I got it to work (I think!).

I'll come right back here if I have any more issues.

 

[JChandler22]

More Problems with this combo box

Just when i thought I had it!

(Updated) attachment... View attachment db2.zip

Now, when I open the form and click the arrow to select something from the combo box, I get an error message saying:

The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

After I push OK, I can select from the combo box and the record changes appropriately a couple times. However, when I go to the table from which the combo box draws, the records of the Program field have all changed to single digit numbers. The code that I have for the form field is:

Private Sub Programs_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ProgramID] = " & Str(Nz(Me![Programs], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any suggestions?

 

[JChandler22]

I'm starting to work through my own problem here, but I still need a little guidance.

I figured out that that last error has to do with the "Bound Column" Parameter. It was set to Column 1 (Program ID, numeric value), but set to show column 2 (Program, text value). When I change the bound column to 2, I no longer get that error... however, the combo box then does not jump to records.

Again, any advice is much appreciated!

 

[Bilbo_Baggins_Esq]

I looked at your db and I have a couple of suggestions.

first:
to get rid of your error, remove the control source reference for the drop box.
LEAVE the ROW SOURCE AS IT IS, but remove the control source.

It's important to remember that the control source is the ACTUAL VALUE IN THE ACTUAL TABLE (or query). If you change its value, you change the record.

Sometimes, it helps to open the underlying tables and watch what happens when you operate your code.
In this case, I noticed that when you made a selection from the drop box, it actually changed the value of the active record, to the record number of the chosen record. This is BAD.

so remove the control source, and it wirks fine.
The only down side to that is that you can't edit the program name from that field.
If you need to allow edits of the actual name, then add a simple text box back to the form that does have the control source set to it.


secondly, I've lookedat your new structure, and your moving in the right direction, but I still suggest you to add a numeric Program_ID column to the Programs table, and then in your program_contacts table change the Program column from that to Program_ID.

 

[JChandler22]

Bilbo - what a simple fix! Thank you.

So, if I want editing capabilities, are you saying that I'd have to add a textbox field to my current form? Seems a bit much.

Is there a way to keep it a combo box, but be able to edit the program name?

Again, thanks for all your suggestions. It's relieving to hear that I'm going in the right direction! I actually did take your suggestion about the program_id - that column was just hidden. You'll see it if you take another look.

Leaving the office for now, but I'll check back again tomorrow. Thanks.

 

[Bilbo_Baggins_Esq]

Is there a way to keep it a combo box, but be able to edit the program name?

Actually, you wouldn't have been able to do as it was anyway. The only way you could have changed it would be IF you changed it to a value already contained in the drop down list, and even then, the end result would have been the numeric record value of the item you changed it to.

As far as I'm aware, you would not be able ADD OR CHANGE A VALUE to/in a drop box list from within the actual drop box list. Basically, the drop box list is a read only query.

Adding the plain text box back the the form is easy.

I'm glad you added the ID column, but you still should consider changing the [Program_Contacts]![Program] column to be the actual ProgramID instead of the program name.