if checked go here...

[fishqqq]

can someone please tell me how to do the following:

1) the user hits the command button to open FORM A
2) the program actually opens FORM 1 and looks to see if checkbox 1 is
yes or no
3) if yes then the program opens FORM A, if no the programs opens FORM
B

can anyone show me how to do this?

i appreciate your suggestions.
Tks
Steve

 

[Guest]

Does FORM 1 have anything other than the check box on it? Does the user
check the box before one of the other forms opens? If the check box is
either checked or unchecked by code in FORM 1, where does it get its
information to know where to make it checked or unchecked?

The reason I ask is, based on your description, FORM 1 opens with a check
box already either checked or unchecked and you make a decision on which form
to open based on the value of the check box.

It that is correct, would it be possible to move the logic that either
checks or unchecks the box to the Click event of the command button that
currently opens FORM 1? In this case, there would be no real need for FORM
1, you could open FORM A or FORM B from this point.

If, on the other hand, the user has to check the box on FORM 1 before either
A or B can be opened, the logic to do that should be in the After Update
event of the check box.

Dim strFormName as String

If Me.MyCheckBox = True Then
strFormName = "FORM A"
Else
strFormName = "FORM B"
End If
DoCmd.OpenForm strFormName

 

[fishqqq]

Thanks let me try this. i'm confused myself now about the check
box...ha.

 

[John Nurick]

This seems unnecessarily complicated.

If the checkbox on FORM 1 is bound to a field in a table, it's simpler
to use DLookup() to get the corresponding value directly from the table.
E.g. this air code (see Help on DLookup() for more):

If DLookup("SomeField", "SomeTable", "Some criterion") = True Then
DoCmd.OpenForm "FormA" ...
Else
DoCmd.OpenForm "FormB" ...
End If

If the checkbox is unbound, opening the form and immediately reading the
value will just get you its default value, which will presumably always
be the same, in which case there's no need for the logic at all.

 

[fishqqq]

Thanks John,
how do i pick a specific field from a specific record in the table???

the field is called "sent to Accting and print inv"
the table is called "Full Air Quote"

therefore i would have the code say...

If DLookup("sent to Accting and print inv", "Full Air Quote", "Some
criterion") = True Then
DoCmd.OpenForm "FormA" ...
Else
DoCmd.OpenForm "FormB" ...
End If

there will ALWAYS be an underlying form open called "General Quote"
which has a field called "Ref # ". The table this form is based on is
called "Full Air Quote". So when the user is in record 10372 (for
example) the user will have the option of opening another form called
"Profit Tracking 2" - if the "sent to accting and print inv" field is
"NO" , or to another form called "Profit Tracking 3" - if the "sent to
accting and print inv" field is set to "YES".

I hope this explains things a bit better? I should be able to figure it
out if you can show me how to pick a specific field in a specific
record from a table - keep in mind the user can be in ANY record and i
need that Ref # to be the one used in the code.

Tks

 

[John Nurick]

Try this:

If DLookup("sent to Accting and print inv", "Full Air Quote", _
"[Ref # ]=" & Me.Controls("Ref # ").Value) = True Then
...

It's partly a matter of taste, but in my experience names like the ones
you're using make life unnecessarily complicated (especially the
trailing space in "Ref # ". As a rule, life is easier if you

-don't use spaces or special characters in the names of fields or other
object, just letters, numbers and _underscores_.

-don't give controls on forms the same names as the fields they are
bound do. Instead, use a convention like
RefNum - field
txtRefNum - textbox bound to RefNum field.

 

[fishqqq]

help....

The "if DLookup..." line has some sort of problem. is there supposed to
be 2 lines of code on the "if DLookup..." line or is that formatting my
web browser is doing??? when i run the code this line turns yellow and
gives me a syntax error in the query expression 'send to Accting and
print inv'

Private Sub Command192_Click()
If DLookup("send to Accting and print inv", "Full Air Quote", _
"[Ref # ]=" & Me.Controls("Ref # ").Value) = True Then


DoCmd.OpenForm "Profit Tracking 3"
Else
DoCmd.OpenForm "Profit Tracking 2"
End If

End Sub

 

[John Nurick]

The space+underscore at the end of the first line is the VBA line
continuation character: it causes the two lines to be treated as one.

Have you tried enclosing the field name and table name in brackets, e.g.

...DLookup("[send to Accting and print inv]", ...

Is the name of the field in the [Full Air Quote] table actually [send to
Accting and print inv] ? (Life is simpler without all these spaces in
names!)

Is the [Ref # ] field a number (as its name indicates) or a text field?
If the latter, you need to use apostrophes or quotes to enclose it, e.g.

..., "[Ref # ]='" & Me.Controls("Ref # ").Value & "'")

help....

The "if DLookup..." line has some sort of problem. is there supposed to
be 2 lines of code on the "if DLookup..." line or is that formatting my
web browser is doing??? when i run the code this line turns yellow and
gives me a syntax error in the query expression 'send to Accting and
print inv'

Private Sub Command192_Click()
If DLookup("send to Accting and print inv", "Full Air Quote", _
"[Ref # ]=" & Me.Controls("Ref # ").Value) = True Then


DoCmd.OpenForm "Profit Tracking 3"
Else
DoCmd.OpenForm "Profit Tracking 2"
End If

End Sub

 

[fishqqq]

Hi John, I'm sorry if this is replying to your email and not the
NG...Google only offers me a 'reply' button - doesn't tell me where
it's linked to... I'll see if there is another way to reply somehow...

pls let me know if there's an obvious mistake in the code as it's not
working correctly.

 

[John Nurick]

One of the problems with posting from Google is that your replies don't
include any context from the message you're replying to. This means I
can't tell which of my messages you're responding to and so can't start
to give a useful answer.

People posting to another newsgroup are told to do this to get their
message to include the message they're replying to. See if it works for
you:

"If you want to post a followup via groups.google.com, don't use the
broken "Reply" link at the bottom of the article. Click on "show
options" at the top of the article, then click on the "Reply" at the
bottom of the article headers."

Also, you say the code "isn't working correctly". That's a big help in
troubleshooting. Please explain what it is doing, or is not doing,
that's different from what you expect to happen.

 

[fishqqq]

Hi John, the brackets worked great. not the correct form opens up but
it's not linking to the correct record. i looked into the the form that
should link and the linking field is actually called [ref link]. i
tried inserting that in both spots in the code and again the correct
form opens up but the records aren't linking. btw [ref link] is a text
field and [ref # ] is an autonumber field .

Thanks
Steve



The space+underscore at the end of the first line is the VBA line
continuation character: it causes the two lines to be treated as one.


Have you tried enclosing the field name and table name in brackets,
e.g.


...DLookup("[send to Accting and print inv]", ...


Is the name of the field in the [Full Air Quote] table actually [send
to
Accting and print inv] ? (Life is simpler without all these spaces in
names!)


Is the [Ref # ] field a number (as its name indicates) or a text field?

If the latter, you need to use apostrophes or quotes to enclose it,
e.g.


..., "[Ref # ]='" & Me.Controls("Ref # ").Value & "'")


On 14 Jan 2006 13:42:38 -0800, "(e-mail address removed)"

 

[fishqqq]

I just realized it's linking to the correct subform (via [ref #])
within the main form but not the correct form (Profit Tracking 2). If i
can get it to direct to the [ref link] field in the [Profit Tracking 2]
form then this should work correctly. can you suggest anything else?
Thanks



Hi John, the brackets worked great. not the correct form opens up but
it's not linking to the correct record. i looked into the the form that

should link and the linking field is actually called [ref link]. i
tried inserting that in both spots in the code and again the correct
form opens up but the records aren't linking. btw [ref link] is a text
field and [ref # ] is an autonumber field .


Thanks
Steve


The space+underscore at the end of the first line is the VBA line
continuation character: it causes the two lines to be treated as one.


Have you tried enclosing the field name and table name in brackets,
e.g.


...DLookup("[send to Accting and print inv]", ...


Is the name of the field in the [Full Air Quote] table actually [send
to
Accting and print inv] ? (Life is simpler without all these spaces in
names!)


Is the [Ref # ] field a number (as its name indicates) or a text field?



If the latter, you need to use apostrophes or quotes to enclose it,
e.g.


..., "[Ref # ]='" & Me.Controls("Ref # ").Value & "'")


On 14 Jan 2006 13:42:38 -0800, "(e-mail address removed)"


Reply

 

[John Nurick]

You haven't previously mentioned anything about "linking" or doing
anything special when you open one or other of the two Profit Tracking
forms, and I'm not sure I understand what you're trying to do.

If you want to open a form so it shows only records that meet a specific
condition, you need to use the WhereCondition argument of
DoCmd.OpenForm. This uses the same sort of expression as the last
argument of DLookup, e.g.

DoCmd.OpenForm blah blah, _
"[ref link]='" & Me.Controls("ref link").Value & "'"

See Help on OpenForm, or search the web and/or Google groups for more.

If you want to open a form at a particular record but make it possible
to scroll to all other records, here's something Larry Linson MVP posted
a few years ago:

Set the RecordSource of that Form to a Query that returns the entire
recordset you want. Pass the unique identifier of the record you want in the
OpenArgs argument of the DoCmd.OpenForm statement you use to open the form.
In the OnOpen or OnLoad event, put code to use the passed value to do a
FindFirst on the RecordsetClone of the Form, then if the NoMatch property is
False, set the Form's Bookmark property to the Bookmark property of the
RecordsetClone.

Here's sample code for the DoCmd.OpenForm:

Dim stDocName As String
stDocName = "tblEmployees"

DoCmd.OpenForm stDocName, , , , , , Me![Combo0]

Here's corresponding sample code for the Form_Open event of the second Form:

If Len(Me.OpenArgs) > 0 Then
Me.RecordsetClone.FindFirst "[EmployeeNo] = " & Me.OpenArgs
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "The record you requested was not found; opened at first
record"
End If
End If

It's not complete with error handling, but it's not air code.





I just realized it's linking to the correct subform (via [ref #])
within the main form but not the correct form (Profit Tracking 2). If i
can get it to direct to the [ref link] field in the [Profit Tracking 2]
form then this should work correctly. can you suggest anything else?
Thanks



Hi John, the brackets worked great. not the correct form opens up but
it's not linking to the correct record. i looked into the the form that

should link and the linking field is actually called [ref link]. i
tried inserting that in both spots in the code and again the correct
form opens up but the records aren't linking. btw [ref link] is a text
field and [ref # ] is an autonumber field .


Thanks
Steve


The space+underscore at the end of the first line is the VBA line
continuation character: it causes the two lines to be treated as one.


Have you tried enclosing the field name and table name in brackets,
e.g.


...DLookup("[send to Accting and print inv]", ...


Is the name of the field in the [Full Air Quote] table actually [send
to
Accting and print inv] ? (Life is simpler without all these spaces in
names!)


Is the [Ref # ] field a number (as its name indicates) or a text field?



If the latter, you need to use apostrophes or quotes to enclose it,
e.g.


..., "[Ref # ]='" & Me.Controls("Ref # ").Value & "'")


On 14 Jan 2006 13:42:38 -0800, "(e-mail address removed)"


Reply

 

[fishqqq]

Thanks i'll try it and see

13. John Nurick
Jan 15, 2:23 pm show options

Newsgroups: microsoft.public.access.formscoding
From: John Nurick <[email protected]> - Find messages by
this author
Date: Sun, 15 Jan 2006 19:23:46 +0000
Local: Sun, Jan 15 2006 2:23 pm
Subject: Re: if checked go here...
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You haven't previously mentioned anything about "linking" or doing
anything special when you open one or other of the two Profit Tracking
forms, and I'm not sure I understand what you're trying to do.


If you want to open a form so it shows only records that meet a
specific
condition, you need to use the WhereCondition argument of
DoCmd.OpenForm. This uses the same sort of expression as the last
argument of DLookup, e.g.


DoCmd.OpenForm blah blah, _
"[ref link]='" & Me.Controls("ref link").Value & "'"


See Help on OpenForm, or search the web and/or Google groups for more.


If you want to open a form at a particular record but make it possible
to scroll to all other records, here's something Larry Linson MVP
posted
a few years ago:




- Hide quoted text -
- Show quoted text -

 

[fishqqq]

The following gives me a 'type mismatch error' and i don't know what to
do. can you suggest anything? i think i followed your advise perfectly?

Private Sub Command192_Click()
If DLookup("[send to Accting and print inv]", "[Full Air Quote]", _
"[ref link]='" & Me.Controls("[Ref #]").Value & "'") = True
Then


DoCmd.OpenForm "Profit Tracking 3", _
"[ref link]='" & Me.Controls("ref link").Value & "'"

Else
DoCmd.OpenForm "Profit Tracking 2", _
"[ref link]='" & Me.Controls("ref link").Value & "'"
End If

End Sub


--------------------------------
13. John Nurick
Jan 15, 2:23 pm show options

Newsgroups: microsoft.public.access.formscoding
From: John Nurick <[email protected]> - Find messages by
this author
Date: Sun, 15 Jan 2006 19:23:46 +0000
Local: Sun, Jan 15 2006 2:23 pm
Subject: Re: if checked go here...
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You haven't previously mentioned anything about "linking" or doing
anything special when you open one or other of the two Profit Tracking
forms, and I'm not sure I understand what you're trying to do.


If you want to open a form so it shows only records that meet a
specific
condition, you need to use the WhereCondition argument of
DoCmd.OpenForm. This uses the same sort of expression as the last
argument of DLookup, e.g.


DoCmd.OpenForm blah blah, _
"[ref link]='" & Me.Controls("ref link").Value & "'"

 

[John Nurick]

Something seems wrong with the DLookup. It says you're testing the value
of the field [ref link] against the value of the control "Ref #".

From your previous messages it has seemeed that [ref link] and [Ref # ]
are two different fields, and that the control displaying the [Ref # ]
field is called "Ref # " not "Ref #".

If you find this confusing, so do I. But you're the one who chose these
names.

The following gives me a 'type mismatch error' and i don't know what to
do. can you suggest anything? i think i followed your advise perfectly?

Private Sub Command192_Click()
If DLookup("[send to Accting and print inv]", "[Full Air Quote]", _
"[ref link]='" & Me.Controls("[Ref #]").Value & "'") = True
Then


DoCmd.OpenForm "Profit Tracking 3", _
"[ref link]='" & Me.Controls("ref link").Value & "'"

Else
DoCmd.OpenForm "Profit Tracking 2", _
"[ref link]='" & Me.Controls("ref link").Value & "'"
End If

End Sub


--------------------------------
13. John Nurick
Jan 15, 2:23 pm show options

Newsgroups: microsoft.public.access.formscoding
From: John Nurick <[email protected]> - Find messages by
this author
Date: Sun, 15 Jan 2006 19:23:46 +0000
Local: Sun, Jan 15 2006 2:23 pm
Subject: Re: if checked go here...
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You haven't previously mentioned anything about "linking" or doing
anything special when you open one or other of the two Profit Tracking
forms, and I'm not sure I understand what you're trying to do.


If you want to open a form so it shows only records that meet a
specific
condition, you need to use the WhereCondition argument of
DoCmd.OpenForm. This uses the same sort of expression as the last
argument of DLookup, e.g.


DoCmd.OpenForm blah blah, _
"[ref link]='" & Me.Controls("ref link").Value & "'"

 

[fishqqq]

I see what you mean now about the names. unfortunately i didn't have
this foresight when i began putting this thing together...

i hope you will bear with me as it seems where 90% there...

in the form [General Quote] i have a command button [command192]
when the user pushes the button the application is supposed to open one
of two forms.

open form [profit tracking 2] and go to the record that matches the
same referance as [General Quote]. The matching fields are [General
Quote].[Ref #] and [profit tracking 2]. [ref link]
It's only supposed to open form [profit tracking 2] if the checkbox in
a subform in [profit tracking 2] has NOT been checked. the subform is
called [inv status] and the checkbox in that subform is called [send to
Accting and print inv]. (again sorry for the long names).

now your approach was to do this checkbox 'check' via the table. this
is confusing to me as i don't know how you tell the code to go to the
table [Full Air Quote] then go to the same record as [General
Quote].[Ref #] and then see if the [send to Accting and print inv]
checkbox has been checked.

Again, if the checkbox IS checked then the form to go to is [Profit
Tracking 3] linked to the correct record by the [ref link] field in
that form (which links to [General Quote].[Ref #].

I hope you can follow my logic here...
if not please let me know what you don't get and i'll try to explain
better.

I appreciate your time on this..
Steve


Something seems wrong with the DLookup. It says you're testing the
value
of the field [ref link] against the value of the control "Ref #".

From your previous messages it has seemeed that [ref link] and [Ref # ]

are two different fields, and that the control displaying the [Ref # ]
field is called "Ref # " not "Ref #".


If you find this confusing, so do I. But you're the one who chose these

names.

 

[John Nurick]

This is not the problem you originally presented. At first you said

1) the user hits the command button to open FORM A
2) the program actually opens FORM 1 and looks to see if checkbox 1 is
yes or no
3) if yes then the program opens FORM A, if no the programs opens FORM
B

but your latest description (below) says in effect

1) the user hits the button command192 on form "General Quote".

2) this is supposed to open one of two forms "profit tracking 2" and
"profit tracking 3"

3) if the checkbox "send to Accting and print inv" on the "inv status"
subform on "profit tracking 2" would be checked if "profit tracking 2"
form were opened at the record in its recordset that matches the current
record in the "General Quote" form, open "profit tracking 3". Otherwise,
open "profit tracking 2".

One of the things you haven't revealed is how the records displayed by
the "inv status" subform are related to the records displayed by its
parent form "profit tracking 2". Subforms are normally used to display
1:M relationships - in which case one record in "profit tracking 2"'s
recordset can have many related records in the subform, and presumably
the value of the "send to Accting and print inv" checkbox will depend on
which of these records you look at.

The general idea remains the same: use DLookup to get the value of the
field corresponding to the checkbox. But if - as I presume - there are
related tables underlying the form and subform, you'll need to start by
creating a and saving query that joins these tables and returns two
fields:

1 [ref link] from the table underlying [profit tracking 2].
2 for each value of [ref link], the corresponding value of the
field in the table underlying [inv status] to which the checkbox
"send to Accting and print inv" is bound.

Then use DLookup to get the value of the checkbox from the query, using
something like this as the WhereCondition.
"[ref link] = " & Forms![General Quote]![Ref #]
or if these are text fields
"[ref link] = '" & Forms![General Quote]![Ref #] & "'"

Or maybe the field name should be [Ref # ], which is what you've mostly
said it is.

I see what you mean now about the names. unfortunately i didn't have
this foresight when i began putting this thing together...

i hope you will bear with me as it seems where 90% there...

in the form [General Quote] i have a command button [command192]
when the user pushes the button the application is supposed to open one
of two forms.

open form [profit tracking 2] and go to the record that matches the
same referance as [General Quote]. The matching fields are [General
Quote].[Ref #] and [profit tracking 2]. [ref link]
It's only supposed to open form [profit tracking 2] if the checkbox in
a subform in [profit tracking 2] has NOT been checked. the subform is
called [inv status] and the checkbox in that subform is called [send to
Accting and print inv]. (again sorry for the long names).

now your approach was to do this checkbox 'check' via the table. this
is confusing to me as i don't know how you tell the code to go to the
table [Full Air Quote] then go to the same record as [General
Quote].[Ref #] and then see if the [send to Accting and print inv]
checkbox has been checked.

Again, if the checkbox IS checked then the form to go to is [Profit
Tracking 3] linked to the correct record by the [ref link] field in
that form (which links to [General Quote].[Ref #].

I hope you can follow my logic here...
if not please let me know what you don't get and i'll try to explain
better.

I appreciate your time on this..
Steve


Something seems wrong with the DLookup. It says you're testing the
value
of the field [ref link] against the value of the control "Ref #".

From your previous messages it has seemeed that [ref link] and [Ref # ]

are two different fields, and that the control displaying the [Ref # ]
field is called "Ref # " not "Ref #".


If you find this confusing, so do I. But you're the one who chose these

names.

 

[fishqqq]

wow...this got confusing again...(and more complicated then i
suspected). the subform that has the "send to Accting and print inv"
field only has 1 record there is a 1:1 relationship there. (i don't
know if this matters but there is another subform which does have a 1:M
relationship but nothing on this subform pertains to this problem).

I'll try what you suggested.
Tks again for walking me through this.
Steve



This is not the problem you originally presented. At first you said

1) the user hits the command button to open FORM A
2) the program actually opens FORM 1 and looks to see if checkbox 1 is
yes or no
3) if yes then the program opens FORM A, if no the programs opens FORM
B

but your latest description (below) says in effect

1) the user hits the button command192 on form "General Quote".


2) this is supposed to open one of two forms "profit tracking 2" and
"profit tracking 3"


3) if the checkbox "send to Accting and print inv" on the "inv status"
subform on "profit tracking 2" would be checked if "profit tracking 2"
form were opened at the record in its recordset that matches the
current
record in the "General Quote" form, open "profit tracking 3".
Otherwise,
open "profit tracking 2".


One of the things you haven't revealed is how the records displayed by
the "inv status" subform are related to the records displayed by its
parent form "profit tracking 2". Subforms are normally used to display
1:M relationships - in which case one record in "profit tracking 2"'s
recordset can have many related records in the subform, and presumably
the value of the "send to Accting and print inv" checkbox will depend
on
which of these records you look at.


The general idea remains the same: use DLookup to get the value of the
field corresponding to the checkbox. But if - as I presume - there are
related tables underlying the form and subform, you'll need to start by

creating a and saving query that joins these tables and returns two
fields:


1 [ref link] from the table underlying [profit tracking 2].
2 for each value of [ref link], the corresponding value of the
field in the table underlying [inv status] to which the checkbox
"send to Accting and print inv" is bound.


Then use DLookup to get the value of the checkbox from the query, using

something like this as the WhereCondition.
"[ref link] = " & Forms![General Quote]![Ref #]
or if these are text fields
"[ref link] = '" & Forms![General Quote]![Ref #] & "'"


Or maybe the field name should be [Ref # ], which is what you've mostly

said it is.