I have found this code to add the word "all" to a combo box. I suspect the code has been written for A97 and as I'm using A2000 I can't seem to get it to work. The error I'm getting is a type mismatch when the form with the combo box on it opens. Because this code seems to want the combo box properties for Record source type changed from Table/Query to AddAllToList, this seems to be causing the problem.

Here's the code that I have put in the module of the form with the combo box on it:

Function AddAllToList(C As Control, ID As Long, Row As Long, Col As Long, Code As Integer) As Variant

'************************************************* **************
' FUNCTION: AddAllToList()
'
' PURPOSE:
' Adds "(all)" as the first row of a combo box or list box.
'
' USAGE:
' 1. Create a combo box or list box that displays the data you
' want.
'
' 2. Change the RowSourceType property from "Table/Query" to
' "AddAllToList."
'
' 3. Set the value of the combo box or list box's Tag property to
' the column number in which you want "(all)" to appear.
'
' NOTE: Following the column number in the Tag property, you can
' enter a semicolon ( http://www.access-programmers.co.uk/ubb/wink.gif and then any text you want to appear
' other than the default "all."
'
' For example
'
' Tag: 2;<None>
'
' displays "<None>" in the second column of the list.
'
'************************************************* **************
Static DB As Database, RS As Recordset
Static DISPLAYID As Long
Static DISPLAYCOL As Integer
Static DISPLAYTEXT As String
Dim Semicolon As Integer

On Error GoTo Err_AddAllToList

Select Case Code
Case LB_INITIALIZE
' See if the function is already in use.
If DISPLAYID <> 0 Then
MsgBox "AddAllToList is already in use by another Control! """
AddAllToList = False
Exit Function
End If

' Parse the display column and display text from the Tag
' property.
DISPLAYCOL = 1
DISPLAYTEXT = "(All)"
If Not IsNull(C.Tag) Then
Semicolon = InStr(C.Tag, ";")
If Semicolon = 0 Then
DISPLAYCOL = Val(C.Tag)
Else
DISPLAYCOL = Val(Left(C.Tag, Semicolon - 1))
DISPLAYTEXT = Mid(C.Tag, Semicolon + 1)
End If
End If

' Open the recordset defined in the RowSource property.
Set DB = DBEngine.Workspaces(0).Databases(0)
Set RS = DB.OpenRecordset(C.RowSource, DB_OPEN_SNAPSHOT)

' Record and return the ID for this function.
DISPLAYID = Timer
AddAllToList = DISPLAYID

Case LB_OPEN
AddAllToList = DISPLAYID

Case LB_GETROWCOUNT
' Return the number of rows in the recordset.
RS.MoveLast
AddAllToList = RS.RecordCount + 1

Case LB_GETCOLUMNCOUNT
' Return the number of fields (columns) in the recordset.
AddAllToList = RS.Fields.Count

Case LB_GETCOLUMNWIDTH
AddAllToList = -1

Case LB_GETVALUE
' Are you requesting the first row?
If Row = 0 Then
' Should the column display "(All)"?
If Col = DISPLAYCOL - 1 Then
' If so, return "(All)."
AddAllToList = DISPLAYTEXT
Else
' Otherwise, return NULL.
AddAllToList = Null
End If
Else
' Grab the record and field for the specified row/column.
RS.MoveFirst
RS.Move Row - 1
AddAllToList = RS(Col)
End If
Case LB_END
DISPLAYID = 0
RS.Close
End Select

Bye_AddAllToList:
Exit Function

Err_AddAllToList:
Beep: MsgBox Error$, 16, "AddAllToList"
AddAllToList = False
Resume Bye_AddAllToList
End Function

Thankyou for any replies

Rachael

Take a look here at a slightly simpler way to do the same thing...

http://www.mvps.org/access/forms/frm0043.htm

Good luck!

If you want to use the MS code then modify this line to read like this:

Static DB As DAO.Database, RS As DAO.Recordset

Then open any code page where you see the Debug window and select Tools > References. Search for Microsoft DAO 3.6 Object Library and check it. Now the code should work.

Again, good luck.

Thanks for you reply Jack, you come to my rescue alot!

I can now get the MS code to work, sort of, I don't get the type mismatch message and the (All) appears in the combo box list but it won't stay in the combo box as a selection to be used. I use this selecetion to filter records on the next screen.

Perhaps I should try using the simpler code, what do you think?

Cheers, Rachael

Rachael -

I am all for simple so it won't take long to try the other code. What the heck, give it a go!

I just had a thought (scary!); did you get the correct column number in the Tag property for the combo box?

Have I won a bottle of Rosemount Shiraz?

Jack

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

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

Thanks Jack, sorry to bother you again, I got to this part and understand it but my bound column is the primary key and it would take some rework to modify things to make the bound column not the primary key, so what do I do with the statement below to allow things to work?

Quoted from mvps.org website:
you can then easily add "(All)" as the first choice. Also, if CustomerID is the bound field but it's width is set to zero (so that the user only sees CompanyName), you can store a NULL (if the bound field is not the primary key of the table), or someother value in the bound field.

My statement on the combo box:
SELECT [Group].[GroupID], [Group].[Group Name] FROM [Group] UNION Select Null as AllChoice , "(All)" as Bogus from [Group] ORDER BY [Group].[Group Name];

Thanks, What is your mailing address Jack?

Rachael

Rachael -

Tried to send you an email but it failed. I will work on the code from the site and see if I can get it to work the way that you need it to work.

You may email me directly if you like.

Jack

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

Rachael -

Another question. Are you using the combo box as a method of finding a record and displaying it on a form or is it a used in a query as the criteria for a field?

If you are using it as criteria for a query then do the following:

In the first Criteria field of your Query put code like this in the proper column:

[Forms]![FormName]![ComboBoxName]

Directly below it on the next line put this:

[Forms]![FormName]![ComboBoxName] Is Null

Now when the user selects "ALL" they will see all the records but if they select another field in the combo box they will see only the selected record(s).

Jack

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

Thanks for your effort Jack, I have figured the problem out and the answer is along the lines of your response, its all in getting the query criteria right.

While I have your attention what can you tell me about a frontend/backend situation where I want to be able to break/delete the link from the frontend to the backend tables (you might remember a similar post in the general section). I would like to be able to run a simple bit of code to break the links, then I can use the relinking on startup stuff to browse for a re-establish a link to a new backend.

With my limited knowledge of VBA i think i need something like this but modified to break links as they are detected:

Dim strTest As String, DB As DAO.Database
Dim td As DAO.TableDef
Set DB = CurrentDb
For Each td In DB.TableDefs

If Len(td.Connect) > 0 Then
strTest = Dir(Mid(td.Connect, 11))
DoCmd.OpenForm "frm Password"
If Len(strTest) = 0 Then
If MsgBox("Couldn't find the data file " & Mid(td.Connect, 11) & " Please choose new data file", vbExclamation + vbOKCancel + vbDefaultButton1, "Can't find data file") = vbOK Then
DoCmd.OpenForm "frmNewDataFile"
Exit Sub
End If
End If
End If
Next

This code is the check link code on startup, but think it could be modified to run from a command button to break links then call up the 'browse for new data file' form. Hope this makes sense and is it possible, there might be a Rosemount Shiraz in it for you!

Cheers Rachael

Rachael -

I must admit total ignorance in this area, but why can't you just delete the table(s)? All you are doing is deleting the data that Access uses to link to the table so deleting the table should work. I have NOT tried this but I am willing to give it a go, but not tonight as it has been a long day. Time to get back to my book.

Tomorrow I will look at the code to loop through the tables and see if I can delete them without a lot of smoke or grinding noises coming from the computer.

Thank you for mentioning the Rosemount but I have a more than adequate supply at the moment. That particular brand is getting quite popular here and is readily available and very resonable in price for what I consider a good Shiraz, but what do I know... Does Wyndham produce a better product?

I will let you know the outcome of my experiments.

Jack

Jack,

Its only 2.51 in the afternoon here, so will look forward to your response over the weekend. Enjoy your book!

Back to the problem, deleting the tables form the frontend doesn't activate the relinking code, it simply means the database doesn't work 'cos it can't find the table. My thoughts are that MSysObjects lists the link path for each table, so I need to destroy or corrupt that somehow so that I can then use the code that I posted earlier to detect the lost link and then relink via the relink on startup stuff. Then again, could be barking up the wrong tree too!

I have searched many forums for a solution but sometimes unable to totally understand the code to modify it so that I get the result I want.

The wine, personally I like Barossa Valley Shiraz, they probably are best known in Oz for their shiraz, Peter Lehmann is a good one, Yalumba, or Wolf Blass. The Orlando Wyndham I think you would get over there would be fairly mediochre compared to some of the others, but maybe you can't get them! Let me know and I really will post you a bottle!

Rachael

Rachael -

I am an absolute neophite when it comes to wine but I am trying very hard to learn more and be able to tell the rot-gut from Barossa Valley, which my Sydney friends say is really terrific. It is very sweet of you to offer to send me a bottle but I would not feel right about letting you do that. Somehow it would not seem fair...

Can you not trigger the re-link code from the code that would delete the tables? I really am in over my head here as Access is an avocation and I have never had to work on a real network so this is trial and error for me. I have used the relinking code before but a long time ago and I haven't looked at it since so I will need to go back and look at it again.

I just re-read you statment about relinking and you probably have that right. My interest has been peaked so I am still here but not too much longer!

I forgot, today is Friday for you! Lucky you! Enjoy your weekend and I hope I can find something that will be of use to you.

Jack

Jack,

Thanks, I will leave you alone for the night after this post, no need to reply today, but while you are dreaming about my problem, I just thought I would give you a little background on why I'm trying to do what I'm trying to do.

My database tracks student records ( I work for an education institution teaching Viticulture). I have all sorts of combination of remote access, network access, single users, multiple users that are using the database. Different users have different network access, the remote one's - no network access. The managers of these users want to be able to have access to or get the remote dbs emailed periodically so they can have a lot at the students progress, run some reports etc....so....the problem - I need a reasonably automated solution for these managers to unlink from one backend and relink to a new one. Like automating what us developers would do with the Linked Table Manager but obviuosly the managers would only have a .mde (don't want the one who think they're clever messing with things).

I have the relink on startup stuff working well i.e. if the backend is moved for some reason, or I've sent a new version of the frontend and they want to link to their specific data file. I think you yourself may have pointed me to the relinking stuff some months ago in the knowledge base.

Anyway don't worry know, have a think tommorrow, any little clues are a bonus.

Thankyou very much,

Rachael

Rachael -

Search help for Connect Property (search in the VB window). Click Example and then select RefreshLink method. There I found this line of code:

' Delete linked table because this is a demonstration.
dbsCurrent.TableDefs.Delete tdfLinked.Name

You may find the whole bit of code interesting and I will look at it more in the morning as I am starting to flounder...

Jack

Rachael -

This code removes the links. I could not get it to do it on one pass to save my life so that is the reason for the "counter" and the code at the end do keep looping until all the tables are deleted. The minus 5 in the last line of code is because there are 5 MSys tables that do not get deleted. Anyway, maybe you can get this to be a bit more streamlined but it does work...

Function RemoveLinks()
Dim strTest As String, DB As DAO.Database
Dim td As DAO.TableDef
Set DB = CurrentDb
Dim counter, i As Integer

For Each td In DB.TableDefs
counter = counter + 1
Next td

10:

For Each td In DB.TableDefs
If Len(td.Connect) > 0 Then
DB.TableDefs.Delete td.Name
i = i + 1
End If
Next

If i < counter - 5 Then GoTo 10

End Function

Jack

Thankyou so much for your help Jack,

I haven't tried this yet but will let you know how I get on through the day some time.

Thankyou.

Rachael

Rachael -

You beat me by minutes.... I do not know if what I gave you is what you want to do. The code does remove the links which means there is nothing when you click on "Tables" in the Database Window. The code to refresh links looks for existing links and goes from there. With no links there is nothing there to verify but maybe this is what you want. If the user is going to search for the backend they want and relink then you will be good to go.

I hope I got this right! I just started messing with the code above today as I want to do something similar to what you are doing...

Jack

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

Jack, I am not willing to interfere but i feel like you are embarking onto wild, wild seas (not that it couldn t be a nice experience with such good company as Rachael's and some good Shiraz http://www.access-programmers.co.uk/ubb/wink.gif)

You do not need to delete the tabledefs. It might be dangerous without testing the Connect property which holds information about (namely) the database where the tables are linked from, or alternatively the dbAttachedTable/dbAttachedODBC attribute.
Especially if your front end holds local AND linked tables, because local tables may get deleted.

On the other hand, if you delete the reference to linked tables, you will find difficult to re-link them because you won t have anymore indication on what are the tables that you have to re-link.

You rarely have to really un-link and re-link tables. Just changing the path to the linked tables in the connect property and refreshing the tabledef is generally enough.

Example (would need further development if you have to manage secure back-ends, ODBC tables, or to link to various back-ends..):

Public Function Connect(strBackEndPath As String)

Dim mydb As Database
Dim TBDef As TableDef

Set mydb = DBEngine.Workspaces(0).Databases(0)
For Each TBDef In mydb.TableDefs
With TBDef
If (.Attributes And dbAttachedTable) Then
.Connect = ";DATABASE=" & strBackEndPath
'With ODBC tables you will need to change this connect string
'to contain the relevant details for you ODBC type
.RefreshLink
End If
End With
Next TBDef

Set mydb = Nothing
Set TBDef = Nothing

End Function


Hope this helps

Alex

[This message has been edited by Alexandre (edited 03-17-2002).]

Alex -

You NEVER interfere! Your imput is always welcome so please jump in at any time.

I realized that I was heading into deep waters after I posted my response to Rachael. I have been looking at ways to change the tables that a db is linked to and did find code that deleted the tables then relinked new ones. I was looking for an 'automatic' system so the user could select the backend they wanted and the code would do the rest. I am making headway as this is a new area for me and I haven't gone under for the 3rd time yet!

Basically all I want to do is change the Link from 10 tables to 10 other tables.

Maybe you better give me your holiday address so I can come begging for help if I can't get it sorted! (Just kidding, of course!)

As always, thank you for your input and wealth of knowledge!

Now I am sure that I owe you a nice Shiraz or maybe a Riesling.... What about a really good California Chardonnay?

Jack

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

Well, to my shame I must recognize that I am fairly ignorant when it comes to Australian an American wines. But willing to open my mind http://www.access-programmers.co.uk/ubb/smile.gif

If
1 You want to re-link attached tables (Not attach new ones)
2 want to relink all you attached tables to the same back end (not just work with subsets)

You would use the above code, first opening the OpenFile Dialog box to grab the db path and name selected by the user.

Ex:

Dim strPath as String
strPath = GetFilePathAndName("Access Files (*.mdb)", "*.MDB")
If len(nz(strPath,""))<>0 _
And strPath<>"NoFile" then Connect(strPath)


You will find the function GetFilePathAndName at: http://www.mvps.org/access/api/api0001.htm


Alex

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

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

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

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

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).]