Need Help with forms problem

[linuxjr]

I figured one way to do it and I just wanted to see another way for this problem. I got two tables and one table is going to be a migration from a paradox table since upgrading the stuff to Access 2000. The table is Routes which has three columns:

Number, StreetNo, Address

Strange I know but thats how the last one was built then there is a new table Addresses:

Color Type Address

Now the routes table has broken street addresses by putting the number in the first field streetNo while the rest of the address is in the address field in Routes table. The address field in Addresses has the whole street number and street address. I wanted to be able to on a form select Color and Type based on the combination of Streetno + Address to equal the address on the addresses table. So I
thought Simple Solution was a Query

In the query I have Number, StreetNo, Address, Color, Type, Address

Then in the Condition of Addresses.Address I put
Like "*" & [Routes.StreetNo] & " " & [Routes.Address] & "*"

Works like a charm and selects the color and types correctly. Now just trying to figure another way. Say the forms were built and a lot of work has been put on them.

And the form just has the three fields from Routes. Then we want to add the two fields Color and Type from the addresses and I've tried combo boxes with SQL/TextBoxes and I know its something foolishly simple but any suggestions/tips will be greatly appreciated.

 

[braindead2]

I beleive the simplest solution would be to change your forms record source from the table to the query then all the fields would be avaliable.
Or You can use dlookup() in 2 textboxes.
Good luck