Adding All to combo box

[Rachael]

Hi guys,

I am impressed today to see that my post has sparked some interest. Since yesterday I have come up with a solution that seems to be working, now, I don't know how technically sound it is but so far so good.

Here's what I did:

I created an unbound field on a form and used the Browse() function from the relink on startup module that is found in the solutions database.

Then I made another button on the form and put this code in the on click event:

Private Sub cmdLinkNew_Click()
On Error GoTo Err_ChangeLinks

Dim DB As DAO.Database
Dim td As DAO.TableDef
Set DB = CurrentDb

For Each td In DB.TableDefs
If Len(td.Connect) > 0 Then
td.Connect = ";DATABASE=" & Trim([Forms]![frmChangeLink]![NewFileName])
td.RefreshLink
End If
Next

MsgBox "You have successfully connected to a new data file.", vbInformation
DoCmd.Close acForm, Me.Name
DoCmd.Close acForm, [Forms]![Menu].Name
DoCmd.OpenForm "Menu"

Exit_ChangeLinks:
Exit Sub

Err_ChangeLinks:
MsgBox "The data file you have chosen is not appropriate! Please choose a different data file name.", vbInformation
Me.NewFileName.SetFocus
Resume Exit_ChangeLinks
End Sub

It does seem to work well, although I don't fully understand all the code commands and reasons for them, I have begged, borrowed and stole code form all over the place to get to this point.

What do you think? I am very interested to hear your feedback.

Rachael

 

[Rachael]

By the way, I forgot to mention that I used the common dialog control, thought this would be useful for others in the future who have the same problem and find this post, I know I use the search function all the time and only post after lots of searching.

Cheers, Rachael

 

[Jack Cowley]

Rachael -

If it works it is technically sound! At least that is my philosophy!

I am going to try your code because the code I came up with is much more complex.

If you are connected to BackendA and use your code to connect to BackendB (same tables, different data) it it all works? And if you select BackendC it switchs to those tables? Do not get me wrong, I am NOT questioning you, I just want to hear you say again that it works!

Well done Rachael! You must have the 'gift'!

Thank you for sharing your code.

Jack

 

[Rachael]

Well Jack, I don't know about having 'the gift', I think it's pure arse actually, but anyway the code does seem to work so far, today I have installed the new frontend on several different computers and it is working fine. So, don't ask me, sometimes things just go your way. Perhaps having less knowledge sometimes means an easier solution, I don't know!!!

Once again Jack and Alexandre, thanks for your help, we all still need pointing in the right direction and thanks to this forum, I'd be lost without it.

Kind Regards,

Rachael

 

[Jack Cowley]

Rachael -

In my limited trials it works a treat! Now I am indebted to you for a very long time! Thanks!

Jack

PS. If lack of knowledge made it easy I would have solved this days ago! You are too modest...

[This message has been edited by Jack Cowley (edited 03-18-2002).]