Copy Identical Field Values from Form A to Form B?

[Guest]

I have a co-worker who asked me a question yesterday. I have a pretty good
idea as to what needs to be done, but I’m not sure how this is accomplished
in Access 2003.

She has Form A [which is the first form opened] where she looks up
information stored in the table made up of 3 fields. If the company and case
info are found in the database, she then wants to copy the values of those 3
fields into the first three fields on Form B.

I’m going to make up some field names:

Form A: CompID / CompName / CaseNo

She then wants to open a 2nd form [Form B] which has the same exact fields:

Form B: CompID / CompName / CaseNo / + many other fields

She wants to automatically copy the values of the current record on Form A,
into the corresponding fields on Form B.

 

[ruralguy via AccessMonster.com]

You could do it with the OpenArgs argument of the OpenForm command but it
sounds like a normalization violation. All that should be required is
passing a Key to the next form.

I have a co-worker who asked me a question yesterday. I have a pretty good
idea as to what needs to be done, but I’m not sure how this is accomplished
in Access 2003.

She has Form A [which is the first form opened] where she looks up
information stored in the table made up of 3 fields. If the company and case
info are found in the database, she then wants to copy the values of those 3
fields into the first three fields on Form B.

I’m going to make up some field names:

Form A: CompID / CompName / CaseNo

She then wants to open a 2nd form [Form B] which has the same exact fields:

Form B: CompID / CompName / CaseNo / + many other fields

She wants to automatically copy the values of the current record on Form A,
into the corresponding fields on Form B.

 

[Guest]

Hello ruralguy:

My guess is you're right, it probably is a violation of normalization, but I
didn't have time to see or analyze her table structure and relationships.

However, let's assume for a moment that it's not a violation of
normalization and it's something that needs to be done. How would she use
OpenArgs argument of the OpenForm to copy the values of the 3 fields in the
current record to the corresponding fields in the 2nd form?

Thanks,
Robert

 

[Guest]

As rural guy said, there is probably no reason to pass all three fields.
First the OpenArgs is very useful, but it passes only 1 string. You can fool
it by delimiting the string with a character and using the Split function to
pull it apart in Form B, but that is likely a lot of work for nothing.

The correct technique really would be to filter Form B based on the CompID
when you open it. For example purposes, I will assume you are using correct
naming conventions and that CompID is a field in the recordset and that we
have a text box on Form A named txtCompID.
All you need is:

Docmd.OpenForm "FormB", , ,"[CompID] = " & Me.txtCompID

Now the form will open on the selected record with all the controls on the
form populated.

All it takes is:

 

[Guest]

Hey Guys:

My brain must be in fried mode this afternoon because I just re-read my
original posts and realized I left out a very key point. Therefore I owe you
guys an apology for sending you on the wrong track, sorry about that.

The three separate fields in fields in Form A are combined in Form B to
create one unique field.

CompID + CaseNo + CompName[first or last 3 letters of the company name]

ex. GR3-0007598-ARD

Robert

 

[Guest]

Please report to the Headmaster's office for scolding.

A couple of questions
If there a field in Form B's record source that is the combination of the
the 3 fields?
How do you know whether to use the first or last 3 chars of the company name?
And, what if the company is IBM? Do you use the first or last 3

 

[ruralguy via AccessMonster.com]

So put the string together in FormA and pass it to FormB in the OpenArgs.

strArg = F1 & F2 & F3 '<-- You need to put the string together
Docmd.OpenForm "FormB", , , , , , strArg

And then in FormB OnLoad event put

If Not IsNull(OpenArgs) Then
txtYourControl = OpenArgs
End If

Hey Guys:

My brain must be in fried mode this afternoon because I just re-read my
original posts and realized I left out a very key point. Therefore I owe you
guys an apology for sending you on the wrong track, sorry about that.

The three separate fields in fields in Form A are combined in Form B to
create one unique field.

CompID + CaseNo + CompName[first or last 3 letters of the company name]

ex. GR3-0007598-ARD

Robert

 

[Guest]

Klatuu:

[How do you know whether to use the first or last 3 chars of the company name?
And, what if the company is IBM? Do you use the first or last 3 ]

Maybe I should return to the Headmaster for more punishment? It's always the
same, however, I just couldn't remember if she said it was the first or last
3 letters of the company, I think she said it's the last 3 letters of the
company's name. So yes, it's always going to be the same in every record
with that company.

Robert

 

[Guest]

Hi ruralguy:

How do we deal with the hyphens as in

GR3-00045869-TRS?

 

[Guest]

Okay, but I still need to know about a field matching the contantenated
fields to give you a complete answer.

 

[ruralguy via AccessMonster.com]

str1 & "-" & str2 & "-" & str3

Hi ruralguy:

How do we deal with the hyphens as in

GR3-00045869-TRS?

 

[Guest]

What I am trying to get at, Robert, is that my original solution would still
be preferred if form b is to be positioned on an exiting record. You just
have to reference the field name and the contatenation of values in the Where
argument.

It can be done using the OpenArgs, but then you have to write code to find
the record and position it to be the current record. Using the Where
argument of the OpenForm method is a lot less work.

 

[Guest]

Hi ruralguy:

That appears to be fairly easy, thanks for all of your help

Robert

 

[Guest]

Hi Klatuu:

[ What I am trying to get at, Robert, is that my original solution would
still
be preferred if form b is to be positioned on an exiting record. You just
save to reference the field name and the contatenation of values in the Where
argument].

Thanks for the explanation, I didn't realize the above was true. I certainly
like your logic. Now I have to go back to my co-worker and look at her
tables/forms to see how she set this up. Based upon my memory, it looked as
if your solution would be perfect.

Robert

 

[Guest]

Klatuu:

Is there more that I need to do?

You wrote,
The correct technique really would be to filter Form B based on the CompID
when you open it. For example purposes, I will assume you are using correct
naming conventions and that CompID is a field in the recordset and that we
have a text box on Form A named txtCompID.
All you need is:

Docmd.OpenForm "FormB", , ,"[CompID] = " & Me.txtCompID

Now the form will open on the selected record with all the controls on the
form populated.

All it takes is:

Did you forget to add what follows "All it takes:" is, or is the above
enough to accomplish hour goal?

Thanks,
Robert