2 Combo Boxes For 2 Related Values - Make Vise Versa Data

[majddelbany]

Hello,
i am making a taxi booking system on microsoft access.
i am having 2 problems :

- Combo Boxes problem :

I have a clients table, which contains the client name, number and address.
in the order form, i have name,number,address,from to, price.
i added 2 combo boxes, one to search a client by his name and to fill the data above, and another one to search a client by phone number and fill the data above.
i tried using this code but it didn't work on the other combobox and vise versa, ( namebx = clientname.column(1) )

- Vise Versa problem :

Also i created a table to fill the locations, with 2 columns, one is From - To and the second is Price.
in order to not make the user enter the data twice, for example :

From - To : Beirut - Zahle
Price : 50$
and then
From - To : Zahle - Beirut
Price : 50$

i want him to be able to type the data vise versa, in the Orders form.

Kindly find attached 2 screenshots for more informations.
Thank you so much,


Majd Delbany

[June7]

Hard to analyze this issue from images. If you want to provide db for analysis, follow instructions at bottom of my post.

Used that code where - VBA?

[isladogs]

To manage the vice versa issue, create a function similar to the following using Mid & Left functions:

Function ViceVersa(strTest) As String

    ViceVersa = Trim(Mid(strTest, InStr(strTest, "-") + 1) & " - " & Left(strTest, InStr(strTest, "-") - 1))
   
End Function

or more simply, use the Split function

 Function ViceVersa(strTest) As String

    ViceVersa = Trim(Split(strTest, "-")(1) & " - " & Split(strTest, "-")(0))
   
End Function

Example usage:

ViceVersa("Basle - Beirut")

Beirut - Basle

Then on your form have a Vice Versa button with code to run the function on selected data

The issue you may face is if your place name includes hyphens e.g. Weston-super-Mare
If that is possible, the function will need a minor tweak e.g.

ViceVersa = Trim(Split(strTest, " - ")(1) & " - " & Split(strTest, " - ")(0))

Recommend you remove spaces & special characters from field names - use FromTo instead of From - To

[mike60smart]

Hi

Your table structures are wrong seeing as you have "name, number and address" in Both Client and Order Form?

Can you upload a zipped copy of the Db?

[accesstos]

Hi Majd!

You can set the filter of the form depending on the values of the comboboxes as seems below:

Private Sub cboName_AfterUpdate()
    Me.cboPhone = Null
    FindClient
End Sub

Private Sub cboPhone_AfterUpdate()
    Me.cboName = Null
    FindClient
End Sub

Private Sub FindClient()
    Dim strFilter As String
    
    With Me
        With .cboName
            If Not IsNull(.value) Then
                strFilter = "[Client Name]='" & .value & "'"
            End If
        End With
        With .cboPhone
            If Not IsNull(.value) Then
                strFilter = "[Phone Number]='" & .value & "'"
            End If
        End With
        .Filter = strFilter
        .FilterOn = (Len(strFilter) > 0)
    End With
End Sub

For the "Vise Versa problem", I think that you have to redesign your database. You will face many problems keeping the From-To information in a single field. If you have a table for all posible routings, with a field for the "From" and a field for the "To" (and other relevant fields), it's easy to build the direction string for a label in the order form, like [From] & "-" & [To] and [To] & "-" & [From], or to create a new "Vise-Versa" record with an expression like "[To]=" & Me![From], "[From]=" & Me![To].

Cheers,
John

P.S.:
Do you realy need only the name of the cities for an order? What about addresses, date, time etc?

[majddelbany]

Hello,
First of all thanks to everyone who replied.
I attached a DB sample to explain more. DB Sample.zip

I have 3 tables&forms. Clients ( Name, number, address ) - Directions ( From-to, price ) - Orders ( Name, number, from-to, price, address )

1- I added to Orders 2 combo boxes, one from Clients/Name and one from Clients/Number, the thing that i want is if i want to choose a client by his name, i want Orders/Name,number,address to fill automatically. And if i want to choose a client by his Phone number, i want the same result too. ( sometimes people call to order a cab, sometimes they say their name and sometimes their number, so i want to have the 2 options to fill the data )

2- In Orders, i have From-To ( which is a combobox from Directions ) and Price, ( of course i can easily fill the price from the event of From-To, After Update, Price = from_to.column(2) ). But that's not my issue, sometimes i have data entered as Zahle-Beirut ( in the From-To ), sometimes i have an order from Beirut-Zahle, so all i want is a solution so when i enter a data Zahle-Beirut, it automatically enters another data as Beirut-Zahle with the same pricing.

Thank you some much for your help, i am trying to learn as much as possible..
Majd Delbany

[accesstos]

Hi Majd!

I get this error message trying to open the archive:
"The archive is either in unknown format or damaged"

Try to attach your sample here in .zip format.
Follow this link if you need help:
https://www.accessforums.net/showthread.php?t=70301

About the comboboxes, for the current structure, just set the value of each textbox to the corresponding column of the combobox. For example:

Private Sub cboName_AfterUpdate()
    Me.cboPhone = Null
    Me![Client Name] = me.cboName
    Me![Phone Number] = me.cboName.column(1) 'Hidden
    Me![Client Address] = me.cboName.column(2)  'Hidden
End Sub

Private Sub cboPhone_AfterUpdate()
    Me.cboName= Null
    Me![Client Name] = me.cboPhone.column(0) 'Hidden
    Me![Phone Number] = me.cboName.column(1)
    Me![Client Address] = me.cboName.column(2) 'Hidden
End Sub

Cheers,
John

[isladogs]

The zip file opens for me.
I agree with John that this needs a major redesign. For example:

As I said before you shouldn't use special characters in field names. Use FromTo without the hyphen.
Also the Orders table includes a table level lookup for the FromTo field. This is also a bad idea as it masks the fact that what you are storing is the ID value rather than the text. All code and queries will need to use the ID value
However I also recommend you split the field so you have From and To separately

You could also add a YesNo field for the Out or Return journey.

PhoneNumber incorrectly spelt. Suggest you use text so you can include area code e.g. 1234 9876543

In your Orders form, use an after update event to enter the relevant price based on the combo.

There's more I could suggest but see whether you are happy with the above.
I'll leave you in the capable hand of John (accesstos)

[mike60smart]

Hi

The zip opens for me also.

The attached covers most of what Colin suggested.

[ArviLaanemets]

About vice-versa problem:
Unless you have some predefined delimiter in <From To> field, how will you handle e.g. trip from Los Angeles to Alambra. And mark, you can't use anything like "Los Angles - Alambra" - there for sure exist location names with "-" in it! And locations which consist 3 or more words too!

[isladogs]

@Arvil
I gave two solutions for the vice versa problem in post #2.
Both work for your example if using a hyphen and could easily be adapted to test for 'to' instead.

However splitting into From and To fields is a better option

@Mike
Thanks for doing that. I ran out of time earlier

[accesstos]

Hi all!

Well Majd, as the other members says too, you have to recreate your database in a new base.
Your project is very intresting to me because I have to create a similar for my brother. So it was my pleasure and very usefull for me to prepaire a sample database on your requirements as suggestion for this thread.

In the attached database you will see the capabilities of the data independence and in it's queries you will find what you can do if you keep your informations in separate fields.

Make some orders and inspect the queries to see how they works.
The heart of the form is the combobox "From-To" which keeps all the nessesary informations in it's hidden columns.

I hope that helps you and I wish you the best for your project.

Cheers,
John

P.S.: Colin, thank's a lot for the promotion!

[isladogs]

P.S.: Colin, thank's a lot for the promotion!

????
Do you mean the 'capable hand(s)' comment?

[majddelbany]

Hey, thanks again to everyone who replied

thank you John, it worked for the phone/client ( 2 comboboxes ), the problem was to put the second combobox as Null. Thanks again it worked.

Also thanks all for taking a part of your time and rework my sample database

About the From/to, yes i tried putting them in seperate fields, but i had a problem with using them as a single combobox, and also updating the Price in the Orders form which is connected to From/To.
That's why i tried connecting the fields and put it From-To which helped me to get the Data for the Price.
The problem is the vice versa option to create automatically a new record but the locations are vise versa, also in our country there are no Locations names that contain "-" or "e.g." or spacing

[accesstos]

Hey, thanks again to everyone who replied

thank you John, it worked for the phone/client ( 2 comboboxes ), the problem was to put the second combobox as Null. Thanks again it worked.

Also thanks all for taking a part of your time and rework my sample database

About the From/to, yes i tried putting them in seperate fields, but i had a problem with using them as a single combobox, and also updating the Price in the Orders form which is connected to From/To.
That's why i tried connecting the fields and put it From-To which helped me to get the Data for the Price.
The problem is the vice versa option to create automatically a new record but the locations are vise versa, also in our country there are no Locations names that contain "-" or "e.g." or spacing

Hi Majd! You're welcome!


Inspect the "qryCitiesGrid" query in my sample database (Taxi.accdb). This query creates all possible directions from city to city for one way only. I did fill in the table "tblRouting" using this query.

The "qlkpCitiesRouting" query doubles these directions, inverts the names of the cities of the duplicated record in "Route" field and indicates the duplicated record as "ViseVersa" (-1 as ViseVersa).
So, you have two instances of the same "routting" record to chose from the "From-To" combobox. For example, the Beirut-Tripoli and Tripoli-Beirut, is the same record into the table "tblRouting" (rID=1) with the same price etc. The second instance is indicated as ViseVersa. That's all.

For the returning procedure, there is the checkbox "Ale Retour" (sorry for the bad spelling, in Greek is αλέ-ρετούρ) which makes the code to copy the corresponding price (rAleRetourPrice) from the table "tblRouting" into the "Price" field of the new order. So, you have a "From-To-From" direction, with a specific price, in one action.
If you want to create "automatically" a new record with the same routting but in "Vise-Versa" direction of the selected, you only have to execude the same "INSERT INTO" command, with the same values, reversing only the "ViseVersa" ( e.g. ViseVersa= NOT ViseVersa), and of course with new pickup date and time. So easy! Keep in mind that the "ViseVersa" signification is only a concept in human's mind. In the real world, there is no difference for the road and the record. Those are always the same things.

Add some orders and open the "qryOrders" query. Change the ViseVersa value and see the results in the string fields.

I hope that I am clear enough, and that this mini database is a good source for inspiration.

Cheers,
John

P.S.:

????
Do you mean the 'capable hand(s)' comment?

Yes Colin, I responded on that comment.
Forgive me if I don't use always the correct/formal word(s) in my expressions. I speak English better when I speak to computers.
Did I had to use introduction or recommendation?