List box row source (1 Viewer)

kimberlin · Jul 24, 2011

Hi, I only joined this forum yesterday and I feel I've learned quite a bit already about relational tables, so now onto the forms. I've been trawling the web for quite a few hours today after discovering that I could use a form to do a filter from the advanced bit in the filters part of the ribbon (access 2007) and realising that I could change one of the fields on my form from a text box to a list one (I think I need a value one to return the text in that particular field and also make it a multiple selection from that list. The thing that I am wondering now is does anyone know of a way that I can make the row source one of the fields in the query which the form is based on rather than having to manually input the names of goodness knows how many towns? I've got no experience whatsoever of programming but I'm willing to learn.

I'm going for a filter from the form rather than using a query as my co-workers are reluctant to learn how to set them up and I think this could solve the problem of their moaning that it's easier filtering the tables to find the information they're after!

GinaWhipp · Jul 24, 2011

I am not quite sure what you are asking... I think you want to make the Row Source of a Combo Box a field from your query. You can do that, use the Combo Box wizard to set it up.

kimberlin · Jul 25, 2011

Thank you very much for the suggestion Gina, I will certainly look into it. Can I choose more than one item at a time from a combo box, for example say I wanted to look in two or three different nearby towns for a plumber without having to repeat the search?

GinaWhipp · Jul 25, 2011

No you cannot do that using the *standard* combo box. Perhaps this will give you an idea...

http://allenbrowne.com/ser-62.html

...instead of displaying on the form you can use a button to open the report.

kimberlin · Jul 25, 2011

Many thanks Gina. I've bookmarked the link you gave me and will give it a thorough read later on.

GinaWhipp · Jul 25, 2011

Okay, will be here if you have questions as I have used it to generate reports that I need multiple filters on...

kimberlin · Jul 26, 2011

Thank you Gina, I've just read through it and I think it will suit a lot of what I've got in mind. I'm especially pleased to see the bit about searching between dates as one of my co-workers mentioned that he would like to see something along those lines at work today!

Will the same type of language used in the example work ok in Access 2007? I've got 2010 on my pc at work but the rest of the workgroup are on 2007 so I plan on building it all at home where I've got 2007.

GinaWhipp · Jul 26, 2011

Glad to help! We'll be here if you have any questions...

kimberlin · Aug 21, 2011

Ok, I've started working on the form you've referred me to now but although I told the various boxes as i was creating it where to draw their data from, I've hit a snag with the record set for the form itself as it has to extract information from two different tables (I have a one to many relationship between workers and their trades). Is there any sort of workaround I can do?

GinaWhipp · Aug 23, 2011

Sorry for the delay, got a little busy...

You need to create a query with those two tables and base the RecordSource of your form off of that.

kimberlin · Aug 23, 2011

Ok, I'll give that a try. Thank you Gina.

GinaWhipp · Aug 23, 2011

We'll be here waiting to hear the outcome...

kimberlin · Aug 23, 2011

Thanks Gina. I've started to work on it but judging from the time I don't think I'll get it all done tonight!

GinaWhipp · Aug 23, 2011

Tomorrow is another day... Just take your time and when you're done post back!

kimberlin · Aug 24, 2011

This may sound like a daft question and I'm feeling a bit stupid for having to ask it, but from the coding list that I printed out from the website you referred me to, do I put the individual bits in for each field using the code builder in the various properties boxes, in which case, where do I type the first 6 lines of code from that printout in, or is there somewhere else that I go to and type the lot in in one go? also can I put fields in the results that weren't in the original search criteria?

GinaWhipp · Aug 24, 2011

Nope, not a silly question at all... let's make this easy, copy/paste what you have here and let's have a look...

kimberlin · Aug 28, 2011

Hi Gina, sorry I've taken so long to get back to you but have had trouble getting a connection to the net. The code I've got so far is:

Option Compare Database

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
strDelim = """"
strDoc = "Search Form"

With Me.WhichTown
For Each Town In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(varItem) & ""","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Town} IN (" & Left$(strWhre, lngLen) & "0"
lngLen = Len(strDescrip)
If lngLen > 0 Then
strDescrip = "Towns: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllForms(strDoc).IsLoaded Then
DoCmd.Close acForm, strDoc
End If

DoCmd.OpenForm strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

but whether I've adapted it correctly or not I don't know. I followed the link for using a multi-select list box to filter a report (I've changed all the report instances to form) from the link for a search form's criteria that you gave me earlier in this thread and I'm just hoping that I've got the rest of it right as my field name is "Town". I must admit that I was thrown a bit by the instructions for this feature as I was looking for a command button within the list box properties.

GinaWhipp · Aug 28, 2011

Right off the bat I see...

strWhere = "[Town} IN (" & Left$(strWhre, lngLen) & "0"

...see the underline? That should be a bracket *]*. After that you need to tell me what is happening when you run this code.

kimberlin · Aug 28, 2011

Ok, I hadn't spotted that typo so thanks for pointing it out Gina. I'll go and correct it now.

List box row source (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread