List Box - limit to list

[edgarchado]

Hi all,

When a field is a combobox you can decide if the values will be limited to the list or not.

In a list, they are always limited to the list. Is there a way for the users to enter any data, and not only that of the list?

Thanks.
Edgar

 

[Remou]

I guess you could place a textbox just above the list box, into which the user could type. The list could be update with the data using a little code. Or maybe a Double-Click event and an InputBox?

 

[edgarchado]

I would prefer the users, to type directly on the text box.

Or is there a way to populate a text box via Visual Basic?

I mean by first selecting a value from a combobox and the textbox will automatically update itself?

Thanks.

 

[Remou]

You have me confused now. I thought that you wanted to add to a listbox? There is a lot on adding a value to a combobox, or a record to the underlying table, based on limit to list. For a listbox, you need something a little different, such as a textbox with an After Update event. Perhaps, if the rowsource is a Value List:

If MsgBox("Add " & Me.txtNewItem & " ?", vbYesNo) = vbYes Then
Me.lstList.RowSource = Me.lstList.RowSource & ";" & Me.txtNewItem
End If

Or for a table:

If MsgBox("Add " & Me.txtNewItem & " ?", vbYesNo) = vbYes Then
strSQL = "Insert into tblTable ( Field1 ) select '" & Me.txtNewItem & "'"
DoCmd.RunSQL strSQL
Me.lstList.Requery
End If

Just a very rough idea.

 

[edgarchado]

Sorry if I got you confused.

I have a combobox which populates a listbox. I have no problem with that.

What I would need is for the user to add a different value if the one in the listbox is not the one they were looking for.

Thanks

 

[Remou]

Ah! Well I guess you could use some of the combobox Not in List stuff that you will find dotted all over these fora. Depends on how the combobox is populating the list box, of course. Or you could use one of the snippets above.

 

[TheAceMan1]

[blue]I would prefer the users, to type directly on the text box.[/blue]

Hmmmmm . . .

[blue] . . . you could place a textbox just above the list box, [purple]into which the user could type[/purple].[/blue]

See Ya! . . . . . .

 

[edgarchado]

Another option that I am looking at is to populate the text box using a query.

is this possible?

Thanks

 

[Remou]

Yes. You can use code, or Dlookup may suit.

 

[edgarchado]

this is my code for a combobox. how should I change it to use code or dlookup to populate a textbox?

Private Sub Pelicula_AfterUpdate()
Dim loDate As Date

'Get the company's last order date
loDate = DMax("[Fecha_Pedido]", "tblPedido", "[Nombre_de_la_Película] = '" & Me.Pelicula.Value & "'")
'Set rowsource to record matching that date

Me.Director_de_Fotografía.RowSource = "SELECT DISTINCT tblPedido.Director_de_Fotografía FROM tblPedido WHERE tblPedido.ID_Productora=Productora And tblPedido.Nombre_de_la_Película=Pelicula AND Fecha_Pedido= #" & loDate & "#; "

Me!Director_de_Fotografía.Requery

Thanks.

 

[Remou]

I can't say, what do you want to go in the textbox? I cannot get a picture of what you are doing. How do you see the combobox, the textbox, the listbox and the query working together? What is the sql of the query you wish to use to populate the textbox?

 

[edgarchado]

this code is in an afterupdate event of a combobox. form this first combobox i select one of the items, which will populate a second combobox.

I dont want this second one to be a combobox, but a textbox instead.

I get to populate the second combobox with the previous code.

I would like to modifiy the above code, not to populate a combobox, but a textbox instead.

Hope I am clear enough.
Thanks.

 

[Remou]

Yes, thank you. I think that all you have to do is change this:
[tt]Me.Director_de_Fotografía.RowSource = "SELECT DISTINCT tblPedido.Director_de_Fotografía FROM tblPedido WHERE tblPedido.ID_Productora=Productora And tblPedido.Nombre_de_la_Película=Pelicula AND Fecha_Pedido= #" & loDate & "#; "[/tt]

To:
[tt]Me.Director_de_Fotografía=DLooUp("Director_de_Fotografía", "tblPedido", "ID_Productora='" & Me.Productora & "' And Nombre_de_la_Película='" & Me.Pelicula & "' AND Fecha_Pedido= #" & loDate & "#")[/tt]
Where Director_de_Fotografía is a textbox. I am guessing that Productora and Pelicula are the names of textboxes on your form. It seems likely that only one record will match the criteria.

 

[edgarchado]

Productora and Pelicula are 2 comboboxes which will help filter the Director_de_Fotografia textbox.

I have changed the code but I get a Type Mismatch error.

thanks.

 

[PHV]

Get rid of the single quotes surrounding numeric values (Me.Productora I guess).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[edgarchado]

thank you Remou and PHV...

It is working great...