Cascading combo boxes

[colkas]

Hi

I have a very simple two tables and I have searched for a simple example for this on the web but no luck so far, so i hope someone can help.

I have two tables tableClient and tablesite. These are linked by key fields client.clientid and clientsite.clientsiteid

On a quote entry form I have both of these fields in 2 combo boxes.
When I select a client I only want to see sites that the selected client as.

So for example if I select client1 I would expect to see all client1 sites (could be many).

All exmples on the web etc.... seem far to complex, I ahve tried to adapt a couple but then the combo boxes loose data or something goes wrong...

Could someone please point me in the right direction, I am not to up on programming as you may have guessed

Thanks

 

[boblarson]

colkas:

Can you upload a copy of your database (with bogus data of course)? The reason I ask is that it seems rather odd to have your clientID and ClientSiteID linked. You would normally have ClientID also in your ClientSite table (autonumber in the clients table and Long Inteter in the ClientSites table) and then they would be joined as the link.

See here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

for how to upload a database to the forum.

 

[colkas]

Hi

Not sure if the attachemnt will work as all tables are linked into an MySql database, we are just using access for front end use.... However I have them linked because 1 client can have many sites....

So if in Combo 1 I select Leeds Site in combo 2 I would selct Bradfor Site or Wakefiled site etc......

Thanks

 

[boblarson]

What would help is if you can create an Access backend (just create the file, and then instead of linking the tables, just do an IMPORT but after selecting all of the mySQL tables and BEFORE clicking the OK button to import, click the OPTIONS button on the bottom right of the import dialog and select Definition Only. See this screenshot (and then upload that backend so I can relink to it for the other frontend to work).

 

[colkas]

Hi

Good idea, Ive attached the DB again

Many thanks

 

[boblarson]

Okay, here you go.

1. Look at the row source for the site combo box (I also changed the names of the two combo boxes to be something other than the name of the fields. It is a good idea to get in that habit.

2. Then in the form's On Current event and the client combo box's After Update event I have a requery of the site combo box.


see if that helps you see how it goes.

 

[colkas]

Hi Bob

I just tested your copy and it looks perfect. I will apply this to my main one tomorrow and see how it works, should be ok so long as I put the details in correct.

Many many thanks

 

[colkas]

Hi Bob

I ahve applied it to the main DB, but it appears to ahve knocked out my notinlist event proceudre.

Basically if I typed in any name and it was not in the list I YES/NO message box that came up and asked if I wanted to add a new one or not, then it went to anothe form...

Any ideas how to solve

Thanks

 

[colkas]

Hi Bob

Sorry about this I realsied I had changed the combo box name so of course the event was looking at the old combo box name. I have reapplied the correct name and this is ok.....

However and this is part of another Post I have on here but dont seem to get anywhere, perhaps you could advise....

If I enter a name into the Client cbo and it is not there a YES/No prompt comes up and asks if I want to add a new one. This iw where the porblems come in.

1. I f I say No I would like it to blank out the entry it made in the client and do nothing else...
2. If I say Yes, I want it to go through to the clinet/site form and the user can add the new details in (this works ok) Then when I close the client add form and the quote form is still active it keeps the old details (so if I enetere asgddad, it keeps that there) also the new client does not appear in the Client cbo uneless I close the quote form and then open it again.
So ideally for number 2 I would like it to take out the intial entry, and when client is added, show in the client cbo on the quote form without having to close and open it...

Any ideas and many thhanks for you valuable help

 

[boblarson]

Okay, here's the code for the Not in List event:

Private Sub cboQclientid_NotInList(NewData As String, Response As Integer)
   If MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?") = vbYes Then
        DoCmd.OpenForm "subformclient", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

End Sub

And the code for the On Load event of the other form (and you want to remove the On Open code from that one as well)

Private Sub Form_Load()
 If Me.OpenArgs <> vbNullString Then
        Me.ClientCompanyName = Me.OpenArgs
           Me.formsites.SetFocus
    End If
End Sub

 

[colkas]

Hi Bob

Thanks fo this it worked more or less perfect and I really appreciate all your help.

My last thing on this, not so important, but I think can be a little annoying for a user though

If the User as put an entry into the client field in the quote and the YES/NO comes up because it is not there, if the user says no, it keeps the text they enetered there and drops down the combo box..... is there away for it to clear the text entry when clicking No and do nothing else......

Other than that liilte hitch it works and looks well, again many thanks

 

[boblarson]

You can change this:

   If MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?") = vbYes Then
        DoCmd.OpenForm "subformclient", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
 
    End If

To this:

   If MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?") = vbYes Then
        DoCmd.OpenForm "subformclient", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
[COLOR=red]        Me.cboQclientid.Undo
[/COLOR] 
    End If

It still drops down the combo but it is at least clear.