Help needed by a new member

[Remus]

Lets start by saying I am not a programmer but by modifying examples found in books I have over the past few years built an order processing data base for our business. An expert would think what a mess but it works for ME. The problem being I would like my staff to be able to use it.There is one problem which I can get around but they would not be able to.
The database has two tables (well more than that but its two I am talking about here) One which holds details of 3000 + Roads. The other has House or Firms names and directions. On the order form I have two Combo boxes one to enter the Location (House Name) another for the Road Name. When you enter a House name the Not In List event looks to see if we have delivered to
that location before, if so displays the full address. name,number,road,town in the combo box fed from a query which uses an EXPr to re join the data fields to display as one. If the location has not been used before a yes/no box comes up. If yes a new form opens to enter the details and choose the road/town from the road list.Then you are returned to the order form.
The problem is let say you start to enter "Orchard Cottage" up comes Orchard Cottage,London Rd,Larkfield. Want you want to enter is another Orchard Cottage in say Ditton.
Because you already have one in the list the combo box will not ask if you want to enter a new one. I can get around it by putting an extra letter in the name, then once the order had been finished go to the location table and take the letter out. No good for the Staff as you can see. Shame the rest works fine.
Anyone have an answer ?

Kevin.

 

[FoFa]

Three things, you can always create a confirmation of IS THIS THE PROPER ADDRESS Y/N, but then they get tired of having to answer YES all the time. Better to put a little button by the field that allows them to ADD and address (over ride) when it is required.
OR use a list box on your own form, instead of the current combobox, and have it act as the combobox, but you can have a button to ADD also. If they double click the proper address it can proceed as before.
Well at least a few ideas.

 

[ScottGem]

The way I would do this is to include more info in the combobox. Your Orders table should have ONE field for the HouseID (the primary key for the Houses/Firms table). This is the foreign key. That table should have the RoadID as a foreign key. The RowSource of your combo should look like this:

SELECT HouseID, Housename, RoadName, TownName INNER JOIN tblHouses.RoadID = tblRoads.RoadID
FROM tblHouses, tblRoads
ORDER BY Housename;

The Bound column would 1, the Column Count: 4 and the Column Widths:
0";1";1";1"

When the user pulls down the combo they would see
Orchard Cottage London Rd Larkfield
Orchard Cottage Any Rd Ditton

They would then know which one to select or whether to press a button to open the add House form.

 

[Remus]

Thanks go to FoFa and Scottgem for your help. You have given me some ideas to work with.
Expect I will be back at sometime with other questions !

Regards Kevin.