Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: VBA help with nulls and form reference

    I give up for today. My forehead has a desk and random paperclip imprints on it.

    I'm simply trying to write VBA to look at a FK value on a form and if it is not null, open up a new form with the matching PK value otherwise show a message box. Simple with macros.

    What I have so far:

    Code:
    Private Sub btnArchiveData_Click()
    
    Dim intindex As Integer
    
    intindex = [Me]![frmPatient]![Index#]
    
    If Not IsNull(intindex) Then
    DoCmd.OpenForm "pufrmArchiveDataPatientInfoDatabase", acNormal, , _
    [Forms]![pufrmArchiveDataPatientInfoDatabase]![Index No].Value = (intindex)
    
    Else: MsgBox "No archived record", vbInformation + vbOKOnly
    
    End If
    End Sub
    I think I have two problems.

    I get a "cant find the field '|' if the value is Null. Do null's have to be converted using Nz first??

    Otherwise I get the message "can't find the form 'pufrmArchiveDataPatientInfoDatabase'..." where it is not null.
    Why won't it recognise the outside form reference in the condition portion? It opens up fine without a condition.

    Thanks for any assistance with my VBA learning curve!
    Last edited by jonesyfella; 01-23-07 at 12:35.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    pufrmArchiveDataPatientInfoDatabase doesn't exist at the time you try to set the value.

    have a smooch around in help for OpenArgs (hate args myself, but lots of folk find them handy for this sort of stuff)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Smile

    Thanks izy. That all looked a bit too heavy for my liking as you said!

    However, I will not be beaten so I did it in a macro, got Access to convert the macro to VBA and then ripped the code and added to it. Turns out 'tis as follows:

    Code:
    Private Sub btnArchiveData_Click()
    
    If (Eval("[Forms]![frmPatient]![Index#] Is Not Null")) Then
    DoCmd.OpenForm "pufrmArchiveDataPatientInfoDatabase", acNormal, "", _
    "[index No]=[Forms]![frmPatient]![Index#]", , acNormal
    Else: MsgBox "No archived record", vbInformation + vbOKOnly
    
    End If
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •