asked on 5/19/2011

clearing form except for certain fields

This is what I'm trying to do.

I have a form in which a user enters all information for a part in order to add this part into a database. If however the user has multiple numbers of the same part, instead of recreating everything from scratch, I want it so that the user just has to click a "save and duplicate" button in which case the data they just entered will be saved as a record in the database and then the form will requery but maintain the information incertain fields such as part name, NSN, ect. (basically the fields that will not change at all for that particular part) and the other fields will be empty for entering the new information such as Serial Number and such. The following is the code I currently have on that button:

Private Sub Command92_Click()
On Error GoTo Err_Command92_Click
DoCmd.RunCommand acCmdSaveRecord

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.Base = ""
Me.Building = ""
Me.txtHRDate = ""
Me.txtComments = ""
Me.txtContactNumber = ""
Me.txtHRTo = ""
Me.txtHRDueDate = ""

Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub

This, however, is giving me an error: "object does not support this property or method"
The record is being saved to the database, though, its just giving me the above error and then not clearing out the fields I'd like cleared prior to creating a new record.

Microsoft Access VB Script

Last Comment

Nick67

5/20/2011

Helen Feddema

5/19/2011

You have some very old code there -- the DoMenuCommand stuff dates back to Access 95 or earlier. Here is some code I use to copy data in most of the fields on the current record to a new record. Note that the simple method rarely works, because generally there is an AutoNumber key field, which can't be copied.

Simple version -- OK if there is no AutoNumber or other key field in the record source

   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdPasteAppend

This one works with any record source -- put "No Copy" in the Tag property of the controls whose values should not be copied.

It is called from a command button on a form, like this:

   Call CopyRecord(Me)

Public Sub CopyRecord(frm As Access.Form)
'Created by Helen Feddema 26-Sep-2009
'Last modified 26-Sep-2009

On Error GoTo ErrorHandler

   Dim CopyFields() As Variant
   
   lngCount = CountControls(frm)
   Debug.Print "Number of controls to copy: " & lngCount
   
   'Redimension array with actual number of controls to copy
   ReDim CopyFields(lngCount - 1, 1)
   
   'Cycle through all controls on form Detail section (except those
   'marked "No Copy") and save to an array
   lngCount = 0
   lngArrayNo = 0
   
   For Each ctl In frm.Section(acDetail).Controls
      strControlName = ctl.Name
      Debug.Print "Control name: " & strControlName _
         & "; tag: " & ctl.Tag
         
      If InStr(ctl.Tag, "No Copy") = 0 Then
      
         'Check field type and skip those that don't have values
         Debug.Print "Control name: " & strControlName
         lngControlType = ctl.ControlType
         
         Select Case lngControlType
            'Controls with values
            Case acTextBox
               Debug.Print "Control source: " & ctl.ControlSource
               GoTo Copy
            
            Case acComboBox
               GoTo Copy
            
            Case acCheckBox
               GoTo Copy
            
            Case acBoundObjectFrame
               GoTo Copy
               
            Case acListBox
               GoTo Copy
           
            Case acOptionGroup
               GoTo Copy
            
            Case acOptionButton
               'Check for control source, and only copy from
               'option buttons with one (bound option buttons)
               strTest = ctl.ControlSource
               GoTo Copy
               
            Case Else
               'No Value property; nothing to copy
               GoTo NoCopy
         End Select
Copy:
         strFieldName = ctl.ControlSource
         varFieldValue = ctl.Value
         Debug.Print strFieldName & " value: " & varFieldValue
         CopyFields(lngArrayNo, 0) = strFieldName
         CopyFields(lngArrayNo, 1) = varFieldValue
         lngArrayNo = lngArrayNo + 1
         lngCount = lngCount + 1
NoCopy:
      End If
   Next ctl

   Debug.Print "Number of controls to copy: " & lngCount
   
   'Create recordset, add a new record, and copy data to it from array
   Set dbs = CurrentDb
   strTable = "Pharmacy Scheduling"
   
   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
   With rst
      .AddNew
      For lngArrayCount = 0 To lngCount - 1
         Debug.Print "Processing row " & lngArrayCount
         strFieldName = CopyFields(lngArrayCount, 0)
         varFieldValue = CopyFields(lngArrayCount, 1)
         Debug.Print "Field name: " & strFieldName
         Debug.Print "Field value: " & varFieldValue
         If strFieldName <> "" Then
            .Fields(strFieldName) = varFieldValue
         End If
      Next lngArrayCount
      .Update
      .Close
   End With
      
   'Requery form and go to new record
   frm.Requery
   DoCmd.GoToRecord objecttype:=acDataForm, _
      objectname:=frm.Name, _
      record:=acLast
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   If Err.Number = 2455 Then
      'Unbound option button
      Resume NoCopy
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & _
         Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function CountControls(frm As Access.Form) As Long
'Created by Helen Feddema 26-Sep-2009
'Last modified 26-Sep-2009

On Error GoTo ErrorHandler
   
   'Cycle through all controls on form Detail section (except those
   'marked "No Copy") and determine number of controls to copy
   lngCount = 0
   strTest = ""
   
   For Each ctl In frm.Section(acDetail).Controls
      strControlName = ctl.Name
      'Debug.Print "Control name: " & strControlName
      
      If InStr(ctl.Tag, "No Copy") = 0 Then
      
         'Check field type and skip those that don't have values
         lngControlType = ctl.ControlType
         
         Select Case lngControlType
            'Controls with values
            Case acTextBox
               GoTo CountCtl
            
            Case acComboBox
               GoTo CountCtl
            
            Case acCheckBox
               GoTo CountCtl
            
            Case acBoundObjectFrame
               GoTo CountCtl
               
            Case acListBox
               GoTo CountCtl
           
            Case acOptionGroup
               GoTo CountCtl
            
            Case acOptionButton
               'Check for control source, and only count
               'option buttons with one (bound option buttons)
               strTest = ctl.ControlSource
               If strTest <> "" Then
                  GoTo CountCtl
               End If
               
            Case Else
               'No Value property; don't count
               GoTo NoCount
         End Select
         
CountCtl:
         lngCount = lngCount + 1
NoCount:
      End If
   
      strTest = ""
   Next ctl

   Debug.Print "Number of controls to copy: " & lngCount
   CountControls = lngCount
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 2455 Then
      'Unbound option button
      Resume NoCount
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & _
         Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Open in new window

Nick67

5/19/2011

LOL

@Helen, Access 2003 command button wizards create this code for a Save Record button

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Open in new window

So his code isn't necessarily old.

I haven't tried the wizards in 2007 or 2010 yet...but I bet you they may STILL use the same code.
Anyway,

@JARICHARDUSA ,

Attack it the other way around.
You are presently saving, copying the whole record, and then trying to remove some stuff
Go at it the other way.
Save the values from the fields you want to keep into some variables.
Save the record and go to a new one
Write the values of your variables to the controls you'd like pre-loaded
As a bonus, if the user changes their mind, you can ESC or cancel the changes instead of having to delete the now-unwanted record

Dim mySomething as string
Dim MyOtherValue as string
Dim myNumber as long

mySomething = me.txtSomething 'save the value in the control for use in a second
MyOtherValue = me.txtOtherValue 'save the value in the control for use in a second
MyNumber = me.txtNumber 'save the value in the control for use in a second

DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew

me.txtSomething=mySomething 'copy your saved value back
me.txtOtherValue=MyOtherValue 'copy your saved value back
me.txtNumber=MyNumber  'copy your saved value back

Open in new window

JARICHARDUSA

5/19/2011

ASKER

@Nick:

For each text box that I want to save the info then use over, I take it I make a Dim statement for each of those? For instance, say there were two text boxes txt_LIN and txt_NSN, I take it I would write it as:

Dim LIN as string
Dim NSN as string

LIN=me.txt_LIN
NSN=txt_NSN

DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew

me.txt_LIN=LIN me.txt_NSN=NSN
me.txtNumber=MyNumber

Now if the text field contains a number, which each of these does, do I have to usethe Dim MyNumber as long and use a number instead of a title in that phrase? for instance: Dim 1 as long then in the bottom part me.txt_LIN=1

I don't know a whole lot about the coding portion of access.

Jeffrey Coachman

5/19/2011

@Nick:

Access 2007 and 2010 "format" database wizards generate macros (Oy!)

The only way you can make the wizards generate Code is to leave the DB in the 2003 or older format...

:-)

Jeff

ASKER CERTIFIED SOLUTION

Nick67

5/19/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Nick67

5/20/2011

< your answer was precise, accurate and very short and simple>

How comes you did not award any points, then?
Please post your final code, and award points

JARICHARDUSA

5/20/2011

ASKER

I did not intend to select my own comment, I meant to select Nicks response as the qualifying answer with the comments that I had written and awarded full points. Thank you.

JARICHARDUSA

5/20/2011

ASKER

Thank You, same comments as before, I apologize for the accidentally awarding the points to myself.

Nick67

5/20/2011

No big deal, really.

But please, post your final, working code.
There might be some hairballs in it we could help you iron out!