Paste Append

Question

I want to automate the process which would allow a user to copy the content of a field on a form and PasteAppend it another form/table.  I have set up a command button and tried to build a macro but after selecting GoToControl I am unable to find Copy or PasteAppend in the list of actions.

It may of course be better to write some code to achieve this but unfortunately I am not good at writing code.

Would appreciate your help or advice.

Answer 1

The code for the Click event of a button to add the name from the CustomerRef control would be like this:

Const NOCUSTMESSAGETEXT = "No customer ref entered."

Const CONFIRMMESSAGETEXT = "Add customer to Names table?"

Dim strSQL As String

If Not IsNull(Me.CustomerRef) Then

    If MsgBox(CONFIRMMESSAGETEXT, vbYesNo + vbQuestion, "Confirm") = vbYes Then

        strSQL = "INSERT INTO Names " & _

            "VALUES(""" & Me.CustomerRef & """)"

        CurrentDb.Execute strSQL, dbFailOnError

    End If

Else

    MsgBox NOCUSTMESSAGETEXT, vbExclamation, "Invalid Operation"

End If

Do the same for the Guest control.  If you wish to avoid adding a button you could call the code in each control's DblClick event procedure.  Or you could automate it to simulate the use of the NotInlist event procedure by calling the following amended code in the combo box's AfterUpdate event procedure:

Const NOCUSTMESSAGETEXT = "No customer ref entered."

Const CONFIRMMESSAGETEXT = "Add customer to Names table?"

Dim strSQL As String

Dim strCriteria As String

If Not IsNull(Me.CustomerRef) Then

    strCriteria = "[YourNameField] = """ & Me.CustomerRef & """"

    If IsNull(DLookup("[YourNameField]", "Names", strCriteria)) Then

        If MsgBox(CONFIRMMESSAGETEXT, vbYesNo + vbQuestion, "Confirm") = vbYes Then

            strSQL = "INSERT INTO Names " & _

                "VALUES(""" & Me.CustomerRef & """)"

            CurrentDb.Execute strSQL, dbFailOnError

        End If

    End If

Else

    MsgBox NOCUSTMESSAGETEXT, vbExclamation, "Invalid Operation"

End If

You should then be prompted to confirm immediately upon updating the combo box by entering a name not currently represented in the Names table.

BTW I hope you have not used Name as a column name in the Names table?  As the name of a built in property it is a 'reserved' word, so should be avoided as an object name.  Always use more specifically descriptive names like CustomerName or, more generically, ContactName for instance.

Answer 2

This is the real world situation:

There are two fields on a form, CustomerRef (usually a person's name) and Guest, both set up as combo boxes with RowSource set to table [Names].  Quite often both entries are the same.

I would like a user to be able to either select an item from the list or type in something new and have the option to add it to the Names table or not add it (but retain what is typed in) and move on to another field on the form.

Since NotInList event only works when LimitToList property is set to yes, user is forced to add anything not in the list -  sometimes CustomerRef could be an order number which the user would not want to add.

That is why I was thinking of command button to trigger copy and pasteappend to Names table.

I would like to try your suggestion of inserting a row into the table by SQL statement but not sure how to code this.  I hope you can help.

Thank you for taking the trouble to help me Ken, much appreciated.

Answer 3

The second form may not necessarily be open but I assume I would be able to make the click event open it, go to a new record, use the code you have given and shut down the form.

You could, but it would be better to simply insert a row into the relevant table by executing a simple SQL statement in code.

I think it would help if you could explain exactly what you are aiming to do in terms of the real world situation which the database models, rather than how you are attempting to do it in database terms.  From what information you have provided my guess would be that this might be a case where the NotInList event of a combo box would be the appropriate mechansim.

Answer 4

Thank you for your suggestion.

The second form may not necessarily be open but I assume I would be able to make the click event open it, go to a new record, use the code you have given and shut down the form.

The second form only has one control which record names of events and these are then offered in a dropdown list in the first form for the user to either select one or type in a new one.

Answer 5

If you simply wish to copy the value of a control on one open form into a control on another open form you don't need to copy or paste; simply assign the value from one to the other.  In code this can be done in a single line in the Click event procedure of a button on the first form, e.g.

Forms("YourSecondFormName").[NameOfControlInSecondForm] = Me.[NameOfControlInFirstForm]

It should not be difficult to do the same in a macro.