Populating a TextBox with the selected value of a ComboBox

[RunTime91]

Not sure what is going on - but this feels like the 3rd time I am coming here for something so incredibly simple

But neither I nor Google seem to have an answer. Grrrr!!

I just want the end user to select a 2 column ComboBox and have the value of the first column populate one textbox and the value of the second column populate another text box

Below are part & parcel of what I have tried thus far

With Me
  .TxtMonth = .CmboSumResults.Column(0)
  .TxtYear = .CmboSumResults.Column(1)
End With

With Me
  .TxtMonth = .CmboSumResults.ItemsSelected
  .TxtMonth = DLookup(Me.CmboSumResults.Column(0), Me.CmboSumResults)

Etc, Etc, Etc
End With

All of the DLookUp examples I found on Google seem to use a table along with the selected ComboBox value



I just want to use only the ComboBox values.

For some reason adding the Column property to the ComboBox returns a NULL value - If I remove the Column properties then I get both columns of the ComboBox in both TextBoxes.

Thank You in advance

Rt91

[RunTime91]

Just tried this and it seems to work - But is this the correct or even a reliable solution?

  .TxtMonth = .CmboSumResults.Column(0, .CmboSumResults.ListIndex)
  .TxtYear = .CmboSumResults.Column(1, .CmboSumResults.ListIndex)

Thanks...

[pbaldy]

I would have expected your first method to work. Is the combo bound to a multivalue field or something odd like that?

http://www.baldyweb.com/Autofill.htm

[RunTime91]

Hey Paul ~

Yeah I sure thought so as well - But the Intelisense on the ComboBox when using the .Column(0) or Column(1) property shows a '=NULL' value

Remove the Column property and it gives the full value (both columns) of the selected value - If I use ComboBox.ListIndex - it pulls the header value

As for the ComboBox itself, it is an Unbound, 2 Column ComboBox using the below Query for it's RowSource

SELECT Format((DateOfReview),"mmmm") AS [Month], Format((DateOfReview),"yyyy") AS [Year]
FROM Dbo_FacetoFace GROUP BY Format((DateOfReview),"mmmm"), Format((DateOfReview),"yyyy")
ORDER BY Format((DateOfReview),"yyyy") DESC;

[pbaldy]

Curious. I don't think I've ever needed ListIndex with a combo. It is a combo, not listbox, right? I don't suppose you can attach the db here, or a representative sample?

[RunTime91]

Paul ~ Give me a little bit of time and I will be more than happy to send you a scaled down version

It is the least I can do...

[pbaldy]

No biggie, I'm just curious. Don't bother if it's any effort.

[ssanfu]

SELECT Format((DateOfReview),"mmmm") AS [Month], Format((DateOfReview),"yyyy") AS [Year]

Ummm, "Month" and "Year" are reserved words in Access and shouldn't be used as object names.
Maybe

SELECT Format((DateOfReview),"mmmm") AS [ReviewMonth], Format((DateOfReview),"yyyy") AS [ReviewYear]

[RunTime91]

Hey Paul ~

I created a sample Db for you to check out - The code can be found in the CmboSumResults_AfterUpdate() Event

I'm now facing a new dillema in the same event -

I need the ListBox which is populated by the same event, to populate data for the month 'previous' to the month selected in the ComboBox.

I've tried the code below as well as a couple other iterations - the one below doesn't error but it doesn't produce the correct results - The query is the second half of a sub-query

SELECT AssocID, AssocName  FROM Tri_OneonOne " & _
              "WHERE ReviewPer_Mo = Format(DateAdd('M', -1, '" & Me.CmboSumResults.Column(0) & "'), 'MMMM') " & _
              "AND ReviewPer_Yr = Format(DateAdd('Y', 0, '" & Me.CmboSumResults.Column(1) & "'), 'YYYY') " & _
              "GROUP BY AssocID, AssocName

Thanks as always ~

[RunTime91]

Hey Steve ~

Didn't see your post until now - sorry for the delayed response...

I did try your idea - same result -

I wish I could somehow learn to avoid these nagging type issues...

[pbaldy]

This line is what causes you to have to use the more complicated method of getting values.

.CmboSumResults = Me.CmboSumResults.Column(0) & " " & Me.CmboSumResults.Column(1)

without it these work fine:

.TxtMonth = .CmboSumResults.Column(0)
.TxtYear = .CmboSumResults.Column(1)

In a sense you've changed the value that was selected.

This should help you find the problem with your SQL:

http://www.baldyweb.com/ImmediateWindow.htm

[RunTime91]

Hey Paul ~ Can't thank you enough for teaching me how to fish

I use DBPrint regularly - But never used it to test the code result in SQL - Very cool! Thank You Again!

So after trying every DATE 'this' and DATE 'that' - MONTH 'this' MONTH 'that' function in SQL

I have come to the conclusion what I'm trying to do - Get SQL/Access to recognize the string value of a Month as a Month as opposed to just a character string

And yet - I'm convinced I should be able to click March 2020 in a ComboBox and pull results for February 2020 -

The concept is just too simple to believe it can't be done - and yet here I am (again) Ugh!!!!

Here are some of the code samples of what I've tried - Some produce predictiable but un workable results and some produce unpredictible results.

E.g., Why does SELECT Format(DateAdd(Year, 0, '2/1/2020'), 'Y') As Year produce a Month/Year result instead of just the Year?? jsmh

I hate not understanding simple things - but I guess until you know them - they're not simple

SELECT Format(DateAdd(Month, -1, '3/1/2020'), 'MMMM') As Month
SELECT Format(DateAdd(Year, 0, '2/1/2020'), 'Y') As Year
SELECT DateAdd(Month, -1, '3/1/2020') As Month
SELECT DATENAME(MONTH,'3/1/2020') AS BOO
SELECT DATEPART(Month, CAST('1/2/2020' As DateTime))
SELECT MONTH('February')
SELECT MONTHNAME('3/1/2020')

I've got fishing pole in hand if you have time to thow another lesson my way

Again what I'm trying to accomplish - is simply click a ComboBox which is populated with month names and the year

and pull data for the previous month...

Thanks as Always!!

[pbaldy]

Are you working in Access or SQL Server (SS)? Doesn't matter where the data is, but whether the query will be an Access query or a pass through or something that runs in SS. Your earlier posts were Access format, this last one looks like SS syntax.

[pbaldy]

I'm off to lunch, but I'll point out the root cause of your problem is trying to do date math on text. I'm thinking you need to convert "March 2020" to a date, subtract a month, then format the result. I don't like the way data is stored in the table that requires you to compare month and year, but that's me.

[RunTime91]

Hey Paul ~

Here are the details for you...

I am using a pass-through query in Access - I prefer to write in SS then adopt the working code to Access VBE.

You are correct in your analysis of what I am trying to accomplish, but I'll provide perhaps a clearer picture.

The reason for this build is to provide Managers & Supervisors a mechanism to record their 1 on 1's with their agents.

The entire build includes just one table and one UI. I'm sure you noticed the table and the columns involved with what I'm trying to accomplish - (PreviewPer_Mo & PreviewPer_Yr)

And yes, I know I'm violating every one of the five rules of normalization but this table will keep only 6 months of data and will never exceed 1,200 records

So I just decided to leave it flat.

The reason I need the two aforementioned fields is due to the fact some Mgrs/Sups will complete a review for an agents January performance - in March.

Thus the table needs to record not only the date the Mgr/Sup performed the 1-on-1, and the date the record was entered, but also 'for what period' the 1-on-1 applies.

Since the main report produced from the data is a monthly report these two fields become necessary.

The challenge I am having is how to have the user select, say, March 2020 in the ComboBox and have the ListBox show results for February.

I'm definitely curious how you would design the table for this build.

I've written a rather verbose Iif/Then solution - Which works - But it kind of makes me shake my head.

Sorry for the late response Paul - it was a bit of an overwhelming weekend.

Thank You so much for your help with this Paul

Rt91