Selecting field in a table using a form

[astro3ron]

I have a table with a number of fields. I'd like to select one of those fields using a form so I can sort the entries of that specific field. Not sure how to select fields at run time. Any help appreciated.

[Rozeanna Jerry]

I do not really understand you properly but if am correct, try create a data sheet form, bound it to your table, in design view, create a text box and set its record source to the field of the table.

You can also create a query based on the table, select the desired field from the table. Now sort the field by ASC or DESC. Create a data-sheet form based on the query you created.In design view, create a text box and set its record source to the field of the query.

[Ron Woods]

Guess I should have been more specific. Sorry. I have a group of records of people and there are date/year fields for each of those records.
I have a dozen fields, named 2001, 2002, 2003, .... In each of those fields I have a Y or N. I have a form that I use to fill in those year fields and other fields. I'd like to enter a year on the form so I can select that year of people who have a Y in their record. I could create a check box next to the year on the form, but that seems like over kill and the year entry on the form seems cleaner. If my VBA code were up to snuff, I'd code the process like I did in the Fortran coding days.
Thanks

[Rozeanna Jerry]

Please upload the mdb file as zip and I'll have a look.

[Rozeanna Jerry]

You have many fields that you wish to access thier records on a form for each. This might pose a problem. Try and uplaod the db.

The easiest way to proceed with uploading a new database would be to create a new one, and then import from the original database the relevant forms/tables, and then just check that its working before uploading that new database.

[MMcCarthy]

You can add a drop down box with a list of the field names.

I'm not really sure what you mean by this though.

I'd like to enter a year on the form so I can select that year of people who have a Y in their record.

[astro3ron]

OK, I have a number of field names in a table that are by year, 1999, 2001, 2002... as well as other fields. Each record in that table has either a blank, a S or a R in those "year" fields. Using a present form I'd like to select which year to display for all the records of that year that have a S or R so I can print out mailing lables. It really boils down to a generalization of displaying the content of a field for a record, but picking the field at run time using a form. Usually you lay out the fields you want to display on the form and that data is displayed on the form. Well in this case, I'd like to use a list box to select the year (done that and it works) that I want displayed where there's a S or R in that year's field (haven't tried that). So I pull down/select year 2001. That in turn kicks off a query of sorts that displays ONLY records for that year that have a S or R in the field. It's really selecting a field to display at run time on the form. It's dynamic, not static and is only determined when the form is active.

[MMcCarthy]

Set all the relevant controls to invisible at form_load. Then in the after update event of the dropdown box use a select case statement to turn the relevant controls visible. For example ...

Private Sub MyComboboxName_AfterUpdate()
 
    SELECT CASE Me!MyComboboxName
    Case "2001"
        Me!ControlName1.Visible = True
        Me!ControlName2.Visible = False
    Case "2002"
        Me!ControlName1.Visible = False
        Me!ControlName2.Visible = True
    End Select
 
End Sub

[NeoPa]

So, you have a form where you want to select one of the fields in your table, such that the report (Mailing labels, whatever) is filtered by the selected field being non-null?

I think you can see how confused people are, trying to understand your problem. It really is important to communicate your problem clearly and unambiguously at the start. It saves so much wasted effort.

[Mr Key]

Welcome Astro!
I had the similar problem ealier check it at How to create dynamic report pending on user request it might solve your problem

[astro3ron]

Mr Key, thanks for the thread. Liimra had an attachment file, 30.accdb, in a zip file. Couldn't read it. Not familiar with ACE db. I'm using Access 2003. I'll try the FourthField solution. It's been a while since I played with this stuff, so am a little rusty. Sounds like NeoPa has a solution, but is a bit "mum" on the subject. Actually, if one asks the right question, the solution is easy. Once we understand the problem, we usually ask the right question. I have a number of ways to solve this, but I'm trying to find my old notes on what I did for this back on Access '97..
Thanks for the lead tho.

[MMcCarthy]

I'm still not sure exactly what you are trying to do but this article may help.

Create Dynamic Report using VBA

[NeoPa]

I do have solutions, but which one to post depends heavily on what the details of the question are. Now that's clear I'll post what I think is right for you and you can say whether it resolves the issue or not.

You say you have a ListBox where your year field is selected and this is already working. Good. I would generally use a ComboBox control instead, but you may have good reasons why you prefer the ListBox approach, and that can work as well. Your form, at it's most basic, would need this control and a CommandButton control to trigger the running of the report. When the report is run it needs a parameter passed specifying the filter string. It is the WhereCondition parameter of the DoCmd.OpenReport method. The string should be formulated like a SQL WHERE clause but without the word WHERE at the start.

Something akin to :

Private Sub cmdReport_Click()
    Dim strWhere As String
 
    strWhere = "[" & Me.lstYear & "] Is Not Null"
    Call DoCmd.OpenReport(ReportName:="rptMailings",
                          WhereCondition:=strWhere)
End Sub

This code is a bit long-hand for ease of following, but could all be done in a fairly simple single line if you prefer.

[astro3ron]

Thanks, NeoPa. That's sort of the direction I was heading, but it seems I had multiple questions tied up in my post. I seem to recall that there was a generic way to use the results of a ListBox in VBA code and select only the "year" field of interest then read throught the records in that "year" field looking only for records that have a "S" in that "year" field. Right now I'll have to create a report or a query for each year of interest using the results of the ListBox as the filter. Maybe I'm missing some basics here.

[NeoPa]

Astro3ron:
Right now I'll have to create a report or a query for each year of interest using the results of the ListBox as the filter. Maybe I'm missing some basics here.

Why? I hope you're missing something as I don't follow your logic at all. The solution I posted means you shouldn't need to create separate reports. If it's not you missing something basic then it must be me, because your comments didn't tally with my post in any way I understand.

[astro3ron]

Yes, I was missing something! Format and typos and understanding. It's been a while since I've done this. Thanks for the help! It worked.

[astro3ron]

OK, one other refinement. The not null takes care of empty values in the year field, but suppose I want to to select only those records that have an S in the field and not another value. Like I said, I'm a little rusty and it's taking a while to catch up...

[MMcCarthy]

Try this and see if it gives you the results you are looking for ...

Private Sub cmdReport_Click()
    Dim strWhere As String
 
    strWhere = "[" & Me.lstYear & "] Like '*s*'
    Call DoCmd.OpenReport(ReportName:="rptMailings",
                          WhereCondition:=strWhere)
End Sub

[NeoPa]

For line #4 I'd use :

strWhere = "[" & Me.lstYear & "] = 'S'

[astro3ron]

strWhere = "[" & LabelYrSel & "] like '*S*'" works

strWhere = "[" & LabelYrSel & "] = '*S*'" doesn't work

subtlties of the = and Like always seem to get me in queries and filters.
Also, there are some fields where there's a S and others where there's S/R, so I need the wild card on either side. Previously I was combining a query and the filter in the report and they were stomping on each other. That was my problem.

[astro3ron]

In the "old" days, pre 1990s, I would have just written a little BV (not Victorian Bitters, although would taste good about now!) apps to read in the records to an array that was indexed, look in the cell of the "year,record" and checked for a S in the cell, then gone on to the other records doing the same. Finally producing a printable report. Access and VBA has a lot of power and complexity, sometimes to complex, too much flexibility. Oh well, thanks for the help and tutorage, folks. Think I got to where I want to be. Time for a VB or a pint of Ol Peculiar...

[NeoPa]

Ahh Theakston's My mouth is watering just thinking about it :-)

As for the SQL, no-one (least of all I) ever suggested your second version. It seems clear however (from post #20 but not before) that you are looking for records where the record contains an "S" within the field, as opposed to records within the data set whose value is "S". In this case the former would be correct anyway. My version (X = 'S' without wildcards) was for finding the latter case.

I'm pleased you have your solution anyway. Less pleased that you're the one with more immediate access to the Ol' Peculiar, but I'll have to live with that :-D

[astro3ron]

Sorry for the confusion on the selection criteria, but wasn't sure if too much info at the start would get me rambling. Actually, my access to Ol P is further away that yours, as I'm in the States now. But I can still dream of the tour through the Theakston brewery in Masham and relaxing moments in the pubs when I was up in North Yorkshire for work years back. Latched on to that brand and never looked back. Still miss it!! Cheers!

[NeoPa]

Are you a Brit abroad then? Or one of that extremely rare breed of Yanks who appreciate British beer? Ol P is certainly one of my favourites, if a tad on the strong side for too regular imbibement.

[astro3ron]

I be a Yank, who thoroughly enjoys the British beers and food! I came over there, over there... back in 1995 and fell in love with the country side, people, food and imbibement. Loved driving the open roads out in the countryside and could have stayed had I not any attachments back in the States. I liked the verbal banter that could take place and my "dry wit and sarcasm" allowed me to fit right in. Sense of humour and willingness to verbally engage in various topics made me feel as if I were back home. Actually, ancestors were from south England (Wood), so I guess I did sort of come home.

I'd tried a number of dark ales but settled on the taste of Ol P. One or two pints was all I could do at lunch or dinner, WITH food, or I'd fall asleep in the pub, nestled up in the corner booth in front of a warm fireplace. The American beers are more like flavoured water and never cared for them. Newcastle is as close as I could come over here to the English beers. But Theakston was the tops. Cheers and thanks for the help!

[NeoPa]

Astro3ron:
my "dry wit and sarcasm" allowed me to fit right in.

It would. You sound like a natural born Englishman, and from my perspective at least, that's a compliment.

It's my privilege to have helped :-)

[astro3ron]

Thanks for the tip of the hat and the tips for Access as well.