Creating a new record but passing information

[Colin Weir]

Hi there

I am wanting to place a button on a form that would create a new record of information but keeping the same customer details. I want it to pass the name etc over the the new one. Is this possible?

Thanks

Colin

 

[Access Developer]

Yes, it can be done, with Visual Basic for Applications code (or macros).

However, more likely, you should restructure your data and include a foreign
key in both records to the same customer record in a separate customer
table. There are good reasons for following good relational database design
principles (aka "normalizing" data base design) -- makes design and
implementation (and modification) a lot easier.

 

[Colin Weir]

Hi

Thanks for your reply. In this instance I would like to create s new record in the same table. I have the following in the database to pass information from one table to another

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Issues and Concerns"
strPassValues = Me.[Reference Number] & "~" & Me.Surname & "~" & Me.[First Name] & "~" & Me.Town & "~" & Me.Address & "~" & Me.[Address (2)] & "~" & Me.[address(3)] & "~" & Me.Postcode & "~" & Me.[Telephone No:]
stLinkCriteria = "[Reference Number]=" & Me![Reference Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , strPassValues

However adding this to a record opperation button where the DoCmd is GoToRecord doesn't work. i.e the following
DoCmd.OpenForm , , , , stLinkCriteria, , , strPassValues

Do you have any tips for this?

Thanks again

Colin

 

[Bob Quintal]

Hi

Thanks for your reply. In this instance I would like to create s
new record in the same table. I have the following in the
database to pass information from one table to another

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Issues and Concerns"
strPassValues = Me.[Reference Number] & "~" & Me.Surname & "~"
& Me.[First Name] & "~" & Me.Town & "~" & Me.Address & "~" &
Me.[Address (2)] & "~" & Me.[address(3)] & "~" & Me.Postcode &
"~" & Me.[Telephone No:] stLinkCriteria = "[Reference
Number]=" & Me![Reference Number] DoCmd.OpenForm stDocName, ,
, stLinkCriteria, , , strPassValues

However adding this to a record opperation button where the DoCmd
is GoToRecord doesn't work. i.e the following
DoCmd.OpenForm , , , , stLinkCriteria, , , strPassValues

Do you have any tips for this?

Thanks again

Colin

Re-READ mr Linson's reply.

Boyce and Codd's rules for database design say you should only need
to pass one value to the new record, and that is the key value from
the the persons table. [surname], [first name], [town] and the other
values already exist and need not be passed, which is why Access does
not have an easy functionality to do this.

Besides, your code does not define the stLinkCriteria, which you
should not use attempting to add a record, but you should set the
Datamode parameter to the acFormAdd constant, so that Access knows
that it you want it to be going to a new record. You would use the
OpenArgs to pass the value(s) you need, but you need the form's on
load event to parse that (those) value(s) to the cointrols where they
belong.

 

[Access Developer]

I don't have recommendations for adding or editing Records using code to
drive Forms, because it is rare that it's not the most inefficient and
clumsy way to manipulate data.

Assuming "Reference Number" is a unique identifier for the record, it
appears you are trying to open another form displaying the same record, when
what you would need

If the name and address information in your example is the customer
information, my previous recommendation still holds -- it is redundant to
carry that same information in multiple Records... it should be in a
separate Table, with a "foreign key" in this Table identifying the Record in
that separate customer Table. (If you do that, you'll only need to pass that
foreign key to the new record.)

You are putting the values in the OpenArgs argument of OpenForm, but you are
not opening the Form with a DataMode of acFormAdd (to open to add a new
record and acFormAdd is not the default for that argument). If you open the
new Form in DataMode of acAddNew, you can put VBA code in the FormLoad (not
the Open) event to pick up the OpenArgs data and enter it into the
corresponding _Control_ on the Form (you say nothing about code to pick up
the OpenArgs values and do something with them)... that may work for you.
But, CAVEAT EMPTOR, I rarely use Forms with VBA code to manipulate data, and
I can't take the time just now to construct a DB to test this
recommendation, so it's untested speculation.