Results 1 to 4 of 4
  1. #1
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72

    Using a variable throughout the form?

    So I defined a variable within the "On Load" for a new form when it loads, basically it grabs it from the OpenArgs when the main form calls to load the second form (only way I could get it to pass the ID I needed). But I need to use that ID number for once a button is clicked but it appears that variable can't be used outside the on load event now i believe? Here is what I have (sorry it's a bit messy, Ive been messing with it all day.)



    Code:
    Option Compare Database
    
    Private Sub btnUpdate_Click()
    'Check to see if all fields are filled in, avoiding Null entries
    'If IsNull(txtNotes) Or IsNull(boxFixed) Then
    '    MsgBox "Please ensure all fields are filled in!", , "Incomplete!"
    '    Me.Undo
    '    Else
        'No Null were found, insert into table and exit after requery
    '    CurrentDb.Execute ("UPDATE Table1 SET [Resolved By] = '" & boxFixed & "' WHERE [ID] = '" & intID & "'")
    '    MsgBox "New repair request ticket added."
    '    [Forms]![Main1]![lstActive].Requery
    '    DoCmd.Close
    'End If
    lblTest.Caption = intID
    End Sub
    
    Private Sub cmdClose_Click()
        [Forms]![Main1]![lstActive].Requery
        DoCmd.Close
    End Sub
    
    Private Sub Form_Load()
    Dim intID As Integer
    Dim strTitle
    Dim strRequested
    Dim strAssigned
    Dim strPriority
    Dim strStatus
    Dim strComments
    Dim strDate
    
    'Test the OpenArgs to ensure it isn't 0
    If Me.OpenArgs > 0 Then
    intID = Me.OpenArgs
    'Lookup information of obtained selection row from lstActive (Main1)
    strTitle = DLookup("[Title]", "Table1", "[ID]=" & intID)
    strRequested = DLookup("[Requested By]", "Table1", "[ID]=" & intID)
    strAssigned = DLookup("[Assigned To]", "Table1", "[ID]=" & intID)
    strPriority = DLookup("[Priority]", "Table1", "[ID]=" & intID)
    strStatus = DLookup("[Status]", "Table1", "[ID]=" & intID)
    strComments = DLookup("[Comments]", "Table1", "[ID]=" & intID)
    strDate = DLookup("[Submitted]", "Table1", "[ID]=" & intID)
    'Fill labels on Edit form
    lblTitle.Caption = strTitle
    lblRequested.Caption = strRequested
    lblAssigned.Caption = strAssigned
    lblPriority.Caption = strPriority
    boxStatus.Value = strStatus
    lblDate.Caption = strDate
    lblComments.Caption = strComments
    
    End If
    End Sub
    This right here is what I using to test to see if the value was actually getting passed or not. The number DOES work coming on the on_load and allowing to pull the proper record. It just won't let me use it in the btnUpdate_Click().
    Code:
    lblTest.caption = intID

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Declare the variable in the module header section just below the Option Compare.

    BTW, should also include Option Explicit in all modules.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lithium is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    72
    You're a genius. Thank you!

    Is there a way to reset the autonumber thing in the table? I've had to delete all my test fields but the number is still accruing from like 97 now. lol

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Assuming the autonumber is not used as foreign key anywhere and not used in any relationships? Delete the field and create another. Otherwise, a bit complicated.

    If you delete all records, then run Compact and Repair, should reset back to 0.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-28-2013, 12:53 PM
  2. Replies: 2
    Last Post: 03-15-2013, 12:49 PM
  3. Replies: 0
    Last Post: 08-10-2011, 11:59 AM
  4. Replies: 4
    Last Post: 08-05-2010, 01:26 PM
  5. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 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
  •  
Other Forums: Microsoft Office Forums