Results 1 to 8 of 8

Thread: VBA anyone?

  1. #1
    A Straw? And Fruit? Bazzlad's Avatar
    Join Date
    Jul 2003
    Location
    The Big Rhesus House Stourbridge
    Posts
    3,072
    Thanks
    90
    Thanked
    78 times in 44 posts

    VBA anyone?

    Problem referencing a form in a module -
    Here's what I have

    (On the form calling it)

    Code:
    Call addition(Me, Form_frmsalesbyadd.Form)

    In the module
    Code:
    Sub addition(theform As Form, form2 As Form)
    If Not theform.salesnumber.Value = vbNullString Then
    
    Dim salenumber As String salenumber = theform.salesnumber.Value
    Else salenumber = ""
     End If
    
    DoCmd.OpenForm form2
    form2.txtreturn.Value = salenumber
    
    For Each frm In Forms
    If Not frm.Name = form2
    Then DoCmd.Close acForm, frm.Name
    
    End If
    Next frm
    End Sub

    theform works. form2 doesn't.

    Anyone?

  2. #2
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb

    Re: VBA anyone?

    do you need to 'dim' the salenumber outside the first If Not block?

  3. #3
    A Straw? And Fruit? Bazzlad's Avatar
    Join Date
    Jul 2003
    Location
    The Big Rhesus House Stourbridge
    Posts
    3,072
    Thanks
    90
    Thanked
    78 times in 44 posts

    Re: VBA anyone?

    No it's the way I'm referencing the second form in the first block which errs.
    :S

  4. #4
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb

    Re: VBA anyone?

    can you make a function in form2 which is just the eqivalent of:

    setTextReturn( i as Integer)
    textreturn.value = i


    and call form2.setTextReturn( sale number )


    instead?

  5. #5
    Senior Member
    Join Date
    Aug 2005
    Location
    Brisbane
    Posts
    612
    Thanks
    20
    Thanked
    35 times in 22 posts
    • toolsong's system
      • Motherboard:
      • ASUS P7P55D EVO
      • CPU:
      • i7 860 @ 3.8 GHz
      • Memory:
      • 2 x 4GB Corsair Vengeance
      • Storage:
      • SSDs
      • Graphics card(s):
      • GTX970
      • PSU:
      • Seasonic X650
      • Case:
      • Antec P180
      • Operating System:
      • Win7 x64
      • Monitor(s):
      • Dell U2515H
      • Internet:
      • Fibre @ 100/40

    Re: VBA anyone?

    First I think streetster is right, if you enter the Else section of your first statement them the Dim is not executed. Could just be the code you pasted formatted badly, hope it's actually neater

    I think the fix you're looking for is:

    Code:
    Sub addition(theform As Object, form2 As Object)

  6. #6
    A Straw? And Fruit? Bazzlad's Avatar
    Join Date
    Jul 2003
    Location
    The Big Rhesus House Stourbridge
    Posts
    3,072
    Thanks
    90
    Thanked
    78 times in 44 posts

    Re: VBA anyone?

    Thanks for the help lads - Fixed it, basically vba won't allow you to open forms in modules unless they're named - So I open the form before the function, ran the function then closed it like this:

    Code:
    Private Sub cmdsalesbyadd_Click()
        
        DoCmd.OpenForm ("frmsalesbyadd")
        Call addition(Me, [Forms]!frmsalesbyadd)
        
        For Each frm In Forms
            If Not frm.Name = "frmsalesbyadd" Then
        DoCmd.Close acForm, frm.Name
            
        End If
        Next frm
        
    End Sub
    Function:

    Code:
    Sub addition(theform As Form, form2 As Form)
        
        If Not theform.salesnumber.Value = vbNullString Then
        Dim salenumber As String
        salenumber = theform.salesnumber.Value
        
        form2.lblback.Visible = True
        form2.lblnew.Visible = False
        
        Else
        salenumber = ""
        
        form2.lblback.Visible = False
        form2.lblnew.Visible = True
        
        End If
        
        form2.txtreturn.Value = salenumber
    
    End Sub

  7. #7
    Registered+
    Join Date
    Aug 2005
    Posts
    30
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: VBA anyone?

    Could you not have just passed salenumber an an openarg with the docmd.openform?
    Athlon 1800 HIS9550 128mb 17" AcerTFT DVI

  8. #8
    A Straw? And Fruit? Bazzlad's Avatar
    Join Date
    Jul 2003
    Location
    The Big Rhesus House Stourbridge
    Posts
    3,072
    Thanks
    90
    Thanked
    78 times in 44 posts

    Re: VBA anyone?

    No because this is called from multiple forms - it's the form names that needed to be passed over.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access VBA - CPT3
    By Matt1eD in forum Software
    Replies: 0
    Last Post: 13-05-2007, 03:02 PM
  2. VBA -> DoCmd.Close is possessed!
    By Dougal in forum Software
    Replies: 14
    Last Post: 19-08-2005, 10:36 AM
  3. Access VBA - Variable conversion
    By Raz316 in forum Software
    Replies: 0
    Last Post: 28-05-2004, 12:03 PM
  4. VBA - Telnet
    By Raz316 in forum Software
    Replies: 0
    Last Post: 13-04-2004, 11:13 AM
  5. more VBA and SQL 'problems'...
    By streetster in forum Software
    Replies: 7
    Last Post: 07-01-2004, 10:45 PM

Posting Permissions

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