Form; pass value of text box from prior record to add new record (1 Viewer)

[TB11]

Hi. I have a form which opens in edit mode. The form has a textbox fkDetails. I want to add a new record, and copy the fkDetails value (number) to the new record via macro or vba tied to a button.

The prior record from which I am copying may or may not be the last record in the table.

Any thoughts?

 

[The_Doc_Man]

In overview, your special "Add with copy" button would have a button-click routine to do three things in order

1. Store the contents of fkDetails in a string variable.
2. Add a new record and navigate to it.
3. Copy the string variable to the new record's fkDetails control.

Now, this sounds complex? It's not. Make the Command Button with the wizards enabled. You'll have a choice of many options. The one you want is to make a new record. Pick that. The button wizard will build part 2 of the above as VBA code "behind the scenes." Then go back and add step 1 before the "Add record and navigate" code, and add step 3 AFTER the add/navigate code.

Steps 1 and 3 are each one-liners and the wizard will build step 2 for you, so it shouldn't be a heart breaker.

 

[TB11]

@The_Doc_Man THANK YOU! I appreciate the help.

 

[Pat Hartman]

If you open the form, what do you expect the first "previous" value to be? How will you find the record?

 

[arnelgp]

what you have is an FK field?
the simplest is add a button that will "duplicate" your record (duplicate the FKDetails).
add a Command Button that will do the duplication.
add code to the button to actually do the duplication:

Private Sub button1_Click()
    ' duplicate the record
With DoCmd
    .RunCommand acCmdSelectRecord
    .RunCommand acCmdCopy
    .RunCommand acCmdPasteAppend
End With
' now reset Other fields to Null
' you put the Correct fielfname
'Me.txtField1 = Null
'Me.txtfield2=Null
End Sub

 

[TB11]

@Pat Hartman I used the ID field from the record selected where "[fkDetails]=" & [IDDetails]. @arnelgp Thank you for the code. I will try it out, but I found another solution with SetTempVar set on click on first form and SetProperty on load of second form which opened in Add Data Mode.

I appreciate all the help! The people on this forum are the best!

 

[Pat Hartman]

The on load event is the wrong place to set the FK value. That only sets it for ONE record. If you ever need to enter more than one record, none of the others will have the PK.

The correct place to set the FK value is in the form's BeforeInsert event. That way, the FK is correctly populated no matter how many records you add.

 

[TB11]

@Pat Hartman you must be a mind reader - I did need the ability to pass the fk to more than a single record. Your solution is so much better than the work around I came up with.

THANK YOU!

 

[Pat Hartman]

Glad you are happy with it. It is very important to learn what form events are intended to do. Here's a link to a video. Sorry it is so long (half hour) where Uncle Gizmo and I talk about the BeforeUpdate event of the form which is the most important event of all You should be able to view the video without creating an account.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

 

[TB11]

@Pat Hartman the link works. I'm very excited to watch this in full tonight. The before events are not something I ever really considered, but I will now.

Thank you, again!

 

[Pat Hartman]

You're welcome. Get some popcorn and enjoy the show There is another episode on the way.

 

[TB11]

@Pat Hartman popcorns not my thing, but how about apple cider donuts?

is there a way I can get a notification when the next episode comes out?

 

[Pat Hartman]

I'll post it here. Probably in the FAQ section. I think that is where I posted the last one.

 

[TB11]

@Pat Hartman I'll keep my eyes open for it.

 

[TB11]

@Pat Hartman What an awesome video for the BeforeUpdate event, and why it should be used. You were right, that is exactly where I need to put my code to pass thru the fk.

 

[Pat Hartman]

I put the link to the video here also. Look for the next installment in the same forum. I'm hoping a week or two.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[TB11]

@Pat Hartman Love, love, love BeforeUpdate Event. You certainly showed the way to avoid bad data being saved.

THANK YOU.

 

[Pat Hartman]

You're welcome and thank you I'm so glad you liked it