By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Help with form-combo boxes populating other fields.

P: 5
I am trying to create a from that the user can select a street from a combo box (named street), then pick a second street from another combo box, containing only valid cross streets(named Cross). When the second combo box is completed I want a third control, a text box (named ID)to be filled in with a id for that intersection.

I have a table that contains the fields:
Fr--street name
To--cross street name
Node--The unique id for the intersection (this is only unique in that it describes a single point in space, not a unique record)

This table contains duplicates, i.e.:
Fr ______________To _____________ Node
8 ST NW ________2 AVE NW________101
2 AVE NW________8 ST SW __________101

It is possible that more than 2 records could have the same Node
I do not maintain the database that the table is created from and cannot change the design of the table in any way; I can however run any queries, or make tables to manipulate the data.

I have a form and am able to get the first combo box (Street) to work, and the second combo box (cross) to provide a list of only valid cross streets.

The problem is I cannot get the third control (ID) to populate with the value for node.
The closest I can get is
Expand|Select|Wrap|Line Numbers
  1. Private Sub cross_AfterUpdate()
  2. id = "select node from intlist where street.value=fr and cross.value=to"
  3. End Sub 
This only populates the ID field with a direct quote of the sql statement.

I have no idea where to go from here
Any help would be greatly appreciated.

I am working with access 2003 and windows xp professional.
Jul 19 '07 #1
Share this Question
Share on Google+
5 Replies

Scott Price
Expert 100+
P: 1,384
A couple of questions, what are the names of your combo boxes that choose the street and cross? One naming convention that makes it (MUCH) easier to write code is to prefix these with cbo...

It looks like in your SQL statement you are transposing the values that you are needing to use as criteria: i.e. fr needs to be equal to the value of your combo box street, not the other way around, also a little syntax correction is called for.

First of all you should to assign the value of your SQL statement to a variable first, instead of directly to the value of the combo box. Try changing this:
Expand|Select|Wrap|Line Numbers
  1.          Private Sub cross_AfterUpdate()
  2.          id = "select node from intlist where street.value=fr and cross.value=to"
  3.          End Sub
State your variables at the top of the code window immediately under Option Explicit...
Expand|Select|Wrap|Line Numbers
  1. Private Const strSQL1 = "SELECT node" & " FROM intlist WHERE fr = ' "
  2. Private Const strSQL2 = " ' AND to = "
  3. Private strSQL As String
Then in your AfterUpdate event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cross_AfterUpdate()
  2. strSQL = strSQL1 & Me!street.Value & strSQL2 & Me!cross.Value
  3. Me!id.Value = strSQL
  4. Me!id.Requery
  5. End Sub
You may not need the Requery method at the end, but try it first with, then if it throws up, comment that part out by prefixing with an '

Hope this helps!

Jul 19 '07 #2

Scott Price
Expert 100+
P: 1,384
Something urgent has come up here, and I won't be able to get back to this thread until at least Monday. If you have more problems, post back here and hopefully one of the others will be able to take it from there.

I'll try to check in on Monday and see the progress. If you haven't had any more problems, and the fix has worked, please post here to let me know!

Jul 19 '07 #3

P: 5

I took your suggestion and changed the names of the combo boxes to be prefixed by “cbo”. I am just learning VBA and SQL but I think I understand everything..

“It looks like in your SQL statement you are transposing the values that you are needing to use as criteria: i.e. fr needs to be equal to the value of your combo box street, not the other way around, also a little syntax correction is called for.”

So I need to list the field in my table first then the value that I want to compare it to.

I understand what you are saying about using variables, and changed the code to what you posted, now when I select values in the combo boxes I get something like this:
“select node from intlist where fr=1 AVE NE and to=1 ST NE”

I was able to get something like this before, however not with the street names listed, but I still need to get the actual value for the node.

Thank you for your help
Jul 19 '07 #4

Scott Price
Expert 100+
P: 1,384
Hello, I've just snatched a few minutes to work on this.

I set up in my test database a table called tblIntList with this structure:
Expand|Select|Wrap|Line Numbers
  1. IntID  AutoNumber PK
  2. iTo  Text
  3. iFrom  Text
  4. Node  Number
I then created a form based on this table. In the detail section of the form I left one text box visible called Node. The other text boxes with IntID, iTo and iFrom I made invisible (the last two weren't necessary for my purposes but may be for yours). I then created a select query called qryIntersection. In this query I chose to display the field IntID, then in the next field I gave it a custom name by entering the following: Intersection: [iTo] & ", " & [iFrom]

In the header section of this form I created a combo box called cboIntersection. I left this box unbound, but the Row Source field I set to qryIntersection... I made it have 2 columns, the column sizes are 0",2", the bound column is 1, and the Limit to List is set to Yes.

Then in the AfterUpdate event of this combobox, I wrote this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboIntersection_AfterUpdate()
  2. IntTo = Me!cboIntersection.Value
  3. DoCmd.GoToRecord , , acGoTo, IntTo
  4. End Sub
This worked for me making the Node text box show up the value for the corresponding intersection.

I'm sorry that I won't be able to check back in on this Monday, I won't be back to my computer until probably Tuesday evening. Thought I'd throw this at you to see if it could help...

Jul 22 '07 #5

Scott Price
Expert 100+
P: 1,384
BTW "To" is a reserved word in Access and Visual Basic, you should rename your field to something different so as to avoid confusing the poor computer :-)
Jul 22 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.