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

[iNow]

Hi Guys (and Gals),

 

I've really got to learn Visual Basic, but haven't had the opportunity yet. I'm struggling through something which is probably painfully simple, but I've not come very far using my google-fu.

 

Here's the deal.

 

 

I've got a Form in Access that we are using to fill out information about our training programs. The data we enter should go to an Output_Table, so I create a Form with Wizard that grabs all of the Fields from the Output_Table.

 

 

Inside the Form, we have a field called Course_Code. The selection here should be pushed to the Output_Table. I have made this a combo box which pulls data from another table called Courses_Table. Courses_Table has two fields, Course_Code and Course_Title.

 

What I want to do is select the Course_Code from the Combo_Box, have it autopopulate the Course_Title field on the form, and also feed that data into the Title field on my Output_Table.

 

 

I've gotten really close trying like 5 different approaches, but just can't seem to get it working. If someone could walk me through how to get this done, that'd be righteous. I know it's got to be painfully simple, but it eludes me, and I'm normally pretty good about figuring out this sort of thing.

 

 

Courses_Table has Course_Code and Course_Title fields.

Output_Table has Course_Code, Course_Title, Vendor_Name, Cost_Dept, and others.

 

The form should allow the user to select the Course_Code from a combo box, automatically populate the course_title, and then allow entry into the other fields, all of the entry fields for each record being stored in the Output_Table.

 

Once I learn how to do this autopopulate thing, I'll be doing a similar link to the Vendor_Name field from our Vendors_Table, but I can probably do that myself once I understand the basic syntax and processes (and where to perform those steps).

 

 

(Bear in mind, I've used Access like twice before, and I just tend to figure it out as I go).

Edited December 18, 2008 by iNow
it would help if I typed "Form" instead of "From"

[truedeity]

I am using Access 2003. However, the same method should apply in Access 2007 and earlier versions as well.

 

In the Form Design, right click the Combo box and select "Build Event". If a message appears select VBA (code), It does not appear for me. If it does for you, it should be the last selection on the list. Moving on,

 

You will see code module created for your combobox. Which looks like this,

 

Private Sub Combo0_BeforeUpdate(Cancel As Integer)

 

End Sub

 

Here is the exact code you will need to accomplish your task, paste it inside of the sub function.

 

Dim db As Database, strSQL As String

Dim rs As Recordset

 

Set db = CurrentDb

 

strSQL = "SELECT Courses_Table.Course_title " & _

"FROM Courses_Table " & _

"WHERE (((Courses_Table.Course_code)=" & Combo0.Value & "));"

 

Set rs = db.OpenRecordset(strSQL)

 

While Not rs.EOF

 

Text2.Value = CStr(rs.Fields(0))

 

rs.MoveNext

 

Wend

 

 

rs.Close

db.Close

Set db = Nothing

 

 

On the Form I created, I selected the ID from the combobox, and the title populated the TextBox. I think there is infact an easier method, however, its been a few years since i played with access.

 

Hope this helps, let me know if you have any other questions.

[iNow]

Well, first of all, TD, thank you very much for taking the time to work through the example like you did. I was glad to see you response, and also glad to see your desire to help, so huge kudos on both fronts to you.

 

I put your code into my form (although, since I didn't provide the readers of this forum with exact table/field names, I edited those to match what I needed, as I'm at least capable of that bit on my own), but when I tried to test it and selected my first record from the combo box, an error occurred.

 

The statement was "Run-time error '3061'. Too few parameters. Expected 2"

The debugger sent me to this line:

 

Set rs = db.OpenRecordset(strSQL)

 

...which appears above immediately after your SELECT statement.

 

 

If I interpret it correctly, you've opened a temp record set (as rs), and you are trying to populate something there. Either way, it looks like "not enough somethings" were part of the Set statement.

 

Any ideas on what I can try?

[doG]

The statement was "Run-time error '3061'. Too few parameters. Expected 2"

The debugger sent me to this line:

 

Set rs = db.OpenRecordset(strSQL)

 

...which appears above immediately after your SELECT statement.

 

This may have something to do with your version of Access. Try changing that line to "Set rs = db.OpenRecordset(strSQL,dbOpenDynaset)" The type argument should be optional but might not be in later versions of Access. At some point they switched from DAO to ADO and the behavior of some of the older DAO methods changed.

[iNow]

Thanks for the idea, but I got the same 3061 error with that new statement.

 

 

Please note, everyone, I'm perfectly willing to try/learn a different approach to accomplish this if appropriate. I went into this thinking it would be much simpler than it is, but I'm open to basic tutorials and attempts at working through visual basic (with a little bit of guidance on what the various parameters and statements do).

 

 

EDIT: Btw - Access 2003

 

 

[truedeity]

Try typing "set rs = db.OpenRecordset("

 

A caption should appear that shows a list of the parameters; can you screencap it for me?

 

Also, if you put the carrot cursor inside the OpenRecordset text and press F1, a helpfile will appear. Which will have pertinent information pertaining to the usage of OpenRecordset. Send any information across that could help.

 

Thanks for Kudos man.

[iNow]

Try typing "set rs = db.OpenRecordset("

 

A caption should appear that shows a list of the parameters; can you screencap it for me?

 

Okay, so I typed:

set rs = db.OpenRecordset(

and the following caption appeared:

OpenRecordset([b]Name As String[/b],[Type],[Options],[LockEdit] As Recordset

 

I played around a bit, and put the word "String" into the Type field, since that's how we declared strSQL earlier in the Event, but that didn't work either [it threw a Compiler Error, said it expected an open parentheses like ( ] ...Also, Options and LockEdit seem to be optional/non-required fields.

[truedeity]

I am 100% that the error is in SQL string.

 

strSQL = "SELECT Courses_Table.Course_title " & _

"FROM Courses_Table " & _

"WHERE (((Courses_Table.Course_code)=" & Combo0.Value & "));"

 

change Combo0 to the name of your combobox on the form. the name of your combobox is on the function sub. Private Sub Combo0_BeforeUpdate(Cancel As Integer)

 

 

the name of your textbox for the title may also throw an error, double check the textbox name by selecting it and pressing f4 on the form.

 

Text2.Value = CStr(rs.Fields(0))

[iNow]

change Combo0 to the name of your combobox on the form. the name of your combobox is on the function sub. Private Sub Combo0_BeforeUpdate(Cancel As Integer)

Yeah, I did that immediately after pasting your code... went through and changed the variable names to match those in my dbase. I confirmed that my combo box name in the WHERE clause of the SELECT statement matches that shown in the function sub.

 

Still getting the same error, and the debugger takes it to that same line.

Set rs = db.OpenRecordset(strSQL)

 

 

 

the name of your textbox for the title may also throw an error, double check the textbox name by selecting it and pressing f4 on the form.

 

Text2.Value = CStr(rs.Fields(0))

That one is changed, too, but we haven't gotten that far yet. The error seems to come a couple of lines before the Text2.Value = .... statement.

 

 

 

 

 

---

line[/hr]

 

Okay. I'm not feeling this approach. I am not confident that I'll be able to replicate this on my own later.

 

 

What I want is something clean and simple. The logic as I envision it would be to program the Text box using some SQL code like this:

 

SELECT C.Title
FROM Courses_Table AS C
WHERE C.Code = Combo_Box.Value

 

 

Can't this be done in Access somehow? First, I wouldn't know where to put such code to make the text box function that way, and two, I'm not sure how to identify the combo_box.value.

Edited December 19, 2008 by iNow
multiple post merged

[truedeity]

Can you post your SQL string?

 

Yes, my first approach was to see if you can program the textbox in the same manner. However, I could not replicate. I think, its because the property of the textbox does not allow you to obtain infromation from objects on the Form. Ie. ComboBox. But just incase, try this WHERE C.Code = [FormName]!Combo_Box!Value instead. In the textbox properties. Select textbox, press f4, there should be a value property or something along those lines. However, If I remember access correctly, I do not think this works. The only way to do is is though VBA code.

 

Actually, I used to work at an accounts recieveable comany called Mirrus Systems a few years ago I was an Access Programmer, so from experience I am certain that the problem you are having is the SQL statment itself. Often times in programming the debugger will throw an error, but error is in the line above. Like in C# when you forget semicolins...

 

Private Sub Combo0_BeforeUpdate(Cancel As Integer)

On error goto ErrorHandler:

 

[ALL YOUR CODE HERE]

 

 

Exit sub

ErrorHandler:

 

YourTxtBox.Value = strSQL

 

MsgBox Err.Description

 

End Sub

 

 

Now copy the SQL in the textbox and paste it here. I suspect, that your SQL needs a space at the end of each line.

 

You may want to also test the SQL by creating a new Query, access will default to sql design mode, but in the upper right corner you can select SQL and paste the code itself. Then try to run the query. If it doesnt execute, that is the problem.

 

Infact, if you have AIM just hit me up my sn is 'hackintoit'... Perhaps, I can remotely assist you with TightVNC?

 

oh i just realized, easier than creating a textbox on the form, I think you can just use DoCmd.Copy strSQL

 

should copy it to the clipboard. then you can paste.

[doG]

Now copy the SQL in the textbox and paste it here. I suspect, that your SQL needs a space at the end of each line.

 

An alternative is putting all of the SQL statement on one line without breaking it up. Try

 

strSQL = "SELECT Courses_Table.Course_title FROM Courses_Table WHERE (((Courses_Table.Course_code)=" & Combo0.Value & "));"

[iNow]

Truedeity - Again, thanks. You've been a great help. One of the problems I was encountering when googling was not knowing which commands might help me with this. Using your code as examples, I have been working a bit to learn about these specific commands and statements.

 

Before, I was sort of shining a candle in the dark, but now I'm using a flashlight to look at specific spots. That's much nicer, so really... thanks.

 

However, I'm definitely in the "I want to figure this out for myself" club, so I'm trying to avoid just copying/pasting as much as I can (if that makes any sense).

 

 

An alternative is putting all of the SQL statement on one line without breaking it up. Try

 

strSQL = "SELECT Courses_Table.Course_title FROM Courses_Table WHERE (((Courses_Table.Course_code)=" & Combo0.Value & "));"

 

Okay. I did that, but then got a 3061 error like I mentioned above, except this time instead of "Expected 2" it said "Expected 1."

 

 

So, I have the combo box, and I right-clicked and selected "Build Event..." Here's what my code looks like right now, strSQL statement included, with actual field names:

 

Private Sub cboCode_BeforeUpdate(Cancel As Integer)
Dim db As Database, strSQL As String
Dim rs As recordset

Set db = CurrentDb

strSQL = "SELECT Table1.title FROM Table1 WHERE (((Table1.code)=" & cboCode.Value & "));"

Set rs = db.OpenRecordset(strSQL)

While Not rs.EOF

Title.Value = CStr(rs.Fields(0))

rs.MoveNext

Wend


rs.Close
db.Close
Set db = Nothing

End Sub

 

I basically created a dummy table called "Table1" which only has fields "Code" and "Title." I wanted to keep it simple, so sample values in the rows would be Code = Code1 and Title = TitleONE, or Code = Code2 and Title = TitleTWO, and so on.

 

This way, it will be easier to see what is happening, and I could then later replicate whatever we come up with into the actual table.

 

This is basically a side project for me, where I'm actually trying to help out a friend in management in another group. He's been working on getting a database like this setup for months, and I've been consulting him here and there, but I work on a different project so haven't had much time to devote to helping. So, this week we met, and I am now upset with myself for not being able to figure out this puzzle. I'm normally so good at puzzles!

 

Point being... no biggie if we don't solve it, but I'd sure like to know how to do this, as it really seems like it should be incredibly simple. Cheers.

[doG]

In the left margin of the code window click next to the beginning of the subroutine and a red icon should appear indicating a breakpoint. This will halt the code from executing as soon as the subroutine begins. With the code halted you can execute the code one line at a time with the F8 key. Step through to the line strSQL = .... but not past it. Pause your mouse pointer over the cboCode.Value in that line and a tooltip should pop up and show you the current value. That way you'll know it's passing the variable you need.

 

F8 to the next line and see what happens. Then some more. You can pause your mouse over strSQL to see the actual SQL string. Sometimes this will reveal what is happening.

[iNow]

In the left margin of the code window click next to the beginning of the subroutine and a red icon should appear indicating a breakpoint. This will halt the code from executing as soon as the subroutine begins. With the code halted you can execute the code one line at a time with the F8 key. Step through to the line strSQL = .... but not past it. Pause your mouse pointer over the cboCode.Value in that line and a tooltip should pop up and show you the current value. That way you'll know it's passing the variable you need.

Sweet tip! I like that style of thinking. Tackling the problem one line at a time is... well, thanks for showing me how to do that.

 

So, I paused my mouse over cboCode.Value and it said:

 

cboCode.Value = "TitleONE"

...which (I think) is good since I selected Code1 (but, I may be misinterpreting this, and perhaps it should actually be showing "cboCode.Value = "Code1"???).

 

 

 

F8 to the next line and see what happens. Then some more.

Okay. The next line (Set rs = ...) didn't throw an error, but when I hit F8 again, I got a new error at the next line (While Not rs.EOF)... (and, if I interpret the comments above from you and TD correctly, this new error actally refers to the "Set rs =" statement (since it is listed immediately before where the error presented), but I'm not willing to wager any money on that assumption).

 

 

The error which appeared seems to have come from the line:

While Not rs.EOF

, but may also have come from the line (based on my comments just now):

Set rs = db.OpenRecordset(strSQL)

. It was, however, a new error.

 

Here's what the error says:

 

[color="Red"]Run-time error 3075:
Missing ),], or Item in query expression '(((Table1.code)=TitleONE'.[/color]

 

 

So, I played around a bit, and I wasn't able to determine where to put a closed parentheses

)

.

 

Also, I noticed that it's populating cboCode.Value as "TitleONE" in this statement:

WHERE (((Table1.code)=" & cboCode.Value

, but that it looks like it's missing a single apostrophe before the value:

[color="Red"]Run-time error 3075:
Missing ),], or Item in query expression '(((Table1.code)=TitleONE'.[/color]

(((Table1.code)=[color="Blue"][why no single apostrophe here?][/color]TitleONE'

[doG]

Try changing your SQL line to:

 

strSQL = "SELECT Courses_Table.Course_title FROM Courses_Table WHERE (((Courses_Table.Course_code)=[color=#CC0000]'[/color]" & Combo0.Value & "[color=#CC0000]'[/color]));"

 

so that the value passed to the WHERE clause in enclosed in single quotes.

[iNow]

Yeah, no luck there. Also, when doing this change, I realized that last night I may have been misinterpreting the error message.

 

It was not trying to enclose the TitleONE value into single quotes. The error message was ending a quote which began after the word "expression."

 

 

Run-time error 3075:
Missing ),], or Item in query expression '(((Table1.code)=TitleONE'.

 

 

Anyway, I'm reminded why I don't use Access. This is ridiculous.

 

I have been playing around some, and realized that I might be better trying to populate a list box instead of a text box. The text box doesn't have a option to change RowSource, whereas the List box does.

 

So, I changed my title field on the form to a list box, and now I'm trying to find a way to get my RowSource code correct.

 

I've entered

SELECT Title From Table1 Where Table1.Code = cboCode.Value

, where cboCode is the name of my combo box. I've not had luck yet, but sense this is the better approach than text box.

[Pangloss]

Have you tried square brackets around all your column names in your queries? I dimly recall something about Access and simulated-spaces using underscores in the column names. (Normally square brackets are only required if there's a space in the name, but I'm thinking it could be a problem related to the translation of string text through the VB-to-Access interface, e.g. it loses the underscore along the way, possibly producing the error we see here.)

 

Apologies if that's just completely off.

[iNow]

No, it's not a bad thought at all, but I keep getting an error when I try.

 

 

[Cap'n Refsmmat]

Try this:

 

SELECT Title FROM Table1 WHERE '" & cboCode.value &"' = '" & Table1.Code & "'

 

or something like that to make it concatenate.

 

(result of a quick google search)

[iNow]

Try this:

 

SELECT Title FROM Table1 WHERE '" & cboCode.value &"' = '" & Table1.Code & "'

Okay, that made the error go away. Now, when I select the dropdown menu on the combo box, nothing else happens. The title field stays blank.

 

EDIT - Never mind. Had to set the Bound column = 1 (it was set to 2 previously).

 

 

Now, the title field does populate with a title value, but no matter what selection I make in the combo box, it always shows TitleONE. (even if I select Code2 or Code3, it doesn't change to TitleTWO or TitleTHREE, just remains as TitleONE)

 

EDIT2: Okay, so I closed the form, deleted my output table values to start and test from scratch, and now selecting the code value in the combo box doesn't do anything (i.e. The title field remains blank).

 

EDIT3: If I change the Bound column in my Title box to "0," it again shows TitleONE regardless of selection....

 

EDIT4: Never mind. I closed the form, emptied the contents of the output table, and reopened the form to test again... the Title field isn't populating at all, even with BoundColumn in Title field = 0

Edited December 20, 2008 by iNow
multiple post merged

[Pangloss]

Wups, definitely don't put square brackets around variables -- they're only for use when you actually write a query in a text string. Sorry that I wasn't very clear on that.

 

I'm not fully read-up on this thread, but there is a potential problem with this code snippet:

 

SELECT Title FROM Table1 WHERE '" & cboCode.value &"' = '" & Table1.Code & "'

 

This is one of those really confusing areas of VB. It's close but you don't actually need single quotes for this. Here is the correct approach:

 

myVariable = "SELECT [Title] FROM Table1 WHERE " & cboCode.Value & " = " & Table1.Code

 

Note the LACK of quotation marks at the end. In plain english, the string initially ends after the space following the word WHERE. The ampersand then appends the variable cboCode.Value. Then another ampersand appends " = " (a space, an equals sign, and another space), after which another ampersand appends the final variable (Table1.Code). There's no need for a quotation mark on the end because we've already closed the string. Make sense?

 

In other words it breaks down like this:

myVariable = "SELECT [Title] FROM Table1 WHERE " & cboCode.value

 

That's not a valid query, of course, but it would be a valid concatenated string to the VB compiler. The rest of the statement is just attaching a comparison to the above:

 

 & " = " & Table1.Code

 

That tells the Access engine to compare the two values in the query. See how the quotation marks are actually carrying the space-equals-space string to Access? The rest of the above snippet is actually addressed entirely to the VB compiler for processing. The ampersands are concatenation triggers to VB, not comparison cues to Access.

 

The result of all of this is that Access ends up receiving a query that looks like this:

 

SELECT [Title] FROM Table1 WHERE somenumber = someothernumber

 

Is that what you want? If that's not working you may want to take a look at what cboCode.value is -- is that a "title" value from the database? Because your query is comparing titles -- if cboCode.value and Table1.code.value aren't titles, that's not going to work.

 

Edit: Scanning back over the thread, it looks like DoG touched on this back in post #15 but with some minor issues producing another error. There is a time when you do have to use single quotes to isolate things within a string, but that's not necessary here because we're using the "variable =" approach.

 

Also he may be correct about needing a semi-colon at the end of the SQL statement. I dimly recall Access being a stickler for those (with most SQL you only need it if you're adding a second SQL statement immediately after, in the same transaction), so try adding that to my code above if it doesn't work as writ. If that's the case attach the following to the end of the statement:

 

 & ";"

 

(Sorry if any of the above was overly obvious -- I got overly literal just in case any of this was unclear.)

 

(done editing)

Edited December 21, 2008 by Pangloss

[iNow]

(Sorry if any of the above was overly obvious -- I got overly literal just in case any of this was unclear.)

TBH, none of it was obvious at all, in fact, most of it went over my head. I need to look at it again in the morning. I just got done doing some more work in my master bathroom, and am tired.

 

The thing is, I'm not really a code person (yet). I had a class in HS about 14 years ago where we learned some Pascal. The then took an intro to SQL 3 day class about a year ago, and that's all I got. I do a lot of stuff in databases for work, but it's almost all put together using the graphic editors (and self taught at that).

 

Anyway, I know I'm capable of figuring out coding syntax and languages where needed, but I'm not even sure where to start with Access.

 

Thanks for the post above. I'll review it again after Meet the Press tomorrow morning to see if i can digest it and put it into practice.

 

Cheers.

[iNow]

Make sense?

Okay. It's a fresh new day, and I've had my Sunday morning political round table fix (I've grown to enjoy This Week w/George S more than MTP lately, but have pleased with the new tone and pace offered by David Gregory).

 

So, I've read your comments, and think I understand it. The basis seems to be that you don't pass the actual code to the compiler, but instead work with concatenated snippets of various pieces, which when appended to one another, will be read as the proper code statement. More or less correct?

 

 

So, from a high level view, that makes sense to me. I understand the logic, which helps.

 

Here's where I struggle, though. I don't know Visual Basic, neither the syntax or the commands, nor do I really understand how Access forms tie all of the information together. It's as if I've been taught that an adjective modifies a noun, and that a noun is a person, place, or thing, but when shown either (red or table) I don't recognize it as such. I'm like, "Red?" What's that? Never heard that before."

 

 

So, I tried to update the RowSource field on my title value (the one I want to autopopulate based on the users selection of course_code in the combobox), but nothing happened to the Title listbox when I made my combobox selection of course code. It just sat there.

 

I also tried to do the append semicolon (& ";") statement you suggested at the end of your post, but the results did not change. So, in essence, I'm back to where I started.

 

 

 

Before I opened this thread, the closest approach I had was to set the Course_Code combobox with Bound Column = 2. Then, I changed the ControlSource on my Course_Title box to be the "Code" field (instead of the "Title" field). This worked... It populated the correct title into the Title field based on the code selection in the code combobox. (see attachment)

 

The failure, however, was in the Output Table. By changing the Control Source of my Title field to the Code field, I wound up outputting Title information to the Code field of my Output Table, which also left the Title field in the Output table blank (so, it made the form look correct, but didn't feed the data the way I intended).

 

Like I said in the OP, I know I'm missing something painfully simple, I just don't know what that is. Thanks to each of you who have bothered spending a little time to try pointing me in the right direction. Your contributions are not unappreciated.

 

 

[Pangloss]

Well it sounds like you're close, but there's a hitch somewhere. 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. If they're empty then that tells you something. There are tutorials around the net for this but they're kinda spotty and many are quite dated. I'll look around.

 

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.)

 

I'm not as familiar with VBA as I am with more traditional Windows forms and Web forms, but if you want to send it to me I'll be happy to take a look at it.

[truedeity]

I'm back guys. Sorry been away taking care of some errands. Alright, where are we at? Hmm.. Well, I see alot has been posted, I've been able to follow alittle of it reading though, however, was lost somewhere along the line where there are a few misunderstandings about Access. However, let's start this where I left off at, that being the SQL statment you have.

 

I already have this worked this out on my laptop upstairs, kinda too lazy to go up there right now so I wont be double checking my suggestions in this reply. Anyway, before we get ahead of ourselvs I want to illustrate the misconceptions. Keep in mind as mentioned beforehand that you can use debugging and toggle though the code one line at a time.

 

strSQL = "SELECT Table1.title FROM Table1 WHERE (((Table1.code)=" & cboCode.Value & "));"

 

 

So I'm going to use the sql that was first provided as our working example. So I hope its not too much trouble to backtrack alittle...

 

Ok, first let's make this work. Everything at glance looks alright.

 

In SQL we dont need to use the "." accessor unless we are using Left Joins, Inner Joins, Right Joins etc... The syntax is correct here, just not required in this instance.

 

strSQL = "SELECT Table1.title FROM Table1 WHERE (((Table1.code)=" & cboCode.Value & "));"

 

 

strSQL = "SELECT title FROM Table1 WHERE (((code)=" & cboCode.Value & "));"

 

Removed.

 

Also, It seems the use of ('s and )'s are not required to accomplish the task. Although, the usage in this case looks ok... Lets remove them to be safe.

 

strSQL = "SELECT [title] FROM Table1 WHERE

 =" & cboCode.Value & " ;"

 

That should work. Unless ofcourse 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 [code] LIKE '" & cboCode.Value & "' ;"

Notice the single quote is directly beside the double quote.

 

The brackets may not be required... But access understands brackets, so they are useful for this example.

 

This should work.