Replies

Hi,

If you insist, here you go! Play with the code below. I use it with On_DblClick event of ID text box. The code will look up for the first unused ID found and fill in the ID text box for you. If no unused ID found, it will prompt you for the next running number.

Private Sub ID_DblClick(Cancel As Integer)
If Me.NewRecord Then
Dim strID As String, intMax As Integer, strMax As String
strID = FindUnusedID()
If IsNull(strID) Or strID = “” Then
If (MsgBox(“No more vacant ID left” & vbCrLf & _
“Do you want to add the next new ID?”, vbOKCancel, “No vacant ID”)) = vbOK Then
intMax = Val(DMax(“[ID]”, “tblID”)) + 1
Select Case intMax
Case Is < 10
strMax = "00" & CStr(intMax)
Case Is < 100
strMax = "0" & CStr(intMax)
Case Else
strMax = CStr(intMax)
End Select
Me.ID = strMax
Else
Cancel = True
Exit Sub
End If
Else
Me.ID = strID
End If
End If

End Sub

Function FindUnusedID()
Dim dbs As Database, rst As Recordset
Dim strID As String, intID As Integer

Set dbs = CurrentDb
' You have to open recordset as Dynaset to enable Sort
Set rst = dbs.OpenRecordset("tblID", dbOpenDynaset)

With rst
If Not .EOF Then
' You have to sort the recordset beforehand
.Sort = "ID"
.MoveLast
.MoveFirst
For intID = 1 To .RecordCount
If Val(!ID) intID Then
Select Case Val(!ID) – 1
Case Is < 10
strID = "00" & CStr(intID)
Case Is < 100
strID = "0" & CStr(intID)
Case Else
strID = CStr(intID)
End Select

FindUnusedID = strID
'Debug.Print strID
Exit Function

End If

.MoveNext
Next intID
End If
End With

Set rst = Nothing
Set dbs = Nothing

End Function

HTH.
Tim K.

Hi,

You have several ways to handle this. Do you plan to insert the URL into the table or just gather the URL when a use clicks a command button. If the latter, apply the code below and play with it to fit your need.

Private Sub cmdURL_Click()
Dim strURL as String
strURL = “www.ourwebsite.com/companypage.htm?compID=” & Me.compID
FollowHyperlink strURL
End Sub

HTH.

Tim K.

Hi,

You have to add # to the link to make it valid as HyperLink. Try the code below.

Sub ValidateHyperLink()
Dim dbs As Database, rst As Recordset
Dim I As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblPDFPath”)
If Not rst.EOF Then
rst.MoveFirst
For I = 1 To rst.RecordCount
rst.Edit
rst(“PDFPath”) = “Link to ‘” & rst(“PDFPath”) & “‘” & “#” & rst(“PDFPath”) & “#”
rst.Update
rst.MoveNext
Next I
End If
End Sub

In the future, you can is the code below to facilitate your work.

‘ Set References to Microsoft Office 9.0 Object Library first
Private Sub Test()
Dim dbs As Database, rst As Recordset
Dim FS As FileSearch, FilePath As String, MyPath As String
Dim I As Integer, strFileName As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblPDFPath”)
Set FS = Application.FileSearch
MyPath = “I:”
strFileName = “*.PDF”
With FS
.LookIn = MyPath
.SearchSubFolders = True
.FileName = strFileName
‘.FileName = “*.doc”
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
Debug.Print .FoundFiles.Count
FilePath = .FoundFiles(I)
With rst ‘DoCmd.TransferText , , “tblPath”, .FoundFiles(i)
.AddNew
!PDFPath = FilePath
.Update
End With
Next I
Else
MsgBox “There were no files found.”
End If

End With

End Sub

Modify both to fit your need.

HTH.
Tim K.

If I understand your need well, I add the code below for the case 36
Case 36
‘ Not Ready Yet
Dim DateStart As Date, intDays As Integer, I As Integer
DateStart = DateSerial(Year(dteDate), Month(dteDate), 1)

‘ Calculate how many days in this dteDate
intDays = DateSerial(Year(dteDate), Month(dteDate) + 1, Day(dteDate)) _
– DateSerial(Year(dteDate), Month(dteDate), Day(dteDate))

‘ Show all days of the Type
For I = 1 To intDays
If DatePart(“w”, DateStart) = DayNum Then
MyDate = MyDate & DateStart & vbCrLf
End If
DateStart = DateStart + 1
Next I

Tim K.

Hello Mark,
I have fixed the problem now after I studied the
http://support.microsoft.com/support/kb/ar…s/q244/2/64.asp
I followed the following paragraph:-
…Re-register the application by typing the path to the server in the Start and then Run dialog box, and then append /RegServer to the end of the line. Press OK. This should silently run the application and re-register it as a COM server. If the problem is with a missing registry key, this will typically correct it….

Just typed this
“D:Program FilesAccess97OfficeWINWORD.EXE” /regserver
in the Run command line. And It worked.

Thanks for your help anyhow.

Tim K.

Hi Mark,
I did try both. They stoped at the same line I mentioned below.

Hi Mark,
It may sound ridicuous, but true.
Here’s the code

Sub CodeRunningOutsideWord()
Dim wdApp As Word.Application
Dim docNew As Word.Document

‘ Create new hidden instance of Word.
Set wdApp = New Word.Application
‘ Create a new document.
Set docNew = wdApp.Documents.Add
‘ Add text to document.
wdApp.Selection.TypeText “Four score and seven years ago”
‘ Display document name and count of words, and then close
‘ document without saving changes.
With docNew
MsgBox “‘” & .Name & “‘ contains ” & .Words.Count & ” words.”
.Close wdDoNotSaveChanges
End With
wdApp.Quit
Set wdApp = Nothing
End Sub

The message error shows

Run-time error ‘429’
ActiveX component can’t create object.

It stops at
Set docNew = wdApp.Documents.Add

It’s OK when I do the similar process to call Excel.

Any idea?

In the mean time, I am studying something at
http://support.microsoft.com/support/kb/ar…s/Q244/2/64.ASP
It seems very much like my problem.

Thanks for your replies.

Tim K.

Thank you, Ken.
I will study the code.

Tim K.

If you installed A97, you should find Solution.mdb in the samples folder. The code is working OK in this version.
I deal with the limited column by sending the data to pre-defined Excel file (format, print area). It works well.

Tell me if you still need help.

Tim K.
Thailand

I use the code below to deal with the problem.

Private Sub CEmail_AfterUpdate()
Dim strEmail As String

If Len(Me.CEmail) > 0 Then
strEmail = Left(Me.CEmail, InStr(Me.CEmail, “#”) – 1)
strEmail = strEmail & “#mailto:” & strEmail & “#”
Me.CEmail = strEmail
End If

End Sub

HTH

Cheers!!

Tim K.
Thailand