Copying record help needed

[SpandexBobcat]

Hi folks,

I know this is something that has come up a few times, I have searched TT but don't seem to be able to find what I want...

I have a form (enterCONT) for entering contract info. On this form I need a button that will save the current record, copy it, then refresh/reopen the same form (enterCONT) so that the newly copied record can be altered (if needed) and saved. The process must be able to happen an infinate number if times. All fields except the primary key need to be copied across.

Can anyone point me in the right direction?

Thanks in advance,

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[hymn]

Copy your existing table that form works off if it is a query and there is more than one table involved then change it to a make table query then change it back. Copy your form and set the control source to your new table do not have a an autonumber in the new table just a number

Private Sub Command39_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ‘you are selecting a record
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 ‘copying a record
DoCmd.OpenForm "frmDeletedPerson", , , acNew ‘open the another form to save it to

DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 ‘pasting the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 ‘saving the record
DoCmd.Close acForm, "frmDeletedPerson" ‘closing the form to return you back to where you were
End Sub

Dont forget to change the form names
Don’t forget you can’t paste autonumber into a table [/color red]

Hope this helps
Hymn

 

[SpandexBobcat]

Hi Hymn,

Is is half way there. Is it me doing something wrong or have I totally lost the plot?

The forms record source is a query. Some of the data is being copied across, the contract information the user enters but not the customer information that is pulled across from the record source. The record source query is qryCUST/CONT, derived from the two tables, CONT and CUST.

You mention changing something to a make table query then changing it back again, could you please explain that in a bit more detail.

Thanks.

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[hymn]

If you are using two tables (CONT and CUST)in the query that is the control source of the current form you will have to make a table that contains all the the fields of the current form.
So if you go to the current Select Query and change it from Select to Make table Query run it then change it back to a Select Query now you will have a Table that contains all the fields of your form's controls (Make sure that there are NO autonumber in the new table.
The control source of the Form in which you are going to copy to, should be the new Table.


Hope this helps
Hymn

 

[TheAceMan1]

How are ya SpandexBobcat . . . . .

For all the controls that need to be copied, in the [blue]Tag Property[/blue] enter [purple]Xfer[/purple] (if you group select you'll only have to enter it once), then add the following code to your command button:

[blue]   Dim ctl As Control
   
   DoCmd.RunCommand acCmdSaveRecord
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]Xfer[/b][/purple]" Then
         If IsDate(ctl) Then
            ctl.DefaultValue = "#" & ctl & "#"
         Else
            ctl.DefaultValue = ctl
         End If
      End If
   Next
   
   DoCmd.RunCommand acCmdRecordsGoToNew[/blue]

Give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[SpandexBobcat]

Hi AceMan, I'm doing well thanks, hows your good self?

As for your code suggestion, it came back with:

Run-time error 438

Object doesnt support this property or method.

This line of code is highlighted:

ctl.DefaultValue = ctl

Any ideas what is going on?
What is your method trying to do? I don't understand the code enough to figure it out...

I wasn't getting on too badly with Hymn's approuch but I haven't solved it yet...


All help is appreciated though!


"If at first you don't succeed, destroy all evidence that you tried.

 

[TheAceMan1]

OK SpandexBobcat . . . . .

Lets try full qualifiers. Replace with the following:

[blue]   Dim ctl As Control
   
   DoCmd.RunCommand acCmdSaveRecord
   
   For Each ctl In Me.Controls
      If ctl.Tag = "Xfer" Then
         If IsDate(ctl[purple][b].Value[/b][/purple]) Then
            ctl.DefaultValue = "#" & ctl[purple][b].Value[/b][/purple] & "#"
         Else
            ctl.DefaultValue = ctl[purple][b].Value[/b][/purple]
         End If
      End If
   Next
   
   DoCmd.RunCommand acCmdRecordsGoToNew[/blue]

By chance, if the button is on another form (main/subform), let me know as referencing is wrong in the code.

See Ya! . . . . . .

 

[RoyVidar]

One guess - some controls not supporting either the .defaultvalue property and/or the .value property, has gotten their tag property set. In stead of searching them all, one could exclude them by for instance:

[tt]For Each ctl In Me.Controls
select case ctl.controltype
case actextbox, accombobox, aclistbox
'... all allowed controltypes. Search on the term
' in the helpfiles to get a list
If ctl.Tag = "Xfer" Then
If IsDate(ctl.Value) Then
ctl.DefaultValue = "#" & ctl.Value & "#"
Else
ctl.DefaultValue = ctl.Value
End If
End If
end select
Next ctl
set ctl=nothing [/tt]

- some more control looping tips can be found here faq702-5010

Roy-Vidar

 

[TheAceMan1]

SpandexBobcat . . . . .

[blue]RoyVidar[/blue] has made an excellent point!

If you've set the [purple]Tag Property[/purple] for anything other that a [purple]TextBox or Combobox[/purple], list what they are! . . . .

See Ya! . . . . . .

 

[SpandexBobcat]

Well there are two list boxes (Contact_Source and Handled_By), the rest are all just textboxes (21 in total)

I will give them a go and get back to you.

Thanks

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[SpandexBobcat]

Right....

AceMan
Your second suggetion came back with the same error with this line highlighted:

If ctl.Tag = "Xfer" Then

Roy
It came back with a run-time error 13, Type Mismatch with this line highlighted:

ctl.DefaultValue = ctl.Value

Things for me are never as straight cut as I would like them!

"If at first you don't succeed, destroy all evidence that you tried.

 

[RoyVidar]

I don't understand how. To find out what's happening, do a

[tt]debug.print ctl.controltype, ctl.value, ctl.defaultvalue, ctl.name[/tt]

on the line prior to assigning the default value. To find out which control, what value and what type of control it is bombing on... (hit ctrl+g to study the results, and/or post back). What datatype is the field the control, is the control bound or unbound...

Roy-Vidar

 

[SpandexBobcat]

Hi Roy,

This is what came out as a result of the ctrl+g

109 27/10/2004 Date() Date
109 Null Alias

the list boxes have a control source from the forms query record source, although the row source for the Handled_By is based on the currentuser and a users table... Not sure if that makes a difference though.

I did have a look at the faq you linked to. I have tried the "6. Carry over values to new record (using DefaultValue)" with some success. When I tested it it did copy the desired fields, I moved the form along to the next record using the nav button and sure enough the fileds moved across. I tried it again but it bombed out... Would it make life easier if I posted a cut down version of what I have?

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[RoyVidar]

Do not name neither fields, nor other objects with reserwed words. Date is a function, and using it as the name of objects will at some time create headaches. I'm not sure this is one of those, but it will occur. I think alias might also be a reserwed word, though I'm not entirely sure where it's used.

It seems we've overlooked what might happen if the control value is Null (it should be OK with an empty string ""), but try:

[tt]For Each ctl In Me.Controls
select case ctl.controltype
case actextbox, accombobox, aclistbox
'... all allowed controltypes. Search on the term
' in the helpfiles to get a list
If ctl.Tag = "Xfer" Then
if trim$(ctl.value & "") = "" then
ctl.defaultvalue=""
' remove previous defaultvalue if no value???
else
If IsDate(ctl.Value) Then
ctl.DefaultValue = "#" & ctl.Value & "#"
Else
ctl.DefaultValue = ctl.Value
End If
end if
End If
end select
Next ctl
set ctl=nothing [/tt]

If this doesn't do the trick, I'd think I'd like to see the code, and I'd also like to know which line "my faq code" bombed on, what errormessage what type of control and the vales of the control. There might be things I've overlooked. That code should handle Nulls by not assigning anything, and "" is usually OK to assign, but you could try altering the line

[tt]Case IsNull(ctl.Value)[/tt]

to

[tt]Case trim$(ctl.Value & "")= ""[/tt]

Roy-Vidar

 

[SpandexBobcat]

Hi Roy,

I have been getting on well with the faq code so if it's ok could you have a look at my code and if possible see where it is going wrong.

On pressing cmdDuplicate it does as intended, assigns a new primary key (RC_No), saves the record and goes to a new record with the fields populated from the previous record. The problem is when you press cmdDuplicate again it assigns a number but then a msgbox pops up saying the primary or index field cannot be a null value.....

Here is the code for your attention:

Private Sub cmdDuplicate_Click()
On Error GoTo Err_Handler

    Dim strMsg As String
    Dim strHdr As String
    Dim db As Database
    Dim rstSystem As DAO.Recordset
    Dim rstCONT As DAO.Recordset
    Dim rstFrmCONT As Recordset
    Dim wrk As Workspace
    Dim booInTrans As Boolean
    Dim lngNextRC As Long
    Dim strSQL As String
    
    Dim ctl As Control

    
    ' Validation of fields and save**********************************************
    
    strHdr = "Invalid Entry"
    
    If IsNull(Me![Nature of Work]) Then
        strMsg = "Nature of Work cannot be blank"
        MsgBox strMsg, vbOKOnly, strHdr
        Me![Nature of Work].SetFocus
        Exit Sub
    End If
    
    
    
    ' Get next RC No. from System table
    If IsNull(Me!RC_No) Then
        Set db = CurrentDb()
        Set wrk = DBEngine.Workspaces(0)
        Set rstSystem = db.OpenRecordset("RCsystem", dbOpenDynaset)
    
        rstSystem.MoveFirst
        If rstSystem!SysLock = False Then            'Check the locked flag on the System table
            rstSystem.Edit
            rstSystem!SysLock = True                 'Set system record flag to 'locked'
            rstSystem.Update
        Else
            MsgBox "RC numbers are in use, please try again"
            
            Exit Sub
        End If
    
        'Get the next RC no.
        lngNextRC = rstSystem!SysNextRC
        
        ' Start of transaction
        wrk.BeginTrans
        booInTrans = True
        rstSystem.Edit
        
        ' Update the next RC no.
        rstSystem!SysNextRC = lngNextRC + 1
        rstSystem.Update
        
        ' Save enquiry record with new RC No.
        Me!RC_No = lngNextRC
        Me.Dirty = False
        
                 strHdr = "          New RC Created"
                 strMsg = "       RC No is " & lngNextRC & vbCrLf
        strMsg = strMsg & "The next record will now be created"
        MsgBox strMsg, vbOKOnly, strHdr
        
        
        'Commit Transaction
        wrk.CommitTrans
        booInTrans = False
        
        
'Copy fields function*********************************************************
   
  
For Each ctl In Me.Controls
    If ((ctl.ControlType = acTextBox) Or _
        (ctl.ControlType = acComboBox) Or _
        (ctl.ControlType = acListBox) Or _
        (ctl.ControlType = acCheckBox)) Then

        If (Len(ctl.Tag & vbNullString) > 0) Then
            Select Case True
                Case IsNull(ctl.Value)
                    ' If no value, don't change default value
                    ' this will need to be tweaked according to
                    ' requirements. Perhaps use "", so that the
                    ' “old” default value disappears…
                Case IsDate(ctl.Value)
                    ' Dates - hmmmm - because of different
                    ' regional settings (differing
                    ' from US date format) it is sometimes
                    ' necessary to do some formatting
                    ctl.DefaultValue = "#" & _
                        Format(ctl.Value, "yyyy-mm-dd") & "#"
                Case IsNumeric(ctl.Value)
                    ' No formatting for numbers
                    ctl.DefaultValue = ctl.Value
                Case Else
                    ' Then it should be text, four double quotes...
                    ctl.DefaultValue = """" & ctl.Value & """"
            End Select
        End If
    End If
Next ctl

Set ctl = Nothing

DoCmd.GoToRecord , , acNewRec
   
Exit_Proc:
    Set rstFrmCONT = Nothing
    rstSystem.Edit
    rstSystem!SysLock = False                'Set system record flag to 'unlocked'
    rstSystem.Update
    rstSystem.Close
    Set db = Nothing
    Set rstSystem = Nothing
    Exit Sub
    
Err_Handler:
    If Err.Number = 3314 Then                       'Validation failed (required field etc.)
        MsgBox Err.Description
        Exit Sub
    End If
    
    MsgBox Err.Description
    If booInTrans = True Then
        wrk.Rollback
    End If
    Resume Exit_Proc
 End If

End Sub

If you could have a quick look I would really appreciate it.

Thanks,

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[RoyVidar]

I'm a bit surprised. Tried doing some of the same structure, and it did compile and run, but I'm not sure it's intended like that. I think this means it is not related to carrying over values, but the other code (a relief;-)

The matching End If for
[tt]If IsNull(Me!RC_No) Then[/tt]

is the last line of the Error handler??? Is that intended.

WHICH LINE GIVES THE ERROR - sorry, I'm not going to use a lot of time trying to figure out what the different pieces of code performs and where the error might occur...

To get the normal Debug thingie, comment the line starting with on error goto...

Roy-Vidar

 

[hymn]

If you have just copied your code
one of the recordsets is not a DAO and the Db needs to be DAO
Dim db As Database
Dim rstSystem As DAO.Recordset
Dim rstCONT As DAO.Recordset
Dim rstFrmCONT As Recordset

Dim db As DAO.Database
Dim rstSystem As DAO.Recordset
Dim rstCONT As DAO.Recordset
Dim rstFrmCONT As DAO.Recordset

Hope this helps
Hymn

 

[SpandexBobcat]

Well I havent managed to find the problem in the code but I can see from running it what is happening...

On pressing the button the second time to create another copy, for some reason the Customer_No field goes blank... Thus the 'indexed or primary fields cannot contain null values' error occurs. Why it is happening I have no idea.

I will soldier on...

"If at first you don't succeed, destroy all evidence that you tried.

 

[TheAceMan1]

SpandexBobcat . . . .

Sorry to get back so late . . . . wee bit of a hectic day. After going over this thread in depth, here's my synopsis.

First, the method were using here, is that of [blue]setting the [purple]Default Values[/purple] for the controls.[/blue] [purple]Be aware: this only works for a new record![/purple] Its a nice way to preset values in a new record without triggering [blue]Edit Mode![/blue]

I couldn't help but notice that in your code, the postional residence of the code we've been working with, is [blue]out of sync[/blue] with the rest of the code. It explains the latest problem your having. Consider this:

[blue]        ' Save enquiry record with new RC No.
        Me!RC_No = lngNextRC
        Me.Dirty = False[/blue]

When you write to [blue]RC_No[/blue] the record switches to [blue]Edit Mode[/blue]. Then, when you set [blue]Dirty to False[/blue], [purple]the record is saved and Edit Mode is turned off![/purple] [blue]Consider; it is no longer a new record![/blue]. Further down the line we start setting defaults, and as it should be, the defaults go in the new record, not the record just saved!. Hence you have a record with RC_No and no defaults and a new record with no RC_No but has defaults.

Now there's a [blue]secondary problem related[/blue] to the code block where you assign a value to [blue]RC_No[/blue]. This is indicitive of the focus being set to a new record. On initial run of the code, [blue]a new record is surely not where you want to copy defaults from.[/blue] [purple]The focus should be on a previously selected record and should stay there per your requirement of successive running of the code![/purple]

We also have an issue with the ListBoxes. . .
[purple]Are you saying the ListBoxes are bound (Control Source Properties are set to fields)? [/purple]
If there not, then [blue]Xfer[/blue] needs to be removed from their [blue]Tags.[/blue]

Taking all the above into account, the following is the proper sequence of events using your code:

[blue]1) Hold PrimaryKey for return to Selected Source Record

2) Set Defaults

3) Move Focus to New record

4) Set PrimaryKey/RC_No

5) Save Record

6) Requery the Form

7) Set focus back to Source Record (via recordset bookmark) for the next run[/blue]

[purple]Let me know what ya think . . . . .[/purple]

See Ya! . . . . . .

 

[SpandexBobcat]

AceMan,

Thanks for getting back to me. What you are saying sounds very sensible, I only wish I could implement the damn thing! I have tried changing the code but don't seem to be having much luck...
You are probably right in saying that the record default should be set before RC_No is written, after all, this is the only field on the foprm I don't want to copy across. (although in testing the code it didn't seem to copy it across...)
The listboxes record sources are fields in a table and the copying of them has worked successfully.
I don't want to take up too much of your time on this, I am sure you have more important things to do. I really do appreciate your help with this but I am going to admit defeat and pass it on to a friend to have a look at.
I will post back with the resolution. Thanks again,
SB


"If at first you don't succeed, destroy all evidence that you tried.