Carry data to another form

S

Secret Squirrel

I have the following code to carry over my RecordID from one form to another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
K

Ken Snell \(MVP\)

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If
 
S

Secret Squirrel

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

Ken Snell (MVP) said:
Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

Secret Squirrel said:
Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

Ken Snell (MVP) said:
Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
S

Secret Squirrel

Dennis, Can you give me an example of how you would do this?

Dennis said:
Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

Secret Squirrel said:
Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

Ken Snell (MVP) said:
Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


Secret Squirrel said:
Dennis, Can you give me an example of how you would do this?

Dennis said:
Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

Secret Squirrel said:
Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
S

Secret Squirrel

Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

Dennis said:
Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


Secret Squirrel said:
Dennis, Can you give me an example of how you would do this?

Dennis said:
Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

Secret Squirrel said:
Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

Dennis said:
Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


Secret Squirrel said:
Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
S

Secret Squirrel

Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

Dennis said:
PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

Secret Squirrel said:
Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

Dennis said:
Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

Yup. Just keep your filter setup, but before you open Form2, populate a set
of PUBLIC vars with the data you want available to you. Once Form2 is open
you can use the PUBLIC fields to populate the controls of your choice.
HOWEVER, this method DOES NOT allow the PUBLIC fields to be used as
additional filter criteria. The PUBLIC vars are just holders, and not
associated with ANYTHING.

You could pass those values as parameters to a QUERY (for example) or
populate a report with them, or anything else you wanted to do. Maybe I'm not
explaining this well. Your best bet is to create a couple of small test forms
(not linked to any tables) and play with the concept. Once you see it in
action, it becomes crystal clear.



Secret Squirrel said:
Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

Dennis said:
PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

Secret Squirrel said:
Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

:

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
S

Secret Squirrel

I understand the concept with the Public Vars but I do have one question. I
seem to have it working but every time I open Form2 it automatically creates
a new record and assigns an ID to it. I just want this data to be carried
over as the default for certain controls. I don't want it to generate a new
record unless I start typing data into controls on that form. Do you know
what I mean?

Dennis said:
Yup. Just keep your filter setup, but before you open Form2, populate a set
of PUBLIC vars with the data you want available to you. Once Form2 is open
you can use the PUBLIC fields to populate the controls of your choice.
HOWEVER, this method DOES NOT allow the PUBLIC fields to be used as
additional filter criteria. The PUBLIC vars are just holders, and not
associated with ANYTHING.

You could pass those values as parameters to a QUERY (for example) or
populate a report with them, or anything else you wanted to do. Maybe I'm not
explaining this well. Your best bet is to create a couple of small test forms
(not linked to any tables) and play with the concept. Once you see it in
action, it becomes crystal clear.



Secret Squirrel said:
Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

Dennis said:
PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

:

Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

:

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

Yes. If that's the case, populate the controls using Form2's BeforeUpdate
event. That way, the ONLY way any table fields will be changed is if Access
intends to post a new record.

Secret Squirrel said:
I understand the concept with the Public Vars but I do have one question. I
seem to have it working but every time I open Form2 it automatically creates
a new record and assigns an ID to it. I just want this data to be carried
over as the default for certain controls. I don't want it to generate a new
record unless I start typing data into controls on that form. Do you know
what I mean?

Dennis said:
Yup. Just keep your filter setup, but before you open Form2, populate a set
of PUBLIC vars with the data you want available to you. Once Form2 is open
you can use the PUBLIC fields to populate the controls of your choice.
HOWEVER, this method DOES NOT allow the PUBLIC fields to be used as
additional filter criteria. The PUBLIC vars are just holders, and not
associated with ANYTHING.

You could pass those values as parameters to a QUERY (for example) or
populate a report with them, or anything else you wanted to do. Maybe I'm not
explaining this well. Your best bet is to create a couple of small test forms
(not linked to any tables) and play with the concept. Once you see it in
action, it becomes crystal clear.



Secret Squirrel said:
Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

:

PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

:

Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

:

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
S

Secret Squirrel

But that won't make the default values appear until after the record is
updated. I want the data to show in these controls when the form is opened
but not create a new record. If you look in my first post I have my original
code that carries over the [RMA] control from Form1 and puts it in a control
Yes. If that's the case, populate the controls using Form2's BeforeUpdate
event. That way, the ONLY way any table fields will be changed is if Access
intends to post a new record.

Secret Squirrel said:
I understand the concept with the Public Vars but I do have one question. I
seem to have it working but every time I open Form2 it automatically creates
a new record and assigns an ID to it. I just want this data to be carried
over as the default for certain controls. I don't want it to generate a new
record unless I start typing data into controls on that form. Do you know
what I mean?

Dennis said:
Yup. Just keep your filter setup, but before you open Form2, populate a set
of PUBLIC vars with the data you want available to you. Once Form2 is open
you can use the PUBLIC fields to populate the controls of your choice.
HOWEVER, this method DOES NOT allow the PUBLIC fields to be used as
additional filter criteria. The PUBLIC vars are just holders, and not
associated with ANYTHING.

You could pass those values as parameters to a QUERY (for example) or
populate a report with them, or anything else you wanted to do. Maybe I'm not
explaining this well. Your best bet is to create a couple of small test forms
(not linked to any tables) and play with the concept. Once you see it in
action, it becomes crystal clear.



:

Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

:

PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

:

Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

:

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
D

Dennis

Then use unbound controls for those fields that you want to show (but not
trigger an update). Then, IF a record is to be created, you can then set the
table fields in the BeforeUpdate event.

Secret Squirrel said:
But that won't make the default values appear until after the record is
updated. I want the data to show in these controls when the form is opened
but not create a new record. If you look in my first post I have my original
code that carries over the [RMA] control from Form1 and puts it in a control
Yes. If that's the case, populate the controls using Form2's BeforeUpdate
event. That way, the ONLY way any table fields will be changed is if Access
intends to post a new record.

Secret Squirrel said:
I understand the concept with the Public Vars but I do have one question. I
seem to have it working but every time I open Form2 it automatically creates
a new record and assigns an ID to it. I just want this data to be carried
over as the default for certain controls. I don't want it to generate a new
record unless I start typing data into controls on that form. Do you know
what I mean?

:

Yup. Just keep your filter setup, but before you open Form2, populate a set
of PUBLIC vars with the data you want available to you. Once Form2 is open
you can use the PUBLIC fields to populate the controls of your choice.
HOWEVER, this method DOES NOT allow the PUBLIC fields to be used as
additional filter criteria. The PUBLIC vars are just holders, and not
associated with ANYTHING.

You could pass those values as parameters to a QUERY (for example) or
populate a report with them, or anything else you wanted to do. Maybe I'm not
explaining this well. Your best bet is to create a couple of small test forms
(not linked to any tables) and play with the concept. Once you see it in
action, it becomes crystal clear.



:

Well what I'm trying to do is carry over some data from a record that I have
opened in Form1 and put it in a specific field on Form2. The data is carried
over and is using the RecordID from Form1 as the filter. Does that make sense?

:

PUBLIC vars are not "anything"-specific. Think of them as holding-areas for
values. Once you set them, ANY form can have its controls populated by them.
And their values DO NOT CHANGE until you do it. Or, you can use them in an
ADO record-add, or as a key in a SELECT statement to lookup other records.
It's really up to you, how you use those PUBLIC vars once you have them.

If that doesn't help, perhaps I misunderstood your last question...?

:

Dennis, Does it matter if this data is record specific or will it carry the
data that is in the current record when I open Form2?

:

Create a new "module". Always add "option explicit" to any VBA
form/report/etc that you code up.

In that new module...

PUBLIC MyVar1 as string
PUBLIC MyVar2 as integer
PUBLIC MyVar3 as date
(etc, just like DIM statements)

SAVE the module as some name. I usually use "globalDefs".

Then, in the code for Form1 (in the correct event):

MyVar1 = me.MyForm1VarX
MyVar2 = me.MyForm1VarY
(etc)

Then, in Form2 (in the correct event - usually in the FormOpen event):

me.MyForm2VarX = MyVar1
me.MyForm2VarY = MyVar2
(etc)

That's it.


:

Dennis, Can you give me an example of how you would do this?

:

Personally, I define PUBLIC variables in a module, then set those values in
Form1. When I open Form2, I populate it's controls from those values. I don't
have to "pass" anything. It's all there, and available to every form I open.

:

Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

:

Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
K

Ken Snell \(MVP\)

It's not populating that control because my example code didn't show that
step -- would have been a real guess on my part to know the name of the
control or what you wanted to do with the customer value, so I showed how
you could read the customer value from the OpenArgs string by putting the
value into a variable.

Try this in the frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
Me![Customer].Value = varOpenArgs(1)
End If
--

Ken Snell
<MS ACCESS MVP>

Secret Squirrel said:
Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

Ken Snell (MVP) said:
Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the
pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA]
&
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to
also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) &
Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 
K

Ken Snell \(MVP\)

And if you want to set the Default Value of the Customer control to that
value instead:

Try this in the frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
Me![Customer].DefaultValue = Chr(34) & varOpenArgs(1) & Chr(34)
End If

--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
It's not populating that control because my example code didn't show that
step -- would have been a real guess on my part to know the name of the
control or what you wanted to do with the customer value, so I showed how
you could read the customer value from the OpenArgs string by putting the
value into a variable.

Try this in the frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
Me![Customer].Value = varOpenArgs(1)
End If
--

Ken Snell
<MS ACCESS MVP>

Secret Squirrel said:
Hi Ken,
I did what you said but it's not populating the "Customer" control on the
'frmCorrectiveAction". Am I missing something?

Ken Snell (MVP) said:
Concatenate the values into a text string for the OpenArgs string, then
split the string in the new form. In the example below, I am using the
pipe
( | ) character as the delimiter.


Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) & Me![RMA]
&
Chr(34), , , Me![RMA] & "|" & Me!Customer

frmCorrectiveAction:

Dim varOpenArgs As Variant
Dim strCustomer As String
If Len(Me.OpenArgs) > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me![RMA#].DefaultValue = Chr(34) & varOpenArgs(0) & Chr(34)
strCustomer = varOpenArgs(1)
End If

--

Ken Snell
<MS ACCESS MVP>



message I have the following code to carry over my RecordID from one form to
another
but now I want to carry over other data as well. The field I want to
also
carry over is called "Customer". How would I re-write this code to add
another data field to be carried over?

Main Form:

DoCmd.OpenForm "frmCorrectiveAction", , , "[RMA#]=" & Chr(34) &
Me![RMA] &
Chr(34), , , Me![RMA]

frmCorrectiveAction:

If Len(Me.OpenArgs) > 0 Then
Me![RMA#].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top