opening a form from a form

[Linda]

I am trying to open a form from a button on the main form. I need to pass a
current identifier to the form being called and then would like all records
in the table with that identifier. I used OpenArgs to pass that identifier.
Then I tried DoCmd.FindRecord in the OnOpen event. However, all the records
in the table are being returned and assigned the passed identifier. What am
I doing wrong? Is there another way to do this, besides a subform. This
secondary form also will need to be openable from the main switchboard so
stands alone in that case.
Thanks in advance for the help.

 

[BruceM]

You could use the Where condition of the OpenForm action:

DoCmd.OpenForm "FormName", , ,"[SomeField] = " & Me.[SomeField]

This would be as an alternative to OpenArgs. You could use OpenArgs, but I
think it would be simpler to specify the Where condition when you open the
form.

In general, if you are having trouble with code, post the code. "I tried
DoCmd.FindRecord in the OnOpen event" doesn't provide much information.

 

[Maverick]

Why are you using the OpenArgs function to filter the results? You could just
use the WhereCondition (without the WHERE) to filter the form.

DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"

Me.ID is the identifier on the main form and ID is the identifier on the
form you are opening.

This method will have no affect on opening the form as a stand alone.
However, if you use an OpenArgs method, you would need to have the code check
if OpenArgs is Null before proceeding.

HTH

 

[BruceM]

I did say it would be best to see the code. Using the Where condition
involves one step, while OpenArgs involves passing the argument, then acting
on it. IMHO, doing this in one step makes the most sense.

That being said, I agree that both will work if used properly, but trying to
manipulate the recordset in the Open event will not, as it has not been
loaded. I should have mentioned that in my initial reply. Maybe they
should have been called LoadArgs to discourage the temptation to use them in
the Open event. Oh well.

 

[Maverick]

I agree with everyone. OpenArgs would work, if done correctly. I did say that
if Linda wanted to use an OpenArgs approach that she would need to have an If
statement on the target form to check if there is a value for OpenArgs so
that the form could be opened as a stand alone.

Because everyone beat me to the question of seeing her code, I saw no reason
to reiterate it.... only to offer another suggestion while waiting for her
response.

 

[Linda]

Thank you all for the continued discussion. I have been trying the various
suggestions but still can't quite get it right. I will include my code for
my attempts below:


Bruce M: DoCmd.OpenForm "FormName", , ,"[SomeField] = " & Me.[SomeField]
My Code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number

Assoc_AC_Num_txt is the name of the field on the 2nd form (called form)
SPR_Number is the name of the field on the main form

Result: A dialogue box pops up requesting Assoc_AC_Num_txt

Maverick: DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"
(note: the two suggestions seem opposite to me in what equals what)
My code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Me.SPR Number] = 'Assoc_AC_Num_txt'"

Result: Error "Invalid braceting of name '[Me.Spr Number]'.

Tried removing square brackets and got an error of missing operator.

Linq Adams: OpenArgs
My code: In Form IIRProblemReport1

Private Sub RiskBtn_Click()
On Error GoTo Err_RiskBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_Tracker"
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
DoCmd.OpenForm stDocName, OpenArgs:=stLinkCriteria

My Code: in Form Risk_Tracker

Private Sub Form_Open(Cancel As Integer)

If Len(Me.OpenArgs) > 0 Then
Me.Assoc_AC_Num_txt = Me.OpenArgs
End If

Result:
Opens the Risk_Tracker form but returns all of the records in the risk table
with an Assoc_AC_Num equal to the number passed in from OpenArgs.

Desired result:
Risk_Tracker form would open displaying only those records that have the
same AC_Num as the record its called from in the main form. If none exists,
a blank form would open. The risk tracker form needs to be able to open from
the switchboard with no associated AC Number.

I've tried many "tweaks" to these but I just can't quite get it right.
Thanks so much for your help!
Linda

 

[BruceM]

This line of code:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number
means essentially "Open the Risk_Tracker form, loading only the record in
which the field Assoc_AC_Num_txt is the same as SPR_Number from the calling
form (the form containing RiskBtn).

The assumptions behind the code as written are that Assoc_AC_Num_txt is a
field in the record source for Risk_Tracker, and that SPR_Number is a Number
field. If it is a text field you need to change the quotes:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = " " " &
Me.SPR_Number & " " " "
or
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = ' " & Me.SPR_Number
& " ' "
(spaces between the quotes added for clarity)

Remember that Assoc_AC_Num_txt is a *field* in the called form's record
source, not a control on the called form. It could be the control and the
field have the same name (I prefer to avoid this), but it has to be a field,
whatever else it may be.

This is the correct order in the Where argument: First the field in the
called form's record source, then a field in the calling form's record
source or a control on the calling form.

Regarding this expression:
"[Me.ID] = 'ID'"
it should be:
"[ID] = 'ID'"
with this caveat: the syntax will work only if you are seeking the literal
value "ID". I described above the different approach to a number field and
a text field when you use the field's value rather than a literal value.

Regarding OpenArgs, I don't think this syntax will work:
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
The Me prefix identifies the current form (or report). If [SPR Number] is a
field in the form's record source or a control on the form you could have:
stLinkCriteria = Me.[SPR Number]
(or Me![SPR Number])
or you could do:
stLinkCriteria = Forms!Form_IIRProblemReport1![SPR Number]
There is no need I can see for the longer syntax in this case, since the
shorter version will work.

In any case, you need to be clear about whether you are using text or number
values. If you are using a number (long integer) value you should probably
declare it that way:

Dim stDocName As String
Dim lngLinkCriteria As Long

stDocName = "Risk_Tracker"
lngLinkCriteria = Me.[SPR Number]

DoCmd.OpenForm stDocName, OpenArgs:=lngLinkCriteria

Using the string may work, but it is best to be clear what type of value is
being used.

In any case, add a break point to be sure the OpenArgs value is what you
expect. To do this, open the code window, and click in the vertical bar
just to the left of the code, next to the line lngLinkCriteria = Me.[SPR
Number]. You should see a red dot in the bar, and the line of code should
be highlighted. Go to the form and run the code by clicking the command
button. The code will break at the break point you set. Press the F8 key
to step through the code one line at a time (use F5 to run through the code
either to the end or to the next break point). When you are past the
lngLinkCriteria = Me.[SPR Number] line, go back and hover the mouse over
lngLinkCriteria. You will see the value. That is the value being passed to
the called form.

Thank you all for the continued discussion. I have been trying the
various
suggestions but still can't quite get it right. I will include my code
for
my attempts below:


Bruce M: DoCmd.OpenForm "FormName", , ,"[SomeField] = " & Me.[SomeField]
My Code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number

Assoc_AC_Num_txt is the name of the field on the 2nd form (called form)
SPR_Number is the name of the field on the main form

Result: A dialogue box pops up requesting Assoc_AC_Num_txt

Maverick: DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"
(note: the two suggestions seem opposite to me in what equals what)
My code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Me.SPR Number] = 'Assoc_AC_Num_txt'"

Result: Error "Invalid braceting of name '[Me.Spr Number]'.

Tried removing square brackets and got an error of missing operator.

Linq Adams: OpenArgs
My code: In Form IIRProblemReport1

Private Sub RiskBtn_Click()
On Error GoTo Err_RiskBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_Tracker"
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
DoCmd.OpenForm stDocName, OpenArgs:=stLinkCriteria

My Code: in Form Risk_Tracker

Private Sub Form_Open(Cancel As Integer)

If Len(Me.OpenArgs) > 0 Then
Me.Assoc_AC_Num_txt = Me.OpenArgs
End If

Result:
Opens the Risk_Tracker form but returns all of the records in the risk
table
with an Assoc_AC_Num equal to the number passed in from OpenArgs.

Desired result:
Risk_Tracker form would open displaying only those records that have the
same AC_Num as the record its called from in the main form. If none
exists,
a blank form would open. The risk tracker form needs to be able to open
from
the switchboard with no associated AC Number.

I've tried many "tweaks" to these but I just can't quite get it right.
Thanks so much for your help!
Linda

--
Linda

I agree with everyone. OpenArgs would work, if done correctly. I did say
that
if Linda wanted to use an OpenArgs approach that she would need to have
an If
statement on the target form to check if there is a value for OpenArgs so
that the form could be opened as a stand alone.

Because everyone beat me to the question of seeing her code, I saw no
reason
to reiterate it.... only to offer another suggestion while waiting for
her
response.

 

[Linda]

Ok,
The record source for the field in the form being called is Assoc_AC_Num.
(I agree in keeping the names different). The field in the form with the
button is a text field and is named SPR Number. (No underscore between the
words.) So I'm trying
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num] = """ & Me.[SPR Number]
& """"

The result is a prompt for the Assoc_AC_Num. So appears it doesn't get a
vaule for Me.[SPR Number]. I tried an assignment statement in the code and
get the expected value for Me.[SPR Number] so I must still have quotes or
something messed up in the openform command. Do you see my error?
Thanks,
Linda
--
Linda

This line of code:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number
means essentially "Open the Risk_Tracker form, loading only the record in
which the field Assoc_AC_Num_txt is the same as SPR_Number from the calling
form (the form containing RiskBtn).

The assumptions behind the code as written are that Assoc_AC_Num_txt is a
field in the record source for Risk_Tracker, and that SPR_Number is a Number
field. If it is a text field you need to change the quotes:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = " " " &
Me.SPR_Number & " " " "
or
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = ' " & Me.SPR_Number
& " ' "
(spaces between the quotes added for clarity)

Remember that Assoc_AC_Num_txt is a *field* in the called form's record
source, not a control on the called form. It could be the control and the
field have the same name (I prefer to avoid this), but it has to be a field,
whatever else it may be.

This is the correct order in the Where argument: First the field in the
called form's record source, then a field in the calling form's record
source or a control on the calling form.

Regarding this expression:
"[Me.ID] = 'ID'"
it should be:
"[ID] = 'ID'"
with this caveat: the syntax will work only if you are seeking the literal
value "ID". I described above the different approach to a number field and
a text field when you use the field's value rather than a literal value.

Regarding OpenArgs, I don't think this syntax will work:
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
The Me prefix identifies the current form (or report). If [SPR Number] is a
field in the form's record source or a control on the form you could have:
stLinkCriteria = Me.[SPR Number]
(or Me![SPR Number])
or you could do:
stLinkCriteria = Forms!Form_IIRProblemReport1![SPR Number]
There is no need I can see for the longer syntax in this case, since the
shorter version will work.

In any case, you need to be clear about whether you are using text or number
values. If you are using a number (long integer) value you should probably
declare it that way:

Dim stDocName As String
Dim lngLinkCriteria As Long

stDocName = "Risk_Tracker"
lngLinkCriteria = Me.[SPR Number]

DoCmd.OpenForm stDocName, OpenArgs:=lngLinkCriteria

Using the string may work, but it is best to be clear what type of value is
being used.

In any case, add a break point to be sure the OpenArgs value is what you
expect. To do this, open the code window, and click in the vertical bar
just to the left of the code, next to the line lngLinkCriteria = Me.[SPR
Number]. You should see a red dot in the bar, and the line of code should
be highlighted. Go to the form and run the code by clicking the command
button. The code will break at the break point you set. Press the F8 key
to step through the code one line at a time (use F5 to run through the code
either to the end or to the next break point). When you are past the
lngLinkCriteria = Me.[SPR Number] line, go back and hover the mouse over
lngLinkCriteria. You will see the value. That is the value being passed to
the called form.

Thank you all for the continued discussion. I have been trying the
various
suggestions but still can't quite get it right. I will include my code
for
my attempts below:


Bruce M: DoCmd.OpenForm "FormName", , ,"[SomeField] = " & Me.[SomeField]
My Code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number

Assoc_AC_Num_txt is the name of the field on the 2nd form (called form)
SPR_Number is the name of the field on the main form

Result: A dialogue box pops up requesting Assoc_AC_Num_txt

Maverick: DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"
(note: the two suggestions seem opposite to me in what equals what)
My code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Me.SPR Number] = 'Assoc_AC_Num_txt'"

Result: Error "Invalid braceting of name '[Me.Spr Number]'.

Tried removing square brackets and got an error of missing operator.

Linq Adams: OpenArgs
My code: In Form IIRProblemReport1

Private Sub RiskBtn_Click()
On Error GoTo Err_RiskBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_Tracker"
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
DoCmd.OpenForm stDocName, OpenArgs:=stLinkCriteria

My Code: in Form Risk_Tracker

Private Sub Form_Open(Cancel As Integer)

If Len(Me.OpenArgs) > 0 Then
Me.Assoc_AC_Num_txt = Me.OpenArgs
End If

Result:
Opens the Risk_Tracker form but returns all of the records in the risk
table
with an Assoc_AC_Num equal to the number passed in from OpenArgs.

Desired result:
Risk_Tracker form would open displaying only those records that have the
same AC_Num as the record its called from in the main form. If none
exists,
a blank form would open. The risk tracker form needs to be able to open
from
the switchboard with no associated AC Number.

I've tried many "tweaks" to these but I just can't quite get it right.
Thanks so much for your help!
Linda

--
Linda

I agree with everyone. OpenArgs would work, if done correctly. I did say
that
if Linda wanted to use an OpenArgs approach that she would need to have
an If
statement on the target form to check if there is a value for OpenArgs so
that the form could be opened as a stand alone.

Because everyone beat me to the question of seeing her code, I saw no
reason
to reiterate it.... only to offer another suggestion while waiting for
her
response.

:

I did say it would be best to see the code. Using the Where condition
involves one step, while OpenArgs involves passing the argument, then
acting
on it. IMHO, doing this in one step makes the most sense.

That being said, I agree that both will work if used properly, but
trying to
manipulate the recordset in the Open event will not, as it has not been
loaded. I should have mentioned that in my initial reply. Maybe they
should have been called LoadArgs to discourage the temptation to use
them in
the Open event. Oh well.

Either method is perfectly acceptable in this circumstance,
gentlemen.
Offering an alternative solution such as using the Where clause in
opening
the form is fine, but there occasions when OpenArgs is needed, so we
should
also try to figure out ***why*** it's not working for the OP by
examing
her
code.

 

[BruceM]

Does the record source for Risk_Tracker have the field Assoc_AC_Num? If so,
check the spelling. The error is because Access cannot find Assoc_AC_Num in
the record source for Risk_Tracker.

Another point is that the syntax you posted is appropriate if SPR_Number is
a text field. If it is a number field:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num] = " & Me.[SPR Number]

I don't know if that could be the cause of the unwanted prompt you are
getting, but you need to get the syntax correct in any case.

Ok,
The record source for the field in the form being called is Assoc_AC_Num.
(I agree in keeping the names different). The field in the form with the
button is a text field and is named SPR Number. (No underscore between the
words.) So I'm trying
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num] = """ & Me.[SPR Number]
& """"

The result is a prompt for the Assoc_AC_Num. So appears it doesn't get a
vaule for Me.[SPR Number]. I tried an assignment statement in the code
and
get the expected value for Me.[SPR Number] so I must still have quotes or
something messed up in the openform command. Do you see my error?
Thanks,
Linda
--
Linda

This line of code:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number
means essentially "Open the Risk_Tracker form, loading only the record in
which the field Assoc_AC_Num_txt is the same as SPR_Number from the
calling
form (the form containing RiskBtn).

The assumptions behind the code as written are that Assoc_AC_Num_txt is a
field in the record source for Risk_Tracker, and that SPR_Number is a
Number
field. If it is a text field you need to change the quotes:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = " " " &
Me.SPR_Number & " " " "
or
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = ' " &
Me.SPR_Number
& " ' "
(spaces between the quotes added for clarity)

Remember that Assoc_AC_Num_txt is a *field* in the called form's record
source, not a control on the called form. It could be the control and
the
field have the same name (I prefer to avoid this), but it has to be a
field,
whatever else it may be.

This is the correct order in the Where argument: First the field in the
called form's record source, then a field in the calling form's record
source or a control on the calling form.

Regarding this expression:
"[Me.ID] = 'ID'"
it should be:
"[ID] = 'ID'"
with this caveat: the syntax will work only if you are seeking the
literal
value "ID". I described above the different approach to a number field
and
a text field when you use the field's value rather than a literal value.

Regarding OpenArgs, I don't think this syntax will work:
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
The Me prefix identifies the current form (or report). If [SPR Number]
is a
field in the form's record source or a control on the form you could
have:
stLinkCriteria = Me.[SPR Number]
(or Me![SPR Number])
or you could do:
stLinkCriteria = Forms!Form_IIRProblemReport1![SPR Number]
There is no need I can see for the longer syntax in this case, since the
shorter version will work.

In any case, you need to be clear about whether you are using text or
number
values. If you are using a number (long integer) value you should
probably
declare it that way:

Dim stDocName As String
Dim lngLinkCriteria As Long

stDocName = "Risk_Tracker"
lngLinkCriteria = Me.[SPR Number]

DoCmd.OpenForm stDocName, OpenArgs:=lngLinkCriteria

Using the string may work, but it is best to be clear what type of value
is
being used.

In any case, add a break point to be sure the OpenArgs value is what you
expect. To do this, open the code window, and click in the vertical bar
just to the left of the code, next to the line lngLinkCriteria = Me.[SPR
Number]. You should see a red dot in the bar, and the line of code
should
be highlighted. Go to the form and run the code by clicking the command
button. The code will break at the break point you set. Press the F8
key
to step through the code one line at a time (use F5 to run through the
code
either to the end or to the next break point). When you are past the
lngLinkCriteria = Me.[SPR Number] line, go back and hover the mouse over
lngLinkCriteria. You will see the value. That is the value being passed
to
the called form.

Thank you all for the continued discussion. I have been trying the
various
suggestions but still can't quite get it right. I will include my code
for
my attempts below:


Bruce M: DoCmd.OpenForm "FormName", , ,"[SomeField] = " &
Me.[SomeField]
My Code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" &
Me.SPR_Number

Assoc_AC_Num_txt is the name of the field on the 2nd form (called form)
SPR_Number is the name of the field on the main form

Result: A dialogue box pops up requesting Assoc_AC_Num_txt

Maverick: DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"
(note: the two suggestions seem opposite to me in what equals what)
My code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Me.SPR Number] =
'Assoc_AC_Num_txt'"

Result: Error "Invalid braceting of name '[Me.Spr Number]'.

Tried removing square brackets and got an error of missing operator.

Linq Adams: OpenArgs
My code: In Form IIRProblemReport1

Private Sub RiskBtn_Click()
On Error GoTo Err_RiskBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_Tracker"
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
DoCmd.OpenForm stDocName, OpenArgs:=stLinkCriteria

My Code: in Form Risk_Tracker

Private Sub Form_Open(Cancel As Integer)

If Len(Me.OpenArgs) > 0 Then
Me.Assoc_AC_Num_txt = Me.OpenArgs
End If

Result:
Opens the Risk_Tracker form but returns all of the records in the risk
table
with an Assoc_AC_Num equal to the number passed in from OpenArgs.

Desired result:
Risk_Tracker form would open displaying only those records that have
the
same AC_Num as the record its called from in the main form. If none
exists,
a blank form would open. The risk tracker form needs to be able to
open
from
the switchboard with no associated AC Number.

I've tried many "tweaks" to these but I just can't quite get it right.
Thanks so much for your help!
Linda

--
Linda


:

I agree with everyone. OpenArgs would work, if done correctly. I did
say
that
if Linda wanted to use an OpenArgs approach that she would need to
have
an If
statement on the target form to check if there is a value for OpenArgs
so
that the form could be opened as a stand alone.

Because everyone beat me to the question of seeing her code, I saw no
reason
to reiterate it.... only to offer another suggestion while waiting for
her
response.

:

I did say it would be best to see the code. Using the Where
condition
involves one step, while OpenArgs involves passing the argument,
then
acting
on it. IMHO, doing this in one step makes the most sense.

That being said, I agree that both will work if used properly, but
trying to
manipulate the recordset in the Open event will not, as it has not
been
loaded. I should have mentioned that in my initial reply. Maybe
they
should have been called LoadArgs to discourage the temptation to use
them in
the Open event. Oh well.

Either method is perfectly acceptable in this circumstance,
gentlemen.
Offering an alternative solution such as using the Where clause in
opening
the form is fine, but there occasions when OpenArgs is needed, so
we
should
also try to figure out ***why*** it's not working for the OP by
examing
her
code.

 

[Linda]

Thanks, Bruce. I finally got the information passed into the calling form
with the following code:

Private Sub Form_Load()
Dim strPAnumber As String
Dim strSQLRecSrc As String

If Not IsNull(Me.OpenArgs) Then
strPAnumber = Me.OpenArgs
strSQLRecSrc = "SELECT * FROM Risk_Tracker WHERE Assoc_PA_Num ='" &
strPAnumber & "'"
Me.RecordSource = strSQLRecSrc
If CurrentDb().OpenRecordset(strSQLRecSrc).RecordCount = 0 Then
Me.Assoc_AC_Num_txt = strPAnumber
End If
End If
Form_Risk_Tracker.SaveBtn.Enabled = False
Form_Risk_Tracker.UndoBtn.Enabled = False

End Sub

Now, I have two command buttons on the risk form that are disabled when the
form is loaded. I would like to enable them as soon as any typing occurs on
the form. I'm using the on dirty event at the form level but it is not
firing when filling in a new record. If I modifiy a field in an existing
record then the dirty fires. Any idea what is getting me there?

Thanks so much, you've been a great help.
--
Linda

Does the record source for Risk_Tracker have the field Assoc_AC_Num? If so,
check the spelling. The error is because Access cannot find Assoc_AC_Num in
the record source for Risk_Tracker.

Another point is that the syntax you posted is appropriate if SPR_Number is
a text field. If it is a number field:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num] = " & Me.[SPR Number]

I don't know if that could be the cause of the unwanted prompt you are
getting, but you need to get the syntax correct in any case.

Ok,
The record source for the field in the form being called is Assoc_AC_Num.
(I agree in keeping the names different). The field in the form with the
button is a text field and is named SPR Number. (No underscore between the
words.) So I'm trying
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num] = """ & Me.[SPR Number]
& """"

The result is a prompt for the Assoc_AC_Num. So appears it doesn't get a
vaule for Me.[SPR Number]. I tried an assignment statement in the code
and
get the expected value for Me.[SPR Number] so I must still have quotes or
something messed up in the openform command. Do you see my error?
Thanks,
Linda
--
Linda

This line of code:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" & Me.SPR_Number
means essentially "Open the Risk_Tracker form, loading only the record in
which the field Assoc_AC_Num_txt is the same as SPR_Number from the
calling
form (the form containing RiskBtn).

The assumptions behind the code as written are that Assoc_AC_Num_txt is a
field in the record source for Risk_Tracker, and that SPR_Number is a
Number
field. If it is a text field you need to change the quotes:
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = " " " &
Me.SPR_Number & " " " "
or
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] = ' " &
Me.SPR_Number
& " ' "
(spaces between the quotes added for clarity)

Remember that Assoc_AC_Num_txt is a *field* in the called form's record
source, not a control on the called form. It could be the control and
the
field have the same name (I prefer to avoid this), but it has to be a
field,
whatever else it may be.

This is the correct order in the Where argument: First the field in the
called form's record source, then a field in the calling form's record
source or a control on the calling form.

Regarding this expression:
"[Me.ID] = 'ID'"
it should be:
"[ID] = 'ID'"
with this caveat: the syntax will work only if you are seeking the
literal
value "ID". I described above the different approach to a number field
and
a text field when you use the field's value rather than a literal value.

Regarding OpenArgs, I don't think this syntax will work:
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
The Me prefix identifies the current form (or report). If [SPR Number]
is a
field in the form's record source or a control on the form you could
have:
stLinkCriteria = Me.[SPR Number]
(or Me![SPR Number])
or you could do:
stLinkCriteria = Forms!Form_IIRProblemReport1![SPR Number]
There is no need I can see for the longer syntax in this case, since the
shorter version will work.

In any case, you need to be clear about whether you are using text or
number
values. If you are using a number (long integer) value you should
probably
declare it that way:

Dim stDocName As String
Dim lngLinkCriteria As Long

stDocName = "Risk_Tracker"
lngLinkCriteria = Me.[SPR Number]

DoCmd.OpenForm stDocName, OpenArgs:=lngLinkCriteria

Using the string may work, but it is best to be clear what type of value
is
being used.

In any case, add a break point to be sure the OpenArgs value is what you
expect. To do this, open the code window, and click in the vertical bar
just to the left of the code, next to the line lngLinkCriteria = Me.[SPR
Number]. You should see a red dot in the bar, and the line of code
should
be highlighted. Go to the form and run the code by clicking the command
button. The code will break at the break point you set. Press the F8
key
to step through the code one line at a time (use F5 to run through the
code
either to the end or to the next break point). When you are past the
lngLinkCriteria = Me.[SPR Number] line, go back and hover the mouse over
lngLinkCriteria. You will see the value. That is the value being passed
to
the called form.

Thank you all for the continued discussion. I have been trying the
various
suggestions but still can't quite get it right. I will include my code
for
my attempts below:


Bruce M: DoCmd.OpenForm "FormName", , ,"[SomeField] = " &
Me.[SomeField]
My Code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Assoc_AC_Num_txt] =" &
Me.SPR_Number

Assoc_AC_Num_txt is the name of the field on the 2nd form (called form)
SPR_Number is the name of the field on the main form

Result: A dialogue box pops up requesting Assoc_AC_Num_txt

Maverick: DoCmd.OpenForm "YourFormName", , , ,"[Me.ID] = 'ID'"
(note: the two suggestions seem opposite to me in what equals what)
My code:

Private Sub RiskBtn_Click()
DoCmd.OpenForm "Risk_Tracker", , , "[Me.SPR Number] =
'Assoc_AC_Num_txt'"

Result: Error "Invalid braceting of name '[Me.Spr Number]'.

Tried removing square brackets and got an error of missing operator.

Linq Adams: OpenArgs
My code: In Form IIRProblemReport1

Private Sub RiskBtn_Click()
On Error GoTo Err_RiskBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_Tracker"
stLinkCriteria = Form_IIRProblemReport1.[SPR Number]
DoCmd.OpenForm stDocName, OpenArgs:=stLinkCriteria

My Code: in Form Risk_Tracker

Private Sub Form_Open(Cancel As Integer)

If Len(Me.OpenArgs) > 0 Then
Me.Assoc_AC_Num_txt = Me.OpenArgs
End If

Result:
Opens the Risk_Tracker form but returns all of the records in the risk
table
with an Assoc_AC_Num equal to the number passed in from OpenArgs.

Desired result:
Risk_Tracker form would open displaying only those records that have
the
same AC_Num as the record its called from in the main form. If none
exists,
a blank form would open. The risk tracker form needs to be able to
open
from
the switchboard with no associated AC Number.

I've tried many "tweaks" to these but I just can't quite get it right.
Thanks so much for your help!
Linda

--
Linda


:

I agree with everyone. OpenArgs would work, if done correctly. I did
say
that
if Linda wanted to use an OpenArgs approach that she would need to
have
an If
statement on the target form to check if there is a value for OpenArgs
so
that the form could be opened as a stand alone.

Because everyone beat me to the question of seeing her code, I saw no
reason
to reiterate it.... only to offer another suggestion while waiting for
her
response.

:

I did say it would be best to see the code. Using the Where
condition
involves one step, while OpenArgs involves passing the argument,
then
acting
on it. IMHO, doing this in one step makes the most sense.

That being said, I agree that both will work if used properly, but
trying to
manipulate the recordset in the Open event will not, as it has not
been
loaded. I should have mentioned that in my initial reply. Maybe
they
should have been called LoadArgs to discourage the temptation to use
them in
the Open event. Oh well.

Either method is perfectly acceptable in this circumstance,
gentlemen.
Offering an alternative solution such as using the Where clause in
opening
the form is fine, but there occasions when OpenArgs is needed, so
we
should
also try to figure out ***why*** it's not working for the OP by
examing
her
code.

 

[BruceM]

I would use the form's Before Insert event, which fires just once for a new
record, rather than code that runs with each key stroke. As I understand,
the Dirty event is similar to Before Insert, but for an existing record.

 

[BruceM]

I did some testing, and found that the Dirty event does not fire for a new
record. I inserted the line:
Debug.Print "Dirty" into a form's Dirty event, and Debug.Print "Before
Insert" into its Before Insert event. I switched to Form view, modified one
existing record, and added one new record. Afterward the immediate window
showed:

Dirty
Before Insert

If the Dirty event ran for a new record I would have seen "Dirty" twice in
the immediate window. Similar testing using message boxes for immediate
feedback likewise showd the Dirty event firing for an existing record, and
Before Insert firing similarly for a new record.

I agree that in neither case will the code run when typing into an unbound
text box, but did not get the idea from the OP that unbound text boxes were
involved. She did say "when any typing occurs on the form", but shortly
thereafter spoke about "filling in a new record" and "modify an existing
field", which suggest bound controls.

OnDirty should fire up regardless of existing or new record, unless the
cursor is in a unbound control.

Good point on suggestion of BeforeInsert Event. Still it will not fire up
if
it a unbound control.

I would use the form's Before Insert event, which fires just once for a
new
record, rather than code that runs with each key stroke. As I understand,
the Dirty event is similar to Before Insert, but for an existing record.

Why the convoluted approach? If there are no reason for you to disable
the
controls, just leave them enabled.

[quoted text clipped - 9 lines]

firing when filling in a new record. If I modifiy a field in an
existing
record then the dirty fires. Any idea what is getting me there?

 

[BruceM]

I've done some more checking. In the form I was using for a test I had the
following in the form's Current event:
If Me.NewRecord Then
Me.NumberField = DMax("NumberField","tblMain") + 1
End If

When I go to a new record the Before Insert event fires. Testing for Dirty
in the Before Insert event shows the Dirty property to be False. The Dirty
event does not fire. Testing the Dirty property in a text box Double Click
event (it's what I happened to use in my test) shows it to be True. The
reason, I expect, is that the form is dirtied in the Current event, so the
form is already dirty when the first character is typed, so the Dirty event
does not fire.

However, if I change the Current code to:
Me.NumberField.DefaultValue = DMax("NumberField","tblMain") + 1
the Before Insert event fires when I type a character into a new record,
then the Dirty event.

If I type a character into an existing record the Dirty event fires.

In all of the above cases the Before Insert and Dirty events show the Dirty
property to be False.

In any case, testing the Dirty property (using a text box Double Click event
in my test) immediately after typing the first character or changing a
character in an existing record shows the Dirty property to be True, as
expected.

From what I can figure out, the Dirty event and the Before Insert event
always show the Dirty property to be false, so it seems testing for the
Dirty property is redundant. In the case of the control it will always be
enabled in your code sample.

I'm not entirely certain how to sort out all of this, but I see it may not
be possible to use the Before Insert event to enable a control (or do
anything else) when the user starts typing in a new record if I have used
anything like this is the Current event:
If Me.NewRecord Then
[Assign a field value]
End If
Using the Before Insert event would have the same effect as enabling the
control in the Current event after the NewRecord test.

However, if I use the Default Value property to assign a field value the
Before Insert event will fire when the user starts typing.

BTW, I can't get the form's Key Down or Key Press event to do anything. For
a control, yes, but for the form I can't see how it works.

It is rather inscrutable logic that leads to the Dirty property always being
False in the Dirty event.

Bottom line seems to be to take care when assigning automatic values, as
there may be unintended consequences in terms of the rest of the code.

 

[BruceM]

From what I can figure out, the Dirty event and the Before Insert event

That depends on how you design your form. Can't say much from what you
have
descriped.

I thought I had described it in some detail. I have not found a situation
in which the Dirty property is true during the Before Insert or Dirty event.
Can you describe such a situation? Not meaning to sound like I'm issuing a
challenge here, but you seem to be siuggesting that with a different form
design the Dirty property would be True for the Before Insert or Dirty
events.

IMO, the correct method to enable controls where you do not want users who
may not have user rights to alter data is to use the OnOpen event of the
form.
If the OP has a function on user security, say....
'in the open event
If FunctionUserAdminRights Then
'enabled controls
Else
'Disable controls
End if

As you can see, you only run once for the above as to compare with
Dirty/BeforeInsert event on each and every time when records are edited or
inserted.

As I understand, the OP was talking about enabling the controls when a user
starts typing into a record. I can sort of understand why that approach
might be taken for a new record, but it is harder to see why it would happen
for an existing record. In any case I did not take the question to be about
different permissions for different users. Having said that, I agree that
if there are security issues the Open event is a good place to hide or
disable controls. With User Level Security in place that is how I approach
it so I do not have to design different FEs for different users.

 

[BruceM]

OK, so we seem to agree that testing for Dirty in the Before Insert or Dirty
events is pointless, because it will always be False *during* the event.
Canceling as part of the event will leave the Dirty property at False.

The Before Update event by definition runs when the record has been dirtied;
otherwise there is nothing to update. Dirty will always be True *during*
Before Update.

Hi Bruce,


That's what I am saying, A misunderstanding here, no offence taken,
BeforeInsert/Dirty event,
1.If there are no code in these event, when user key input data into a
field
the result is Dirty = True.
2.If there is code, the user can choose the edit or insert records, if
user
cancel then the result Dirty = False. If the user did not cancel, then the
result is True.

3.If there is no code, we'll probably use the BeforeUpdate event the check
the Dirty value.

What I'm saying, is that the initial result or during in the
BeforeInsert/Dirty event the Dirty is False. It is only after the event
that
you need to evaluated the result of "Dirty".


So, what's the point of disable/enable the controls when use have user
level
security? As accord to OP, how are you going to control security with such
event?

The OP said nothing about ULS. About command buttons she said:
"I would like to enable [the two command buttons] as soon as any typing
occurs on the form. I'm using the on dirty event at the form level but it
is not firing when filling in a new record."
I don't understand the point of enabling controls as soon as the form is
dirtied, although as I said I can understand reasons for doing so in a new
record. However, I chose not to attempt to discourage the OP from that
course. Without seeing more of the code I do not see why Dirty did not fire
for a new record, but if the idea is to enable the buttons when typing
starts in a new record I would just use the Before Insert event. That's all
I was saying. In any case, the OP seems to have dropped out of this
conversation some while ago.