Help: MS Access - Populating Text Box from a Combo Box Selection in a Form

[iNow]

Usually when I'm in that position I throw down some break points, add some variables to a watch list, and run the debugger. But I'm guessing you're not really familiar with that practice. It's kinda hard to describe, but basically it allows you to run each line of code one step at a time and monitor the values in the variables to see if they're correct at that point in time.

Yeah, doG described this process to me on the last page of this thread. I'd never seen it before, and it was cool. However, the suggestion is a bit like telling me to split a novel written in Swahili into individual sentences. It doesn't matter how small I make the chunks I'm analyzing, I don't speak Swahili.

 

 

 

Have you checked to make sure all your variables match up and that you're calling all objects by their correct name from the form? (Meaning if you call an object "textbox1" on the form then you have to call it "textbox1" in the code -- seems obvious but it's easy to get confused.)

Yeah, I may be wrong, but I don't think this is the problem. I set most of it up using the wizard. I didn't write a single line of code. I just changed the values in the Properties window.

 

Like I said, I was really close before opening this thead, but just couldn't get it to work. My gut tells me that the reason the title is populating the code field in my output table is that I set the textbox "Control Source" equal to "Code." I am pretty sure that doing so told Access that I wanted whatever value is in the Title field of the form to be entered into the Code field of the output table.

 

It makes my form look good, but seems to miss the intent on my output.

 

 

Anyway, thanks for the offer to look at my code, but as I mentioned above, I haven't written any. Any code I entered was a copy/paste job from truedeity and doG. Unfortunately, none of those got me where I needed to be (or, more accurately, I didn't understand it well enough to make it work), so I reverted back to my previous approach which at least had the form working as I wanted.

 

 

 

 

 

---

line[/hr]

That should work. Unless of course the code field is a string field and not a number field. You may want to verify that in the table design. Make certain that it is a number field. Otherwise, you can change the SQL to look like this:

 

 

 

strSQL = "SELECT [title] FROM Table1 WHERE

 LIKE '" & cboCode.Value & "' ;"

Okay, thanks mate. First, the Course_Code (or, just "Code") field is a text value, as it's something like TRAINING005 or some similar. So, I used the last bit of code I quoted here (note, I'm running tests on two different forms now... one where I set it up per my response to Pangloss, and another where I set it up per your suggestions with the code)...

 

So, when I ran the example using your code, I got an Information Message telling me "Characters found after end of SQL statement."

Although, strangely, when I run it line by line with the F8 key, I don't get any errors. Hmmm... It's not populating the Title value though.

Edited December 22, 2008 by iNow
multiple post merged

[Pangloss]

Try taking off the semi-colon on the end (actually everything after .Value) and see if that does it.

 

strSQL = "SELECT [title] FROM Table1 WHERE

 LIKE '" & cboCode.Value

 

or

 

strSQL = "SELECT [title] FROM Table1 WHERE [code] LIKE " & cboCode.Value

 

(the second one just removes the single quote after LIKE)

 

If that doesn't work, try this one:

 

strSQL = "SELECT [title] FROM Table1 WHERE [code] LIKE '" & cboCode.Value & "'"

 

(single-double after LIKE, then double-single-double at the end)

 

I didn't realize you were using VBA rather than regular VB -- they're slightly different, and I should have picked up on that sooner since you were talking about Access, sorry. But it looks like truedeity picked up on it. It sounds like you're real close to the end.

[iNow]

If that doesn't work, try this one:

 

strSQL = "SELECT [title] FROM Table1 WHERE

 LIKE '" & cboCode.Value & "'"

 

(single-double after LIKE, then double-single-double at the end)

How interesting. This seems to be the way to go. But, get this. Nothing filled in on my form, but the output table populated correctly. So, I selected "Code2" in my combobox, and the Title box (in the form) remained blank. However, just for kicks, I went and looked at the table, and the fields were properly inserted.

 

Basically, it made the data do exactly what I wanted to in the Output table, so thanks!

 

 

New problem: How the hell do I get it to show up in the Title listbox of my form during the entry process, as opposed to just pushing the (correct) value to my output table?

 

I can't wait until I go back to working in MySQL and Hyperion Intelligence Explorer. They're so much more straight forward

[Pangloss]

That's VBA for you; I sympathize. It's a lot better with Visual Studio and SQL Server as well. Heck, it's better in the world of data-driven iPhone applications. (lol)

 

I'm not sure about all that you're doing in your app, but the big-picture answer to your question is that you need to run the query at form load to populate the listbox. If it's staggering down to subsequent listboxes after the first one is chosen, then you need to have those trigger off selection events for the previous listbox. I don't know how that is done in VBA/Access.

[truedeity]

Okay, thanks mate. First, the Course_Code (or, just "Code") field is a text value, as it's something like TRAINING005 or some similar. So, I used the last bit of code I quoted here (note, I'm running tests on two different forms now... one where I set it up per my response to Pangloss, and another where I set it up per your suggestions with the code)...

 

So, when I ran the example using your code, I got an Information Message telling me "Characters found after end of SQL statement."

Although, strangely, when I run it line by line with the F8 key, I don't get any errors. Hmmm... It's not populating the Title value though.

 

Ah, I had initially assumed it was a Number field.

 

The SQL executes, however, it does not return any rows. Thus, your Text box does not update...

 

Suggested Fix.

 

-Adding * inside the single quote's of the comparison string.

-Removing the space " ;" before the semicolon?

 

Try this:

strSQL = "SELECT [title] FROM Table1 WHERE

 LIKE '*" & cboCode.Value & "*';"

 

And try this just incase. (Without semicolon, however, the semi shouldn't pose any problems.):

 

strSQL = "SELECT [title] FROM Table1 WHERE [code] LIKE '*" & cboCode.Value & "*'"

 

Ensure that data in Table1 in the code column does not contain extra characters, such as "spaces", however, the * usage should account for this occurrence!

 

IF ALL ELSE FAILS... Try this one...

 

 

strSQL = "SELECT [title] FROM Table1 WHERE LTrim(RTrim(Ucase([code]))) LIKE '*" & LTrim(RTrim(Ucase(cboCode.Value))) & "*'"

 

and (The same as above, just includes the semicolon.)

 

strSQL = "SELECT [title] FROM Table1 WHERE LTrim(RTrim(Ucase([code]))) LIKE '*" & LTrim(RTrim(Ucase(cboCode.Value))) & "*';"

[iNow]

Okay. I tried your suggested code. It basically did the same thing where Pangloss and I had left off. I select the value for Course_Code in my combobox, the Course_Title field (on the form) remains blank (no change), but the output table has all of the data it should, and in all of the right places (code and title filled in for that row appropriately).

 

I didn't check for spaces in the Table1 values as I built my own test data, and I know there aren't any. Further, once I transport the code (when/if I ever get it working the way I intend) to the actual data, those course codes will be pulled from a larger database that disallows spaces anyhow.

 

 

I am pretty sure that, in addition to the code you guys have helped me put in place to populate the title field in the output table, that I will also need to add some sort of code so that value appears in the Title field of the form while filling it out.

 

I'll play around with that bit some more to see if I can figure out how to do that. Thanks again for all of the time and effort. If you have any ideas on how to make the title value appear on the form, let me know.