Replies

I use Rick Fisher’s “Find & Replace”, found at http://www.RickWorld.com. This is an absolutely indispensable tool for Access developers.

RG,
How did you insert the code like that? Is it an image?

I use a different technique for linking:

Code:

   Set dbData = DBEngine.Workspaces(0).OpenDatabase(strDataLocationPath)
   txtConnection.Visible = True
   For Each mytdf In mydb.TableDefs
      If Left(mytdf.Name, 1)  "~" And Len(mytdf.Connect) > 0 And Left(mytdf.Connect, 1) = ";" Then
         strTableName = mytdf.Name
         txtConnection.Value = strTableName       'just shows progress
         Me.Repaint
         mytdf.Connect = ";Database=" & strDataLocationPath
         mytdf.RefreshLink
      End If
   Next
   txtConnection = "Linking Complete!"
   mydb.Close
   Set mydb = Nothing
   dbData.Close
   Set dbData = Nothing

This technique automatically finds all linked tables; the assumption is that all linked tables are connected to the same backend (a little extra coding if that is not the case). “strDataLocationPath” is that backend location, which may come from different places. Usually I have a linking form that contains the path to the Backend; often a choice between a network location (for production) and a local location (for testing). I believe this technique of refreshing the connection string is faster especially if you have a lot of tables.

Definitely not a stupid question. Exactly how are you blocked? Can you open the database with the Shift key depressed? Does it require a database password? If the latter, you should be able to find a 3rd party product to crack the db password.

Just out of curiousity, how many providers do you have? If there are a lot of rows, Access will delay fully populating a listbox. Just a way of managing resources.

Luke,
What is the error you are getting? Statement looks OK to me, if you realize that it will return NULL unless ‘Leicester’ is found. You might want to put in an ELSE statement.

As I see it, the WHERE clause of your SQL should be:
WHERE [Email1] Is Not Null AND [Citizen/Leg] IN (“SFL”, “YAL”, “C”,”F”)

This says “I want to return all records that have an email value and that have 1 of 4 acceptable values for citizen/leg”. This assumes that the Email1 field can not contain a zero-length string. If it can, then use: WHERE [Email1] & “” “” AND …

On a separate issue, you should get out of the habit of using special characters in your fieldnames, with the exception of the underscore character.

If you’ve installed the SQL Server Management Studio, you can detach the database and then copy/move it.

Are you sure it is that routine that is asking you twice? Is it possible that in the form’s BeforeUpdate event you have another “Are you sure?”?

What was wrong with: format([Calc_Total],”currency”)

The Controlsource for a single textbox would be:

=[Qty] & ” items purchased from ” & [Sold_By] & ” at a cost of ” & format([Calc_Total],”currency”)

Status = Switch(Left([Stage],6)=”Closed”,”Closed”,[Client Priority]=”On Hold”,”Inactive”,[Client Priorty]=”Awaiting Client”,”Inactive”,True,”Active”)

I don’t understand how Client_Priority and Stage are related. For example what if Client_Priority is “Low” (which yields an “Active” Status), but State is “Closed – Withdrawn” (“Closed” Status)?

The quick way is to use: DateDiff(“yyyy”,[17 Init Date], [Enter Start date in format mm/dd/yyyy])

The IIF statement evaluates both “sides”; that is, even if the condition is met, the other condition is also evaluated. So in your example, if Text40 is Null, then Val(text40) will fail. You might try Val(text40 + “”).