Auto Populate Fields when Command Button Clicked (1 Viewer)

Cirrus

Registered User.
Local time
Today, 02:21
Joined
May 14, 2013
Messages
20
Hello everyone.

I have a fairly complex access database with multiple tables, queries and forms. Some forms are embedded in other forms. It is a database that I have worked with in the past and it functions correctly. I am now trying to add some additional functionality and I have not been able to figure how to do it even after searching the posts on this site.



I have a functioning command button that will add a new record within an embedded from. I now want this command button to not only open a new record but to auto-populate certain fields based on the previous record entered. I also want to create two additional command buttons that will add a new record and auto-populate different fields based on the previous record entered.


I suspect that I need code attached to the "On Click" event of the command button but I am not experienced in writing code from scratch.



Hopefully this gives everyone enough information to start a dialogue.


Here is the VBA code for the command button:


Private Sub AddNewPlotCommand_Click()
On Error GoTo Err_AddNewPlotCommand_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddNewPlotCommand_Click:
Exit Sub

Err_AddNewPlotCommand_Click:
MsgBox Err.Description
Resume Exit_AddNewPlotCommand_Click

End Sub
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:21
Joined
Aug 30, 2003
Messages
36,118
Along the lines of:

DoCmd.GoToRecord , , acNewRec
Me.TextboxName = DesiredValue

The desired value could be hard-coded or come from the record displayed before the button was clicked, or come from a table.
 

Cirrus

Registered User.
Local time
Today, 02:21
Joined
May 14, 2013
Messages
20
Thanks for your response Paul. The code works well when I enter in a static value. Can you tell me the code required to equal the record displayed before the button was clicked


DoCmd.GoToRecord , , acNewRec

Me.TextboxName = <last value entered in the table>


Thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:21
Joined
Aug 30, 2003
Messages
36,118
Presuming the desired record is on the form when the button is clicked:

Code:
Dim varWhatever As Variant

varWhatever = Me.TextboxName
DoCmd.GoToRecord , , acNewRec
Me.TextboxName = varWhatever

I used Variant as it's the only data type that can handle Null, but you could also use the appropriate data type along with the Nz() function.
 

Cirrus

Registered User.
Local time
Today, 02:21
Joined
May 14, 2013
Messages
20
Thanks again Paul. That code also worked well on auto-populating the data from one field.



Now, is there a way to have multiple fields auto-populate with data from the last record when the command button is clicked? Some of the field names that I want to pull the data from are: ArrayNumber, GridNumber, GridLAT, GridLONG, etc.



Once again thanks for helping out someone with limited coding knowledge!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:21
Joined
Aug 30, 2003
Messages
36,118
You simply repeat the necessary lines:

varWhatever = Me.TextboxName
varWhatever2 = Me.TextboxName2
varWhatever3 = Me.TextboxName3

DoCmd.GoToRecord , , acNewRec

Me.TextboxName = varWhatever
Me.TextboxName2 = varWhatever2
Me.TextboxName3 = varWhatever3

In real life the variables should have meaningful names.
 

Cirrus

Registered User.
Local time
Today, 02:21
Joined
May 14, 2013
Messages
20
I just figured it out! This code is working for me. Thanks for your help Paul!



Dim varArrayNum As Variant
varArrayNum = Me.ArrayNumber
Dim varGridNum As Variant
varGridNum = Me.GridNumber


DoCmd.GoToRecord , , acNewRec

Me.ArrayNumber = varArrayNum
Me.GridNumber = varGridNum
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:21
Joined
Aug 30, 2003
Messages
36,118
Glad you got it figured out while I was typing!
 

Users who are viewing this thread

Top Bottom