Rowsource Locked

[Ugurovic]

Hi,

I have a Form, Listbox and a field 'field1'. In 'field1' I search for label, description in table1, and table1 is connected to Listbox so I get in my listbox, only the records that I searched for. In the rowsource of the properties I have a sql the get records when I put something in 'field1'.

Now I want to search on date. I have a field called 'date1' and another field called 'date2'. When I put dates in those boxes I get my records in the listbox that I want. This is the code.

strSQL = "SELECT * FROM [Names] WHERE date >= " & Chr(35) & _
Format(Me.date1, "dd/mm/yyyy") & Chr(35) & " AND date <= " _
& Chr(35) & Format(Me.date2, "dd/mm/yyyy") & Chr(35)
Forms!Names!ListNames.RowSource = strSQL
Me.Requery

When I searched for it, my rowsource stays locked. I want to have to code that clears this search function so I can search in 'field1', but now I can't because the rowsource is locked by the strSQL by searching on date.

How do I solve this so I can search on field1 after I searched on date.

Kind regards

Ugurovic

 

[pbaldy]

I'm not sure what you mean by the row source being locked. You should be able to use similar code to change the SQL to search on a different field.

 

[Ugurovic]

By locking I mean that the rowsource sticks locked by strSQL I've done with VBA (see above), so I need another function to clear this rowsource so that I get the whole table in my list and not the one I've searched for in my strSQL in VBA. How the clear rowsource strSQL, any idea?

Is there a possibility that I can put my searchfunction in the rowsource of the properties of the list? So that way I don't have to write VBA?

Kind regards

u.

 

[nIGHTmAYOR]

-We can see the code but which control and which event runs it ?

-What do you mean (And try to use access terms) by table1 is connected to Listbox ?

-Is your list box bound to a "Control Source" ?

-Please elaborate on the need for unlock your list box since it's an output field not an input one...

 

[nIGHTmAYOR]

argggggg ...
ok would this do it for u ?

strSQL = "SELECT * FROM [Names]"
Forms!Names!ListNames.RowSource = strSQL
Me.Requery

 

[Ugurovic]

Ok, I have 2 situations

1. I have a table 'Names'. In the rowsource of the properties of my list I have a SQL that filters records of the listbox via an extern field. When I put a value in this field, it filters records on this list based on the table. Ok? So my suggestion was, I have putted 2fields extra on the form, 1 is begindate, the other one is enddate. What I want is, when I put values in those field, my list is also getting filtered like that other field I use to filter? But my problem was to get a clear sql in the rowsource in the properties of list to search on the date begin en date end. I always gettin errors, so I tried out VBA, see question 2...

2. I tried to put code in the properties of the list to search on date begin and date end but it always gave errors, so I tried it out in VBA but when I used the strSQL and rowsource and when I putted date begin and date end in it, the list gets locked and I COULDNT SEARCH ON SOMETHING ELSE. When the list is listing 2records after search on date, it stays 2 records, you can't search on something, so I wanted to cancel the rowsource in VBA by a button but how, so I can search on anything I want after I searched on date, that's what I meaned by locked, I hope its clear now... I want to search again on anything I want that's my problem..

Kind regards

U.

 

[Ugurovic]

Anybody who can solve my issue?

Kind regards

Ugurovic

 

[pbaldy]

Did you try what nIGHTmAYOR posted earlier? It would reset the source to all records. Otherwise, a sample db might help clarify the problem.

 

[Ugurovic]

No that doesn't... I need the code to undo the strSQL function so I can search in the listbox via another field... undo strSQL, that's what I need..

Kind regards

U.