Help with form-combo boxes populating other fields.

[a Wellner]

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 ______________T o _____________ 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

Private Sub cross_AfterUpdate()
id = "select node from intlist where street.value=fr and cross.value=to"
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.

[Scott Price]

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:

         Private Sub cross_AfterUpdate()
         id = "select node from intlist where street.value=fr and cross.value=to"
         End Sub

State your variables at the top of the code window immediately under Option Explicit...

Private Const strSQL1 = "SELECT node" & " FROM intlist WHERE fr = ' "
Private Const strSQL2 = " ' AND to = "
Private strSQL As String

Then in your AfterUpdate event:

Private Sub cross_AfterUpdate()
strSQL = strSQL1 & Me!street.Value & strSQL2 & Me!cross.Value
Me!id.Value = strSQL
Me!id.Requery
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!

Regards,
Scott

[Scott Price]

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!

Thanks,
Scott

[a Wellner]

Ok

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

[Scott Price]

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:

IntID  AutoNumber PK
iTo  Text
iFrom  Text
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:

Private Sub cboIntersection_AfterUpdate()
IntTo = Me!cboIntersection.Value
DoCmd.GoToRecord , , acGoTo, IntTo
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...

Regards,
Scott

[Scott Price]

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