Duplicate field from one record to the next

[kbrooks]

This should be simple....and I've actually done it before...but I can NOT get it to work this time. I have a simple form with about 4 fields. I want 2 of them (Account and PatientName) to stay the same from one record to the next to save them from retyping the information. They will need to be able to change the information if it's different, but at least that way if they're entering multiple records for the same account#, they won't have to rekey the information.

Thanks!

 

[AlanS]

Assuming you only want the values to carry over when you're entering new records, try something like this:

Dim Hold1 as String, Hold2 as String 'module level variables
...
Private Sub Form_AfterUpdate
Hold1 = txtField1
Hold2 = txtField2
End Sub
...
Private Sub Form_Current
If Me.NewRecord Then
txtField1 = Hold1
txtField2 = Hold2
End If
End Sub

 

[ghudson]

The wizard in Access 97 has a Duplicate record function but I prefer the control my method below offers because I do not always want all fields duplicated. Follow my example to do what you want.

Dim sFirstName As String
Dim sLastName As String
Dim sMailingLocation As String
Dim sPhoneNumber As String

sFirstName = FirstName 'FirstName is the name of the table field
sLastName = LastName
sMailingLocation = MailingLocation
sPhoneNumber = PhoneNumber

DoCmd.GoToRecord , , acNewRec

FirstName = sFirstName
LastName = sLastName
MailingLocation = sMailingLocation
PhoneNumber = sPhoneNumber

sFirstName = ""
sLastName = ""
sMailingLocation = ""
sPhoneNumber = ""

HTH

 

[DonBro]

I have the same issue as kbrooks.

I have a form that I want my end users to enter data in and one of the fields I want to stay the same from one record to the next.

I have tried to use AlanS' code and it works find when I open the form. The problem is that I need to take the form and use it as a subform.

When I do that and open the new form I receive "The Microsoft Jet database engine can't find a record in the table <name> with key matching field(s) <name>." Once I click okay, it let's me into the form and I can add new data. The problem is that I don't want my end users to get this error.

Since I am a true newbie when it comes to code, I can't figure how to fix this.

Any suggestions.

Thanks

 

[AlanS]

My guess is that the problem does not relate to form vs. subform, but rather a problem with the way your subform is set up. You might want to use the subform wizard to insert one form into another, to ensure that critical properties are set properly. Then go back and compare the properties of the subform control created by the wizard with the subform control you created manually, and you'll probably find the root of this.

There is one (I think unrelated to this error message) issue wiith my code - it won't work properly on the first record you add after opening the form, because the hold variables haven't yet been set by an update event.

 

[DonBro]

AlanS - I am wondering if you may have hit the nail on the head regarding the problem with the first record.

The field in the subform that I am duplicating, is also the field that I am using in my relationships between two tables. It seems like when I open the form, the database is not happy with the field being blank.

I tried using the wizard and checking it against the one I built and they look the same.

Thanks, Don

 

[AlanS]

I'm not sure what else to suggest. The error message "The Microsoft Jet database engine can't find a record in the table <name> with key matching field(s) <name>." is usually caused by attempting to update or save a record on the many side of a one-to-many relationship when there is no matching record on the one side of the relationship. The problem may lie in how you are initializing fields in the subform, or by attempting to save a record without the linked field being properly set. You might want to check what your event procedures are doing, look for cascading events, put in break points so you can trace the control flow during execution, etc. Good luck.

 

[Jerry Stoner]

try this

This changes the default value and should solve your problem.

If Not IsNull(Me.cboPPNID) Then
Me.cboPPNID.DefaultValue = "=" & Me.cboPPNID
'For text fields 'Me.Operatorid.DefaultValue = "='" & Me.OperatorID & "'"
'For date fields 'Me.Operatorid.DefaultValue = "=#" & Me.Operatorid & "#"
'For number fields 'Me.Operatorid.DefaultValue = "=" & Me.Operatorid
End If
ExitHere:
Exit Sub

 

[DonBro]

Jerry - I am a true newbie with code. Can you explain exactly how I would set up the code? Thanks, don

 

[Jerry Stoner]

Put the code in the after update event of Account and PatientName. Use the appropriate syntax provided for various data types.