Posts by the_sleeper

    Re: Send email from Lotus Notes using different database other than personal .nsf

    A workaround for this issie is to explicitly assign a value to the 'from' and 'Principal' fields. (see below)

    The email still gets sent from your personal Inbox but it looks as though it was sent from the alias/group inbox.

    MailDoc.From = "CN=Other Inbox Name/OU=xxx/O=yyyy"
      MailDoc.INetFrom = "[email protected]"
      MailDoc.Principal = "CN=Other Inbox Name/OU=xxx/O=yyyy"
      MailDoc.INetPrincipal = "[email protected]"

    I am trying to send en email via Lotus Notes using VBA (in Access) but I am unable to get the email to send from a different mail database other than my personal one.

    I have tried to explicitly enter the ServerName and DatabaseName in the following section

    Set Maildb = session.GETDATABASE("ServerName", MailDbName)

    The below excerpt displays the entire code which still sends via my personal Mail Inbox:

    Re: Web data into Access form

    I couldn't quite figure out how to 'complete' the thread after I have been doing some more web searching and came across this fantastic article by By Rick Strahl at West Wind

    Although it was for Visual FoxPro I managed to adapt it to my needs in VBA/Access.

    See function below which brings back the body html and filters only for information between the start and end string

    I am trying to use Access/VBA to get some information from a webpage and enter it into a textbox.

    I suppose you could draw comparisons to the Get External Data (Web Query) functionality in Excel but instead using Access.

    Can you do this using ADO or DDE?

    Are there any examples that I can reference?

    Any assistance would be kindly appreciated

    Re: SOLVED: MonthView GetDayBoldEvent

    I managed to avoid using this control as I was essentially able to create a form with approx 40 text boxes and looped through the days of the current month, populating the relvant days with particular infomation in a recordset



    I am having trouble getting the above Event for the Microsoft MonthView Control to work

    I have read the MSDN article below…usingmonthviewcontrol.asp and copied the EXACT code into the sample database attached below and receive the following attached error message when the form loads (or the event is triggered). This message only appears once and no formatting occurs.

    I am trying to build a form with a Calendar control that looks at the dates in a recordset and formats them in the Calendar control in a different format (in this eg - bold).

    I am also trying to avoid using thrid-party Active-X controls due to licensing and instability issues etc and would much prefer to use the packaged Microsoft ones to avoid installing additional controls on user's machines.

    Does anyone know how I can get the Monthview Control to bold particular dates correctly (without showing the error message)...would I be missing a VBA Reference or something?

    (I have also included another MonthView control on the Form to demonstrate the controls properties that show by default (pre-defined name etc)

    Any assistance would be much appreciated



    Hi andy,

    The code I used is based on the following passage found at (a great website)

    ...and I added your EditBinary File eg and adapted it for myRecord

    I hope this helps???


    Option Explicit

    Type MyBinRecordInfo
    LastName As String
    FirstName As String
    BirthDate As Date
    End Type

    Sub WriteBinaryFile()
    Dim MyRecord As MyBinRecordInfo, FileNum As Integer, i As Integer
    If Dir("C:\FOLDERNAME\BINFILE.DAT") <> "" Then
    ' deletes the file if it exists
    End If
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #FileNum
    ' creates the new file
    ' write records to the binary file
    For i = 1 To 100
    With MyRecord
    .LastName = "LastName" & i
    .FirstName = "FirstName" & i
    .BirthDate = Date
    End With
    WriteBinaryRecord MyRecord, FileNum ' save the record
    Next i
    Close #FileNum ' close the file
    End Sub

    Sub WriteBinaryRecord(tRecord As MyBinRecordInfo, fn As Integer)
    ' writes the content of tRecord to the next record in an open binary file
    Dim sSize As Integer
    With tRecord
    sSize = Len(.LastName) ' get the length of the LastName variable
    Put fn, , sSize ' write the length information
    Put fn, , .LastName ' write the variable
    sSize = Len(.FirstName) ' get the length of the FirstName variable
    Put fn, , sSize ' write the length information
    Put fn, , .FirstName ' write the variable
    Put fn, , .BirthDate ' write the variable (fixed length)
    End With
    End Sub

    Sub ReadBinaryFile()
    Dim MyRecord As MyBinRecordInfo, FileNum As Integer, i As Integer
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #FileNum
    ' open the binary file
    ' read records from the binary file
    For i = 1 To 100
    While Loc(FileNum) < LOF(FileNum)
    ReadBinaryRecord MyRecord, FileNum
    ' do something with the input
    With MyRecord
    Debug.Print .LastName, .FirstName, .BirthDate
    End With
    Next i
    Close FileNum ' close the file
    End Sub

    Sub ReadBinaryRecord(tRecord As MyBinRecordInfo, fn As Integer)
    ' reads the next record from an open binary file
    Dim sSize As Integer
    With tRecord
    Get fn, , sSize ' size of the LastName field
    .LastName = String(sSize, " ") ' set the variable length
    Get fn, , .LastName ' read the variable string field
    Get fn, , sSize ' size of the FirstName field
    .FirstName = String(sSize, " ") ' set the variable length
    Get fn, , .FirstName ' read the variable string field
    Get fn, , .BirthDate ' read the BirtDate field (fixed length)
    End With
    End Sub

    Sub EditBinaryFile()
    Dim intUnit As Integer
    Dim typInfo As MyBinRecordInfo
    Dim lngIndex As Long, lngPos As Long

    intUnit = FreeFile
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #intUnit 'Access 'Write As intUnit Len = Len(typInfo)
    typInfo.FirstName = "John"
    typInfo.LastName = "Smith"
    typInfo.BirthDate = #1/1/2001#
    ' Replace record 3 - Charlie
    lngPos = 1 + ((3 - 1) * Len(typInfo))
    Put #intUnit, lngPos, typInfo
    Close intUnit
    ' show changes
    End Sub

    Thanks Andy for the code....

    but I still cannot get it to work, when I read the file again it becomes jumbled and incohesive.

    I noticed two things with the code.

    What is the lngIndex variable for? I noticed it was declared but not used....

    Furthernore with my code I copied your code and adapted it for my use but my binary file contains 110 records but the length function returns a value of 82 (ie len(myrecord) = 82).

    Also with your code when you comment that it replaces record 3 is this becasue the 3 is hardcoded and to apply this to look for another record index I would change the 3 to an index no.

    am slowly getting closer to the solution but it I just need a few more verifications

    Thanks for your help again


    Hi Guys,

    I was wondering if there is a way to open a Binary file, read all the data until a particular record is found and then overwrite that record with new information.

    I have tried to do this with the put method and used and iindex but all the records in the entire file are erased except the one I just wrote.

    Does anyone have any experiece in using Binary files in VBA as this is the only way that I can acomplish the project I have running at the moment.

    Any assistance would be much appreciated


    Hi Will,

    There are actually two different queries (could have been a typo). One query works if there is a batch and the other work if there is no batch present.

    I have used a combo box because I don't know how to select a value from a query other than using the recordsource method of the combo box.



    Hi Guys,

    I was wondering if there is a way to somehow get a combo box on an access form to do something like the following

    iif([BatchCheck]=True,SELECT [CatalogueNumberStockBatch_Transfer_to_WIP].[SumOfStockQty] FROM CatalogueNumberStockBatch_Transfer_to_WIP;,SELECT [CatalogueNumberStock_Transfer_to_WIP].[SumOfStockQty] FROM CatalogueNumberStock_Transfer_to_WIP;)

    Is this possible, I would love to use just one query to do this but I cannot get it to work if there is a null batch field (ie the query won't return anything)

    I hope this is enough info..



    Hey Andy,

    Thanks for the update.....

    I noticed you used the {ul} key to underline the text in the balloon.Do you know of any others that can be used besides the {cf...}colour keys. Unfortunately I guess that there isn't a hyperlink key {hyp} perhaps :)



    Hi Guys,

    I was wondering if there is a simple way of inserting a hyperlink into the text of the Assistant.Balloon. I know of the [cf249] etc tags within the text to change to colours buts was curious to know if there was a flag for a hyperlink?

    Any assistance would be greatly appreciated



    Hi everyone,

    I have come across a trait of Excel which I'm not sure to get around, but I really need to.

    At the moment I have an Excel add-in that opens up an Admin-type file.
    If the user runs a macro that needs to open up this Admin-type file (file containing user info etc etc) then the
    admin file, the data retrieved and then closed (all pretty straight forward)-however (and here is the problem as I see it) the Book1.xls workbook that existed before the Admin-type file was opened is now GONE...

    I have found that this occurs in normal circumstances (without a macro) in that if a workbook is immediately opened when Excel is opened then the Book1.xls workbook no longer appears (it closes).

    I tried this by opening Excel (without my add-in) and then just opening a file and found that Book1.xls no longer appears.

    Why is this??? :o

    It's just that I would like this book to still be there when my add-in opens (and then closes) the admin file.

    Any thoughts, suggestions or questions would be very much appreciated.

    Thanks in advance...

    DW (the_sleeper)