Combo box related problems!!

[swift]

Another issue to resolve!!

I have a form called sample register. On it users input data and use combo boxes to populate fields such as date, material type, sample type etc. I'm having a problem with the material type combo box.
On another form, I have text boxes displaying a selection of the items recorded in the sample register. One of them is material type. However, the text box in question is displaying the ID number of the field which was selected in the sample register, instead of the material type to which the ID belongs. In the properties of the text box in question I can't find anything related to the bound column, which is how (I think!) I have resolved these things in the past.

Can anybody help??

swifty

 

[boblarson]

Text boxes do not have columns. They have a single field (column). So, if you want to display something other than the ID, include the other table in the query underlying the table and use the field that is the description of the related ID in it. However, it will then be non updateable because you are just displaying the related value. To display the related value from the other table but still be able to select it, use a combo box. You can set the rowsource to the table where you look up the values and you can, set the combo to have 2 columns, the ID field (set to 0" to hide it) and the description column, which will be set at a column width, of say 1", to display the associated value.

 

[dillonhh]

Bob, regarding your response, my question is this...

I have a combo box with two columns in the row source, they are 2 dates. The combobox is looking up an ID field but displaying these dates in the row source related to that ID.

After i select a set of dates in this combox box, only the left column is displayed in the form. However, i would like to know if there is a way to display both columns in the form after selecting an item in the drop down.

Thanks, Dillon

 

[CraigDolphin]

Sure there is...in addition to your key field, create just one column that concatenates the two date field values...maybe separated with a delimiter like a dash.

So, you would remove [datefield1] and [datefield2] from your query grid and then add a new field that is something like:

MyCombinedDateField: [datefield1] & " - " & [datefield2]

Then adjust the column count and column width properties of your combo to reflect that you now have a total of two columns, not three.

Obviously, you will need to substitute the actual field names.

 

[dillonhh]

NICE!

Craig thanks a lot, i appreciate that

 

[dillonhh]

Craig, I lied, my problem now is that i am trying to incorporate this into a rowsrouce that changes...

The rowsource is located in a subform and needs a WHERE clause that limits the rowsource based on the ID of the main form. Is there a way i can code this in VBA as an on enter event for the combobox, i have tried this and also tried to incorporate it into the actual rowsource of the combo box and am having no luck at all.

 

[CraigDolphin]

Dillon, sorry, I'm not following you. The advice I gave you just changed the way the field information is SHOWN in the combo, not which piece of information is STORED by the combo: that hasn't changed and should still remain the key field unless you've also changed the bound column property of the combo.

It might be helpful if you posted a zipped version of the db (stripped of any sensitive information first) and then explain exactly which form(s) and combo boxes you are referring to, what you are doing to generate the problem, and what behavior you are wanting to get.

 

[dillonhh]

ya that was not very clear, youre right...

Here is the SQL that worked well for me when i changed it in the RowSource property of the combo box... but i need to limit the rowsource and i cannot get a dynamic WHERE to work in that query editor. The idea is kind of like once you click a country in a drop down, you only want the states that apply to that country to appear in the states combobox...

SELECT CruiseDates.CruiseDateID, CruiseDates.[EmbarkDate] & " - " & CruiseDates.[DisembarkDate] AS Dates
FROM CruiseDates
ORDER BY CruiseDates.EmbarkDate, CruiseDates.DisembarkDate;

Below is the VBA with the SQL i am trying to use but it reads the quote marks as part of the string and thinks i am subtracting two things...i think

    Dim S As String
    S = "SELECT CruiseDates.CruiseDateID, CruiseDates.[EmbarkDate] & " - " & CruiseDates.[DisembarkDate] As Dates FROM CruiseDates where CruiseDates.CruiseID =" & [Forms]![frmCruises]![CruiseID]
    Me.CruiseDateID.RowSource = S

 

[CraigDolphin]

Have you done any reading on cascading combo boxes?

The idea is that you include the field that you need to use to limit the list in the rowsource, and then reference a control on a form as the criteria for that field. Although the value in the control might change, the field itself that is being used to limit the list does not change. you only need dynamic sql if the fields to be included change or you're using a multiselect listbox as your criteria.

Then you use the on_enter event of the combo to requery the list (in case the value in the form control has changed).

 

[dillonhh]

In my above post, that is exactly what i have done. The second code box there is what i had in the on_enter for the combo box. as you can see i have it reference a control on the main form but it can just as well be on the subform itself, as i have done below. that is no problem, but it is not the issue i am having. the problem is how to incorporate what we were first writing about, making two columns show up in one after the selection is made.

Private Sub CruiseDateID_Enter()
    Dim S As String
    S = "SELECT CruiseDates.CruiseDateID, CruiseDates.[EmbarkDate] & " - " & CruiseDates.[DisembarkDate] As Dates FROM CruiseDates where CruiseDates.CruiseID =" & Me.[CruiseID]
    Me.CruiseDateID.RowSource = S
End Sub

This part, CruiseDates.[EmbarkDate] & " - " & CruiseDates.[DisembarkDate], is not working for the string because of the " - ". I am wondering if you know how i can get this aspect to work.

 

[CraigDolphin]

Oh! Just change that bit from " - " to ' - '

 

[dillonhh]

i should mention that i just tried it like this and it works fine, but the dates are jammed together without a space or anything...

Private Sub CruiseDateID_Enter()
    Dim S As String
    S = "SELECT CruiseDates.CruiseDateID, CruiseDates.[EmbarkDate] & CruiseDates.[DisembarkDate] As Dates FROM CruiseDates where CruiseDates.CruiseID =" & [Forms]![frmCruises]![CruiseID]
    Me.CruiseDateID.RowSource = S
End Sub

 

[dillonhh]

hahah yup that did it! i cant believe i didnt know how to fix that problem!! thanks a ton