Results 1 to 6 of 6
  1. 07-11-2012, 01:19 PM #1
    whojstall11
    whojstall11 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Posts
    57

    Update then Clear Form


    I have an button when clicked sends and email then updates my current data. I also need it to clear the form but not the information in the database.
    Code:
    Private Sub CmdUpdate_Click()

    Dim notesdoc As Object
    Dim notesrtf As Object
    Dim notessession As Object
    Dim strEmail As String
    Dim strCopy As String
    Dim strBCC As String
    Dim i As Integer
    
    Set notessession = CreateObject("Notes.Notessession")
    Set notesdb = notessession.GetDatabase("", "")
    Call notesdb.openmail
    Set notesdoc = notesdb.CreateDocument
    Call notesdoc.ReplaceItemValue("Subject", "Asset Transferred")
    Set notesrtf = notesdoc.CreateRichTextItem("body")
    Call notesrtf.AppendText(Me.body1)
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText(Me.Manufacturer)
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.DeviceType)
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.model)
   Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText("Serial Number")
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.[Serial Number])
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText("Asset Number")
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.[Asset Number])
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText(Me.body2)
    strEmail = Me.txtemail
    strCopy = ""
    strBCC = ""
    notesdoc.SendTo = strEmail
    notesdoc.CopyTo = strCopy
    notesdoc.BlindCopyTo = strBCC
    Call notesdoc.Save(True, False)
    notesdoc.SaveMessageOnSend = True
    Call notesdoc.Send(False, strEmail)
    Set notessession = Nothing

DoCmd.SetWarnings False
 
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Assigned] = """ & 0 & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Me.AssetNumber & """"

DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Status] = """ & 1 & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Me.[Status] & """"
    
DoCmd.RunSQL "UPDATE [Assignment History]" & _
    " SET [Assignment History].[AH_Date_UnAssigned] = '" & Me.[Date Unassigned] & _
    "' WHERE [Assignment History].[AH_Assigned_to_LoginID] = '" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AH_Assigned_to_LoginID] & "' And [Assignment History].[AH_Assinged_AssetNumber] = '" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AssetNumber] & "' And [Assignment History].[AH_Date_Assigned] = #" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AH_Date_Assigned] & "#"
DoCmd.RefreshRecord
End Sub
    Reply With Quote Reply With Quote
  2. 07-11-2012, 01:58 PM #2
    June7's Avatar
    June7
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    This is an unbound form? Simply set the the controls to null after data saved.

    Me.controlname = Null
    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.
    Reply With Quote Reply With Quote
  3. 07-11-2012, 02:09 PM #3
    whojstall11
    whojstall11 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Posts
    57
    the fields are all unbound but one. Its stills tells me that a field is not in the table and i cant go to the next page or do anything else to the form.
    Code:
    Private Sub CmdUpdate_Click()

    Dim notesdoc As Object
    Dim notesrtf As Object
    Dim notessession As Object
    Dim strEmail As String
    Dim strCopy As String
    Dim strBCC As String
    Dim i As Integer
    
    Set notessession = CreateObject("Notes.Notessession")
    Set notesdb = notessession.GetDatabase("", "")
    Call notesdb.openmail
    Set notesdoc = notesdb.CreateDocument
    Call notesdoc.ReplaceItemValue("Subject", "Asset Transferred")
    Set notesrtf = notesdoc.CreateRichTextItem("body")
    Call notesrtf.AppendText(Me.body1)
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText(Me.Manufacturer)
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.DeviceType)
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.model)
   Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText("Serial Number")
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.[Serial Number])
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText("Asset Number")
    Call notesrtf.AppendText(" ")
    Call notesrtf.AppendText(Me.[Asset Number])
    Call notesrtf.Addnewline(1)
    Call notesrtf.AppendText(Me.body2)
    strEmail = Me.txtemail
    strCopy = ""
    strBCC = ""
    notesdoc.SendTo = strEmail
    notesdoc.CopyTo = strCopy
    notesdoc.BlindCopyTo = strBCC
    Call notesdoc.Save(True, False)
    notesdoc.SaveMessageOnSend = True
    Call notesdoc.Send(False, strEmail)
    Set notessession = Nothing

DoCmd.SetWarnings False
 
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Assigned] = """ & 0 & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Me.AssetNumber & """"

DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Status] = """ & 1 & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Me.[Status] & """"
    
DoCmd.RunSQL "UPDATE [Assignment History]" & _
    " SET [Assignment History].[AH_Date_UnAssigned] = '" & Me.[Date Unassigned] & _
    "' WHERE [Assignment History].[AH_Assigned_to_LoginID] = '" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AH_Assigned_to_LoginID] & "' And [Assignment History].[AH_Assinged_AssetNumber] = '" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AssetNumber] & "' And [Assignment History].[AH_Date_Assigned] = #" & [Forms]![Main Page Navigation Form]![SideTabNaviForm].[Form].[AssetAssignmentForm].[Form].[AH_Date_Assigned] & "#"
DoCmd.Save

Me.AH_Assigned_to_LoginID.Value = Null
Me.AssetNumber.Value = Null
Me.AH_Date_Assigned.Value = Null
Me.[Date Unassigned].Value = Null
Me.Status.Value = Null
End Sub
    Reply With Quote Reply With Quote
  4. 07-11-2012, 02:16 PM #4
    June7's Avatar
    June7
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The form is bound to table? Why only one bound control? Why not all bound? Why using code to append/update data?

    What field is demanding a value? Change the property in table to not require a value in that field?

    Perhaps a form/subform arrangement should be considered.

    Do you want to provide db for analysis? Follow instructions at bottom of my post.
    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.
    Reply With Quote Reply With Quote
  5. 07-11-2012, 02:44 PM #5
    whojstall11
    whojstall11 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2011
    Posts
    57
    user:Admin
    pass: password
    Go to the assign asset tab
    Assign a user an Hardware (fill out all fields) in the AssignHardware then Save
    then go to next tab Unassign hardware
    Unassign that user (fill out all fields) then click update then if you try to click on any other field there is and error

    Asset Inventory Database first round1.zip
    Reply With Quote Reply With Quote
  6. 07-12-2012, 01:20 AM #6
    June7's Avatar
    June7
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    When attempting to unassign asset, the DLookup code in the AssetNumber combobox AfterUpdate event is setting value of fields of new record. Either unbind the form or use code to make the correct assignment record the current record on the form. The DataEntry property would have to be No.

    If a user was assigned the same asset multiple times and that asset shows Assigned yes, the asset will be duplicated in the AssetNumber combobox RowSource.

    Model Number textbox on Software Order Form has invalid control source error.
    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.
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Need help to clear a Form Field
    By vkumar in forum Access
    Replies: 4
    Last Post: 05-31-2012, 02:18 PM
  2. clear form
    By slimjen in forum Forms
    Replies: 1
    Last Post: 05-16-2012, 02:53 PM
  3. Clear Form
    By hithere in forum Access
    Replies: 4
    Last Post: 03-28-2012, 09:28 AM
  4. Form Fields Clear
    By rajulasb in forum Access
    Replies: 1
    Last Post: 08-06-2011, 01:11 AM
  5. How to clear the form?
    By Mrcams in forum Access
    Replies: 3
    Last Post: 01-03-2011, 12:15 AM

Posting Permissions

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

Forum Rules

Other Forums: Microsoft Office Forums